How to sum data week wise in SQL 2000

Question

Dear All

How to sum data week wise in MS-SQL 2000

I have a Table "Reports" with the following columns.

TotalSubmissions, ZoneID, RptMonth, RptDay, RptYear
2,1,6,1,2010
1,1,6,2,2010
1,1,6,3,2010
1,2,6,1,2010
1,2,6,2,2010
2,2,6,3,2010
1,2,6,4,2010
1,4,6,1,2010
1,4,6,3,2010
1,4,6,4,2010

I want to make a report week wise for a specific zone from DateRange1 to DateRange2

Example: I need Sum of Submissions week wise for zone2 from 06/01/2010 to 06/24/2010.

Please help me to achieve the above task.

Regards,

Mehboob Khan Afridi

Answer 1

I think this will do what you want.

I've included some sample data for testing.

Create table #t
(
TotalSubmissions int,
ZoneId int,
RptMonth varchar(2),
RptDay varchar(2),
RptYear varchar(4)
)

Insert Into #t
Values (2,1,6,1,2010)
Insert Into #t
Values (1,1,6,2,2010)
Insert Into #t
Values (1,1,6,3,2010)
Insert Into #t
Values (1,2,6,1,2010)
Insert Into #t
Values (1,2,6,2,2010)
Insert Into #t
Values (2,2,6,3,2010)
Insert Into #t
Values (1,2,6,4,2010)
Insert Into #t
Values (1,4,6,1,2010)
Insert Into #t
Values (1,4,6,3,2010)
Insert Into #t
Values (1,4,6,4,2010)

declare @Date1 datetime,
        @Date2 datetime

Set @Date1 = '2010-06-01'
Set @Date2 = '2010-06-24'

Select  Sum(TotalSubmissions) as 'TotalSubmissions',
        ZoneId,
        DatePart(week, Cast((RptYear + '-' + RptMonth + '-' + RptDay) as datetime)) as 'WeekNumber',
        DatePart(year, Cast((RptYear + '-' + RptMonth + '-' + RptDay) as datetime)) as 'Year'

From #t
Where Cast((RptYear + '-' + RptMonth + '-' + RptDay) as datetime) >= @Date1
And Cast((RptYear + '-' + RptMonth + '-' + RptDay) as datetime) <= @Date2
Group By datepart(year,Cast((RptYear + '-' + RptMonth + '-' + RptDay) as datetime)),
        ZoneID, DatePart(week, Cast((RptYear + '-' + RptMonth + '-' + RptDay) as datetime))

drop table #t

Answer 2

Example: I need Sum of Submissions week wise for zone2 from 06/01/2010 to 06/24/2010.

This T-SQL should do it:
(I kept the date cast in one spot because 'yyyy-mm-dd' is not a language-independent date format.)

DECLARE
    @StartDate  DATETIME,
    @EndDate    DATETIME,
    @TargetZone INT

SET @StartDate  = '2010-06-01'
SET @EndDate    = '2010-06-24'
SET @TargetZone = 2


SELECT
    SUM (dwd.TotalSubmissions)      AS TotalSubmissions,
    dwd.ZoneID,
    DATEPART (week, dwd.ReportDate) AS WeekOfTheYear
FROM
    (
        SELECT
            r.TotalSubmissions,
            r.ZoneID,
            CAST ( (CAST (r.RptYear AS varchar(4)) + '-' + CAST (r.RptMonth AS varchar(2)) + '-' + CAST (r.RptDay AS varchar(2))) AS DATETIME)  AS ReportDate
        FROM
            Reports r
        WHERE
            r.ZoneID    = @TargetZone
    )
    AS dwd
WHERE
    dwd.ReportDate      >= @StartDate
AND
    dwd.ReportDate      <= @EndDate
GROUP BY
    dwd.ZoneID,
    DATEPART (year, dwd.ReportDate),
    DATEPART (week, dwd.ReportDate)

Sample Data:

CREATE TABLE Reports
(
    TotalSubmissions    INT,
    ZoneID              INT,
    RptMonth            INT,
    RptDay              INT,
    RptYear             INT
)

INSERT INTO
    Reports (TotalSubmissions, ZoneID, RptMonth, RptDay, RptYear)
SELECT
    2, 1, 6, 1, 2010      UNION ALL SELECT
    1, 1, 6, 2, 2010      UNION ALL SELECT
    1, 1, 6, 3, 2010      UNION ALL SELECT
    1, 2, 6, 1, 2010      UNION ALL SELECT
    1, 2, 6, 2, 2010      UNION ALL SELECT
    2, 2, 6, 3, 2010      UNION ALL SELECT
    1, 2, 6, 4, 2010      UNION ALL SELECT
    1, 4, 6, 1, 2010      UNION ALL SELECT
    1, 4, 6, 3, 2010      UNION ALL SELECT
    1, 4, 6, 4, 2010    


INSERT INTO Reports (TotalSubmissions, ZoneID, RptMonth, RptDay,    RptYear)
SELECT               TotalSubmissions, ZoneID, RptMonth, RptDay+10, RptYear
FROM        Reports

INSERT INTO Reports (TotalSubmissions, ZoneID, RptMonth, RptDay,    RptYear)
SELECT               TotalSubmissions, ZoneID, RptMonth, RptDay+20, RptYear
FROM        Reports

DELETE FROM Reports
WHERE       RptDay > 30

Answer 3

Which day of the week is the start of 'your' week?

Depending on the login's regional settings as well as the session option DATEFIRST, the result of your query can be different each time.

Here's a solution based on the login's definition of the first day of the week.

SELECT SUM(TotalSubmissions) AS TotalSubmissions,
       ZoneId, 
       DATEADD(DAY, -DATEPART(WEEKDAY, Cast(RptYear + '-' + RptMonth + '-' + RptDay AS DATETIME)) + 1,
        Cast(RptYear + '-' + RptMonth + '-' + RptDay AS DATETIME)) AS WeekBeginning

FROM Reorts

WHERE CAST(RptYear + '-' + RptMonth + '-' + RptDay AS DATETIME) >= '2010-06-01'
  AND CAST(RptYear + '-' + RptMonth + '-' + RptDay AS DATETIME) <= '2010-06-24'

GROUP BY ZoneId,
  DATEADD(DAY, -DATEPART(WEEKDAY, Cast(RptYear + '-' + RptMonth + '-' + RptDay AS DATETIME)) + 1,
    Cast(RptYear + '-' + RptMonth + '-' + RptDay AS DATETIME))