按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);
通过周:
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);
到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);
对于DAY,还有其他选项,例如使用CAST提取日期部分或您似乎拥有的FormatDateTime函数。我使用DATEPART来保持一致性。