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