Use LEAD to get the next date

Question

Use LEAD to get the next date

Navigation

#1 by (0 votes)

0

I'm trying to put together a query that answers the following question:

Number of customers (registration in the case) that has a 26-day reading date, number of clients that have 27-day reading, and so on up to 31 days, I will show an image exactly what I want:

My query is not returning what I want, it is returning zero in every QTD_DIAS column, the query is conforming to the image (I put it after the order by the dat_read, ta)

So, I believe I need to do some kind of subquery, but I do not know where to start, could anyone help me? Thank you so much!

select distinct top 10 sigla_unidade AS UNIDADE
                , dsc_localidade as LOCALIDADE
                , o.competencia AS COMPETENCIA
                --, DAT_LEITURA AS QTD_DIAS
                , lead(dat_leitura, 1) over (PARTITION by dat_leitura order by) prox_leitura
                , datediff(day, dat_leitura, lead(dat_leitura, 1) over(PARTITION by dat_leitura)) QTD_DIAS
                , o.inscricao as QTD_INSCRICOES
from BASE o
join USUARIOS u 
on o.inscricao = u.inscricao
join UNIDADES n
on u.cod_und_negocio = n.cod_unid_negocio
join LOCALIDADES 1
on u.localidade = 1.cod_localidade
--group by sigla_unidade, desc_localidade, o.competencia, dat_leitura, a.inscricao

sql sql-server query

asked by anonymous 21.03.2018 / 06:34

1 answer

How can I make my site in git-pages recognize the files that are inside the css, img and js folders? Upload and display on-screen loop-time with PHP, Jquery, AJAX, javascript

score 0 · Answer 1

Given the information provided so far, this is a suggestion:

-- código #1
with Base2 as (
SELECT *,
       datediff(day,
                lag(dat_leitura, 1) over (partition by inscricao order by competencia),
                dat_leitura
               ) as QTD_DIAS
  from BASE
)
SELECT sigla_unidade AS UNIDADE,
       dsc_localidade as LOCALIDADE,
       o.competencia AS COMPETENCIA,
       QTD_DIAS,
       count(*) as QTD_INSCRICOES
  from BASE2 as o
       join USUARIOS as u on o.inscricao = u.inscricao
       join UNIDADES as n on u.cod_und_negocio = n.cod_unid_negocio
       join LOCALIDADES as l on u.localidade = l.cod_localidade
  where QTD_DIAS between 26 and 31
  group by sigla_unidade, desc_localidade, o.competencia, QTD_DIAS;