sql-serversql-server-2014-express

I want to a get Result in SQL Server Using UNION and Group ignore 0


Here is my code

DECLARE @Fromdate date = '2021-06-5';
DECLARE @Todate date = '2021-06-5';
DECLARE @till01 varchar(200) = 'ST-LO-01';
DECLARE @till02 varchar(200) = 'ST-LO-02';
DECLARE @till03 varchar(200) = 'ST-LO-03';
DECLARE @till04 varchar(200) = 'ST-LO-04';
DECLARE @till05 varchar(200) = 'ST-LO-05';
DECLARE @till06 varchar(200) = 'ST-LO-06';

SELECT Srt.TRANSDATE,
       Srt.TERMINAL AS TILL,
       ISNULL(COUNT(Srt.TRANSACTIONID), 0) AS Total_Inv,
       0 AS Empty_Info
FROM RETAILTRANSACTIONTABLE Srt
    LEFT JOIN RetailTransactionInfoCodeTrans Cinf ON Cinf.TRANSACTIONID = Srt.TRANSACTIONID
WHERE Srt.TRANSDATE >= @Fromdate
  AND Srt.TRANSDATE <= @Todate
  AND Srt.TYPE = 2
  AND Srt.TERMINAL IN (@till01, @till02, @till03, @till04)
GROUP BY Srt.TRANSDATE,
         Srt.TERMINAL
UNION
SELECT Srt.TRANSDATE,
       Srt.TERMINAL AS TILL,
       0 AS TOTAL,
       ISNULL(COUNT(Srt.TRANSACTIONID), 0) AS EMPTY
FROM RETAILTRANSACTIONTABLE Srt
    LEFT JOIN RetailTransactionInfoCodeTrans Cinf ON Cinf.TRANSACTIONID = Srt.TRANSACTIONID
WHERE Srt.TRANSDATE >= @Fromdate
  AND Srt.TRANSDATE <= @Todate
  AND Srt.TYPE = 2
  AND Srt.TERMINAL IN (@till01, @till02, @till03, @till04)
  AND Cinf.information IS NULL
GROUP BY Srt.TERMINAL,
         Srt.TRANSDATE
ORDER BY Srt.TRANSDATE,
         Srt.TERMINAL,
         Empty_Info ASC;

Result is:

TRANSDATE TILL Total_Inv Empty_Info
2021-06-05 ST-LO-01 197 0
2021-06-05 ST-LO-01 0 2
2021-06-05 ST-LO-02 175 0
2021-06-05 ST-LO-02 0 4
2021-06-05 ST-LO-03 240 0
2021-06-05 ST-LO-03 0 9
2021-06-05 ST-LO-04 207 0
2021-06-05 ST-LO-04 0 9

I want this result:

TRANSDATE TILL Total_Inv Empty_Info
2021-06-05 ST-LO-01 197 2
2021-06-05 ST-LO-02 175 4
2021-06-05 ST-LO-03 240 9
2021-06-05 ST-LO-04 207 9

Solution

  • Seems you should just be using some conditional aggregation:

    SELECT Srt.TRANSDATE,
           Srt.TERMINAL AS TILL,
           COUNT(Srt.TRANSACTIONID) AS Total_Inv, --Count can return 0 already, no need for the ISNULL
           COUNT(CASE WHEN Cinf.information  IS NULL THEN Srt.TRANSACTIONID END) AS Empty_Info
    FROM RETAILTRANSACTIONTABLE Srt
        LEFT JOIN RetailTransactionInfoCodeTrans Cinf ON Cinf.TRANSACTIONID = Srt.TRANSACTIONID
    WHERE Srt.TRANSDATE >= @Fromdate
      AND Srt.TRANSDATE <= @Todate
      AND Srt.TYPE = 2
      AND Srt.TERMINAL IN (@till01, @till02, @till03, @till04)
    GROUP BY Srt.TRANSDATE,
             Srt.TERMINAL;