October 26, 2004 at 5:29 pm
How can I take the following CODE and make it loop to get 52 weeks of sales history ?
=========================
SELECT COUNT(orderno) AS NumberofTotalOrders, SUM(shipitemcount) AS
NumberofTotalPieces
FROM [order-header]
WHERE (entrydate >= '20010101') AND (entrydate <= '+7')
LOOP
=========================
End Result
Start End # of Orders # of Pieces
02/01/04 02/07/04 1,555 406
02/08/04 02/14/04 1,233 434
02/15/04 02/21/04 1,253 436
02/22/04 02/28/04 1,546 442
October 26, 2004 at 6:27 pm
Maybe something like this will help - calculate week number and then group on that - no looping required.
SELECT
(datediff(d,'2001-01-01',entrydate) / 7 + 1) as week
COUNT(orderno) AS NumberofTotalOrders,
SUM(shipitemcount) AS NumberofTotalPieces
FROM [order-header]
WHERE (entrydate >= '20010101') AND (entrydate <= '2001-31-12')
group by (datediff(d,'2001-01-01',entrydate) / 7 + 1)
order by 1 --saves writing out the entire datediff expression again!
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply