--貎朱Μ
WITH Dumy(CF)
AS
(
SELECT 0 CF
UNION ALL
SELECT CF + 1 FROM Dumy
WHERE CF + 1 <= 12 --1豺襷螻壱.
),
MM(CF)
AS
(
SELECT CF FROM Dumy
)
SELECT
AccountID 螻
, YYYYMM + RIGHT('00' + BeginDD, 2)
, YYYYMM +
CASE
WHEN RIGHT(YYYYMM, 2) <> SUBSTRING(EndDT, 5, 2) THEN RIGHT('00' + EndDD, 2)
ELSE RIGHT(EndDT, 2)
END 譬襭
, SUBSTRING(BeginDT, 5, 2) 蟆一
, RIGHT(YYYYMM, 2)
FROM
(
SELECT
CONVERT(CHAR(6),DATEADD(mm, CF, BeginDT),112) YYYYMM
, CONVERT(VARCHAR(2),
CASE
WHEN CF = 0 THEN DATEPART(dd, BeginDT)
ELSE 1
END) BeginDD
, CONVERT(VARCHAR(2),
CASE
WHEN CONVERT(CHAR(6), EndDT, 112) =
CONVERT(CHAR(6),
DATEADD(dd,-1,
CONVERT(CHAR(6),
DATEADD(mm,CF + 1, BeginDT),112) + '01'
),112)
THEN DATEPART(dd,EndDT)
ELSE DATEPART(dd,
DATEADD(dd, -1,
CONVERT(CHAR(6),DATEADD(mm,CF + 1, BeginDT), 112)+'01'))
END) EndDD
, BeginDT
, EndDT
, AccountID
FROM #temp A CROSS JOIN (SELECT CF FROM MM) B
WHERE CF <= DATEDIFF(mm, BeginDT, EndDT)
) T
ORDER BY 螻,