按日期对多列进行计数功能(SQL Server) [英] Count Function on Multiple Columns by Date (SQL Server)

问题描述

我正在使用SQL Server 2008 R2，并尝试对不同的列进行多次计数，但无法弄清楚如何使其正常工作.我有两个独立工作的查询，但是在尝试合并它们时遇到了麻烦.尝试计算在每个日期通过@StartDate和@EndDate时打开和关闭的票证数量.

I'm using SQL Server 2008 R2 and trying to do multiple counts on different columns and can't figure out how to get it to work properly. I have two queries that work independently, but having trouble when trying to combine them. Trying to count the number of tickets opened and closed during each individual date while passing through an @StartDate and @EndDate.

例如，这是我的第一个查询:(我们分配了某些代码来区分创建票证的不同原因，只是想将其丢掉，以防行造成任何混乱.而且我们的日期是datetime格式，因此使用CAST)

For example, here's my first query: (we assign certain codes to differentiate between different reasons tickets are created, just wanted to throw that out there in case that line creates any confusion. Also our dates are datetime format, hence the use of CAST)

SELECT 
Cast(CreateDate AS Date) AS 'Date',
Count(CreateDate) AS '# Created'

FROM dbo.Tickets

WHERE 
Cast(CreateDate AS Date) >= @StartDate and 
Cast(CreateDate AS Date) <=@EndDate and
(Reason >= 36 and Reason <= 41 OR Reason = 17)

GROUP BY Cast(CreateDate AS Date)

ORDER BY 'Date'

将产生以下结果:

Date             # Created
----------       -------------
5/1/2013         396
5/2/2013         418
5/3/2013         288
5/4/2013         28
5/5/2013         100

我的第二个查询与创建日期"查询完全相同，只是在创建日期"所在的位置被解析日期"替换.但是，2013年5月4日没有解决的票证.

My second query is the exact same as the "Create Date" query, just replaced with "Resolved Date" wherever "CreateDate" is located. However, there were no tickets resolved on the date 5/4/2013.

如何将这两个查询合并为一个，并使其返回如下所示的结果集?

How can I combine these two queries into one, and get it to return a result set like below?

 Date             # Created           # Resolved
----------       -------------        -------------
5/1/2013         396                  400
5/2/2013         418                  322
5/3/2013         288                  280
5/4/2013         28                   0 
5/5/2013         100                  11

我认为让我失望的是，表中没有5/4/2013的已解决日期.任何帮助将非常感激.谢谢！

I think the part that is throwing me off is that there is no resolved date of 5/4/2013 in the table. Any help would be much appreciated. Thanks!

推荐答案

尝试一下:

with all_dates(the_date)
as
(
    select min(cast(createdate as date))
    from tickets
    UNION ALL
    select DATEADD(day,1,the_date)
    from all_dates
    where all_dates.the_date < GETDATE()+1

)

select the_date,
        SUM(case when CAST(t.createdate as DATE) = ad.the_date then 1 else 0 end) as CreatedCount,
        SUM(Case when CAST(t.resolveddate as DATE) = ad.the_date then 1 else 0 end) as ResolvedCount
from all_dates ad
left outer join tickets t 
on ad.the_date = CAST(t.createdate as DATE)
or ad.the_date = CAST(t.resolveddate as DATE)
group by ad.the_date
option (MAXRECURSION 10000)

我创建了一个CTE，以保存从创建的第一个日期到今天之间的所有日期.这样，即使您没有在某个日期创建或解决故障单，您仍然会在结果中看到该天.

I created a CTE to hold all of the dates between the 1st created date and today. This way, even if you don't have a ticket created or resolved on a date you will still see that day in your results.

这篇关于按日期对多列进行计数功能(SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！