quote:

---

Originally posted by Twi78

Hi, I need some help creating a query in SQL.

I have a table with the following fields:
Event history (primary key numerical value created in process order per part number)
Part_Number
Process
Date_Time
Weight

Each part number can have multiple processes.

I want to be able to create a stock movements table in just one SQL query if possible, whereby I select a start and end date, lets say 01/02/2012 06:00 to 01/03/2012 06:00, and the query would return the following:

Part_Number
Process
Opening_Stock_Weight (derived as the last process prior to 01/02/2012 06:00)
Process_Weight (derived as the weight associated with each process occuring between 01/02/2012 06:00 and 01/03/2012 06:00)
Closing_Stock_Weight (derived as the last process prior to 01/03/2012 06:00)

Thanks
Garry

---

SELECT Part_number,Process,PrevWeight AS Opening_Stock_Weight,
SUM(Weight) AS Process_Weight,
MAX(CASE WHEN Rn =1 THEN Weight END) AS Closing_Stock_Weight
FROM
(
SELECT t1.*,t2.PrevWeight,ROW_NUMBER() OVER (PARTITION BY t1.Part_number,t1.Process ORDER BY t1.Date_Time DESC) AS Rn
FROM table t1
OUTER APPLY (SELECT TOP 1 Weight AS PrevWeight
             FROM table
             WHERE Part_number = t1.Part_Number
             AND Date_Time < t1.Date_Time
             ORDER BY Date_Time DESC)t2
)t
GROUP BY Part_number,Process,PrevWeight