CODEWITHSUNDEEP
sqlsql-servert-sql
Belliez
Belliez

Reputation: 5386

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

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.

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.

Upvotes: 1

Views: 11976

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294467

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.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166606

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

Upvotes: 3

bleeeah
bleeeah

Reputation: 3604

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

Upvotes: 1

Related Questions