Forum Discussion
DanielWillis
2 years agoValued Contributor
There are probably a lot of other ways to do it in SQL (perhaps others more optimal even) but one way is doing something like the following. You can join on OSPeriods instead of sticking your periods in the WHERE clauses. Note that I've hardcoded the starting date twice which you'd have to make dynamic. I just threw it together now but performance seems OK. Building a hard-coded query dynamically in OS may yield better results instead of running this query a lot of times at once.
WITH dateList(periodDate) AS (
SELECT CAST('01-JAN-22' AS datetime)
UNION ALL
SELECT DATEADD(month, -1, periodDate)
FROM dateList
WHERE periodDate > DATEADD(month,-11,CAST('01-JAN-22' AS datetime))
),
OSPeriods(OSPeriod) AS (
SELECT format(periodDate,'yyyy\MM') AS OSPeriod FROM dateList
)
select OSPeriod from OSPeriods;
Related Content
- 4 months ago
- 11 months ago
- 3 years ago
- 3 years ago