Selecting specific snapshot dates in Microsoft SQL Server Management Studio?-CodePudding

I'm looking at a snapshot table in Microsoft SQL Server Management Studio with distinct dates captured every few days. The table has records stretching back to 2014, and should continue updating with new snapshots for the foreseeable future. As an example, the dates for 2022 are as follows:

2022-01-03 00:00:00
2022-01-10 00:00:00
2022-01-12 00:00:00
2022-01-18 00:00:00
2022-01-24 00:00:00
2022-02-03 00:00:00
2022-02-05 00:00:00
2022-02-09 00:00:00
2022-02-14 00:00:00
2022-02-21 00:00:00
2022-03-01 00:00:00
2022-03-07 00:00:00
2022-03-14 00:00:00

What I'm looking to do is select data (stretching back to 2019) from only the date captured dates closest to the beginning of the month (or, perhaps, quarter), and have the query work so that it will function when April/May/June/etc. roll around. In the above example, essentially, I'd be looking to return data from the 1/03, 2/03 and 3/01 snapshots with nothing else considered.

How do I go about doing this? Thank you!

CodePudding user response：

Try an APPLY ... TOP 1 query, like this example against AdventureWorksDW2017:

select d.CalendarYear, d.EnglishMonthName, firstSale.*
from DimDate d
outer apply 
(
  select top 1 * 
  from FactInternetSales s
  where s.OrderDate >= d.FullDateAlternateKey
    and s.OrderDate < dateadd(month,1,d.FullDateAlternateKey)
  order by s.OrderDate 
) firstSale
where d.DayNumberOfMonth = 1
and d.CalendarYear in (2012,2013)
order by d.FullDateAlternateKey

That's not a periodic snapshot fact, but the query is the same.

CodePudding user response：

Another approach that doesn't require a calendar is to use the ROW_NUMBER() window function to assign ordinal values to each row within each month, which is then used to filter out all but the first (ordinal = 1). Something like:

SELECT S.Date
FROM (
    SELECT *, Ordinal = ROW_NUMBER() OVER(PARTITION BY YEAR(Date), MONTH(Date) ORDER BY Date)
    FROM SnapshotDates
) S
WHERE S.Ordinal = 1
ORDER BY S.Date

The equivalent query using a CTE (Common Table Expression) is:

WITH CTE_SnapshotDates AS (
    SELECT *, Ordinal = ROW_NUMBER() OVER(PARTITION BY YEAR(Date), MONTH(Date) ORDER BY Date)
    FROM @SnapshotDates
)
SELECT S.Date
FROM CTE_SnapshotDates S
WHERE S.Ordinal = 1
ORDER BY S.Date

See this db<>fiddle for a demo.