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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 CTE to fill in missing month end values
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chanley54
Starting Member

6 Posts

Posted - 03/30/2013 :  14:35:02  Show Profile  Reply with Quote
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 - 03/30/2013 :  14:39:34  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 03/31/2013 :  03:20:26  Show Profile  Reply with Quote
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 - 03/31/2013 :  08:17:17  Show Profile  Reply with Quote
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 - 03/31/2013 :  08:22:58  Show Profile  Reply with Quote
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 - 03/31/2013 :  08:46:58  Show Profile  Reply with Quote
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 - 03/31/2013 :  09:58:31  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000