CREATE TABLE MeterProduction (proddate DATETIME PRIMARY KEY, metercount
INTEGER NOT NULL)
INSERT INTO MeterProduction VALUES ('20030102',1000)
INSERT INTO MeterProduction VALUES ('20030103',500)
INSERT INTO MeterProduction VALUES ('20030110',999)
INSERT INTO MeterProduction VALUES ('20030117',1234)
SELECT CEILING(DATEDIFF(DAY,'20030101',proddate)/7.0) AS week_number,
MIN(proddate) AS from_date,
MAX(proddate) AS to_date,
SUM(metercount) AS weekly_total,
FROM MeterProduction AS M2
BETWEEN '20030101' AND MAX(M1.proddate))
FROM MeterProduction AS M1
GROUP BY CEILING(DATEDIFF(DAY,'20030101',proddate)/7.0)
ORDER BY from_date
You can set the base date for the weeks (2003-01-01 in this example) to
whatever you require.
Please reply only to the newsgroup
"Jim" <jim.holmesdevro-casings.com> wrote in message
news:68dfae14.0307100558.577ba86eposting.google.c om...> Hi,
> I'm thinking this should be easy but I can't figure out a way to do
> this without using DTS, SPROC or trigger. I have a table that contains
> 365 records each one with an amount of meters produced for that day (a
> record for each day of the year).
> I would like to create a view that displays each record with a rolling
> total to date. i.e week 1 (record 1) might have 1000 meters, week 2
> (record 2) might be 1500 meters so the rolling total would be 2500
> meters, week 3 (record 3) might be 1250 meters so the rolling total
> for record 3 would be 3750.
> Any help would be greatly appreciated.