SQL SERVER Count of items in a Calculated Field [duplicate]

Question

SQL SERVER Count of items in a Calculated Field [duplicate]

Navigation

#1 by (0 votes)

0

Good afternoon, I'm having trouble counting items in SQL SERVER, I need to perform the count of a column that I EVEN CREATE, where there is a rule, and there are several errors in my tests, errors in GroupBy among others,

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

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 count to be made by the [CAM_PF] field

As I mentioned, when I query, I can not apply the rule below:

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

or I get error in GroupBy

Can anyone help me with this count / sum?

o Result should bring 03 columns being: [CAM_PF], [CAM_DT_REGISTRY], COUNT

CAM

sql sql-server

asked by anonymous 10.07.2017 / 21:50

1 answer

django-admin command startproject does not create manage.py file How to sign the same plan twice (subscription button of PagSeguro)

score 0 · Answer 1

Have you tried this?

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];