Count from multiple tables in a single report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count from multiple tables in a single report

Author

Topic

kaswanzy
Starting Member

1 Post

Posted - 2014-09-11 : 11:03:24

I am pretty new to SQL and I am trying to figure out how to count from multiple tables. So currently I pull two separate reports to show the count of TaskIDs by customer state and another to show WrapID by Customer state for last month:

TaskID Count by Customer state Query:
---------------------------------------
SELECT CU.CustomerState as 'State'
,Count (CM.TaskID) as 'CaseCount'

From Customer CU
LEFT OUTER JOIN ACN_CCPCaseManagementTask CM ON CU.CustID=CM.CustID

WHERE datediff(mm, CM.CreatedDate, getdate())=1

Group by CU.CustomerState
Order by CU.CustomerState
----------------------

WrapID Count by Customer State:
-----------------------------
SELECT CU.CustomerState as 'State'
,Count (CW.WrapCodeID) as 'WrapCount'

From Customer CU
LEFT OUTER JOIN acn_ccpwrapcode CW ON CU.CustID=CW.CustID

WHERE datediff (mm, CW.DateCreated, getdate())=1

Group by CU.CustomerState
Order by CU.CustomerState
---------------------------------

I am wanting to add both these counts into a single report so i tried the follow query but the counts don't match to the reports I pull separately. Just wanting to know if I am doing this right.
--------------------------

SELECT CU.CustomerState as 'State'
,Count (CM.TaskID) as 'CaseCount'
,Count (CW.WrapCodeID) as 'WrapCount'

From Customer CU
LEFT OUTER JOIN ACN_CCPCaseManagementTask CM ON CU.CustID=CM.CustID
LEFT OUTER JOIN acn_ccpwrapcode CW ON CU.CustID=CW.CustID

WHERE datediff(mm, CM.CreatedDate, getdate())=1
AND datediff (mm, CW.DateCreated, getdate())=1

Group by CU.CustomerState
Order by CU.CustomerState

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-11 : 11:23:35

Try the APPLY operator like shown below:

SELECT  CU.CustomerState AS 'State' ,
	a.N AS 'CaseCount' ,
	b.N AS 'WrapCount'
FROM
	Customer CU
	OUTER APPLY
	( 
		SELECT COUNT(CM.TaskID) AS N 
		FROM ACN_CCPCaseManagementTask CM
		WHERE CU.CustID = CM.CustID
	) a
	OUTER APPLY
	( 
		SELECT COUNT(CW.WrapCodeID) AS N 
		FROM acn_ccpwrapcode CW 
		WHERE  CU.CustID = CW.CustID
	) B

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-11 : 11:28:39

Also, if you want to use the filter on CM.CreatedDate and CW.DateCreated consider couple of things:
a) put the where clauses inside the outer apply rather than as a where clause. When you use a where clause that contains a column from a table that is in the left join, that effectively transforms the left join to an inner join.
b) rather datediff(mm, CM.CreatedDate, getdate())=1 use CM.CreateDate >= DATEADD(mm,-1,GETDATE())
c) using the datediff the way you have (or the modified version in (b) above, depending on whether or not the CreatedDate has time portion, the behavior will be different.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources