Search code examples
sql-servergroup-bysql-server-2014

SQL Server 2014 - Error in code


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


Solution

  • 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.