Board index » delphi » Small but complicated query

Small but complicated query

I have a unique type of query that I just cant seem to figure out.  We dev
software for petroleum industry and have a three tables Contracts,
ContractPricing, and Loads.  Contracts is a master table containing contract
infomation.  ContractPricing is contains two fields (Date and Price) as it
represents periodic dated price changes.  Finally, the Loads table is a
large table of loads hauled with every load having a valid DeliveryContract
field which links to Contracts.

So, the Contract table may have a contract number of 1001 and the
ContractPricing table may contain the following data:

ContractNo    Date                    Price Per Gallon
1001                05/01/1998        .25
1001                06/01/1998        .255
1001                07/01/1998        .265

And any number of loads records have been entered into Loads table which
have delivery dates within these ranges.

My problem is that I need to display a grid reflecting the date, price, and
a count of loads delivered to contract 1001 within the date range.  In other
words, I need the grid to look as follows:

ContractNo    Date                    Price Per Gallon        LoadsCount
LoadsGallons
1001                05/01/1998        .25                                10
95,000
1001                06/01/1998        .255                                5
25,000
1001                07/01/1998        .265                                7
55,000

I can't seem to put this in the form of an sql statement.  What gives me
problems is that the count of loads must be where DeliveryDate >= one
ContractPricing records date but < the next ContractPricing records date.

If anyone can point me in the right direction, I would be unbelievably
appreciative.

Thanks in advance

 

Re:Small but complicated query


This is pretty interesting. I did spent some time on this.  I could not exactly get your query. But this is where I reached. (May be I'll to try get the exact query later if time permits!)
(I have tried this on MS SQL Server 7)

Fol. are the 3 sample tables I created for this purpose.

CREATE TABLE CONTRACTS (
  CONTRACT_NO int,
  CONTRACT_DESC varchar(50))

CREATE TABLE CONTRACT_PRICING (
  CONTRACT_NO int,
  REF_DATE datetime,
  PRICE money )

CREATE TABLE LOADS (
  CONTRACT_NO int,
  DELIVERY_DATE datetime,
  LOAD_COUNT int,
  LOAD_GALLONS int )

And the query is

Select P.CONTRACT_NO, P.REF_DATE, P.PRICE, Sum(L.LOAD_COUNT) LOAD_COUNT, SUM(L.LOAD_GALLONS)LOAD_GALLONS
from CONTRACT_PRICING P, LOADS L
where (P.CONTRACT_NO = L.CONTRACT_NO) and
(L.DELIVERY_DATE >= P.REF_DATE)and
(L.DELIVERY_DATE <  (Select MIN(P1.REF_DATE) from CONTRACT_PRICING P1 where P1.REF_DATE > P.REF_DATE))
Group by P.CONTRACT_NO, P.REF_DATE, P.PRICE
order by P.CONTRACT_NO

This will not give you the exact result set you wanted. But I just thought it might be good starting point.

cheers

RathnaRaj T.J.
iCode Software Pvt.Ltd.
mailto: rathna...@icodeindia.soft.net

Quote
Bob Tucker wrote in message <79skqf$d...@forums.borland.com>...
>I have a unique type of query that I just cant seem to figure out.  We dev
>software for petroleum industry and have a three tables Contracts,
>ContractPricing, and Loads.  Contracts is a master table containing contract
>infomation.  ContractPricing is contains two fields (Date and Price) as it
>represents periodic dated price changes.  Finally, the Loads table is a
>large table of loads hauled with every load having a valid DeliveryContract
>field which links to Contracts.

>So, the Contract table may have a contract number of 1001 and the
>ContractPricing table may contain the following data:

>ContractNo    Date                    Price Per Gallon
>1001                05/01/1998        .25
>1001                06/01/1998        .255
>1001                07/01/1998        .265

>And any number of loads records have been entered into Loads table which
>have delivery dates within these ranges.

>My problem is that I need to display a grid reflecting the date, price, and
>a count of loads delivered to contract 1001 within the date range.  In other
>words, I need the grid to look as follows:

>ContractNo    Date                    Price Per Gallon        LoadsCount
>LoadsGallons
>1001                05/01/1998        .25                                10
>95,000
>1001                06/01/1998        .255                                5
>25,000
>1001                07/01/1998        .265                                7
>55,000

>I can't seem to put this in the form of an sql statement.  What gives me
>problems is that the count of loads must be where DeliveryDate >= one
>ContractPricing records date but < the next ContractPricing records date.

>If anyone can point me in the right direction, I would be unbelievably
>appreciative.

>Thanks in advance

Re:Small but complicated query


Thanks for looking at this.  I am going to work on it some more today and will let you know results.  I am determined not to loop through numerous records an write to temp file, etc.  Thanks again.
    RathnaRaj wrote in message <7a3ga3$s...@forums.borland.com>...
    This is pretty interesting. I did spent some time on this.  I could not exactly get your query. But this is where I reached. (May be I'll to try get the exact query later if time permits!)
    (I have tried this on MS SQL Server 7)

    Fol. are the 3 sample tables I created for this purpose.

    CREATE TABLE CONTRACTS (
      CONTRACT_NO int,
      CONTRACT_DESC varchar(50))

    CREATE TABLE CONTRACT_PRICING (
      CONTRACT_NO int,
      REF_DATE datetime,
      PRICE money )

    CREATE TABLE LOADS (
      CONTRACT_NO int,
      DELIVERY_DATE datetime,
      LOAD_COUNT int,
      LOAD_GALLONS int )

    And the query is

    Select P.CONTRACT_NO, P.REF_DATE, P.PRICE, Sum(L.LOAD_COUNT) LOAD_COUNT, SUM(L.LOAD_GALLONS)LOAD_GALLONS
    from CONTRACT_PRICING P, LOADS L
    where (P.CONTRACT_NO = L.CONTRACT_NO) and
    (L.DELIVERY_DATE >= P.REF_DATE)and
    (L.DELIVERY_DATE <  (Select MIN(P1.REF_DATE) from CONTRACT_PRICING P1 where P1.REF_DATE > P.REF_DATE))
    Group by P.CONTRACT_NO, P.REF_DATE, P.PRICE
    order by P.CONTRACT_NO

    This will not give you the exact result set you wanted. But I just thought it might be good starting point.

    cheers

    RathnaRaj T.J.
    iCode Software Pvt.Ltd.
    mailto: rathna...@icodeindia.soft.net

    Bob Tucker wrote in message <79skqf$d...@forums.borland.com>...
    >I have a unique type of query that I just cant seem to figure out.  We dev
    >software for petroleum industry and have a three tables Contracts,
    >ContractPricing, and Loads.  Contracts is a master table containing contract
    >infomation.  ContractPricing is contains two fields (Date and Price) as it
    >represents periodic dated price changes.  Finally, the Loads table is a
    >large table of loads hauled with every load having a valid DeliveryContract
    >field which links to Contracts.
    >
    >So, the Contract table may have a contract number of 1001 and the
    >ContractPricing table may contain the following data:
    >
    >ContractNo    Date                    Price Per Gallon
    >1001                05/01/1998        .25
    >1001                06/01/1998        .255
    >1001                07/01/1998        .265
    >
    >And any number of loads records have been entered into Loads table which
    >have delivery dates within these ranges.
    >
    >My problem is that I need to display a grid reflecting the date, price, and
    >a count of loads delivered to contract 1001 within the date range.  In other
    >words, I need the grid to look as follows:
    >
    >ContractNo    Date                    Price Per Gallon        LoadsCount
    >LoadsGallons
    >1001                05/01/1998        .25                                10
    >95,000
    >1001                06/01/1998        .255                                5
    >25,000
    >1001                07/01/1998        .265                                7
    >55,000
    >
    >I can't seem to put this in the form of an sql statement.  What gives me
    >problems is that the count of loads must be where DeliveryDate >= one
    >ContractPricing records date but < the next ContractPricing records date.
    >
    >If anyone can point me in the right direction, I would be unbelievably
    >appreciative.
    >
    >Thanks in advance
    >
    >
    >
    >

Other Threads