Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 CTE to fill in missing month end values

Author  Topic 

chanley54
Starting Member

6 Posts

Posted - 2013-03-30 : 14:35:02
Hi,

Forgive me if this has been asked before. I have a table that contains the following:

ACCT_NUM AMOUNT END_OF_MONTH DOS_DT_WID
17290 6390 20100930 20100930
17290 -6085 20101031 20100930
17290 0 20101130 20100930 missing
17290 -304 20101231 20100930
2222 6390 20100930 20100930
2222 -6085 20101031 20100930
2222 0 20101130 20100930 missing
2222 -304 20101231 20100930

I need to identify month end values and re-insert them back into the table.. The final result of this will be an aging/balance table.

Thanks and kind regards.

chanley54
Starting Member

6 Posts

Posted - 2013-03-30 : 14:39:34
Hi, I am looking for something similar to the below code that was posted before on this forum but instead of 7 day increments I need the month end value.

DECLARE @Tab TABLE
(
CustomerCode VARCHAR(10),
Active BIT,
CSR_Request BIT,
ActivityDate DATETIME
)

INSERT INTO @Tab VALUES
('C42000',1,0,'2010-05-07'),
('C42000',1,1,'2010-05-21'),
('C42000',0,1,'2010-06-04'),
('C43000',1,0,'2010-04-30'),
('C43000',1,1,'2010-05-07'),
('C43000',0,1,'2010-06-04')

--SELECT * FROM @Tab ORDER BY CustomerCode,ActivityDate


;WITH cteAll AS
(
SELECT CustomerCode,MIN(ActivityDate) ActivityDate,MAX(ActivityDate) MaxActivityDate
FROM @Tab
GROUP BY CustomerCode

UNION ALL

SELECT C.CustomerCode,DATEADD(DD,7,C.ActivityDate),C.MaxActivityDate
FROM cteAll C
WHERE DATEADD(DD,7,C.ActivityDate) < C.MaxActivityDate
),

CteMissing AS
(
SELECT C.CustomerCode,T.Active,T.CSR_Request,C.ActivityDate,DATEDIFF(WW,T.ActivityDate,C.ActivityDate) Diff
FROM cteAll C
CROSS JOIN @Tab T
WHERE C.CustomerCode = T.CustomerCode
AND DATEDIFF(WW,T.ActivityDate,C.ActivityDate) > 0
)


INSERT INTO @Tab
SELECT C.CustomerCode,C.Active,C.CSR_Request,C.ActivityDate
FROM CteMissing C
WHERE NOT EXISTS ( SELECT 1 FROM @Tab WHERE CustomerCode = C.CustomerCode AND ActivityDate = C.ActivityDate )
AND C.Diff = ( SELECT MIN(I.Diff) FROM CteMissing I WHERE C.CustomerCode = I.CustomerCode AND C.ActivityDate = I.ActivityDate )
ORDER BY CustomerCode

SELECT * FROM @Tab ORDER BY CustomerCode,ActivityDate
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-31 : 03:20:26
just replace


..
UNION ALL

SELECT C.CustomerCode,DATEADD(DD,7,C.ActivityDate),C.MaxActivityDate
FROM cteAll C
WHERE DATEADD(DD,7,C.ActivityDate) < C.MaxActivityDate



with


UNION ALL

SELECT C.CustomerCode,DATEADD(MM,1,C.ActivityDate)-1,C.MaxActivityDate
FROM cteAll C
WHERE DATEADD(MM,1,C.ActivityDate)-1 < C.MaxActivityDate




in above code and try

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

chanley54
Starting Member

6 Posts

Posted - 2013-03-31 : 08:17:17
Thank you for the reply. I made the change but the missing values are not all month end. To illustrate this, I have taken out some fields. The temp table has three month end values. I need to insert a fourth month end value to make it work as needed.

DECLARE @Tab TABLE
(
CustomerCode VARCHAR(10),
ActivityDate DATETIME
)

INSERT INTO @Tab VALUES
('C42000','2010-09-30'),
('C42000','2010-10-31'),
('C42000','2010-12-31')

--SELECT * FROM @Tab ORDER BY CustomerCode,ActivityDate


;WITH cteAll AS
(
SELECT CustomerCode,MIN(ActivityDate) ActivityDate,MAX(ActivityDate) MaxActivityDate
FROM @Tab
GROUP BY CustomerCode

UNION ALL

SELECT C.CustomerCode,DATEADD(MM,1,C.ActivityDate)-1,C.MaxActivityDate
FROM cteAll C
WHERE DATEADD(MM,1,C.ActivityDate)-1 < C.MaxActivityDate

),

CteMissing AS
(
SELECT
C.CustomerCode,
C.ActivityDate,
DATEDIFF(WW,T.ActivityDate,C.ActivityDate) Diff
FROM cteAll C
CROSS JOIN @Tab T
WHERE C.CustomerCode = T.CustomerCode
AND DATEDIFF(mm,T.ActivityDate,C.ActivityDate) > 0
)


INSERT INTO @Tab
SELECT
C.CustomerCode,
C.ActivityDate
FROM CteMissing C
WHERE NOT EXISTS ( SELECT 1 FROM @Tab WHERE CustomerCode = C.CustomerCode AND ActivityDate = C.ActivityDate )
AND C.Diff = ( SELECT MIN(I.Diff) FROM CteMissing I WHERE C.CustomerCode = I.CustomerCode AND C.ActivityDate = I.ActivityDate )
ORDER BY CustomerCode

SELECT * FROM @Tab ORDER BY CustomerCode,ActivityDate

CustomerCode ActivityDate
C42000 2010-09-30 00:00:00.000
C42000 2010-10-31 00:00:00.000
C42000 2010-11-28 00:00:00.000 -- need this value to be month end 2010-11-30
C42000 2010-12-27 00:00:00.000 -- not needed
C42000 2010-12-31 00:00:00.000

Thanks!


Go to Top of Page

chanley54
Starting Member

6 Posts

Posted - 2013-03-31 : 08:22:58
I also changed
AND DATEDIFF(ww,T.ActivityDate,C.ActivityDate) > 0
to
AND DATEDIFF(mm,T.ActivityDate,C.ActivityDate) > 0
Go to Top of Page

chanley54
Starting Member

6 Posts

Posted - 2013-03-31 : 08:46:58
Correction.. I made the following change and am still getting extra/wrong values...
CteMissing AS
(
SELECT
C.CustomerCode,
C.ActivityDate,
DATEDIFF(mm,T.ActivityDate,C.ActivityDate) Diff
FROM cteAll C
CROSS JOIN @Tab T
WHERE C.CustomerCode = T.CustomerCode
AND DATEDIFF(mm,T.ActivityDate,C.ActivityDate) > 0
)
Go to Top of Page

chanley54
Starting Member

6 Posts

Posted - 2013-03-31 : 09:58:31
RESOLVED!!!!

DECLARE @Tab TABLE
(
CustomerCode VARCHAR(10),
Active int,
CSR_Request int,
ActivityDate DATETIME
)

INSERT INTO @Tab VALUES
('C42000',1,0,'2010-09-30'),
('C42000',1,1,'2010-10-31'),
('C42000',1,2,'2010-12-31'),
('C42001',1,0,'2010-09-30'),
('C42001',1,1,'2010-10-31'),
('C42001',1,2,'2010-12-31')

--select * from @Tab

;WITH cteAll AS
(
SELECT
CustomerCode, -- Anchor value
MIN(ActivityDate) ActivityDate,
MAX(ActivityDate) MaxActivityDate
FROM @Tab
GROUP BY CustomerCode

UNION ALL

SELECT
C.CustomerCode,
DATEADD(MM,1,C.ActivityDate)-1,
C.MaxActivityDate
FROM cteAll C
WHERE DATEADD(MM,1,C.ActivityDate)-1 < C.MaxActivityDate

),


CteMissing AS
(
SELECT
C.CustomerCode,
T.Active,
T.CSR_Request,
-- Convert ActivityDate to month end value with 00:00:00.000 ending format.
DATEADD(D,DATEDIFF(D,0,DATEADD(S,-1,DATEADD(M,DATEDIFF(m,0,C.ActivityDate),0))),0) as ActivityDate,
DATEDIFF(mm,T.ActivityDate,C.ActivityDate) Diff
FROM cteAll C
CROSS JOIN @Tab T
WHERE C.CustomerCode = T.CustomerCode
AND DATEDIFF(mm,T.ActivityDate,C.ActivityDate) > 0
)

--select * from CteMissing order by ActivityDate

INSERT INTO @Tab
SELECT
C.CustomerCode,
C.Active,
C.CSR_Request,
C.ActivityDate
FROM CteMissing C
WHERE NOT EXISTS ( SELECT 1 FROM @Tab WHERE CustomerCode = C.CustomerCode AND ActivityDate = C.ActivityDate )
AND C.Diff = ( SELECT MIN(I.Diff) FROM CteMissing I WHERE C.CustomerCode = I.CustomerCode AND C.ActivityDate = I.ActivityDate )
ORDER BY CustomerCode

SELECT * FROM @Tab ORDER BY CustomerCode,ActivityDate
Go to Top of Page
   

- Advertisement -