Using LEAD to get next date

Question

I need to create a query that answers the following question:

I want to return the number of registrations that has a reading date of 26 days, number of entries that has a reading date of 27 days and there goes up to 31 days, the last 6 months. I need you to stay like this:

My expected output is this:

LOCALE   COMPETENCE    Reading_date(DAYS)     INSCRIPTION (Quantity)
------    ----         ----------             ---------
CEARA    JAN18          26                      20
CEARA    JAN18          27                      02
CEARA    JAN18          28                      34
CEARA    JAN18          29                      07
CEARA    JAN18          30                      12
CEARA    JAN18          31                      11
CEARA    FEV18          26                      21
CEARA    FEV18          27                      09

(and go...)

But my table is this:

INSCRIP  COMPETENCE     Reading_date
00183938    201801      2018-01-02
00183938    201802      2018-02-01
00183946    201709      2017-09-01
00183946    201710      2017-10-01
00183946    201711      2017-11-01

My sql that I'm trying is this (please abstract the joins):

select top 10   LOCALE
               , COMPETENCE
               , lead(Reading_date, 1) over (PARTITION by Reading_date ORDER BY Reading_date) next_lecture
               , datediff(day, Reading_date, next_lecture) Reading_date
               , INSCRIPTION
from BASE o
join LOCALE l
on u.localidade = l.cod_localidade
group by LOCALE, COMPETENCE, Reading_date, INSCRIPTION

somebody help me please? Thanks and sorry for bad english

Answer 1

I made the comment, but I'll post it as an answer as well. The error is pretty self explanatory:

Message 207, Level 16, State 1, Line 27 Invalid column name 'next_lecture'.

There is no column, next_lecture in your table. That's because, like I said in the comment:

You're trying to reference another field in your SELECT by it's alias; you can't do that.

The only place you can refer to a field by it's alias within the query itself (so not outside of it, for example when using a subquery) is within the ORDER BY clause; anywhere else will result in an error. So, a simple statement like this will fail:

SELECT 1 + 1 AS Two, Two * 2 AS Four;

You have to provide the full expression, thus:

SELECT 1 + 1 AS Two, (1 + 1) * 2 AS Four;    

As a result your query becomes:

SELECT DISTINCT TOP 10
       LOCALE,
       COMPETENCE
       LEAD(Reading_date, 1) OVER (PARTITION by Reading_date ORDER BY Reading_date) AS next_lecture
       DATEDIFF(DAY, Reading_date, LEAD(Reading_date, 1) OVER (PARTITION by Reading_date ORDER BY Reading_date)) Reading_date
       INSCRIPTION
FROM BASE o
     JOIN LOCALE l ON u.localidade = l.cod_localidade;

Note I have also remvoed the GROUP BY and used DISTINCT. You have no aggregate functions in your query, so I assume that is what you're trying to do with the use of it.

Edit: Random guess of what the OP wants, but it doesn't get there. I've added this for their reference, however, things are too unclear still:

WITH VTE AS (
    SELECT INSCRIP,COMPETENCE,CONVERT(date,reading_date) AS Reading_date
    FROM (VALUES('00183938',201801,'20180102'),
                ('00183938',201802,'20180201'),
                ('00183946',201709,'20170901'),
                ('00183946',201710,'20171001'),
                ('00183946',201711,'20171101')) V(INSCRIP,COMPETENCE,reading_date))
SELECT COMPETENCE,
       LEAD(Reading_Date) OVER (/*PARTITION BY LOCALE*/ ORDER BY Reading_Date) AS next_lecture,
       DATEDIFF(DAY,Reading_date, LEAD(Reading_Date) OVER (/*PARTITION BY LOCALE*/ ORDER BY Reading_Date)) AS ReadingDate,
       INSCRIP
FROM VTE
ORDER BY Reading_date ASC