Maciej Los
wizy@2020 написал:
Хороший Мацей.
Чего я действительно хочу добиться, так это получить следующие результаты
е.г
RecDate MonthNo
2020-01-01 1
2020-01-02 1
...
2020-01-25 1
2020-01-26 2
...
2020-02-25 2
2020-02-26 3
...
up to
2020-12-31 12
У меня есть таблица с более чем 20000 записями, и я хочу в любой момент получить правильный номер месяца. Это должно быть в состоянии работать с любым годом.
Этот запрос должен произвести набор из - за потребностей ОП:
--initial range
DECLARE @initstartdate DATE = CAST('2019-01-01' AS DATE); --define initial start date
DECLARE @initenddate DATE = CAST('2020-12-31' AS DATE); --define initial end date
--declare and fill-in reference table to store fiscal year details
DECLARE @reftable TABLE(refdayfrom INT, refmonthfrom INT, refdayto INT, refmonthto INT, fiscalmonth INT)
INSERT INTO @reftable(refdayfrom, refmonthfrom, refdayto, refmonthto, fiscalmonth)
VALUES(1, 1, 25, 1, 1),
(26, 1, 25, 2, 2),
(26, 2, 25, 3, 3),
(26, 3, 25, 4, 4),
(26, 4, 25, 5, 5),
(26, 5, 25, 6, 6),
(26, 6, 25, 7, 7),
(26, 7, 25, 8, 8),
(26, 8, 25, 9, 9),
(26, 9, 25, 10, 10),
(26, 10, 25, 11, 11),
(26, 11, 31, 12, 12)
--get all dates between initial start date and end date
;WITH alldates AS
(
SELECT @initstartdate RecDate
UNION ALL
SELECT DATEADD(DD, 1, RecDate) RecDate
FROM alldates
WHERE DATEADD(DD, 1, RecDate)<=@initenddate
),
--get fiscal year details for years between initial start date and end date
dateranges AS
(
--initial query
SELECT DATEFROMPARTS(YEAR(@initstartdate), refmonthfrom, refdayfrom ) startdate, DATEFROMPARTS(YEAR(@initstartdate), refmonthto, refdayto) enddate, fiscalmonth
FROM @reftable
UNION ALL
----recursive part
SELECT DATEFROMPARTS(YEAR(DATEADD(YY, 1, startdate)), MONTH(startdate), DAY(startdate)) startdate,
DATEFROMPARTS(YEAR(DATEADD(YY, 1, enddate)), MONTH(enddate), DAY(enddate)) enddate, fiscalmonth
FROM dateranges
WHERE DATEADD(YY, 1, enddate)<= @initenddate
)
-- mix all together ;)
SELECT ad.RecDate, dr.fiscalmonth MonthNo
FROM alldates ad INNER JOIN dateranges dr ON ad.RecDate BETWEEN dr.startdate AND dr.enddate
ORDER BY RecDate
OPTION (MAXRECURSION 0);
Вышеприведенный запрос производит (731 запись за 0,001 секунды):
RecDate MonthNo
2019-01-01 1
up to
2020-12-31 12
Примечание: Вы можете создать справочную таблицу как физическую таблицу (а не переменную /тип таблицы/). Тогда вы сможете использовать его в запросе, не создавая и не заполняя переменную.