我有一个问题,我有一个月跳跃的数据。如您所见,记录日期总是一次跳跃1个月。
我需要为2017年1月1日生成一条新线路。基本上,每次有一个月的差距时,我都需要生成一条新线路。
我该怎么做?财政周只是一个月,月份的一周仅是一个月和年份。NUM正在运行一周,几年前开始并保持积累,我可以在一个不是问题的特殊桌子上查找,我只会使用一周,年,月来获取它并使用最小或最大该月的最后一周)
[Week Num] [Record Date] [Record Date] [Fiscal Year] [Fiscal Month] [Fiscal Week] [Customer #] [Group Description]
158 01/10/2016 NULL 2016 10 41 12345 aDrive
162 01/11/2016 NULL 2016 11 45 12345 aDrive
166 01/12/2016 NULL 2016 12 49 12345 aDrive
基本上,我不想生成十月,11月,我想生成最后的记录,其中没有后续记录,但也没有差距。
帮助我。
我试图理解您的问题。尝试以下查询,也许会为您提供帮助。但是我不知道它可能在SQLServer 2005中不起作用,因为我在这里使用了递归CTE。
DECLARE @ToDate date
SET @ToDate='20171201' -- we'll calculate new rows until this date
;WITH monthCTE AS(
-- get the last row from your table
SELECT [Week Num],[Record Date],[Fiscal Year],[Fiscal Month],[Fiscal Week],[Customer #],[Group Description]
FROM
(
SELECT TOP 1 *
FROM [Your data]
ORDER BY [Week Num] DESC
) q
UNION ALL
-- calculate the next row
SELECT
[Week Num]+DATEDIFF(WEEK,[Record Date],[New Record Date]),
[New Record Date],
YEAR([New Record Date]),
MONTH([New Record Date]),
CASE
WHEN MONTH([New Record Date])=1 THEN 1 -- return 1 if it is the first month in the year
ELSE [Fiscal Week]+DATEDIFF(WEEK,[Record Date],[New Record Date])
END,
[Customer #],
[Group Description]
FROM
(
SELECT
[Week Num],[Record Date],[Fiscal Year],[Fiscal Month],[Fiscal Week],[Customer #],[Group Description],
DATEADD(MONTH,1,[Record Date]) [New Record Date] -- add one month to the previous date
FROM monthCTE
WHERE [Record Date]<@ToDate
) q
)
SELECT [Week Num],[Record Date],[Fiscal Year],[Fiscal Month],[Fiscal Week],[Customer #],[Group Description]
FROM [Your data]
UNION -- this operation also exclude duplicate for the last row
SELECT [Week Num],[Record Date],[Fiscal Year],[Fiscal Month],[Fiscal Week],[Customer #],[Group Description]
FROM monthCTE
ORDER BY [Week Num]
如果我正确理解您并且有效,那么您需要检查所有表达式的准确性。