Bring the count of the word "DATA" from a column calculated in SQL Server

Question

Good morning, I have a query that brings in a column calculated the information of "Data" and "Historicos", in this column I have the item Prefix (vehicle plate) and the column with the respective calculation. I need to change this query so that you bring me the Prefix information and count how many words "Data" appeared for this Item. Below follows the query I am used as a basis to try to develop this:

 SELECT
     [CAM_CD] as COD
    ,[CAM_PF] As CAM
    ,[CAM_DS_PLACA] as PLACA
    ,[CAM_CR_LATITUDE] as LATITUDE
    ,[CAM_CR_LONGITUDE] as LONGITUDE
    ,[CAM_DT_REGISTRO] as DT_REGISTRO
    ,convert(char(8), [CAM_DT_REGISTRO], 108) as 'HoraA'
    ,DATEPART(Hour, [CAM_DT_REGISTRO]) as 'HoraB'   
    ,DATEPART(DD,[CAM_DT_REGISTRO]) as 'Dia'
    ,DATEPART(MM,[CAM_DT_REGISTRO]) as 'Mes'
    ,DATEPART(YY,[CAM_DT_REGISTRO]) as 'Ano'   
    ,[CAM_DT_BD]
    --Coluna que deve ser contada na nova query
     ,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))                                
           then 'Historico' else 'Dados' 
    end as [Tipo] 
FROM [KLABIN_SGL].[dbo].[TB_CAMINHAO] CONSCM
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]; 

The result of this table brings a great list cfme below:

The expected result is a query with the following structure:

 CAM    Historico   Dados
 BBM013   20        50
 BBM017   15        45

In short, I’m trying to use the query that I mounted in a subquery, where bring in the main query the count of "DATA" and "HISTORICO", I really could not do this count of a calculated field.

Can someone please help me? give a few tips, examples?

Grateful!

Answer 1

Use the GROUP BY with the function COUNT:

SELECT CONSCM.CAM_PF AS CAM,
       COUNT(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)) THEN 1
               ELSE NULL) AS HISTORICOS,
       COUNT(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)) THEN NULL
               ELSE 0) AS DADOS
  FROM KLABIN_SGL.DBO.TB_CAMINHAO CONSCM
 GROUP BY CONSCM.CAM_PF

Answer 2

Good afternoon, I tried to use this example, but I’m still in some trouble, I can’t bring the data, give this error: "Windowed functions cannot be used in the context of Another Windowed Function or Aggregate."

Follow the SQL I’m using:

SELECT CAM_PF AS CAM,

Sum(CASE WHEN 
([CAM_DT_REGISTRO] < LAG([CAM_DT_REGISTRO],1) OVER 
(PARTITION BY [CAM_PF] ORDER BY [CAM_CD] ASC))
THEN 1
ELSE NULL
end) AS HISTORICOS

FROM KLABIN_SGL.DBO.TB_CAMINHAO
GROUP BY CAM_PF,
[CAM_DT_REGISTRO],
[CAM_CD]

has more hint of how to proceed?

As I mentioned, I need you to count how many items in the condition below:

([CAM_DT_REGISTRO] < LAG([CAM_DT_REGISTRO],1) OVER (PARTITION BY [CAM_PF] ORDER BY [CAM_CD] ASC))

Bringing the result by:

CAM_PF, [CAM_DT_REGISTRO], [CAM_CD]

But is giving error in Groupby,

Can you give me a hand?

Sds