Is there a better way?

Question

Is there a better way to write this query? 

DECLARE @startDate AS DATE, 
@endDate AS DATE;

SET @startDate = (GetDate() - 183);

SET @endDate = (GetDate());

WITH   [dates] ([Date])
AS     (SELECT @startDate AS [Date]
        UNION ALL
        SELECT DATEADD(d, 1, [Date]) AS [Date]
        FROM   [dates]
        WHERE  [Date] < @endDate)
SELECT [Date],
       (SELECT COUNT(*)
        FROM   cat_transaction
        WHERE  CONVERT (VARCHAR (10), trxdate, 20) = [dates].date AND trxsubtype ='prn' ) AS 'Print',
       
       (SELECT COUNT(*)
        FROM   cat_transaction
        WHERE  CONVERT (VARCHAR (10), trxdate, 20) = [dates].date AND trxsubtype ='cpy' ) AS 'Copy' 
        
FROM   [dates]
OPTION (MAXRECURSION 0);

The reason I ask is it takes almost a minute to run on a table that has 121183 rows.  Here is an example of the table structure that I'm querying:

trxdate trxsubtype
2011-10-10 12:53:36.000 cpy
2011-10-10 13:50:12.000 prn
2011-10-10 13:53:15.000 prn
2011-10-10 13:54:52.000 prn
2011-10-10 13:55:53.000 prn
2011-10-10 13:57:05.000 prn
2011-10-10 13:59:09.000 prn
2011-10-10 14:01:08.000 prn
2011-10-10 14:16:32.000 prn
2011-10-10 14:18:24.000 prn

and we're trying to get it to display as:

Date	Print	Copy
7/1/2012	17	0
7/2/2012	633	40
7/3/2012	328	53
7/4/2012	29	12
7/5/2012	325	41
7/6/2012	221	27
7/7/2012	7	0
7/8/2012	55	7
7/9/2012	481	67
7/10/2012	449	76

Answer 1

Don't know if it will be any faster, but this produces the desired output:

Declare	@tvTable Table (
	DateField	datetime
	,TypeField	char(3)
)

Insert	@tvTable
Values	('20130101', 'cpy')
		,('20130101', 'cpy')
		,('20130101', 'prn')
		,('20130101', 'cpy')
		,('20130101', 'prn')
		,('20130102', 'cpy')
		,('20130103', 'cpy')
		,('20130103', 'prn')
		,('20130103', 'cpy')
		,('20130105', 'prn')
		,('20130105', 'prn')
		,('20130104', 'prn')
		,('20130103', 'prn')

;with cteDates as
(
	Select	DATEADD([month], DATEDIFF([month], '20000101', CURRENT_TIMESTAMP), '19991101') dtDate
	Union All
	Select	DATEADD(dd, 1, dtDate)
	From	cteDates
	Where	DATEADD(dd, 1, dtDate) <= CURRENT_TIMESTAMP
)
,cteCountCpy as
(
	Select	Count(DateField) CountCpy
			,DateField
	From	@tvTable
	Where	TypeField = 'cpy'
	Group By DateField
)
,cteCountPrn as
(
	Select	Count(DateField) CountPrn
			,DateField
	From	@tvTable
	Where	TypeField = 'prn'
	Group By DateField
)
Select	d.dtDate
		,Copy = IsNull(cpy.CountCpy, 0)
		,Prnt = IsNull(prn.CountPrn, 0)
From	@tvTable t
join	cteDates d
	on	t.DateField = d.dtDate
left join cteCountCpy cpy
	on	t.DateField = cpy.DateField
left join cteCountPrn prn
	on	t.DateField = prn.DateField
Group By d.dtDate, cpy.CountCpy, prn.CountPrn

And, side comment, anyone know why the forum is now partially in Hebrew?

Edit: Can also do it this way, which is less verbose, and perhaps more straightforward:

;with cteDates as
(
	Select	DATEADD([month], DATEDIFF([month], '20000101', CURRENT_TIMESTAMP), '19991101') dtDate
	Union All
	Select	DATEADD(dd, 1, dtDate)
	From	cteDates
	Where	DATEADD(dd, 1, dtDate) <= CURRENT_TIMESTAMP
)
Select	d.dtDate
		,Copy = Sum(Case
						When t.TypeField = 'cpy' Then 1 Else 0 End)
		,Prnt = Sum(Case
						When t.TypeField = 'prn' Then 1 Else 0 End)
From	@tvTable t
join	cteDates d
	on	t.DateField = d.dtDate
Group By d.dtDate

Answer 2

You should use a calendar table instead of generating one on the fly.

Here is the query with that table:

SELECT c.DateCol, ct.PRINT, ct.Copy FROM CalendarTable 
LEFT JOIN (SELECT CONVERT (VARCHAR (10), trxdate, 20) dtCol,  
SUM(CASE WHEN trxsubtype ='prn' THEN 1 ELSE 0 END)  AS 'Print',
,SUM(CASE WHEN trxsubtype ='cpy' THEN 1 ELSE 0 END) AS 'Copy' 
 FROM   cat_transaction
 GROUP BY  CONVERT (VARCHAR (10), trxdate, 20)
 ) ct ON c.DateCol=CreateTable.dtCol
 WHERE c.DateCol>=@startDate AND c.DateCo<=@endDate

Answer 3

Your conversion trxdate to varchar is one obvous slowdown.  Casting a datetime to date will remove the time portion; there are other ways to achieve that as well. 

left join your [table of dates] to cat_transaction on cast(trxdate as date) = dates.date and using summing (e.g., sum(case trxsubtype  when 'prn' then 1 else 0 end) as 'Print'. Your table of dates can be the cte you have now, a UDF that does the same thing, a calendar table, etc. Obviously you would want to limit the rows selected from cat_transaction to the 2 types of interest. 

You might want to consider materializing trxdate as an actual date in some fashion - especially if you do this a lot.

Answer 4

To add to the answers here, you should rewrite where clause to avoid manipulation on the filtered columns. This will allow SQL Server to efficiently use an index. You currently have this one twice in your query.

CONVERT (VARCHAR (10), trxdate, 20) = [dates].date