How can I use LAG to get me the previous rows LAG result?
This is my code:
SELECT etv.Year, etv.week, etv.Country, etv.name, cc.Date,
CASE
WHEN DATEDIFF(day,LAG(cc.Date) OVER (ORDER BY Country, name, cc.Date),cc.Date) > 50 THEN Year
ELSE LAG(etv.Year) OVER (ORDER BY etv.Country, etv.name, Date)
END as StartYear
FROM EUROPETOP10VOLUME etv
LEFT OUTER JOIN (SELECT MIN(Date) as Date,[FIN YEAR], [FIN WEEK] FROM Retail.dbo.gc_CUST_CALS GROUP BY [FIN YEAR], [FIN WEEK]) cc ON etv.Year = cc.[FIN YEAR] and etv.week = [FIN WEEK]
WHERE descr = 'Avocado' and CAST(CONCAT(Year,RIGHT(CONCAT('00',week),2)) as int) >= 202227 and name = 'AGRICOM LTDA'
GROUP BY cc.Date,Country,name, etv.Year, etv.week
But the result I get from the lag is always the year,
| Year | week | Country | name | Date | StartYear |
|---|---|---|---|---|---|
| 2022 | 47 | CHILE | AGRICOM LTDA | 2022-11-20 00:00:00.000 | 2022 |
| 2022 | 48 | CHILE | AGRICOM LTDA | 2022-11-27 00:00:00.000 | 2022 |
| 2022 | 49 | CHILE | AGRICOM LTDA | 2022-12-04 00:00:00.000 | 2022 |
| 2022 | 50 | CHILE | AGRICOM LTDA | 2022-12-11 00:00:00.000 | 2022 |
| 2022 | 51 | CHILE | AGRICOM LTDA | 2022-12-18 00:00:00.000 | 2022 |
| 2022 | 52 | CHILE | AGRICOM LTDA | 2022-12-25 00:00:00.000 | 2022 |
| 2023 | 1 | CHILE | AGRICOM LTDA | 2023-01-01 00:00:00.000 | 2022 |
| 2023 | 2 | CHILE | AGRICOM LTDA | 2023-01-08 00:00:00.000 | 2022 |
| 2023 | 3 | CHILE | AGRICOM LTDA | 2023-01-15 00:00:00.000 | 2022 |
| 2023 | 4 | CHILE | AGRICOM LTDA | 2023-01-22 00:00:00.000 | 2022 |
| 2023 | 5 | CHILE | AGRICOM LTDA | 2023-01-29 00:00:00.000 | 2022 |
| 2023 | 6 | CHILE | AGRICOM LTDA | 2023-02-05 00:00:00.000 | 2022 |
| 2023 | 8 | CHILE | AGRICOM LTDA | 2023-02-19 00:00:00.000 | 2022 |
| 2023 | 9 | CHILE | AGRICOM LTDA | 2023-02-26 00:00:00.000 | 2022 |
| 2023 | 40 | CHILE | AGRICOM LTDA | 2023-10-01 00:00:00.000 | 2023 |
| 2023 | 41 | CHILE | AGRICOM LTDA | 2023-10-08 00:00:00.000 | 2023 |
| 2023 | 42 | CHILE | AGRICOM LTDA | 2023-10-15 00:00:00.000 | 2023 |
| 2023 | 43 | CHILE | AGRICOM LTDA | 2023-10-22 00:00:00.000 | 2023 |
| 2023 | 44 | CHILE | AGRICOM LTDA | 2023-10-29 00:00:00.000 | 2023 |
| 2023 | 45 | CHILE | AGRICOM LTDA | 2023-11-05 00:00:00.000 | 2023 |
| 2023 | 46 | CHILE | AGRICOM LTDA | 2023-11-12 00:00:00.000 | 2023 |
| 2023 | 47 | CHILE | AGRICOM LTDA | 2023-11-19 00:00:00.000 | 2023 |
| 2023 | 48 | CHILE | AGRICOM LTDA | 2023-11-26 00:00:00.000 | 2023 |
| 2023 | 49 | CHILE | AGRICOM LTDA | 2023-12-03 00:00:00.000 | 2023 |
| 2023 | 50 | CHILE | AGRICOM LTDA | 2023-12-10 00:00:00.000 | 2023 |
| 2023 | 52 | CHILE | AGRICOM LTDA | 2023-12-24 00:00:00.000 | 2023 |
| 2024 | 1 | CHILE | AGRICOM LTDA | 2023-12-31 00:00:00.000 | 2023 |
how can I change it to give me the year from the previous lag. The example above is what I am trying to acheive. Year 2022 Week 47 to 2023 Week 9 need to have start Year 2022. Then Year 2023 week 40 to 2024 Week 1 need to have start Year 2023
Any help would be appreciated