I am using Azure SQL.

I want to generate a lookup report with 30 columns in which to check the evolution of a column for this period of time, but only for current day and not for all of the columns. Each column would represent the price in the past 1, 2, 3, ... 30 days.

If I apply a where clause on the current date, the lag function will not look any days back.

This is what it currently looks:

This is what it should look:

The query that I tried:

select 
value, date,
    LAG(value,1) OVER ( partition by name, adress ORDER by date)AS day_1,
    LAG(value,2) OVER ( partition by name, adress ORDER by date)AS day_2,
    LAG(value,3) OVER ( partition by name, adress ORDER by date)AS day_3,
    LAG(value,4) OVER ( partition by name, adress ORDER by date)AS day_4,
    LAG(value,5) OVER ( partition by name, adress ORDER by date)AS day_5,
    LAG(value,6) OVER ( partition by name, adress ORDER by date)AS day_6
   from
products where date = cast(GETDATE() as Date)

CodePudding user response：

Try this:

WITH CTE1 AS
(
select 
value, date,
    LAG(value,1) OVER ( partition by name, adress ORDER by date)AS day_1,
    LAG(value,2) OVER ( partition by name, adress ORDER by date)AS day_2,
    LAG(value,3) OVER ( partition by name, adress ORDER by date)AS day_3,
    LAG(value,4) OVER ( partition by name, adress ORDER by date)AS day_4,
    LAG(value,5) OVER ( partition by name, adress ORDER by date)AS day_5,
    LAG(value,6) OVER ( partition by name, adress ORDER by date)AS day_6
   from products
)
SELECT * 
FROM CTE1
WHERE date = cast(GETDATE() as Date);

The problem you are facing is because the data get filtered as soon as you put WHERE clause for current date filter and then LAG function will be applied on the rows having current date only(since other date data is already filtered out).

So the solution is to first apply LAG function and then apply WHERE clause on the result you got from the previous query.