Snapshot query for end of year

Question

I have the following table with sample data:

drop table if exists #temp

select '1' as LocationId, '2512' as EquipmentNum, '7459' as RecNum, cast('5/2/2023 13:30' as datetime) as VersionBeginDt
into #temp
UNION ALL
select '1' as LocationId, '2512' as EquipmentNum, '7459' as RecNum, cast('12/15/2018 14:14' as datetime) as VersionBeginDt
UNION ALL
select '1' as LocationId, '2512' as EquipmentNum, '7459' as RecNum, cast('12/28/2018 7:30' as datetime) as VersionBeginDt
UNION ALL
select '2' as LocationId, '2512' as EquipmentNum, '7460' as RecNum, cast('12/23/2020 14:51' as datetime) as VersionBeginDt
UNION ALL
select '2' as LocationId, '2512' as EquipmentNum, '7460' as RecNum, cast('12/28/2018 7:30' as datetime) as VersionBeginDt

select *
from #temp
order by LocationId, VersionBeginDt

I want to create a query that will give me end of year snapshot for the past 6 years (2018-2023) data for each locationid, equipmentNum, and RecordNum based on the effectivity of the VersionBeginDt. So for example, for locationId 1, equipmentnum 2512, and recnum 7460 above, i would need to create a snapshot row for each year starting with the minimum year (2018) and ending with the maximum year (2023) and selecting the latest row that is effective for that year end. so the results would be:

the full result would look like this:

I was thinking maybe using a while loop, but not sure about performance or even how to do the joins correctly.

any help would be appreciated.

thanks

Scott

babatundedallas · Answer

scabral79&nbsp;&nbsp;you can achieve this by using a combination of common table expressions (CTEs) and the ROW_NUMBER() window function to identify the latest row for each year. &nbsp;WITH YearlyData AS (
    SELECT
        LocationId,
        EquipmentNum,
        RecNum,
        VersionBeginDt,
        ROW_NUMBER() OVER (PARTITION BY LocationId, EquipmentNum, RecNum, YEAR(VersionBeginDt) ORDER BY VersionBeginDt DESC) AS RowNum
    FROM
        #temp
    WHERE
        YEAR(VersionBeginDt) BETWEEN 2018 AND 2023
)
SELECT
    LocationId,
    EquipmentNum,
    RecNum,
    VersionBeginDt
FROM
    YearlyData
WHERE
    RowNum = 1
ORDER BY
    LocationId,
    EquipmentNum,
    RecNum,
    VersionBeginDt;&nbsp;Let me know if this works for you.&nbsp;@ me in replies, or I'll lose your thread!!!&nbsp;&nbsp;Note:&nbsp;If this post&nbsp;helps, please consider&nbsp;Accepting it as the solution&nbsp;to help others find it more quickly.&nbsp;ORIf my answers&nbsp;help&nbsp;arrive at a solution? Give it a kudos by clicking the&nbsp;Thumbs Up!&nbsp;Best Regards,Dallas.

Forum Discussion

Snapshot query for end of year

Share

Resources