CODEWITHSUNDEEP
sqlsql-servert-sql
sgsergio
sgsergio

Reputation: 99

SQL to returns the value before last value in an ordered partition of a result set

I want to get a data into a new column (previous year end head count) after doing partition on the year and taking latest headcount value from that partition and setting it in the subsequent year for the new column.

Create table and insert data script: 

    CREATE TABLE #test1(
        [DateTime] [date] NULL,
        [HeadCount] [int] NULL
    )
    GO
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2020-06-02' AS Date), 2)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2020-05-02' AS Date), 2)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2020-02-20' AS Date), 3)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2020-01-20' AS Date), 3)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2019-06-05' AS Date), 7)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2019-05-01' AS Date), 7)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2019-04-20' AS Date), 5)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2019-01-02' AS Date), 5)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-12-15' AS Date), 8)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-10-02' AS Date), 9)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-09-12' AS Date), 6)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-08-06' AS Date), 3)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-07-10' AS Date), 2)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-06-05' AS Date), 3)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-03-20' AS Date), 3)
    INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-02-10' AS Date), 7)

Solution I have tried: 

    SELECT 
    datetime,headcount,
    LAST_VALUE(headcount) OVER(
                PARTITION BY year(datetime)
            ORDER BY datetime desc
            RANGE BETWEEN 
                UNBOUNDED PRECEDING AND 
                UNBOUNDED FOLLOWING
        ) PrevYearEndHeadCnt
    FROM 
        #test1 order by datetime desc

Result I am getting vs expectation:

    datetime    headcount   PrevYearEndHeadCnt  Expected
    02-06-2020  2           3                   7
    02-05-2020  2           3                   7
    20-02-2020  3           3                   7
    20-01-2020  3           3                   7
    05-06-2019  7           5                   8
    01-05-2019  7           5                   8
    20-04-2019  5           5                   8
    02-01-2019  5           5                   8
    15-12-2018  8           7                   0
    02-10-2018  9           7                   0
    12-09-2018  6           7                   0
    06-08-2018  3           7                   0
    10-07-2018  2           7                   0
    05-06-2018  3           7                   0
    20-03-2018  3           7                   0
    10-02-2018  7           7                   0

Upvotes: 0

Views: 60

Answers (2)

JohnyL
JohnyL

Reputation: 7142

with x as (
    select *,
           cnt = count(*) over (partition by year([DateTime])),
           rownum = row_number() over (partition by year([DateTime])
                                       order by [DateTime] desc)
    from #test1
)
select [DateTime],
       HeadCount,
       PrevYearEndHeadCnt = isnull(lead(HeadCount, cnt - rownum + 1)
                                   over(order by [DateTime] desc), 0)
from x order by [DateTime] desc;

Upvotes: 1

VTi
VTi

Reputation: 1319

This should help you get your expected result set. I am just using self join to compare this year with last year and getting the latest headcount from last year using the rank function. 

    SELECT curr.datetime,curr.headcount,
           ISNULL(prev.HeadCount,0) as PrevYearEndHeadCnt
    FROM #test1 curr
    LEFT JOIN (Select datetime,HeadCount, 
                      RANK() OVER (PARTITION BY year(datetime) ORDER BY datetime desc) as rn_headcount
               FROM #test1) prev ON YEAR(prev.DateTime) = YEAR(curr.DateTime) - 1
                                AND rn_headcount = 1
    ORDER BY curr.datetime DESC

Upvotes: 1

Related Questions