Sum IIF in T-SQL

a need a help with T-SQL, I have an query to select current row and subtract with previous row. The goal is SUM results from IIF and group by date and shift.

the query:

DECLARE @timeStart time(4) = '04:00:00';
DECLARE @timeLast time(4) = '14:09:00'; 
SELECT 
    IIF(DATEPART(HOUR, Icas.HORA) <= 3, DATEADD(day, - 1, Icas.DATA), Icas.DATA) AS 'Data_Abate',
    IIF(Icas.HORA < @timeLast AND Icas.HORA > @timeStart , 1,2) AS Turno
    /* Linha 01 */
    ,SUM(IIF((
            Icas.PESO_BALANCA_L1 - COALESCE(LAG(Icas.PESO_BALANCA_L1) OVER (ORDER BY Icas.COD_ICAS), 0)) > 0 
            AND (Icas.PESO_BALANCA_L1 - COALESCE(LAG(Icas.PESO_BALANCA_L1) OVER ( ORDER BY Icas.COD_ICAS),0)) < 9999999, 
            (Icas.PESO_BALANCA_L1 - COALESCE(LAG(Icas.PESO_BALANCA_L1) OVER (ORDER BY Icas.COD_ICAS), 0)), 0)) AS 'Linha 01'
FROM VW_ICAS Icas
group by IIF(DATEPART(HOUR, Icas.HORA) <= 3, DATEADD(day, - 1, Icas.DATA), Icas.DATA), IIF(Icas.HORA < @timeLast AND Icas.HORA > @timeStart , 1,2)

the error that is displayed

Msg 8120, Level 16, State 1, Line 8
A coluna 'VW_ICAS.COD_ICAS' é inválida na lista de seleção porque não está incluída numa função de agregação ou na cláusula GROUP BY.
Msg 4109, Level 15, State 1, Line 8
Não é possível utilizar as funções no modo de janela no contexto de outro agregado ou função em modo de janela.

Msg 4109, Level 15, State 1, Line 3 Windowed functions cannot be used in the context of another windowed function or aggregate.
Msg 8120, Level 16, State 1, Line 1 Column 'VW_ICAS.COD_ICAS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Example data:

COD_ICAS    DATA    HORA    PESO_BALANCA_L1
7006    2019-11-25  13:03:21.0  2653132
7007    2019-11-25  13:04:21.0  2673282
7008    2019-11-25  13:05:21.0  2713632
7009    2019-11-25  13:06:21.0  2732392
7010    2019-11-25  13:07:21.0  2752757
7011    2019-11-25  13:08:21.0  2794297
7012    2019-11-25  13:09:21.0  2813667
7013    2019-11-25  13:10:21.0  2833867
7014    2019-11-25  13:11:21.0  2871702
7015    2019-11-25  13:12:21.0  2891462
7016    2019-11-25  13:13:21.0  2911222
7017    2019-11-25  13:14:21.0  2930557
7018    2019-11-25  13:15:21.0  2951997
7019    2019-11-25  13:16:21.0  2992322
7020    2019-11-25  13:17:21.0  3034242

Answer

If I understand correctly, you need to do the lag() in a subquery:

select Data_Abate, Torno,
       sum(case when i.PESO_BALANCA_L1 - coalesce(prev_pb, 0) > 0 and
                     i.PESO_BALANCA_L1 - coalesce(prev_pb, 0) < 9999999, 
                then i.PESO_BALANCA_L1 - coalesce(prev_pb, 0)
                else 0
            end) as Linha_01
FROM (select i.*,
             (case when datepart(hour, i.HORA) <= 3 then dateadd(day, - 1, i.DATA) else i.DATA end) AS Data_Abate,
             (case when i.HORA < @timeLast and i.HORA > @timeStart , then 1 else 2 end) AS Turno,
             lag(i.PESO_BALANCA_L1) over (order by i.COD_ICAS) as prev_pb
      from VW_ICAS i
     ) i
group by data_abate, Torno;

I also strongly encourage you to use coalesce() because it is standard SQL. IIF() is retrofit to support MS Access-style queries.

Advertisement

Answer