December 28, 2011 at 4:54 am
Hi,
I have two tables and the join is on column ReceiverID. I have provided some sample data as well as the result I want. Basically I want to loop through the #AccPayments table and if PaymentStatus = 9 for 3 consecutive periods prior to IncidentDate(which is found on the #ClaCases table) then Flag as 'Y' ELSE 'N'. Each PaymentStatus has a corresponding DueDate and this can be compared with IncidentDate.
NOTE: A VERY important requirement is that the loop only go back 6 months prior to IncidentDate
Here is the sample data and the result which I seek:
--DROP TABLE #ClaCases
DROP TABLE #ClaCases
CREATE TABLE #ClaCases (ClaCaseID INT,ReceiverID int, IncidentDate Date )
INSERT INTO #ClaCases VALUES (1234,80451,'2008-11-21')
INSERT INTO #ClaCases VALUES (2442,84244,'2010-03-23')
INSERT INTO #ClaCases VALUES (6774,45676,'2008-06-14')
select * from #ClaCases
--DROP TABLE #AccPayments
CREATE TABLE #AccPayments (PaymentStatus INT,ReceiverID int, DueDate Date )
INSERT INTO #AccPayments VALUES (4,80451,'2008-07-01')
INSERT INTO #AccPayments VALUES (4,80451,'2008-08-01')
INSERT INTO #AccPayments VALUES (9,80451,'2008-09-01')
INSERT INTO #AccPayments VALUES (9,80451,'2008-10-01')
INSERT INTO #AccPayments VALUES (4,80451,'2008-11-01')
INSERT INTO #AccPayments VALUES (4,80451,'2008-12-01')
INSERT INTO #AccPayments VALUES (4,84244,'2009-11-01')
INSERT INTO #AccPayments VALUES (4,84244,'2009-12-01')
INSERT INTO #AccPayments VALUES (9,84244,'2010-01-01')
INSERT INTO #AccPayments VALUES (9,84244,'2010-02-01')
INSERT INTO #AccPayments VALUES (9,84244,'2010-03-01')
INSERT INTO #AccPayments VALUES (9,84244,'2010-04-01')
INSERT INTO #AccPayments VALUES (4,84244,'2010-05-01')
INSERT INTO #AccPayments VALUES (9,84244,'2010-06-01')
INSERT INTO #AccPayments VALUES (9,45676,'2008-01-01')
INSERT INTO #AccPayments VALUES (4,45676,'2008-02-01')
INSERT INTO #AccPayments VALUES (9,45676,'2008-03-01')
INSERT INTO #AccPayments VALUES (4,45676,'2008-04-01')
INSERT INTO #AccPayments VALUES (9,45676,'2008-05-01')
INSERT INTO #AccPayments VALUES (4,45676,'2008-06-01')
INSERT INTO #AccPayments VALUES (4,45676,'2008-07-01')
select * from #AccPayments
--Drop Table #Result
CREATE TABLE #Result (ClaCaseID INT,ReceiverID int, IncidentDate Date, Missed varchar(25) )
INSERT INTO #Result VALUES (1234,80451,'2008-11-21','N')
INSERT INTO #Result VALUES (2442,84244,'2010-03-23','Y')
INSERT INTO #Result VALUES (6774,45676,'2008-06-14','N')
Select * from #Result
Hope someone may be able to assist with this. Thanks very much!
December 28, 2011 at 6:43 am
3 Consective Period loop will go back. Please check the following code. if any other logic is available Please let me know.
[Code="sql"]
DROP TABLE #ClaCases
CREATE TABLE #ClaCases (ClaCaseID INT,ReceiverID int, IncidentDate Date )
INSERT INTO #ClaCases VALUES (1234,80451,'2008-11-21')
INSERT INTO #ClaCases VALUES (2442,84244,'2010-03-23')
INSERT INTO #ClaCases VALUES (6774,45676,'2008-06-14')
--select * from #ClaCases
DROP TABLE #AccPayments
CREATE TABLE #AccPayments (PaymentStatus INT,ReceiverID int, DueDate Date )
INSERT INTO #AccPayments VALUES (4,80451,'2008-07-01')
INSERT INTO #AccPayments VALUES (4,80451,'2008-08-01')
INSERT INTO #AccPayments VALUES (9,80451,'2008-09-01')
INSERT INTO #AccPayments VALUES (9,80451,'2008-10-01')
INSERT INTO #AccPayments VALUES (4,80451,'2008-11-01')
INSERT INTO #AccPayments VALUES (4,80451,'2008-12-01')
INSERT INTO #AccPayments VALUES (4,84244,'2009-11-01')
INSERT INTO #AccPayments VALUES (4,84244,'2009-12-01')
INSERT INTO #AccPayments VALUES (9,84244,'2010-01-01')
INSERT INTO #AccPayments VALUES (9,84244,'2010-02-01')
INSERT INTO #AccPayments VALUES (9,84244,'2010-03-01')
INSERT INTO #AccPayments VALUES (9,84244,'2010-04-01')
INSERT INTO #AccPayments VALUES (4,84244,'2010-05-01')
INSERT INTO #AccPayments VALUES (9,84244,'2010-06-01')
INSERT INTO #AccPayments VALUES (9,45676,'2008-01-01')
INSERT INTO #AccPayments VALUES (4,45676,'2008-02-01')
INSERT INTO #AccPayments VALUES (9,45676,'2008-03-01')
INSERT INTO #AccPayments VALUES (4,45676,'2008-04-01')
INSERT INTO #AccPayments VALUES (9,45676,'2008-05-01')
INSERT INTO #AccPayments VALUES (4,45676,'2008-06-01')
INSERT INTO #AccPayments VALUES (4,45676,'2008-07-01')
-- My Code Starts Here
declare @tReceiverID int
declare @tDueDate date
declare @tClaCaseID int
declare @cnt int
declare @Conseqmonthcnt int
declare @monthdec int
declare @recExistsflag varchar(3)
Declare @noofconsecutivemonths int
select @noofconsecutivemonths = 3
Drop Table #Result
CREATE TABLE #Result (ClaCaseID INT,ReceiverID int, IncidentDate Date, Missed varchar(25) )
declare tempcur cursor for select ClaCaseID ,ReceiverID, IncidentDate from #ClaCases --where ReceiverID='45676'
open tempcur
fetch tempcur into @tClaCaseID , @tReceiverID ,@tDueDate
while (@@FETCH_STATUS=0)
begin
-- Initially we are checking whether the 3 consecutive months data is available
select @recExistsflag = 'Y'
if (selectCOUNT(@tDueDate)
from#AccPaymentsx ,
#ClaCasesy
where x.ReceiverID=y.ReceiverID
andx.PaymentStatus = 9
andx.ReceiverID=@tReceiverID
andx.DueDate < = @tDueDate)>=@noofconsecutivemonths -- # of consecutive months
begin
select @cnt =@noofconsecutivemonths -- # of Continous Month
select @Conseqmonthcnt = 0
select @monthdec = 0;
while(@cnt>0)
begin
if Exists(select'Y'
from#AccPaymentsx ,
#ClaCasesy
where x.ReceiverID=y.ReceiverID
andx.PaymentStatus = 9
andx.ReceiverID=@tReceiverID
andx.DueDate < = @tDueDate
andMONTH(x.DueDate) = MONTH(@tDueDate)-@monthdec)
Begin
select @Conseqmonthcnt = @Conseqmonthcnt +1
End
select @cnt = @cnt - 1
select @monthdec = @monthdec + 1 -- Month Checking
End
if @Conseqmonthcnt>=@noofconsecutivemonths -- # of Consective Months
begin
INSERT INTO #Result VALUES (@tClaCaseID,@tReceiverID,@tDueDate,'Y')
End
else
begin
select @recExistsflag = 'N' -- There no Consective Months
End
End
Else
Begin
select @recExistsflag = 'N' -- There no Consective Months
End
if (@recExistsflag = 'N')
begin
INSERT INTO #Result VALUES (@tClaCaseID,@tReceiverID,@tDueDate,'N')
End
fetch next from tempcur into @tClaCaseID,@tReceiverID ,@tDueDate
End
close tempcur
deallocate tempcur
Select 'Result' , * from #Result
[/Code]
Thanks
Dharma
December 28, 2011 at 7:31 am
I'm busy testing your code now. Thanks so much for the help. I'll provide you with feedback tomorrow;-)
December 29, 2011 at 12:54 am
Hi:
Your code works in some instances but there are examples where it doesn't. Condition was that the needs to be 3 consecutive PaymentStatus=9 and 6 months PRIOR to IncidentDate (So anything after IncidentDate, consecutive PaymentStatus =9 or not are excluded).
Also the code takes some time to run, is there any way it can be optimised? I have 42 000 records and it took over 2min to return 200 results.
Here is an example of where your code doesnt return the desired result.
--DROP TABLE #ClaCases
CREATE TABLE #ClaCases (ClaCaseID INT,ReceiverID int, IncidentDate Date )
INSERT INTO #ClaCases VALUES (1234,80451,'2008-08-05')
select * from #ClaCases
--DROP TABLE #AccPayments
CREATE TABLE #AccPayments (PaymentStatus INT,ReceiverID int, DueDate Date )
INSERT INTO #AccPayments VALUES (9,80451,'2008-03-17')
INSERT INTO #AccPayments VALUES (4,80451,'2008-03-31')
INSERT INTO #AccPayments VALUES (4,80451,'2008-04-30')
INSERT INTO #AccPayments VALUES (9,80451,'2008-05-31')
INSERT INTO #AccPayments VALUES (9,80451,'2008-06-26')
INSERT INTO #AccPayments VALUES (4,80451,'2008-07-25')
INSERT INTO #AccPayments VALUES (9,80451,'2008-07-29')
INSERT INTO #AccPayments VALUES (4,80451,'2008-08-21')
INSERT INTO #AccPayments VALUES (4,80451,'2008-08-28')
INSERT INTO #AccPayments VALUES (9,80451,'2008-09-28')
INSERT INTO #AccPayments VALUES (9,80451,'2008-10-07')
INSERT INTO #AccPayments VALUES (9,80451,'2008-10-23')
INSERT INTO #AccPayments VALUES (9,80451,'2008-10-30')
INSERT INTO #AccPayments VALUES (4,80451,'2008-11-18')
INSERT INTO #AccPayments VALUES (9,80451,'2008-11-24')
INSERT INTO #AccPayments VALUES (4,80451,'2008-12-04')
INSERT INTO #AccPayments VALUES (9,80451,'2009-02-09')
INSERT INTO #AccPayments VALUES (4,80451,'2009-04-20')
select * from #AccPayments
--Drop Table #Result
CREATE TABLE #Result (ClaCaseID INT,ReceiverID int, IncidentDate Date, Missed varchar(25) )
INSERT INTO #Result VALUES (1234,80451,'2008-08-05','Y')
Select * from #Result
December 29, 2011 at 12:57 am
Hi:
Your code works in some instances but there are examples where it doesn't. Condition was that the needs to be 3 consecutive PaymentStatus=9 and 6 months PRIOR to IncidentDate (So anything after IncidentDate, consecutive PaymentStatus =9 or not are excluded).
Also the code takes some time to run, is there any way it can be optimised? I have 42 000 records and it took over 2min to return 200 results.
Here is an example of where your code doesnt return the desired result.
--DROP TABLE #ClaCases
CREATE TABLE #ClaCases (ClaCaseID INT,ReceiverID int, IncidentDate Date )
INSERT INTO #ClaCases VALUES (1234,80451,'2008-08-05')
select * from #ClaCases
--DROP TABLE #AccPayments
CREATE TABLE #AccPayments (PaymentStatus INT,ReceiverID int, DueDate Date )
INSERT INTO #AccPayments VALUES (9,80451,'2008-03-17')
INSERT INTO #AccPayments VALUES (4,80451,'2008-03-31')
INSERT INTO #AccPayments VALUES (4,80451,'2008-04-30')
INSERT INTO #AccPayments VALUES (9,80451,'2008-05-31')
INSERT INTO #AccPayments VALUES (9,80451,'2008-06-26')
INSERT INTO #AccPayments VALUES (4,80451,'2008-07-25')
INSERT INTO #AccPayments VALUES (9,80451,'2008-07-29')
INSERT INTO #AccPayments VALUES (4,80451,'2008-08-21')
INSERT INTO #AccPayments VALUES (4,80451,'2008-08-28')
INSERT INTO #AccPayments VALUES (9,80451,'2008-09-28')
INSERT INTO #AccPayments VALUES (9,80451,'2008-10-07')
INSERT INTO #AccPayments VALUES (9,80451,'2008-10-23')
INSERT INTO #AccPayments VALUES (9,80451,'2008-10-30')
INSERT INTO #AccPayments VALUES (4,80451,'2008-11-18')
INSERT INTO #AccPayments VALUES (9,80451,'2008-11-24')
INSERT INTO #AccPayments VALUES (4,80451,'2008-12-04')
INSERT INTO #AccPayments VALUES (9,80451,'2009-02-09')
INSERT INTO #AccPayments VALUES (4,80451,'2009-04-20')
select * from #AccPayments
--Drop Table #Result
CREATE TABLE #Result (ClaCaseID INT,ReceiverID int, IncidentDate Date, Missed varchar(25) )
INSERT INTO #Result VALUES (1234,80451,'2008-08-05','Y')
Select * from #Result
December 29, 2011 at 11:15 am
;WITH OrderedData AS (
SELECT
a.*, c.ClaCaseID,
c.IncidentDate,
rn = ROW_NUMBER() OVER(PARTITION BY a.ReceiverID ORDER BY a.DueDate)
FROM #AccPayments a
INNER JOIN #ClaCases c ON c.ReceiverID = a.ReceiverID
WHERE a.DueDate >= DATEADD(month,-6,c.IncidentDate)
) SELECT
o1.ClaCaseID,
o1.ReceiverID,
o1.IncidentDate,
Missed = MAX(CASE WHEN o3.ReceiverID IS NULL THEN 'N' ELSE 'Y' 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.IncidentDate
With 200,000 rows it might be more efficient to use a temp table rather than a CTE, and index on rn.
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 29, 2011 at 8:51 pm
specifically you asked like " you want this logic in looping concept" , thats why i have implemented that way, otherwise i would have tried in the efficient way also.
Any way your code is looks like more efficient way to achieve your logic.:-)
Thanks
dharma
December 29, 2011 at 10:01 pm
@ChrisM@home
Your code runs however the results are incorrect. I'll post some sample data but from what I see the code is returning 'Y' when there are no records in the #AccPayments table 6 months prior to IncidentDate ie If IncidentDate is '2008-05-31' and Earliest DueDate captured is '2008-04-25' then your code returns 'Y'.
Also I made a small error but I don't think this will effect your code. In table #ClaCases ReceiverID is actually NameID but does map to ReceiverID in #AccPayments.
I also noticed that not all the rows were affected by your query. I did a sample of 200 and 132 were only affected.
CREATE TABLE #ClaCases (ClaCaseID INT,NameID int, IncidentDate Date )
INSERT INTO #ClaCases VALUES (2292,314129,'2008-05-31')
select * from #ClaCases
--DROP TABLE #AccPayments
CREATE TABLE #AccPayments (PaymentStatus INT,ReceiverID int, DueDate Date )
INSERT INTO #AccPayments VALUES (4,314129,'2008-05-20')
INSERT INTO #AccPayments VALUES (4,314129,'2008-06-20')
INSERT INTO #AccPayments VALUES (4,314129,'2008-07-21')
INSERT INTO #AccPayments VALUES (4,314129,'2008-08-20')
INSERT INTO #AccPayments VALUES (4,314129,'2008-09-20')
INSERT INTO #AccPayments VALUES (4,314129,'2008-10-20')
INSERT INTO #AccPayments VALUES (4,314129,'2008-11-20')
INSERT INTO #AccPayments VALUES (4,314129,'2008-12-20')
INSERT INTO #AccPayments VALUES (4,314129,'2008-08-28')
select * from #AccPayments
--Drop Table #Result
CREATE TABLE #Result (ClaCaseID INT,ReceiverID int, IncidentDate Date, Missed varchar(25) )
INSERT INTO #Result VALUES (2292,314129,'2008-05-31','Y')
Select * from #Result
December 29, 2011 at 10:16 pm
Sorry I just picked up something else in the data. There may be several transactions per month.
CREATE TABLE #AccPayments (PaymentStatus INT,ReceiverID int, DueDate Date )
INSERT INTO #AccPayments VALUES (9,314129,'2009-05-20')
INSERT INTO #AccPayments VALUES (9,314129,'2008-05-27')
INSERT INTO #AccPayments VALUES (9,314129,'2008-06-05')
INSERT INTO #AccPayments VALUES (9,314129,'2008-06-17')
INSERT INTO #AccPayments VALUES (4,314129,'2008-06-29')
Select * from #AccPayments
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.
December 29, 2011 at 10:26 pm
Sorry another thing is that ClaCaseID is unique but a NameID can have multiple ClaCaseID's:w00t:
December 30, 2011 at 12:46 am
Hi,
Can you give us more data to work with? I'm trying to build the query but I don't understand the full set of rules you require, it seems like you are only understanding the data you have to work with once people respond and then you add another rule. I'd be glad to try and help if you can give me a more definitive set of results and data. Does the solution have to be a loop? Just want to know if that's the only type of solution I'm allowed to come up with.
Regards,
William 😀
December 30, 2011 at 2:02 am
pdharmaraju (12/29/2011)
specifically you asked like " you want this logic in looping concept" , thats why i have implemented that way, otherwise i would have tried in the efficient way also.Any way your code is looks like more efficient way to achieve your logic.:-)
Thanks
dharma
Hi Dharma
Folks on SSC would generally question the reason for explicitly requiring a loop-based method and strongly encourage the fastest-performing solution. Good attention to detail though 😉
Edit: typo
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 2:10 am
mic.con87 (12/29/2011)
Sorry another thing is that ClaCaseID is unique but a NameID can have multiple ClaCaseID's:w00t:
Post an extended sample data set which yields the incorrect results you are getting from my query and includes the new conditions.
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 2:27 am
Hi, sorry for the lack of understanding of the data. It's never been analyzed before so I'm pioneering it:-P
Here is some proper data:
--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 (4,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
FYI ClaCaseID = 37430 has Missed = 'N' since PaymentStatus = 9 occurred twice in a single month so the count is 1 not 2
December 30, 2011 at 2:35 am
You can use ANY method to solve the issue at hand and doesn't need to be a loop based method
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply