Totals

SQL Apprentice Question
I have a table that is populated everyday with daily totals, I'm
trying to teach myself SQL using this table.


Someone kindly gave me a query that gave the weekly totals based on
the table:
SELECT week_start, week_start + 6 AS week_end, SUM(Total3) AS
production
FROM (SELECT Date - DATEPART(weekday, Date + @@DATEFIRST -
1) + 1 AS week_start, Total3
FROM dbo.A_Totals) AS d
GROUP BY week_start
ORDER BY week_start


How can I adapt this to divide the weekly totals into years so I'd
have: week number, year, year-1,year-2 etc.


Thanks in advance


Celko Answers

>> I have a table that is populated everyday with daily totals, I'm trying to teach myself SQL using this table. <<


Bad idea; get a good book and a tutorial, then work on a problem.

Your approach to SQL is typical of someone coming in via procedural
code and looking for functions to solve it. SQL is a data retrieval
language that likes to use sets (tables). This is a WHOLE DIFFERENT
PROGRAMMING PARADIGM. After you have a few basics, then look up the
use of calendars -- temporal stuff is one of the hardest parts of
RDBMS, so it is a horrible starting point for a self-education. Try
something like this:


CREATE TABLE ReportPeriods
(period_name VARCHAR(20) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date));


Create and load a table of reporting periods of whatever range you
want. They can overlap, have gaps, etc. They can be crazy, one shot
things, etc. If you are a Don Martin fan:


INSERT INTO ReportPeriods
VALUES ('National Gorilla Suit Week', 2007-05-21', 2007-05-27');
etc.


You also have to learn to design data which should take about a year,
if you really work at it. So if your weeks are encoding the ISO-8601
year-week number format, then you can simply write:


SELECT period_name, SUM(some_col) AS week_total
FROM DailyReports AS D, ReportPeriods AS R
WHERE D.report_date BETWEEN start_date AND end_date
AND period_name LIKE 'WEEK-2007__;


Change the wild cards as needed.