Help with complex query pls 1

[fredp1]

Hi,

I could think of a appropriate Subject, but I need help puting a query togther. It can also be a stored proc if thats the way to go.

Table1 - Master data
OrderNo char(10), CostingDate datetime
1, 2004-04-01
1, 2004-06-01
2, 2004-04-02
2, 2004-05-02
2, 2004-06-02
3, 2004-06-03

Table2 - Transaction data
TranDate, OrderNo, Qty
2004-04-01, 1, 1
2004-05-01, 1, 1
2004-06-01, 1, 1
2004-04-02, 2, 1
2004-05-02, 2, 1
2004-06-02, 2, 1
2004-04-03, 3, 1

Results need...
OrderNo, CostingDate, FirstTrandate, LastTranDate
1, 2004-04-01, 2004-04-01, 2004-04-01
1, 2004-06-01, 2004-05-01, 2004-06-01
2, 2004-04-02, 2004-04-02, 2004-04-02
2, 2004-05-02, 2004-05-02, 2004-05-02
2, 2004-06-02, 2004-06-02, 2004-06-02
3, 2004-06-03, 2004-04-03, 2004-06-03

Rules.
OrderNo and CostingDate are from Table1
LastTranDate is a alias for CostingDate from Table1
So far this is simple.

The tricky bit i'm struglling with is the FirstTranDate
Basically is trying to capture the date range between costings. e.g.
order 3, costing date 2004-04-01 needs to look at transactions between the 2004-04-01 and 2004-04-1
order 3, costing date 2004-06-01(the second costing) needs to look at transactions after tyhe first costing, between the 2004-05-01 and 2004-06-1

Its always <= CostingDate
If a OrderNo has a count of 1 record in Table 1, then FirstTranDate min(TranDate) e.g. Order3
if a OrderNo has multiple 'costingdates', like OrderNo 1,
then the frist record has a FirstTranDate=min(TranDate)
The next record has a FirstTranDate is the next occurance of Trandate
Note Order 1, costingdate 2004-06-01 and a FirstTranDate of 2004-05-01

Is this clear enough?

Thanks in advance

 

[indrahig]

I give you this select query, from this you can create derived table or temporary table, anyway you want..
nothing fancy, but it works

select A.OrderNo, A.CostingDate,
  case when B.LastCostingDate is null then
    (select min(Table2.TranDate) from Table2
    where Table2.OrderNo = A.OrderNo 
      and Table2.TranDate <= A.CostingDate)
  else
    (select min(Table2.TranDate) from Table2
    where Table2.OrderNo = A.OrderNo 
      and Table2.TranDate > B.LastCostingDate)
  end as FirstTranDate,
  A.CostingDate as LastTranDate
from Table1 as A
  inner join (
    select A.OrderNo, A.CostingDate,
      max(B.CostingDate) as LastCostingDate
    from Table1 as A
      left outer join Table1 as B on 
          A.OrderNo = B.OrderNo 
        and B.CostingDate < A.CostingDate
    group by A.OrderNo, A.CostingDate
    ) as B on A.OrderNo = B.OrderNo
      and A.CostingDate = B.CostingDate

 

[fredp1]

Thanks indrahig!
That worked just great!

Thanks again.

Fred

 

[indrahig]

Glad can help..