SQL SERVER Counting Items from a Calculated Field

Question

Good afternoon, I’m having a hard time doing an item count on SQL SERVER, I need to count a column that MYSELF CREATED, where there is a rule, and there are several errors in my tests, errors in Groupby and others,

I need to count the TYPE column, only of the items that are classified in: "DICE"

SELECT 
 [CAM_PF] As CAM
 ,[CAM_DT_REGISTRO] as DT_REGISTRO  
  ,[CAM_DT_BD]
  --Esta coluna deve ser contada, preciso saber quantos iten "DADOS" e quantos "HISTORICO" tem na relação que ele traz
     ,case when 
        ([CAM_DT_REGISTRO] < lag([CAM_DT_REGISTRO],1)over (PARTITION BY [CAM_PF] order by [CAM_CD] asc)) OR
        ([CAM_DT_REGISTRO] < lag([CAM_DT_REGISTRO],2)over (PARTITION BY [CAM_PF] order by [CAM_CD] asc)) OR
        ([CAM_DT_REGISTRO] < lag([CAM_DT_REGISTRO],3)over (PARTITION BY [CAM_PF] order by [CAM_CD] asc))                                    
        then 'Historico' else 'Dados' 
    end as Tipo
    FROM [KLABIN_SGL].[dbo].[TB_CAMINHAO]
    Where  [CAM_DT_REGISTRO] between GETDATE()-1 and GETDATE()
    and DATEPART(Hour, [CAM_DT_REGISTRO]) <= DATENAME(HOUR,SYSDATETIME())-1
    Order By [CAM_PF], [CAM_CD];

I need this countdown to be done by the field [CAM_PF]

As I mentioned, when I ride the queryes, I can’t apply the rule below:

 ([CAM_DT_REGISTRO] < lag([CAM_DT_REGISTRO],1)over (PARTITION BY [CAM_PF] order by [CAM_CD] asc))

or I have Groupby error

Can anyone help me carry out this count / sum?

the result should bring 03 columns being:[CAM_PF], [CAM_DT_REGISTRO], COUNT

CAM

Answer 1

Have you tried that?

SELECT 
 [CAM_PF] As CAM
 ,[CAM_DT_REGISTRO] as DT_REGISTRO  
  ,[CAM_DT_BD]
  --Esta coluna deve ser contada, preciso saber quantos iten "DADOS" e quantos "HISTORICO" tem na relação que ele traz
     ,case when 
        ([CAM_DT_REGISTRO] < lag([CAM_DT_REGISTRO],1)over (PARTITION BY [CAM_PF] order by [CAM_PF], [CAM_CD] asc)) OR
        ([CAM_DT_REGISTRO] < lag([CAM_DT_REGISTRO],2)over (PARTITION BY [CAM_PF] order by [CAM_PF], [CAM_CD] asc)) OR
        ([CAM_DT_REGISTRO] < lag([CAM_DT_REGISTRO],3)over (PARTITION BY [CAM_PF] order by [CAM_PF], [CAM_CD] asc))                                    
        then 'Historico' else 'Dados' 
    end as Tipo
    FROM [KLABIN_SGL].[dbo].[TB_CAMINHAO]
    Where  [CAM_DT_REGISTRO] between GETDATE()-1 and GETDATE()
    and DATEPART(Hour, [CAM_DT_REGISTRO]) <= DATENAME(HOUR,SYSDATETIME())-1
    Order By [CAM_PF], [CAM_CD];