January 17, 2005 at 1:10 pm
I have two SQL aggregate queries that I'm trying to role into one query with no luck. The queries are both at the end of this posting. What I would like is a recordset that includes the date, original count and corrected count grouped by date.
Thanks,
Matt
--Get the original transacation count by day
SELECT CONVERT(varchar(10), TD.CreatedDateTime, 101) AS BusinessDate, COUNT(TD.CreatedDateTime) AS [OrigCount]
FROM TransactionDetail TD INNER JOIN
TransactionType TT ON TD.TransactionTypeCode = TT.TransactionTypeCode
WHERE TT.ShortDescription IN ('TN','TB','RN')
GROUP BY CONVERT(varchar(10), TD.CreatedDateTime, 101)
--Get the corrected transacation count by day
SELECT CONVERT(varchar(10), TD.CreatedDateTime, 101) AS BusinessDate, COUNT(TD.CreatedDateTime) AS [CorrCount]
FROM TransactionDetail TD INNER JOIN
TransactionType TT ON TD.TransactionTypeCode = TT.TransactionTypeCode
WHERE TT.ShortDescription IN ('TU','RG')
GROUP BY CONVERT(varchar(10), TD.CreatedDateTime, 101)
January 17, 2005 at 2:16 pm
Matt, I'm sure there is a much more creative way to do this without a temp table, but here is an untested solution with a temp table.
CREATE TABLE #UnionTable (BusinessDate Varchar(10), OrigCount Integer, CorrCount Integer)
GO
INSERT INTO #UnionTable (BusinessDate, OrigCount)
SELECT CONVERT(varchar(10), TD.CreatedDateTime, 101) AS BusinessDate, COUNT(TD.CreatedDateTime) AS [OrigCount]
FROM TransactionDetail TD INNER JOIN
TransactionType TT ON TD.TransactionTypeCode = TT.TransactionTypeCode
WHERE TT.ShortDescription IN ('TN','TB','RN')
GROUP BY CONVERT(varchar(10), TD.CreatedDateTime, 101)
GO
INSERT INTO #UnionTable (BusinessDate, CorrCount)
SELECT CONVERT(varchar(10), TD.CreatedDateTime, 101) AS BusinessDate, COUNT(TD.CreatedDateTime) AS [CorrCount]
FROM TransactionDetail TD INNER JOIN
TransactionType TT ON TD.TransactionTypeCode = TT.TransactionTypeCode
WHERE TT.ShortDescription IN ('TU','RG')
GROUP BY CONVERT(varchar(10), TD.CreatedDateTime, 101)
GO
SELECT * FROM #UnionTable
GO
DROP TABLE #UnionTable
GO
Good Luck
Ryan
January 17, 2005 at 8:12 pm
I this something like this should work:
SUM([OrigCount]) as OrigCount,
SUM(CorrectedCount) as CorrectedCount
FROM (
select CONVERT(varchar(10), TD.CreatedDateTime, 101) AS BusinessDate,
1 as OrigCount, 0 as CorrectedCount
FROM TransactionDetail TD INNER JOIN
TransactionType TT ON TD.TransactionTypeCode = TT.TransactionTypeCode
WHERE TT.ShortDescription IN ('TN','TB','RN')
UNION
SELECT CONVERT(varchar(10), TD.CreatedDateTime, 101) AS BusinessDate,
0 as OrigCount, 1 as CorrectedCount
FROM TransactionDetail TD INNER JOIN
TransactionType TT ON TD.TransactionTypeCode = TT.TransactionTypeCode
WHERE TT.ShortDescription IN ('TU','RG'))
GROUP BY BusinessDate
Basically, you modify your two queries so that you use 1 for the value you want to count. Then combine the two queries with UNION and make the UNION query a virtual table. Finally, you do the aggregate on the virtual table summing up the 1's.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 18, 2005 at 12:06 am
Here is a way without temp table or union.
SELECT CONVERT(varchar(10), TD.CreatedDateTime, 101) AS BusinessDate
, sum ( case when TT.ShortDescription IN ('TN','TB','RN') then 1 else 0 end ) AS [OrigCount]
, sum ( case when TT.ShortDescription IN ('TU','RG') then 1 else 0 end ) as [CorrCount]
FROM TransactionDetail TD INNER JOIN
TransactionType TT ON TD.TransactionTypeCode = TT.TransactionTypeCode
WHERE TT.ShortDescription IN ('TN','TB','RN', 'TU', 'RG')
GROUP BY CONVERT(varchar(10), TD.CreatedDateTime, 101)
Robert
January 18, 2005 at 7:12 am
I believe that will meet your needs best as well.
January 18, 2005 at 4:46 pm
Create 2 functions one for each calculation and then do the select. e.g Select dbo.FunctionA(param1 , param2) as result1, dbo.FunctionB(param3, param4) from tableX
January 26, 2005 at 4:18 pm
Robert-
Thanks for that clean solution. That worked great.
Matt
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply