SQL Help - loop through dates
Posted on 2013-06-11
Trying to create a query which will retrieve information and store in a temp table so I can report on the values. The report needs to show the information by each Friday. I am creating a temp table which adds each Friday by date
reportdate source item_no quantity trx_dt promise_dt
2013-06-14 00:00:00.000 NULL NULL NULL NULL NULL
2013-06-21 00:00:00.000 NULL NULL NULL NULL NULL
2013-06-28 00:00:00.000 NULL NULL NULL NULL NULL
However, my other data doesn't fall into each Friday order.
source item_no quantity doc_type trx_dt
P ITK2236 60.0000 O 2013-06-14 00:00:00.000
O ITK2236 14.0000 A 2013-06-14 00:00:00.000
P ITK2236 60.0000 O 2013-06-17 00:00:00.000
P ITK2236 60.0000 O 2013-06-19 00:00:00.000
O ITK2236 15.0000 A 2013-06-21 00:00:00.000
O ITK2236 27.0000 A 2013-07-26 00:00:00.000
How can I loop, cursor, whatever through these records and sum the values of the quantity by the trx_dt by the appropriate Friday. This will only run by 1 item_no at a time, but there could be muliple records per trx_dt.
Any help would be appreciated?