SQL Syntax: Select the 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.

  • 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.

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.

Hello, buddy!责编内容来自:Hello, buddy! (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » SQL Syntax&colon; Select the results for each date between a range of dates

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录