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

Navigation
1

Good morning, I have a query that brings in a calculated column the information of "Data" and "Historicos", in this column I have the Prefix of the item (vehicle license plate) and the column with its calculation. I need to change this query so that it brings me the Prefix information and the count of how many words "Data" appeared for this Item.  Below is the query I'm using as a basis for trying 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 large cfme list below:

Theexpectedresultisaquerywiththefollowingstructure:

CAMHistoricoDadosBBM0132050BBM0171545

Inshort,I'mtryingtousethequeryIputupinasubquery,whereIbringthe"DATA" and "HISTORIC" count in the main query, I really could not do that counting for a calculated field.

Can someone please help me? give some tips, examples?

Thank you!

    
asked by anonymous 07.07.2017 / 20:29

2 answers

1

Use GROUP BY with 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
    
07.07.2017 / 20:37
0

Good afternoon, I tried to use this example, but I still have some problems, I can not bring the data, it gives this error: "Windowed functions can not be used in the context of another windowed function or aggregate."

Here's 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]

Do you have any other tips on 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 to: 

CAM_PF, [CAM_DT_REGISTRO], [CAM_CD]

But you're giving GroupBy an error,

Can you give me a hand?

Sds

    
10.07.2017 / 20:18
Substitute for ProgressDialog AWS: Error installing php Myadmin