December 30, 2011 at 2:53 am
Hi mic.con87
If i understand the rules you require then the result set you posted is incorrect for ClaCaseID 28584 and NameID 580627. If you consider the last DueDate for June 2009 was the 27th and the status was a 4 then there was no status 9 for that month and therefore there werent three 9's in a row to qualify for a 'Y'
...The payment status value we would be interested in corresponds to the maximum date per month so in the above sample data for the month of May PaymentStatus = 9 and for June PaymentStatus = 4. ...
Or perhaps I've missed the boat completely? Could you please post all the rules you require in 1 post that would help me a lot as I'm having to read between all the previous posts to get the correct rules.
Regards,
William
December 30, 2011 at 3:08 am
So sorry 2009-06-27 should have a 9 not a 4.
Here are the rules, hope this helps and thanks for help, I really appreciate it!
Rules:
1) Compare IncidentDate and DueDate for each ClaCaseID. The DueDate records of interest are ONLY those 6 Months prior to IncidentDate.
2) After Isolating the relevant DueDate Records Find the MAX DueDate for each month. There may be multiple DueDate's per month with DIFFERENT PaymentStatus's. The DueDate we focus on is the Latest DueDate for the month and it's corresponding PaymentStatus.
3) Now search for PaymentStatus = 9 that is repeated consecutively for THREE different months but which fall in the criteria of point 1)
4) If there are 3 or more consecutive PaymentStatus = 9 then FLAG and in column Missing place 'Y' ELSE 'N'
5) Note there can be multiple ClaCAseID's for a single NameID's
Here is the corrected sample date
--DROP TABLE #ClaCases
DROP TABLE #ClaCases
CREATE TABLE #ClaCases (ClaCaseID INT,NameID int, IncidentDate Date )
INSERT INTO #ClaCases VALUES (4370,375454,'2008-09-01')
INSERT INTO #ClaCases VALUES (13245,964085,'2009-02-20')
INSERT INTO #ClaCases VALUES (41727,964085,'2009-12-11')
INSERT INTO #ClaCases VALUES (206308,964085,'2011-03-31')
INSERT INTO #ClaCases VALUES (28584,580627,'2009-08-02')
INSERT INTO #ClaCases VALUES (37430,580627,'2009-11-11')
select * from #ClaCases
--DROP TABLE #AccPayments
CREATE TABLE #AccPayments (PaymentStatus INT,ReceiverID int, DueDate Date )
INSERT INTO #AccPayments VALUES (4,375454,'2008-05-17')
INSERT INTO #AccPayments VALUES (4,375454,'2008-05-19')
INSERT INTO #AccPayments VALUES (4,375454,'2008-06-04')
INSERT INTO #AccPayments VALUES (4,375454,'2008-06-30')
INSERT INTO #AccPayments VALUES (4,375454,'2008-07-28')
INSERT INTO #AccPayments VALUES (4,375454,'2008-08-28')
INSERT INTO #AccPayments VALUES (4,375454,'2008-09-29')
INSERT INTO #AccPayments VALUES (4,375454,'2008-10-13')
INSERT INTO #AccPayments VALUES (4,375454,'2008-10-28')
INSERT INTO #AccPayments VALUES (4,375454,'2008-11-28')
INSERT INTO #AccPayments VALUES (4,375454,'2008-12-29')
INSERT INTO #AccPayments VALUES (4,375454,'2009-01-28')
INSERT INTO #AccPayments VALUES (4,964085,'2008-10-06')
INSERT INTO #AccPayments VALUES (4,964085,'2008-11-05')
INSERT INTO #AccPayments VALUES (4,964085,'2008-12-05')
INSERT INTO #AccPayments VALUES (4,964085,'2009-01-05')
INSERT INTO #AccPayments VALUES (4,964085,'2009-02-05')
INSERT INTO #AccPayments VALUES (4,964085,'2009-03-05')
INSERT INTO #AccPayments VALUES (4,964085,'2009-04-06')
INSERT INTO #AccPayments VALUES (4,964085,'2009-05-05')
INSERT INTO #AccPayments VALUES (4,964085,'2009-06-05')
INSERT INTO #AccPayments VALUES (4,964085,'2009-07-06')
INSERT INTO #AccPayments VALUES (4,964085,'2009-08-05')
INSERT INTO #AccPayments VALUES (4,964085,'2009-09-05')
INSERT INTO #AccPayments VALUES (4,964085,'2009-10-05')
INSERT INTO #AccPayments VALUES (9,964085,'2009-11-05')
INSERT INTO #AccPayments VALUES (9,964085,'2009-11-21')
INSERT INTO #AccPayments VALUES (4,964085,'2009-12-05')
INSERT INTO #AccPayments VALUES (4,964085,'2010-01-05')
INSERT INTO #AccPayments VALUES (9,964085,'2010-02-05')
INSERT INTO #AccPayments VALUES (4,964085,'2010-02-22')
INSERT INTO #AccPayments VALUES (4,964085,'2010-03-05')
INSERT INTO #AccPayments VALUES (4,964085,'2010-04-06')
INSERT INTO #AccPayments VALUES (4,964085,'2010-05-05')
INSERT INTO #AccPayments VALUES (4,964085,'2010-06-05')
INSERT INTO #AccPayments VALUES (4,964085,'2010-07-05')
INSERT INTO #AccPayments VALUES (4,964085,'2010-07-29')
INSERT INTO #AccPayments VALUES (4,964085,'2010-08-05')
INSERT INTO #AccPayments VALUES (4,964085,'2010-09-06')
INSERT INTO #AccPayments VALUES (4,964085,'2010-10-05')
INSERT INTO #AccPayments VALUES (4,964085,'2010-11-05')
INSERT INTO #AccPayments VALUES (4,964085,'2010-11-04')
INSERT INTO #AccPayments VALUES (4,964085,'2010-12-06')
INSERT INTO #AccPayments VALUES (4,964085,'2011-01-05')
INSERT INTO #AccPayments VALUES (4,964085,'2011-02-05')
INSERT INTO #AccPayments VALUES (4,964085,'2011-03-05')
INSERT INTO #AccPayments VALUES (4,964085,'2011-04-05')
INSERT INTO #AccPayments VALUES (4,964085,'2011-05-05')
INSERT INTO #AccPayments VALUES (4,964085,'2011-06-06')
INSERT INTO #AccPayments VALUES (4,964085,'2011-06-29')
INSERT INTO #AccPayments VALUES (4,964085,'2011-08-01')
INSERT INTO #AccPayments VALUES (4,580627,'2008-07-28')
INSERT INTO #AccPayments VALUES (9,580627,'2008-07-28')
INSERT INTO #AccPayments VALUES (4,580627,'2008-09-12')
INSERT INTO #AccPayments VALUES (4,580627,'2008-09-27')
INSERT INTO #AccPayments VALUES (4,580627,'2008-10-27')
INSERT INTO #AccPayments VALUES (4,580627,'2008-11-27')
INSERT INTO #AccPayments VALUES (4,580627,'2008-12-10')
INSERT INTO #AccPayments VALUES (9,580627,'2008-12-27')
INSERT INTO #AccPayments VALUES (9,580627,'2009-01-12')
INSERT INTO #AccPayments VALUES (4,580627,'2009-01-27')
INSERT INTO #AccPayments VALUES (4,580627,'2009-02-27')
INSERT INTO #AccPayments VALUES (4,580627,'2009-03-27')
INSERT INTO #AccPayments VALUES (9,580627,'2009-04-28')
INSERT INTO #AccPayments VALUES (9,580627,'2009-05-13')
INSERT INTO #AccPayments VALUES (9,580627,'2009-05-27')
INSERT INTO #AccPayments VALUES (9,580627,'2009-06-12')
INSERT INTO #AccPayments VALUES (9,580627,'2009-06-27')
INSERT INTO #AccPayments VALUES (9,580627,'2009-07-27')
INSERT INTO #AccPayments VALUES (4,580627,'2009-08-12')
INSERT INTO #AccPayments VALUES (4,580627,'2009-08-27')
INSERT INTO #AccPayments VALUES (9,580627,'2009-09-28')
INSERT INTO #AccPayments VALUES (9,580627,'2009-10-13')
INSERT INTO #AccPayments VALUES (9,580627,'2009-10-27')
INSERT INTO #AccPayments VALUES (4,580627,'2009-11-12')
INSERT INTO #AccPayments VALUES (9,580627,'2009-11-27')
INSERT INTO #AccPayments VALUES (9,580627,'2009-12-14')
INSERT INTO #AccPayments VALUES (4,580627,'2009-12-17')
INSERT INTO #AccPayments VALUES (4,580627,'2009-12-23')
INSERT INTO #AccPayments VALUES (4,580627,'2010-01-27')
INSERT INTO #AccPayments VALUES (4,580627,'2010-02-27')
INSERT INTO #AccPayments VALUES (4,580627,'2010-03-27')
INSERT INTO #AccPayments VALUES (4,580627,'2010-04-28')
INSERT INTO #AccPayments VALUES (4,580627,'2010-05-27')
INSERT INTO #AccPayments VALUES (4,580627,'2010-06-28')
INSERT INTO #AccPayments VALUES (4,580627,'2010-07-27')
INSERT INTO #AccPayments VALUES (4,580627,'2010-08-27')
INSERT INTO #AccPayments VALUES (4,580627,'2010-09-27')
INSERT INTO #AccPayments VALUES (4,580627,'2010-10-27')
INSERT INTO #AccPayments VALUES (4,580627,'2010-11-27')
INSERT INTO #AccPayments VALUES (4,580627,'2010-12-23')
INSERT INTO #AccPayments VALUES (4,580627,'2011-01-27')
INSERT INTO #AccPayments VALUES (4,580627,'2011-02-28')
INSERT INTO #AccPayments VALUES (4,580627,'2011-03-28')
INSERT INTO #AccPayments VALUES (4,580627,'2011-04-28')
INSERT INTO #AccPayments VALUES (4,580627,'2011-05-27')
INSERT INTO #AccPayments VALUES (4,580627,'2011-06-27')
INSERT INTO #AccPayments VALUES (4,580627,'2011-07-27')
INSERT INTO #AccPayments VALUES (4,580627,'2011-07-27')
INSERT INTO #AccPayments VALUES (4,580627,'2011-08-27')
INSERT INTO #AccPayments VALUES (4,580627,'2011-09-27')
INSERT INTO #AccPayments VALUES (4,580627,'2011-10-27')
INSERT INTO #AccPayments VALUES (4,580627,'2011-11-28')
INSERT INTO #AccPayments VALUES (4,580627,'2011-12-27')
select * from #AccPayments
--Drop Table #Result
CREATE TABLE #Result (ClaCaseID INT,ReceiverID int, NameID int, IncidentDate Date, Missed varchar(25) )
INSERT INTO #Result VALUES (4370,375454,375454,'2008-09-01','N')
INSERT INTO #Result VALUES (13245,964085,964085,'2009-02-20','N')
INSERT INTO #Result VALUES (41727,964085,964085,'2009-12-11','N')
INSERT INTO #Result VALUES (206308,964085,964085,'2011-03-31','N')
INSERT INTO #Result VALUES (28584,580627,580627,'2009-08-02','Y')
INSERT INTO #Result VALUES (37430,580627,580627,'2009-11-11','N')
Select * from #Result
December 30, 2011 at 3:09 am
I used the query below to view the data. You can tell based on the joins I've done if i've understood the rules correctly ( OR NOT! :-D)
SELECT ClaCaseID
, NameID
, IncidentDate
, DueDate
, PaymentStatus
, YearID
, MonthID
, ROW_NUMBER()OVER(PARTITION BY ClaCaseID, NameID, IncidentDate ORDER BY YearID, MonthID) AS ROWID
FROM #ClaCases a
INNER JOIN #AccPayments b
ON a.NameID = b.ReceiverID
AND b.DueDate <= a.IncidentDate --DueDate prior to Incident Date
AND b.DueDate >= DATEADD(M, -6, a.IncidentDate) --Only check last 6 months
INNER JOIN (
SELECT ReceiverID
, YEAR(DueDate) AS YearID
, MONTH(DueDate) AS MonthID
, MAX(DueDate) AS MaxDate
FROM #AccPayments
GROUP BY ReceiverID
, YEAR(DueDate)
, MONTH(DueDate)
) c
ON b.DueDate = c.MaxDate --Only use the last (max) DueDate for the month
AND b.ReceiverID = c.ReceiverID
December 30, 2011 at 3:18 am
INSERT INTO #Result VALUES (4370,375454,375454,'2008-09-01','N')
INSERT INTO #Result VALUES (13245,964085,964085,'2009-02-20','N')
INSERT INTO #Result VALUES (28584,580627,580627,'2009-08-02','Y')
INSERT INTO #Result VALUES (37430,580627,580627,'2009-11-11','N') -- incorrect
INSERT INTO #Result VALUES (41727,964085,964085,'2009-12-11','N')
INSERT INTO #Result VALUES (206308,964085,964085,'2011-03-31','N')
Select * from #Result
;WITH OrderedData AS (
SELECT
a.*,
c.ClaCaseID,
c.NameID,
c.IncidentDate,
rn = ROW_NUMBER() OVER(PARTITION BY c.ClaCaseID, c.NameID ORDER BY a.DueDate),
Relevant = CASE WHEN a.DueDate BETWEEN DATEADD(month,-6,c.IncidentDate) AND c.IncidentDate THEN 1 ELSE 0 END
FROM #AccPayments a
INNER JOIN #ClaCases c ON c.NameID = a.ReceiverID
) SELECT
o1.ClaCaseID,
o1.ReceiverID,
o1.NameID,
o1.IncidentDate,
Missed = MAX(CASE
WHEN o3.ReceiverID IS NOT NULL AND o1.Relevant = 1 THEN 'Y'
ELSE 'N' END)
FROM OrderedData o1
LEFT JOIN OrderedData o2
ON o2.ReceiverID = o1.ReceiverID AND o2.rn = o1.rn-1 AND o2.PaymentStatus = 9
LEFT JOIN OrderedData o3
ON o3.ReceiverID = o2.ReceiverID AND o3.rn = o2.rn-1 AND o3.PaymentStatus = 9
WHERE o1.PaymentStatus = 9
GROUP BY o1.ClaCaseID, o1.ReceiverID, o1.NameID, o1.IncidentDate
ORDER BY o1.ClaCaseID, o1.ReceiverID, o1.NameID, o1.IncidentDate
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 30, 2011 at 3:38 am
Ok, here's my solution, hope it helps (and that it produces the correct results 😉 )
WITH cte_Payments AS (
SELECT ClaCaseID
, NameID
, IncidentDate
, PaymentStatus = CASE WHEN PaymentStatus = 4 THEN 0 ELSE PaymentStatus END
, ROW_NUMBER()OVER(PARTITION BY ClaCaseID, NameID, IncidentDate ORDER BY YearID, MonthID) AS ROWID
FROM #ClaCases a
INNER JOIN #AccPayments b
ON a.NameID = b.ReceiverID
AND b.DueDate <= a.IncidentDate --DueDate prior to Incident Date
AND b.DueDate >= DATEADD(M, -6, a.IncidentDate) --Only check last 6 months
INNER JOIN (
SELECT ReceiverID
, YEAR(DueDate) AS YearID
, MONTH(DueDate) AS MonthID
, MAX(DueDate) AS MaxDate
FROM #AccPayments
GROUP BY ReceiverID
, YEAR(DueDate)
, MONTH(DueDate)
) c
ON b.DueDate = c.MaxDate --Only use the last (max) DueDate for the month
AND b.ReceiverID = c.ReceiverID
)
SELECT ClaCaseID, NameID, IncidentDate, Missed = CASE WHEN SUM(PaymentStatus) >= 27 THEN 'Y' ELSE 'N' END
FROM cte_Payments a
GROUP BY ClaCaseID, NameID, IncidentDate;
Have a happy new years eve!
December 30, 2011 at 3:44 am
Oh dear, I've just realised my solution doesn't cater for 3 consecutive months 🙁
I'll relook at it!
December 30, 2011 at 3:59 am
Ok, I've found a solution that works for 3 consecutive months:
WITH cte_Payments AS (
SELECT ClaCaseID
, NameID
, IncidentDate
, YearID
, MonthID
, PaymentStatus
, ROW_NUMBER()OVER(PARTITION BY ClaCaseID, NameID, IncidentDate, PaymentStatus ORDER BY YearID, MonthID) AS ROWID
FROM (
SELECT ClaCaseID
, NameID
, IncidentDate
, YearID
, MonthID
, PaymentStatus = CASE WHEN PaymentStatus = 4 THEN 0 ELSE PaymentStatus END
FROM #ClaCases a
INNER JOIN #AccPayments b
ON a.NameID = b.ReceiverID
AND b.DueDate <= a.IncidentDate --DueDate prior to Incident Date
AND b.DueDate >= DATEADD(M, -6, a.IncidentDate) --Only check last 6 months
INNER JOIN (
SELECT ReceiverID
, YEAR(DueDate) AS YearID
, MONTH(DueDate) AS MonthID
, MAX(DueDate) AS MaxDate
FROM #AccPayments
GROUP BY ReceiverID
, YEAR(DueDate)
, MONTH(DueDate)
) c
ON b.DueDate = c.MaxDate --Only use the last (max) DueDate for the month
AND b.ReceiverID = c.ReceiverID
) a
)
SELECT ClaCaseID, NameID, IncidentDate, Missed = MAX(CASE WHEN (ROWID) = 3 AND PaymentStatus = 9 THEN 'Y' ELSE 'N' END)
FROM cte_Payments a
GROUP BY ClaCaseID, NameID, IncidentDate--, ROWID, PaymentStatus;
But, how will this solution work over a 500000 row table? I'm not convinced. :unsure:
December 30, 2011 at 4:01 am
@ChrisM@home
Your solution is almost correct however these conditions are not met:
2) After Isolating the relevant DueDate Records Find the MAX DueDate for each month. There may be multiple DueDate's per month with DIFFERENT PaymentStatus's. The DueDate we focus on is the Latest DueDate for the month and it's corresponding PaymentStatus.
3) Now search for PaymentStatus = 9 that is repeated consecutively for THREE different months
Basically it counts 3 consecutive records but may count TWO PaymentStatus = 9 for two different DueDates in May.
ie
9,2008-04-01
9,2008-05-01
9,2008-05-15
Your query has a similar problem but seems to miss quite a few cases
December 30, 2011 at 4:02 am
As for your earlier query:
Here is the result I received:
CREATE TABLE #Result (ClaCaseID INT,ReceiverID int,IncidentDate Date, DueDate Date, PaymentStatus int,YearID int, MonthID int, ROWID int )
INSERT INTO #Result VALUES (2040,146587,'2008-05-14','2008-03-25',9,2008,3,1)
INSERT INTO #Result VALUES (2040,146587,'2008-05-14','2008-04-25',4,2008,4,2)
INSERT INTO #Result VALUES (1983,147265,'2008-04-25','2008-02-28',4,2008,2,1)
INSERT INTO #Result VALUES (1983,147265,'2008-04-25','2008-03-21',4,2008,3,2)
INSERT INTO #Result VALUES (1983,147265,'2008-04-25','2008-04-18',4,2008,4,3)
INSERT INTO #Result VALUES (2113,147842,'2008-05-10','2008-02-28',9,2008,2,1)
INSERT INTO #Result VALUES (2113,147842,'2008-05-10','2008-03-27',9,2008,3,2)
INSERT INTO #Result VALUES (2113,147842,'2008-05-10','2008-04-23',4,2008,4,3)
INSERT INTO #Result VALUES (1910,148539,'2008-04-27','2008-04-01',4,2008,4,1)
INSERT INTO #Result VALUES (2049,151157,'2008-04-12','2008-02-26',4,2008,2,1)
INSERT INTO #Result VALUES (2049,151157,'2008-04-12','2008-02-26',4,2008,2,2)
INSERT INTO #Result VALUES (2049,151157,'2008-04-12','2008-03-17',4,2008,3,3)
INSERT INTO #Result VALUES (2050,152706,'2008-05-15','2008-03-28',998,2008,3,1)
INSERT INTO #Result VALUES (2050,152706,'2008-05-15','2008-04-29',4,2008,4,2)
INSERT INTO #Result VALUES (2050,152706,'2008-05-15','2008-04-29',4,2008,4,3)
INSERT INTO #Result VALUES (1918,155047,'2008-04-29','2008-02-27',4,2008,2,1)
INSERT INTO #Result VALUES (1918,155047,'2008-04-29','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (1950,155136,'2008-05-4','2008-02-27',4,2008,2,1)
INSERT INTO #Result VALUES (1950,155136,'2008-05-4','2008-03-27',4,2008,3,2)
INSERT INTO #Result VALUES (1950,155136,'2008-05-4','2008-04-28',4,2008,4,3)
INSERT INTO #Result VALUES (1884,156426,'2008-04-22','2008-04-7',4,2008,4,1)
INSERT INTO #Result VALUES (2137,156671,'2008-05-15','2008-03-3',4,2008,3,1)
INSERT INTO #Result VALUES (2137,156671,'2008-05-15','2008-04-25',4,2008,4,2)
INSERT INTO #Result VALUES (1904,157171,'2008-04-22','2008-03-1',4,2008,3,1)
INSERT INTO #Result VALUES (1904,157171,'2008-04-22','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (1905,157171,'2008-04-22','2008-03-1',4,2008,3,1)
INSERT INTO #Result VALUES (1905,157171,'2008-04-22','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (1954,158127,'2008-04-30','2008-03-3',4,2008,3,1)
INSERT INTO #Result VALUES (1954,158127,'2008-04-30','2008-04-25',4,2008,4,2)
INSERT INTO #Result VALUES (1967,158194,'2008-05-8','2008-04-25',4,2008,4,1)
INSERT INTO #Result VALUES (1949,158585,'2008-05-5','2008-03-23',4,2008,3,1)
INSERT INTO #Result VALUES (1949,158585,'2008-05-5','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (1949,158585,'2008-05-5','2008-05-1',4,2008,5,3)
INSERT INTO #Result VALUES (2163,158739,'2008-05-22','2008-04-29',4,2008,4,1)
INSERT INTO #Result VALUES (1911,160105,'2008-04-26','2008-02-28',4,2008,2,1)
INSERT INTO #Result VALUES (1911,160105,'2008-04-26','2008-03-31',4,2008,3,2)
INSERT INTO #Result VALUES (1911,160105,'2008-04-26','2008-04-2',4,2008,4,3)
INSERT INTO #Result VALUES (1957,161020,'2008-05-5','2008-02-28',9,2008,2,1)
INSERT INTO #Result VALUES (1957,161020,'2008-05-5','2008-03-27',4,2008,3,2)
INSERT INTO #Result VALUES (1957,161020,'2008-05-5','2008-04-28',4,2008,4,3)
INSERT INTO #Result VALUES (1932,161128,'2008-04-26','2008-02-29',4,2008,2,1)
INSERT INTO #Result VALUES (1932,161128,'2008-04-26','2008-03-25',4,2008,3,2)
INSERT INTO #Result VALUES (1932,161128,'2008-04-26','2008-04-1',4,2008,4,3)
INSERT INTO #Result VALUES (2106,161942,'2008-05-21','2008-03-17',4,2008,3,1)
INSERT INTO #Result VALUES (2106,161942,'2008-05-21','2008-04-2',4,2008,4,2)
INSERT INTO #Result VALUES (2106,161942,'2008-05-21','2008-05-1',4,2008,5,3)
INSERT INTO #Result VALUES (2310,162248,'2008-06-2','2008-03-5',4,2008,3,1)
INSERT INTO #Result VALUES (2310,162248,'2008-06-2','2008-04-7',4,2008,4,2)
INSERT INTO #Result VALUES (2310,162248,'2008-06-2','2008-05-5',4,2008,5,3)
INSERT INTO #Result VALUES (1922,163260,'2008-04-29','2008-04-26',4,2008,4,1)
INSERT INTO #Result VALUES (2036,165875,'2008-05-13','2008-04-1',4,2008,4,1)
INSERT INTO #Result VALUES (2036,165875,'2008-05-13','2008-05-1',4,2008,5,2)
INSERT INTO #Result VALUES (2277,166049,'2008-05-30','2008-03-6',4,2008,3,1)
INSERT INTO #Result VALUES (2277,166049,'2008-05-30','2008-04-28',9,2008,4,2)
INSERT INTO #Result VALUES (2277,166049,'2008-05-30','2008-05-27',9,2008,5,3)
INSERT INTO #Result VALUES (2147,167533,'2008-05-25','2008-03-28',4,2008,3,1)
INSERT INTO #Result VALUES (2147,167533,'2008-05-25','2008-04-29',4,2008,4,2)
INSERT INTO #Result VALUES (2308,168394,'2008-05-30','2008-04-1',9,2008,4,1)
INSERT INTO #Result VALUES (2308,168394,'2008-05-30','2008-05-28',4,2008,5,2)
INSERT INTO #Result VALUES (1903,171689,'2008-04-25','2008-04-3',4,2008,4,1)
INSERT INTO #Result VALUES (2156,172405,'2008-05-9','2008-03-14',9,2008,3,1)
INSERT INTO #Result VALUES (2156,172405,'2008-05-9','2008-04-25',9,2008,4,2)
INSERT INTO #Result VALUES (2126,173142,'2008-05-23','2008-03-28',4,2008,3,1)
INSERT INTO #Result VALUES (2126,173142,'2008-05-23','2008-04-24',4,2008,4,2)
INSERT INTO #Result VALUES (2065,173169,'2008-05-19','2008-03-25',9,2008,3,1)
INSERT INTO #Result VALUES (2065,173169,'2008-05-19','2008-04-25',9,2008,4,2)
INSERT INTO #Result VALUES (2230,173614,'2008-05-27','2008-04-1',4,2008,4,1)
INSERT INTO #Result VALUES (2230,173614,'2008-05-27','2008-05-1',4,2008,5,2)
INSERT INTO #Result VALUES (1913,176362,'2008-04-27','2008-03-31',4,2008,3,1)
INSERT INTO #Result VALUES (2315,176893,'2008-06-2','2008-03-27',4,2008,3,1)
INSERT INTO #Result VALUES (2315,176893,'2008-06-2','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (2315,176893,'2008-06-2','2008-05-1',4,2008,5,3)
INSERT INTO #Result VALUES (2268,177091,'2008-05-29','2008-03-30',4,2008,3,1)
INSERT INTO #Result VALUES (2268,177091,'2008-05-29','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (2268,177091,'2008-05-29','2008-05-6',4,2008,5,3)
INSERT INTO #Result VALUES (2268,177091,'2008-05-29','2008-05-6',4,2008,5,4)
INSERT INTO #Result VALUES (2027,177334,'2008-05-12','2008-03-14',9,2008,3,1)
INSERT INTO #Result VALUES (2027,177334,'2008-05-12','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (2006,177814,'2008-05-11','2008-04-16',4,2008,4,1)
INSERT INTO #Result VALUES (2066,179566,'2008-05-18','2008-04-1',9,2008,4,1)
INSERT INTO #Result VALUES (1926,180084,'2008-04-30','2008-03-31',4,2008,3,1)
INSERT INTO #Result VALUES (1926,180084,'2008-04-30','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (1964,182060,'2008-04-14','2008-03-10',4,2008,3,1)
INSERT INTO #Result VALUES (1964,182060,'2008-04-14','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (1988,182176,'2008-05-8','2008-04-29',4,2008,4,1)
INSERT INTO #Result VALUES (2125,187585,'2008-05-22','2008-04-1',4,2008,4,1)
INSERT INTO #Result VALUES (2125,187585,'2008-05-22','2008-05-1',4,2008,5,2)
INSERT INTO #Result VALUES (1909,187666,'2008-04-26','2008-03-31',4,2008,3,1)
INSERT INTO #Result VALUES (1951,187739,'2008-05-4','2008-03-13',4,2008,3,1)
INSERT INTO #Result VALUES (1951,187739,'2008-05-4','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (1951,187739,'2008-05-4','2008-05-1',4,2008,5,3)
INSERT INTO #Result VALUES (2110,193623,'2008-05-22','2008-04-26',4,2008,4,1)
INSERT INTO #Result VALUES (1883,193917,'2008-04-22','2008-03-17',4,2008,3,1)
INSERT INTO #Result VALUES (1883,193917,'2008-04-22','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (2129,195219,'2008-05-22','2008-03-27',4,2008,3,1)
INSERT INTO #Result VALUES (2129,195219,'2008-05-22','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (2129,195219,'2008-05-22','2008-05-1',4,2008,5,3)
INSERT INTO #Result VALUES (2280,195847,'2008-05-31','2008-04-1',4,2008,4,1)
INSERT INTO #Result VALUES (2280,195847,'2008-05-31','2008-05-1',4,2008,5,2)
INSERT INTO #Result VALUES (2290,196088,'2008-05-31','2008-04-30',4,2008,4,1)
INSERT INTO #Result VALUES (1917,197912,'2008-04-16','2008-03-30',4,2008,3,1)
INSERT INTO #Result VALUES (1947,197955,'2008-05-5','2008-04-1',9,2008,4,1)
INSERT INTO #Result VALUES (2160,200395,'2008-05-26','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2005,202304,'2008-05-11','2008-04-1',9,2008,4,1)
INSERT INTO #Result VALUES (2005,202304,'2008-05-11','2008-05-1',4,2008,5,2)
INSERT INTO #Result VALUES (1906,202819,'2008-04-25','2008-03-17',9,2008,3,1)
INSERT INTO #Result VALUES (1906,202819,'2008-04-25','2008-04-25',4,2008,4,2)
INSERT INTO #Result VALUES (1920,202991,'2008-04-29','2008-04-26',4,2008,4,1)
INSERT INTO #Result VALUES (2103,205001,'2008-05-21','2008-04-15',4,2008,4,1)
INSERT INTO #Result VALUES (2103,205001,'2008-05-21','2008-05-1',4,2008,5,2)
INSERT INTO #Result VALUES (2133,205486,'2008-05-17','2008-03-17',4,2008,3,1)
INSERT INTO #Result VALUES (2133,205486,'2008-05-17','2008-04-2',4,2008,4,2)
INSERT INTO #Result VALUES (2133,205486,'2008-05-17','2008-05-1',4,2008,5,3)
INSERT INTO #Result VALUES (2193,206938,'2008-05-9','2008-03-20',4,2008,3,1)
INSERT INTO #Result VALUES (2193,206938,'2008-05-9','2008-04-15',4,2008,4,2)
INSERT INTO #Result VALUES (2194,206938,'2008-05-9','2008-03-20',4,2008,3,1)
INSERT INTO #Result VALUES (2194,206938,'2008-05-9','2008-04-15',4,2008,4,2)
INSERT INTO #Result VALUES (2086,207446,'2008-05-21','2008-05-20',4,2008,5,1)
INSERT INTO #Result VALUES (1908,207969,'2008-04-16','2008-03-17',9,2008,3,1)
INSERT INTO #Result VALUES (1955,209066,'2008-05-5','2008-04-21',4,2008,4,1)
INSERT INTO #Result VALUES (2025,210021,'2008-04-20','2008-03-25',4,2008,3,1)
INSERT INTO #Result VALUES (1991,211028,'2008-05-1','2008-04-2',4,2008,4,1)
INSERT INTO #Result VALUES (1991,211028,'2008-05-1','2008-04-2',9,2008,4,2)
INSERT INTO #Result VALUES (2067,211737,'2008-05-17','2008-04-26',4,2008,4,1)
INSERT INTO #Result VALUES (1887,213470,'2008-04-22','2008-04-1',4,2008,4,1)
INSERT INTO #Result VALUES (1914,214396,'2008-04-28','2008-03-25',4,2008,3,1)
INSERT INTO #Result VALUES (1914,214396,'2008-04-28','2008-04-25',4,2008,4,2)
INSERT INTO #Result VALUES (2142,214442,'2008-05-25','2008-04-1',4,2008,4,1)
INSERT INTO #Result VALUES (2142,214442,'2008-05-25','2008-05-2',4,2008,5,2)
INSERT INTO #Result VALUES (2289,216933,'2008-06-1','2008-04-25',4,2008,4,1)
INSERT INTO #Result VALUES (2289,216933,'2008-06-1','2008-05-26',4,2008,5,2)
INSERT INTO #Result VALUES (2141,218979,'2008-05-24','2008-04-25',4,2008,4,1)
INSERT INTO #Result VALUES (2190,219193,'2008-05-26','2008-03-20',4,2008,3,1)
INSERT INTO #Result VALUES (2190,219193,'2008-05-26','2008-04-3',4,2008,4,2)
INSERT INTO #Result VALUES (2190,219193,'2008-05-26','2008-05-3',4,2008,5,3)
INSERT INTO #Result VALUES (2282,220035,'2008-06-1','2008-03-20',4,2008,3,1)
INSERT INTO #Result VALUES (2282,220035,'2008-06-1','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (2282,220035,'2008-06-1','2008-05-1',4,2008,5,3)
INSERT INTO #Result VALUES (2157,220965,'2008-05-26','2008-04-21',4,2008,4,1)
INSERT INTO #Result VALUES (2157,220965,'2008-05-26','2008-05-20',4,2008,5,2)
INSERT INTO #Result VALUES (2054,221589,'2008-05-16','2008-04-1',4,2008,4,1)
INSERT INTO #Result VALUES (2054,221589,'2008-05-16','2008-05-1',4,2008,5,2)
INSERT INTO #Result VALUES (2064,221694,'2008-05-18','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2079,222585,'2008-05-18','2008-03-23',4,2008,3,1)
INSERT INTO #Result VALUES (2079,222585,'2008-05-18','2008-04-15',9,2008,4,2)
INSERT INTO #Result VALUES (2079,222585,'2008-05-18','2008-05-15',4,2008,5,3)
INSERT INTO #Result VALUES (2056,222852,'2008-05-16','2008-04-28',4,2008,4,1)
INSERT INTO #Result VALUES (2104,223239,'2008-05-22','2008-04-28',4,2008,4,1)
INSERT INTO #Result VALUES (2044,223506,'2008-05-14','2008-04-29',4,2008,4,1)
INSERT INTO #Result VALUES (1984,224480,'2008-05-3','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2150,225614,'2008-05-25','2008-03-31',4,2008,3,1)
INSERT INTO #Result VALUES (2150,225614,'2008-05-25','2008-04-16',4,2008,4,2)
INSERT INTO #Result VALUES (2150,225614,'2008-05-25','2008-05-8',4,2008,5,3)
INSERT INTO #Result VALUES (1928,226580,'2008-05-3','2008-04-1',4,2008,4,1)
INSERT INTO #Result VALUES (1928,226580,'2008-05-3','2008-05-1',4,2008,5,2)
INSERT INTO #Result VALUES (1962,226912,'2008-05-7','2008-04-29',4,2008,4,1)
INSERT INTO #Result VALUES (1875,226963,'2008-04-20','2008-04-15',4,2008,4,1)
INSERT INTO #Result VALUES (1927,227056,'2008-05-1','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2007,229628,'2008-05-1','2008-04-25',9,2008,4,1)
INSERT INTO #Result VALUES (2033,229784,'2008-05-13','2008-04-1',4,2008,4,1)
INSERT INTO #Result VALUES (2033,229784,'2008-05-13','2008-05-5',4,2008,5,2)
INSERT INTO #Result VALUES (1935,230677,'2008-05-2','2008-04-17',9,2008,4,1)
INSERT INTO #Result VALUES (2078,233684,'2008-05-18','2008-05-15',9,2008,5,1)
INSERT INTO #Result VALUES (1915,234656,'2008-04-26','2008-03-29',4,2008,3,1)
INSERT INTO #Result VALUES (1915,234656,'2008-04-26','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (2062,235261,'2008-05-16','2008-03-30',4,2008,3,1)
INSERT INTO #Result VALUES (2062,235261,'2008-05-16','2008-04-1',4,2008,4,2)
INSERT INTO #Result VALUES (2062,235261,'2008-05-16','2008-05-1',4,2008,5,3)
INSERT INTO #Result VALUES (1961,235970,'2008-05-6','2008-04-1',4,2008,4,1)
INSERT INTO #Result VALUES (2011,237655,'2008-05-12','2008-03-28',9,2008,3,1)
INSERT INTO #Result VALUES (2011,237655,'2008-05-12','2008-04-21',4,2008,4,2)
INSERT INTO #Result VALUES (2011,237655,'2008-05-12','2008-04-21',4,2008,4,3)
INSERT INTO #Result VALUES (2011,237655,'2008-05-12','2008-05-1',4,2008,5,4)
INSERT INTO #Result VALUES (2041,239119,'2008-05-9','2008-04-28',4,2008,4,1)
INSERT INTO #Result VALUES (2273,239526,'2008-05-30','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2272,240451,'2008-05-30','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2090,241083,'2008-05-19','2008-04-21',9,2008,4,1)
INSERT INTO #Result VALUES (1937,241954,'2008-05-3','2008-04-30',4,2008,4,1)
INSERT INTO #Result VALUES (1959,242004,'2008-05-3','2008-04-29',4,2008,4,1)
INSERT INTO #Result VALUES (2074,242349,'2008-05-18','2008-04-25',4,2008,4,1)
INSERT INTO #Result VALUES (2074,242349,'2008-05-18','2008-05-7',4,2008,5,2)
INSERT INTO #Result VALUES (2304,242691,'2008-06-2','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2081,243310,'2008-05-19','2008-04-26',4,2008,4,1)
INSERT INTO #Result VALUES (2032,243795,'2008-05-10','2008-05-7',4,2008,5,1)
INSERT INTO #Result VALUES (2144,244511,'2008-05-23','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2136,245313,'2008-05-23','2008-04-17',4,2008,4,1)
INSERT INTO #Result VALUES (2130,252913,'2008-05-23','2008-05-14',9,2008,5,1)
INSERT INTO #Result VALUES (2045,253065,'2008-05-10','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2046,253065,'2008-05-13','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (1952,257443,'2008-05-5','2008-04-7',4,2008,4,1)
INSERT INTO #Result VALUES (2061,257788,'2008-05-16','2008-04-24',4,2008,4,1)
INSERT INTO #Result VALUES (2135,258709,'2008-05-23','2008-04-7',4,2008,4,1)
INSERT INTO #Result VALUES (2028,259098,'2008-05-12','2008-04-28',4,2008,4,1)
INSERT INTO #Result VALUES (1987,259330,'2008-05-9','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (1934,259756,'2008-05-1','2008-04-17',4,2008,4,1)
INSERT INTO #Result VALUES (2241,265322,'2008-05-28','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2143,268879,'2008-05-23','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2208,270334,'2008-05-26','2008-04-25',4,2008,4,1)
INSERT INTO #Result VALUES (1939,270474,'2008-05-3','2008-04-30',4,2008,4,1)
INSERT INTO #Result VALUES (2307,270601,'2008-05-30','2008-04-13',9,2008,4,1)
INSERT INTO #Result VALUES (2307,270601,'2008-05-30','2008-05-1',9,2008,5,2)
INSERT INTO #Result VALUES (2134,272582,'2008-04-29','2008-04-21',4,2008,4,1)
INSERT INTO #Result VALUES (2063,272795,'2008-05-17','2008-04-29',9,2008,4,1)
INSERT INTO #Result VALUES (2316,272825,'2008-06-2','2008-04-12',4,2008,4,1)
INSERT INTO #Result VALUES (2316,272825,'2008-06-2','2008-05-1',4,2008,5,2)
INSERT INTO #Result VALUES (2316,272825,'2008-06-2','2008-06-2',4,2008,6,3)
INSERT INTO #Result VALUES (2073,273430,'2008-05-17','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2288,277843,'2008-05-31','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2112,278963,'2008-05-20','2008-04-28',4,2008,4,1)
INSERT INTO #Result VALUES (2112,278963,'2008-05-20','2008-05-17',4,2008,5,2)
INSERT INTO #Result VALUES (2312,280003,'2008-05-28','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (1971,281085,'2008-05-7','2008-04-28',4,2008,4,1)
INSERT INTO #Result VALUES (2004,281425,'2008-05-12','2008-05-4',4,2008,5,1)
INSERT INTO #Result VALUES (2069,283010,'2008-05-17','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2070,283029,'2008-05-17','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2037,283223,'2008-05-13','2008-04-17',4,2008,4,1)
INSERT INTO #Result VALUES (2037,283223,'2008-05-13','2008-04-17',8,2008,4,2)
INSERT INTO #Result VALUES (2037,283223,'2008-05-13','2008-05-1',4,2008,5,3)
INSERT INTO #Result VALUES (2291,284335,'2008-05-30','2008-05-26',4,2008,5,1)
INSERT INTO #Result VALUES (2149,287393,'2008-05-24','2008-04-24',4,2008,4,1)
INSERT INTO #Result VALUES (2149,287393,'2008-05-24','2008-05-1',4,2008,5,2)
INSERT INTO #Result VALUES (2191,288330,'2008-05-26','2008-04-25',4,2008,4,1)
INSERT INTO #Result VALUES (2191,288330,'2008-05-26','2008-05-26',4,2008,5,2)
INSERT INTO #Result VALUES (2145,289507,'2008-05-25','2008-04-27',9,2008,4,1)
INSERT INTO #Result VALUES (2306,290297,'2008-05-28','2008-04-26',4,2008,4,1)
INSERT INTO #Result VALUES (2306,290297,'2008-05-28','2008-05-26',8,2008,5,2)
INSERT INTO #Result VALUES (2306,290297,'2008-05-28','2008-05-26',4,2008,5,3)
INSERT INTO #Result VALUES (2158,294306,'2008-05-12','2008-04-30',4,2008,4,1)
INSERT INTO #Result VALUES (2295,295744,'2008-05-29','2008-05-27',4,2008,5,1)
INSERT INTO #Result VALUES (2030,296899,'2008-05-10','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2305,299006,'2008-05-31','2008-04-26',4,2008,4,1)
INSERT INTO #Result VALUES (2305,299006,'2008-05-31','2008-05-26',4,2008,5,2)
INSERT INTO #Result VALUES (2010,299790,'2008-05-9','2008-04-20',4,2008,4,1)
INSERT INTO #Result VALUES (2010,299790,'2008-05-9','2008-05-1',4,2008,5,2)
INSERT INTO #Result VALUES (2274,300993,'2008-05-29','2008-05-4',4,2008,5,1)
INSERT INTO #Result VALUES (2059,301019,'2008-05-19','2008-04-29',4,2008,4,1)
INSERT INTO #Result VALUES (2058,301973,'2008-05-16','2008-04-21',4,2008,4,1)
INSERT INTO #Result VALUES (2058,301973,'2008-05-16','2008-05-15',9,2008,5,2)
INSERT INTO #Result VALUES (1969,302740,'2008-05-8','2008-04-29',4,2008,4,1)
INSERT INTO #Result VALUES (1912,303933,'2008-04-27','2008-04-25',4,2008,4,1)
INSERT INTO #Result VALUES (1919,304646,'2008-04-30','2008-04-26',4,2008,4,1)
INSERT INTO #Result VALUES (2279,305464,'2008-05-30','2008-05-15',4,2008,5,1)
INSERT INTO #Result VALUES (2162,305723,'2008-05-24','2008-05-1',4,2008,5,1)
INSERT INTO #Result VALUES (2210,307262,'2008-05-28','2008-04-27',4,2008,4,1)
INSERT INTO #Result VALUES (2210,307262,'2008-05-28','2008-05-1',4,2008,5,2)
INSERT INTO #Result VALUES (2206,308412,'2008-05-27','2008-04-25',4,2008,4,1)
INSERT INTO #Result VALUES (2206,308412,'2008-05-27','2008-05-26',4,2008,5,2)
INSERT INTO #Result VALUES (2209,309699,'2008-05-27','2008-04-25',4,2008,4,1)
INSERT INTO #Result VALUES (2083,310514,'2008-05-20','2008-05-20',4,2008,5,1)
INSERT INTO #Result VALUES (2292,314129,'2008-05-31','2008-05-20',4,2008,5,1)
INSERT INTO #Result VALUES (2132,315761,'2008-05-23','2008-04-24',4,2008,4,1)
INSERT INTO #Result VALUES (1938,317454,'2008-05-4','2008-04-29',4,2008,4,1)
INSERT INTO #Result VALUES (2152,318132,'2008-05-23','2008-04-26',4,2008,4,1)
INSERT INTO #Result VALUES (2072,320161,'2008-05-19','2008-04-25',4,2008,4,1)
INSERT INTO #Result VALUES (2297,321133,'2008-05-31','2008-04-25',4,2008,4,1)
INSERT INTO #Result VALUES (2297,321133,'2008-05-31','2008-05-26',4,2008,5,2)
INSERT INTO #Result VALUES (2024,321869,'2008-05-12','2008-04-29',4,2008,4,1)
INSERT INTO #Result VALUES (2195,322776,'2008-05-26','2008-04-30',9,2008,4,1)
INSERT INTO #Result VALUES (2303,327727,'2008-06-1','2008-05-15',4,2008,5,1)
Select * from #Result
December 30, 2011 at 4:13 am
I've just tested your latest code and it appears to work perfectly!!!!:-D:-D:-D I've tested about 15 cases of 'Y' but I will do more. My office is closing now so I'll do further tests after the New Years weekend. Thanks everyone for your feedback andhave a fantastic New Years!!!:-)
December 30, 2011 at 4:46 am
Glad to have been of help. I'm still not sure how quick the code will run on a large table, but if you run into issues with that we can sort it out later. The case statement in my code where I set payment status = 0 if it is equal to 4 you don't require any more so that will also make the query more efficient.
Hope your new years is a great one! 😀
December 30, 2011 at 6:36 am
Alter the sample data slightly so that there are 3 non-consecutive rows with PaymentStatus = 9 for ReceiverID = 964085:
INSERT INTO #AccPayments VALUES
(4,375454,'2008-05-17'), (4,375454,'2008-05-19'), (4,375454,'2008-06-04'), (4,375454,'2008-06-30'), (4,375454,'2008-07-28'), (4,375454,'2008-08-28'),
(4,375454,'2008-09-29'), (4,375454,'2008-10-13'), (4,375454,'2008-10-28'), (4,375454,'2008-11-28'), (4,375454,'2008-12-29'), (4,375454,'2009-01-28'),
(4,964085,'2008-10-06'),
(4,964085,'2008-11-05'),
(4,964085,'2008-12-05'),
(4,964085,'2009-01-05'),
(4,964085,'2009-02-05'),
(4,964085,'2009-03-05'),
(4,964085,'2009-04-06'),
(4,964085,'2009-05-05'),
(4,964085,'2009-06-05'),
(4,964085,'2009-07-06'),
(4,964085,'2009-08-05'),
(4,964085,'2009-09-05'),
(4,964085,'2009-10-05'),
(9,964085,'2009-11-05'),
(9,964085,'2009-11-21'),
(4,964085,'2009-12-05'),
(4,964085,'2010-01-05'),
(9,964085,'2010-02-05'),
(4,964085,'2010-02-22'),
(4,964085,'2010-03-05'),
(4,964085,'2010-04-06'),
(4,964085,'2010-05-05'),
(4,964085,'2010-06-05'),
(4,964085,'2010-07-05'),
(4,964085,'2010-07-29'),
(4,964085,'2010-08-05'),
(4,964085,'2010-09-06'),
(4,964085,'2010-10-05'),
(4,964085,'2010-11-05'),
(4,964085,'2010-11-04'),
(9,964085,'2010-12-06'), -- changed
(4,964085,'2011-01-05'),
(9,964085,'2011-02-05'), -- changed
(9,964085,'2011-03-05'), -- changed
(4,964085,'2011-04-05'),
(4,964085,'2011-05-05'),
(4,964085,'2011-06-06'),
(4,964085,'2011-06-29'),
(4,964085,'2011-08-01'),
(4,580627,'2008-07-28'),
(9,580627,'2008-07-28'),
(4,580627,'2008-09-12'),
(4,580627,'2008-09-27'),
(4,580627,'2008-10-27'),
(4,580627,'2008-11-27'),
(4,580627,'2008-12-10'),
(9,580627,'2008-12-27'),
(9,580627,'2009-01-12'),
(4,580627,'2009-01-27'),
(4,580627,'2009-02-27'),
(4,580627,'2009-03-27'),
(9,580627,'2009-04-28'),
(9,580627,'2009-05-13'),
(9,580627,'2009-05-27'),
(9,580627,'2009-06-12'),
(9,580627,'2009-06-27'),
(9,580627,'2009-07-27'),
(4,580627,'2009-08-12'),
(4,580627,'2009-08-27'),
(9,580627,'2009-09-28'),
(9,580627,'2009-10-13'),
(9,580627,'2009-10-27'),
(4,580627,'2009-11-12'),
(9,580627,'2009-11-27'),
(9,580627,'2009-12-14'),
(4,580627,'2009-12-17'),
(4,580627,'2009-12-23'),
(4,580627,'2010-01-27'),
(4,580627,'2010-02-27'),
(4,580627,'2010-03-27'),
(4,580627,'2010-04-28'),
(4,580627,'2010-05-27'),
(4,580627,'2010-06-28'),
(4,580627,'2010-07-27'),
(4,580627,'2010-08-27'),
(4,580627,'2010-09-27'),
(4,580627,'2010-10-27'),
(4,580627,'2010-11-27'),
(4,580627,'2010-12-23'),
(4,580627,'2011-01-27'),
(4,580627,'2011-02-28'),
(4,580627,'2011-03-28'),
(4,580627,'2011-04-28'),
(4,580627,'2011-05-27'),
(4,580627,'2011-06-27'),
(4,580627,'2011-07-27'),
(4,580627,'2011-07-27'),
(4,580627,'2011-08-27'),
(4,580627,'2011-09-27'),
(4,580627,'2011-10-27'),
(4,580627,'2011-11-28'),
(4,580627,'2011-12-27')
Will's code fails. Try this:
;WITH MissedPayments AS (
SELECT
ClaCaseID,
NameID,
IncidentDate,
ValidRow,
Seq,
GroupRows = COUNT(*)
FROM (
SELECT
ClaCaseID,
NameID,
IncidentDate,
ValidRow,
Seq = ROW_NUMBER() OVER (PARTITION BY ClaCaseID, NameID ORDER BY DueDate, ValidRow)
- DENSE_RANK() OVER (PARTITION BY ClaCaseID, NameID, ValidRow ORDER BY DueDate)
FROM (
SELECT
c.ClaCaseID,
c.NameID,
c.IncidentDate,
a.DueDate,
ValidRow = CASE WHEN a.PaymentStatus = 9 AND a.DueDate BETWEEN DATEADD(month,-6,c.IncidentDate) AND c.IncidentDate THEN 1 ELSE 0 END,
MonthPicker = ROW_NUMBER() OVER(PARTITION BY c.ClaCaseID, c.NameID, YEAR(a.DueDate), MONTH(a.DueDate) ORDER BY a.DueDate DESC)
FROM #AccPayments a
INNER JOIN #ClaCases c ON c.NameID = a.ReceiverID
) d WHERE MonthPicker = 1
) o
WHERE ValidRow = 1
GROUP BY ClaCaseID, NameID, IncidentDate, ValidRow, Seq
HAVING COUNT(*) > 2
)
SELECT c.ClaCaseID, c.NameID, c.IncidentDate, Missed = CASE WHEN m.ClaCaseID IS NULL THEN 'N' ELSE 'Y' END
FROM #ClaCases c
LEFT JOIN MissedPayments m ON m.ClaCaseID = c.ClaCaseID
AND m.NameID = c.NameID
AND m.IncidentDate = c.IncidentDate
ORDER BY c.ClaCaseID, c.NameID, c.IncidentDate
It's an alternative to the multiple self-join method I posted earlier (which also works if a month filter is included).
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 30, 2011 at 10:28 am
select cc.ClaCaseID,cc.NameID , cc.IncidentDate
,case when exists (select *
from
(select ap.ReceiverID,ap.PaymentStatus, ROW_NUMBER() over(partition by ap.ReceiverID order by DueDate) rn
from (select ReceiverID,PaymentStatus,MAX(duedate) duedate
from #AccPayments a
where a.ReceiverID=cc.NameID
and DueDate >= DATEADD(m,-6,cc.IncidentDate) and DueDate<=cc.IncidentDate
group by ReceiverID,PaymentStatus,YEAR(duedate),MONTH(duedate)
) ap
) x
group by PaymentStatus having PaymentStatus=9 and MAX(rn)-MIN(rn) +1 =COUNT(*) and COUNT(*)>=3
) then 'Y' else 'N' end Missed
from #ClaCases cc
December 30, 2011 at 10:47 am
Hey VIG, this one's right up your street, been expecting you.
Our results differ, I get two positive rows, you get one:
ClaCaseIDNameIDIncidentDateMissed
43703754542008-09-01N
132459640852009-02-20N
285845806272009-08-02Y
374305806272009-11-11Y
417279640852009-12-11N
2063089640852011-03-31N
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 30, 2011 at 11:17 am
ChrisM@home (12/30/2011)
Hey VIG, this one's right up your street, been expecting you.Our results differ, I get two positive rows, you get one:
Why ???
ClaCaseIDNameIDIncidentDateMissed
37430 5806272009-11-11 Y
I think it is wrong
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply