Current year and last year sales

1

The input data pattern is look like below

State Year Sales
NC 2020 1200
NC 2018 1100
NC. 2019 1400
SC. 2017 1500
SC. 2016. 1355

Expected output

State Year sales Last_year_sales
NC. 2020. 1200. 1400
NC. 2019 1400 1100
NC. 2018 1100 Null
SC. 2017. 1500 1355

2 
Create table #Sales (State char(2), YearNum int, Sales int)
insert into #Sales values

('NC',2020 ,1200 ),
('NC',2018 ,1100 ),
('NC', 2019, 1400),
('SC', 2017, 1500),
('SC', 2016, 1355)

select *, lead(Sales) over (Partition by State order by YearNum Desc) as LastYearSales
 from #Sales
3

Removed, I misunderstood the requirements.

4

Hi

Same thing done in a different way !!!

May or may not be the best approach for performance !!!

Hope it helps :slight_smile:

SELECT 'SQL Output',
    a.State
    , a.Year
    , a.Sales
    , b.Sales AS Last_Year_Sales
FROM
    #Sample a
    JOIN #Sample b
        ON a.Year = b.Year + 1
ORDER BY
    a.State
    , a.Year DESC;

image