I am trying to create a report that gives me the Total Counts of transactions per month, for a certain LabourID (L_ID). But I can't seem to get what I'm trying to get.
My code is as follows:
SET NOCOUNT ON
Declare @S AS DateTime = ISNULL(@StartDate,DateAdd(d,-60,GETDATE()))
IF OBJECT_ID('tempdb..#Count') IS NOT NULL DROP TABLE #Count;
SELECT Year(TranxDate) AS YY
,Month(TranxDate) AS MM
,TT.L_ID
,count(IR.T_TransactionId) TranxCount
INTO #Count
FROM IR
INNER JOIN TT ON IR.T_TransactionId = TT.T_TransactionId
WHERE [TranxDate] >= @StartDate
AND [Some condition]
AND [Some condition]
AND [Some condition]
AND TT.L_ID IN ('502','701','18','22','702','503')
GROUP BY Year(TranxDate), Month(TranxDate), TT.L_ID
SELECT YY
,MM
,(SELECT TranxCount FROM #Count WHERE L_ID = '701')ID701
,(SELECT TranxCount FROM #Count WHERE L_ID = '502')ID502
,(SELECT TranxCount FROM #Count WHERE L_ID = '18')ID18
,(SELECT TranxCount FROM #Count WHERE L_ID = '22')ID22
,(SELECT TranxCount FROM #Count WHERE L_ID = '702')ID702
,(SELECT TranxCount FROM #Count WHERE L_ID = '503')ID503
FROM #Count
GROUP BY YY, MM, L_ID
ORDER BY YY, MM
I get an error message from this code:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The results of the TempTable #Count are as follows:
Year Month L_ID TranxCount
2016 9 18 4141
2016 9 22 637
2016 9 502 7782
2016 9 503 531
2016 9 701 7529
2016 9 702 1577
2016 10 18 4611
2016 10 22 1007
2016 10 502 9763
2016 10 503 507
2016 10 701 10093
2016 10 702 2176
2016 11 18 5421
2016 11 22 1471
2016 11 502 11505
2016 11 503 613
2016 11 701 12530
2016 11 702 2569
The results I would like to have are as follows:
YEAR Month ID701 ID502 ID18 ID22 ID702 ID503
2016 9 7529 7782 4141 637 1577 531
2016 10 10093 9763 4611 1007 2176 507
2016 11 12530 11505 5421 1471 2569 613
2016 12 ...
2017 1
...
...
..
.
What am I doing wrong in my code?
EDIT:
As per @Sean suggestion, I added the following code:
SELECT YY
,MM
, MAX(Case when L_ID = '701' then TranxCount end) as ID701
, MAX(Case when L_ID = '502' then TranxCount end) as ID502
, MAX(Case when L_ID = '18' then TranxCount end) as ID18
, MAX(Case when L_ID = '22' then TranxCount end) as ID22
, MAX(Case when L_ID = '702' then TranxCount end) as ID702
, MAX(Case when L_ID = '503' then TranxCount end) as ID503
FROM #Count
GROUP BY YY, MM, L_ID
ORDER BY YY, MM
But I got the following results:
YY MM ID701 ID502 ID18 ID22 ID702 ID503
2017 1 NULL NULL 2793 NULL NULL NULL
2017 1 NULL NULL NULL 1407 NULL NULL
2017 1 NULL 7763 NULL NULL NULL NULL
2017 1 NULL NULL NULL NULL NULL 608
2017 1 7919 NULL NULL NULL NULL NULL
2017 1 NULL NULL NULL NULL 832 NULL
2017 2 NULL NULL 2874 NULL NULL NULL
2017 2 NULL NULL NULL 1694 NULL NULL
2017 2 NULL 7369 NULL NULL NULL NULL
2017 2 NULL NULL NULL NULL NULL 489
2017 2 8306 NULL NULL NULL NULL NULL
2017 2 NULL NULL NULL NULL 778 NULL
2017 3 NULL NULL 3809 NULL NULL NULL
2017 3 NULL NULL NULL 2003 NULL NULL
2017 3 NULL 10368 NULL NULL NULL NULL
At least there's progress. Is there a way to remove the Null Records and have them instead like how I wanted them?
EDIT 2:
As per @Sean suggestion, I added the following code and then made that a Temp Table:
SELECT YY
,MM
, MAX(Case when L_ID = '701' then TranxCount end) as ID701
, MAX(Case when L_ID = '502' then TranxCount end) as ID502
, MAX(Case when L_ID = '18' then TranxCount end) as ID18
, MAX(Case when L_ID = '22' then TranxCount end) as ID22
, MAX(Case when L_ID = '702' then TranxCount end) as ID702
, MAX(Case when L_ID = '503' then TranxCount end) as ID503
INTO #TotCount
FROM #Count
GROUP BY YY, MM, L_ID
ORDER BY YY, MM
SELECT YY
,MM
,SUM(ID701)
,SUM(ID502)
,SUM(ID18)
,SUM(ID22)
,SUM(ID702)
,SUM(ID503)
FROM #TotCount
GROUP BY YY, MM, L_ID
ORDER BY YY, MM
I got an error message:
Invalid Column L_ID
I resolved this by creating another temp table and then pulling up a SUM of the values from the second temp table, which took out the Null Values.
I also removed the grouping for L_ID.
So my code is as follows:
SELECT YY
,MM
, MAX(Case when L_ID = '701' then TranxCount end) as ID701
, MAX(Case when L_ID = '502' then TranxCount end) as ID502
, MAX(Case when L_ID = '18' then TranxCount end) as ID18
, MAX(Case when L_ID = '22' then TranxCount end) as ID22
, MAX(Case when L_ID = '702' then TranxCount end) as ID702
, MAX(Case when L_ID = '503' then TranxCount end) as ID503
INTO #TotCount
FROM #Count
GROUP BY YY, MM, L_ID
ORDER BY YY, MM
SELECT YY
,MM
,SUM(ID701)
,SUM(ID502)
,SUM(ID18)
,SUM(ID22)
,SUM(ID702)
,SUM(ID503)
FROM #TotCount
GROUP BY YY, MM--, L_ID
ORDER BY YY, MM
This code worked and gave me the desired result.
Thank you to @Sean for trying to help. I appreciate it.