SQL Syntax: Select Results for each date between a range of dates

Question

Hi,

I have the following SQL (SQL Server 2005) that I use to calculate the total times for each column:

DECLARE @Param1 DATETIME
DECLARE @Param2 DATETIME
DECLARE @Param3 DATETIME

SET @Param1 = '2009-01-01'
SET @Param2 = '2009-09-09'

SELECT  SUM(IdleSec) AS TotalIdleSec, 
     SUM(ProductionSec) AS TotalProductionSec, 
     SUM(UplineSec) AS TotalUplineSec, 
     SUM(DownlineSec) AS TotalDownlineSec, 
     SUM(UserSec) AS TotalUserSec
FROM            Job
WHERE   (DateTime >= dbo.FormatDateTime(@Param1, 'yyyy-mm-dd')) 
     AND 
     (DateTime < dbo.FormatDateTime(@Param2, 'yyyy-mm-dd'))

GO

This returns a table with 1 row for the items above and works great.

I am a little uncertain how I can return the table with the SUM values per day/week/month i.e.

Sum of all values for each DAY between the range of dates.

Sum of all values for each WEEK between the range of dates.

Sum of all values for each MONTH between the range of dates.

I am sure there is a simple way to do this but uncertain myself. I have seen some tutorials that use the DAY(date) command but I tried and cannot seem to get what I need.

I look forward to your excellent help.

Thanks in advanced.

Paul

Answer 1

Look at the DATEDIFF command , this can give you the number days/weeks/months/years etc between two dates.

Answer 2

You will need a table with the days between

DECLARE @StartDate DATETIME,
     @EndDate DATETIME

SELECT  @StartDate = '01 Sep 2009',
     @EndDate = '09 Sep 2009'

DECLARE @RunDate DATETIME
SELECT  @RunDate = @StartDate

DECLARE @DayTable TABLE(
     DayDate DATETIME
)

WHILE @RunDate <= @EndDate 
BEGIN
    INSERT INTO @DayTable (DayDate) SELECT @RunDate
    SET @RunDate = @RunDate + 1
END

SELECT * FROM @DayTable

Then left join your lookup table to this table, and group by the date from your date table.

This should give you the daily values,

Then you need to do the same for the weekly value. This will give you all days between, even if no stats exist for that date

Answer 3

You add a GROUP BY clause to your query:

by MONTH:

SELECT  SUM(IdleSec) AS TotalIdleSec, 
        SUM(ProductionSec) AS TotalProductionSec, 
        SUM(UplineSec) AS TotalUplineSec, 
        SUM(DownlineSec) AS TotalDownlineSec, 
        SUM(UserSec) AS TotalUserSec,
        DATEPART(year, DateTime) as Year,
        DATEPART(month, DateTime) as Month
FROM            Job
WHERE   (DateTime >= dbo.FormatDateTime(@Param1, 'yyyy-mm-dd')) 
        AND 
        (DateTime < dbo.FormatDateTime(@Param2, 'yyyy-mm-dd'))
GROUP BY DATEPART(year, DateTime),
        DATEPART(month, DateTime);

by WEEK:

SELECT  SUM(IdleSec) AS TotalIdleSec, 
        SUM(ProductionSec) AS TotalProductionSec, 
        SUM(UplineSec) AS TotalUplineSec, 
        SUM(DownlineSec) AS TotalDownlineSec, 
        SUM(UserSec) AS TotalUserSec,
        DATEPART(year, DateTime) as Year,
        DATEPART(week, DateTime) as Week
FROM            Job
WHERE   (DateTime >= dbo.FormatDateTime(@Param1, 'yyyy-mm-dd')) 
        AND 
        (DateTime < dbo.FormatDateTime(@Param2, 'yyyy-mm-dd'))
GROUP BY DATEPART(year, DateTime),
        DATEPART(week, DateTime);

by DAY:

 SELECT  SUM(IdleSec) AS TotalIdleSec, 
        SUM(ProductionSec) AS TotalProductionSec, 
        SUM(UplineSec) AS TotalUplineSec, 
        SUM(DownlineSec) AS TotalDownlineSec, 
        SUM(UserSec) AS TotalUserSec,
        DATEPART(year, DateTime) as Year,
        DATEPART(dayofyar, DateTime) as Day
FROM            Job
WHERE   (DateTime >= dbo.FormatDateTime(@Param1, 'yyyy-mm-dd')) 
        AND 
        (DateTime < dbo.FormatDateTime(@Param2, 'yyyy-mm-dd'))
GROUP BY DATEPART(year, DateTime),
        DATEPART(dayofyear, DateTime);

For DAY there are other options like using CAST to extract the day part or the FormatDateTime function you seem to have. I used DATEPART for consistency.