Vfrdtyky

Need your help.
Having this table:

Airline        WKS        TypePrint   nprints   Eventtime

UX        MAD2AKB503        BTP        1        2018-08-31 09:41:13.360

UX        MAD2AKB503        GPP        1        2018-08-31 09:41:32.723

UX        MAD2AKB503        GPP        1        2018-08-31 09:41:39.700

KLM       MAD2AKB426        GPP        1        2018-08-31 09:46:03.727

KLM       MAD2AKB426        GPP        1        2018-08-28 04:44:22.650

KLM       MAD2AKB426        GPP        1        2018-08-28 04:44:29.000

UX        MAD2AKB497        GPP        1        2018-08-29 06:03:24.517

KLM       MAD2AKB426        GPP        1        2018-08-31 10:10:23.193

KLM       MAD2AKB426        GPP        1        2018-08-31 10:10:30.837

UX        MAD1AKB223        GPP        1        2018-08-05 20:01:57.857

I would like to sum nprints based in typeprint and Airline column by grouping by WKS. I tried with code as follows:

declare @fecha_inicio date='01-01-2018';

declare @fecha_fin date='12-01-2018';

declare @Airline_id varchar(10)='UX'



SELECT wks, 

       @Airline_id, 

       (SELECT Sum (Cast (nprints AS INT)) AS GPP 

        FROM   dw_prints2 B1 

        WHERE  ( B1.airline = @Airline_id ) 

               AND B1.typeprint = 'GPP' 

               AND ( ( B1.eventtime >= @Fecha_Inicio ) 

                     AND ( B1.eventtime <= @Fecha_Fin ) ) 

               AND A.wks = B1.wks) AS GPP, 

       (SELECT Sum (Cast (nprints AS INT)) AS BTP 

        FROM   dw_prints2 C1 

        WHERE  ( C1.airline = @Airline_id ) 

               AND C1.typeprint = 'BTP' 

               AND ( ( C1.eventtime >= @Fecha_Inicio ) 

                     AND ( C1.eventtime <= @Fecha_Fin ) ) 

               AND A.wks = C1.wks) AS BTP 

FROM   dw_prints2 A 

GROUP  BY wks 

ORDER  BY wks 

returning:

wks Airline_id GPP BTP
MAD1AKB223 UX 1 NULL
MAD2AKB426 UX NULL NULL
MAD2AKB497 UX 1 NULL
MAD2AKB503 UX 2 1

However my intention is not to return WKS when sum is NULL for GPP and BTP. I mean:

wks Airline_id GPP BTP
MAD1AKB223 UX 1 NULL
MAD2AKB497 UX 1 NULL
MAD2AKB503 UX 2 1

thanks in advance for your help.

No need use subquery, just use conditional aggregations. And add HAVING to filter NULL results

SELECT wks, 

       @Airline_id as Airline,

       Sum ( CASE WHEN typeprint = 'GPP'       

                  THEN Cast (nprints AS INT)

             END) AS GPP, 

       Sum ( CASE WHEN typeprint = 'BTP' 

                  THEN Cast (nprints AS INT)

             END) AS BTP,            

FROM dw_prints2

WHERE Airline = @Airline_id

  AND  eventtime >= @Fecha_Inicio 

  AND  eventtime <= @Fecha_Fin 

GROUP BY wks

HAVING

       Sum ( CASE WHEN typeprint = 'GPP'       

                  THEN Cast (nprints AS INT)

             END) IS NOT NULL            

   OR Sum ( CASE WHEN typeprint = 'BTP' 

                  THEN Cast (nprints AS INT)

             END) IS NOT NULL

ORDER BY wks