如何获得sql中每个日期的特定值的总数
如何获得sql中每个日期的特定值的总数
提问于 2013-07-28 08:09:35
我有一个这样的存储过程：
ALTER PROCEDURE [dbo].[Driverperformance]
@Ecode NVARCHAR(50),
@startdate DATE,
@enddate DATE
AS BEGIN
SELECT
e.Ecode, q.dtime, e.Ename,
count(q.Ecode) CntEcode,
count(DelEcode) CntDelEcode,
CAST((count(q.Ecode) + count(DelEcode)) as float)/2 as Avrgpoint
FROM
EmployeeMaster_tbl e
INNER JOIN
Transaction_tbl q ON e.Ecode = q.Ecode
WHERE
q.Ecode = @Ecode
AND dtime BETWEEN @startdate AND @enddate
GROUP BY
e.Ecode, e.Ename, q.dtime
ORDER BY
e.Ecode DESC
END在执行以下操作时: out变得如下所示：
Ecode dtime Ename CntEcode CntDelEcode Avrgpoint
E001 2013-05-25 12:55:23.000 Deepu M. Thomas 1 0 0.5
E001 2013-05-25 13:53:16.000 Deepu M. Thomas 1 0 0.5
E001 2013-05-27 03:24:59.000 Deepu M. Thomas 1 1 0.5
E001 2013-05-27 03:24:59.000 Deepu M. Thomas 1 0 0.5
E001 2013-05-28 03:24:59.000 Deepu M. Thomas 1 0 0.5我希望得到输出总数为(CntEcode，CntDelEcode )的每一个date.expected输出如下：
Ecode dtime Ename CntEcode CntDelEcode
E001 2013-05-25 Deepu M. Thomas 2 0
E001 2013-05-27 Deepu M. Thomas 2 1
E001 2013-05-28 Deepu M. Thomas 1 0 回答 1
Stack Overflow用户
回答已采纳
发布于 2013-07-28 08:46:52
如果只想查看dtime的日期部分，请使用CAST(dtime AS DATE)获取日期：
ALTER PROCEDURE [dbo].[Driverperformance]
@Ecode NVARCHAR(50),
@startdate DATE,
@enddate DATE
AS BEGIN
SELECT
e.Ecode,
CAST(q.dtime AS DATE), -- <== CAST to DATE
e.Ename,
count(q.Ecode) CntEcode,
count(DelEcode) CntDelEcode,
CAST((count(q.Ecode) + count(DelEcode)) as float)/2 as Avrgpoint
FROM
EmployeeMaster_tbl e
INNER JOIN
Transaction_tbl q ON e.Ecode = q.Ecode
WHERE
q.Ecode = @Ecode
AND dtime BETWEEN @startdate AND @enddate
GROUP BY
e.Ecode, e.Ename,
CAST(q.dtime AS DATE) -- <== CAST to DATE here, too!
ORDER BY
e.Ecode DESC
END页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/17906275
复制相关文章
相似问题
腾讯云开发者
