Best way to write this query

[blueparaiba]

GID Node Ref Item Date Qty
0 32320 6 0 2008-11-01 00:00:00.000 8
0 32320 6 1 2009-03-01 00:00:00.000 10
0 32320 6 2 2009-05-01 00:00:00.000 6
0 32320 6 3 2009-12-01 00:00:00.000 6
0 32320 6 4 2010-06-01 00:00:00.000 6
0 32320 6 5 2010-07-01 00:00:00.000 12

0 32320 23 0 2008-11-01 00:00:00.000 17.38
0 32320 23 1 2009-03-01 00:00:00.000 17.38
0 32320 23 2 2009-05-01 00:00:00.000 17.38
0 32320 23 3 2009-12-01 00:00:00.000 17.38
0 32320 23 4 2010-06-01 00:00:00.000 20.45
0 32320 23 5 2010-07-01 00:00:00.000 20.45


Results
I am trying to get the maximum(Date) from the reference 6 to calculate the quantity against the date in reference 23 where the (maximum(Date) from reference 6) is <= Date in Reference 23, and then the cycle repeats again taking the next maximum date in reference 6. For example:

The maximum date in reference 6 is:
GID Node Ref Item Date Quantity
0 32320 6 5 2010-07-01 00:00:00.000 12
This date will then compare it in reference 23:
0 32320 23 5 2010-07-01 00:00:00.000 20.45

The date should be either equal or close to the maximum date in reference 6. Then the two quantity can be multiple together, giving a forecasted quantity 12*20.45=245.40 and then the cycle repeats again. How can I accomplish this take?

 

[blueparaiba]

I have tried this query, but I am returning 24 rows and not 6 rows. Can you suggest a improvement, please.

SELECT t.NodeType
, t.Reference
, t.GID
, t.Date
, t.Quantity
, m.Price1
, t.Quantity*m.Price1
FROM ( SELECT NodeType
, Reference
, GID
, MAX(Date) AS latest
, Quantity As Price1
FROM NodeData0
Where Reference=6
AND NodeType = 0
AND GID = 32320
GROUP
BY NodeType,Reference,GID, Quantity ) AS m
INNER
JOIN NodeData0 AS t
ON t.NodeType = m.NodeType
AND t.GID = m.GID
AND t.NodeType = 0
AND t.Reference = 23

 

[bobchuckjim]

I have found it quite difficult with this question and the other one you have posted to
glean exactly what the expected output should be, for example what happens if the max date
in ref 6 is not <= max date in ref 23, do we return 5 rows or just no value for the quantity
for the ref 23 row ? In this case we would have to add a case statement or similar
to the "ref_6.Quantity*ref_23.Quantity as Result" line to format the quantity.

This is my first attempt.

;with CTE as (
select
Reference,
Date,
Quantity,
row_number() over (partition by Reference order by Date desc) as row_num
from NodeData0
where GID = 0
and NodeType = 32320
)
select
ref_23.Date as Ref23_Date,
ref_6.Date as Ref6_Date,
ref_6.Quantity*ref_23.Quantity as Result
from CTE ref_6
left join CTE ref_23
on ref_23.row_num = ref_6.row_num
where ref_6.Reference = 6
and ref_23.Reference = 23
and ref_6.Date <= ref_23.Date

yields

2010-07-01 00:00:00.000 2010-07-01 00:00:00.000 245.4000
2010-06-01 00:00:00.000 2010-06-01 00:00:00.000 122.7000
2009-12-01 00:00:00.000 2009-12-01 00:00:00.000 104.2800
2009-05-01 00:00:00.000 2009-05-01 00:00:00.000 104.2800
2009-03-01 00:00:00.000 2009-03-01 00:00:00.000 173.8000
2008-11-01 00:00:00.000 2008-11-01 00:00:00.000 139.0400

I have removed all columns that are not relevant to the output
i.e where they have been hard-coded as part of the select.

This won't be the solution but it might help move things along

good luck