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 missing17290 -304 20101231 20100930 2222 6390 20100930 20100930 2222 -6085 20101031 20100930 2222 0 20101130 20100930 missing2222 -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) MaxActivityDateFROM @TabGROUP BY CustomerCodeUNION ALLSELECT C.CustomerCode,DATEADD(DD,7,C.ActivityDate),C.MaxActivityDateFROM cteAll CWHERE 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) DiffFROM cteAll CCROSS JOIN @Tab TWHERE C.CustomerCode = T.CustomerCodeAND DATEDIFF(WW,T.ActivityDate,C.ActivityDate) > 0)INSERT INTO @TabSELECT C.CustomerCode,C.Active,C.CSR_Request,C.ActivityDateFROM CteMissing CWHERE 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 CustomerCodeSELECT * FROM @Tab ORDER BY CustomerCode,ActivityDate |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-31 : 03:20:26
|
just replace..UNION ALLSELECT C.CustomerCode,DATEADD(DD,7,C.ActivityDate),C.MaxActivityDateFROM cteAll CWHERE DATEADD(DD,7,C.ActivityDate) < C.MaxActivityDate withUNION ALLSELECT C.CustomerCode,DATEADD(MM,1,C.ActivityDate)-1,C.MaxActivityDateFROM cteAll CWHERE DATEADD(MM,1,C.ActivityDate)-1 < C.MaxActivityDate in above code and try------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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) MaxActivityDateFROM @TabGROUP BY CustomerCodeUNION ALLSELECT C.CustomerCode,DATEADD(MM,1,C.ActivityDate)-1,C.MaxActivityDateFROM cteAll CWHERE DATEADD(MM,1,C.ActivityDate)-1 < C.MaxActivityDate),CteMissing AS(SELECT C.CustomerCode,C.ActivityDate,DATEDIFF(WW,T.ActivityDate,C.ActivityDate) DiffFROM cteAll CCROSS JOIN @Tab TWHERE C.CustomerCode = T.CustomerCodeAND DATEDIFF(mm,T.ActivityDate,C.ActivityDate) > 0)INSERT INTO @TabSELECT C.CustomerCode,C.ActivityDateFROM CteMissing CWHERE 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 CustomerCodeSELECT * FROM @Tab ORDER BY CustomerCode,ActivityDateCustomerCode ActivityDateC42000 2010-09-30 00:00:00.000C42000 2010-10-31 00:00:00.000C42000 2010-11-28 00:00:00.000 -- need this value to be month end 2010-11-30C42000 2010-12-27 00:00:00.000 -- not neededC42000 2010-12-31 00:00:00.000Thanks! |
|
|
chanley54
Starting Member
6 Posts |
Posted - 2013-03-31 : 08:22:58
|
I also changedAND DATEDIFF(ww,T.ActivityDate,C.ActivityDate) > 0to AND DATEDIFF(mm,T.ActivityDate,C.ActivityDate) > 0 |
|
|
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) DiffFROM cteAll CCROSS JOIN @Tab TWHERE C.CustomerCode = T.CustomerCodeAND DATEDIFF(mm,T.ActivityDate,C.ActivityDate) > 0) |
|
|
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 valueMIN(ActivityDate) ActivityDate,MAX(ActivityDate) MaxActivityDateFROM @TabGROUP BY CustomerCodeUNION ALLSELECT C.CustomerCode,DATEADD(MM,1,C.ActivityDate)-1,C.MaxActivityDateFROM cteAll CWHERE 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) DiffFROM cteAll CCROSS JOIN @Tab TWHERE C.CustomerCode = T.CustomerCodeAND DATEDIFF(mm,T.ActivityDate,C.ActivityDate) > 0)--select * from CteMissing order by ActivityDateINSERT INTO @TabSELECT C.CustomerCode,C.Active,C.CSR_Request,C.ActivityDateFROM CteMissing CWHERE 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 CustomerCodeSELECT * FROM @Tab ORDER BY CustomerCode,ActivityDate |
|
|
|
|
|