l would like to write a script that runs through my table
and check for each loan the following:
If the for all the periods the closed status is 'C'
and the DUEDATE is less than Getdate() do Nothing
But if all the periods are closed and the DUEDATE
is in the future (> getdate()) flag it as an error....
LOAN CLOSED PERIOD DUEDATE
========== ====== ====== =======
PIE3007520 C 1 20000430
PIE3007520 C 2 20000531
PIE3007520 C 3 20000630
PIE3007520 C 4 20000731
PIE3007520 C 5 20000831
PIE3007520 C 6 20000930
PIE3007520 C 7 20001031
PIE3007520 C 8 20001130
PIE3007520 C 9 20001231
SITUATION 2
------------
In the Second Situation l want loop through the table and check for
each loan. If they is an 'S' anyway in the loan and the DUEDATE
is less than getdate() flag every thing else with a 'C' status
for that loan, but if they is an 'S' and the DUEDATE is
greater than getdate() flag it as an error or insert it into an
audit table..
LOAN CLOSED PERIOD DUEDATE
========== ====== ====== =======
PIE3007610 C 1 20000430
PIE3007610 C 2 20000531
PIE3007610 C 3 20000630
PIE3007610 4 20000731
PIE3007610 S 5 20000831
PIE3007610 6 20000930
PIE3007610 7 20001031
PIE3007610 8 20001130
PIE3007610 9 20001231
PIE3007610 10 20010131
l tried using a case statemen with much luck.Please help l'm stuck....
select loan,closed,period,duedate,flag=(case when closed = 'C' and duedate < convert(varchar(20),getdate(),112) then 'do nothing'
when closed = 'C' and duedate > convert(varchar(20),getdate(),112) then 'error' else null end)
from loan
select loan,closed,period,duedate,flag=(case when closed = 'C' and duedate < convert(varchar(20),getdate(),112) then 'do nothing' when closed = 'C' and duedate > convert(varchar(20),getdate(),112) then 'error' when closed = 'S' and duedate < convert(varchar(20),getdate(),112) then 'What do you want?' when closed = 'S' and duedate > convert(varchar(20),getdate(),112) then 'error'else null end)from loan
go
Thanks for your help so far...l'm still facing issues..
This update statement will work for one loan.If l want to run it across all the loans
checking for this and updating how can l write the query?
Update a
Set closed = 'C'
from table x
Where closed = 'S' And Duedate < getdate()
And loan = 'OUD9000851'
Update a
Set Indicator = 'N'
from table x
Where closed = 'S' And Duedate < getdate()
And loan = 'OUD9000851'
LOAN CLOSED PERIOD DUEDATE INDICATOR
========== ======== ====== ======= =========
OUD9000851 S 1 20000731 N
ETC ETC ETC ETC ETC
OUD9000851 S 23 20020531 N
OUD9000851 S 24 20020630 N
OUD9100244 29 20030228 N
OUD9100244 30 20030331 N
OUD9100244 31 20030430 Y
OUD9100244 32 20030531 Y
OUD9100244 33 20030630 Y
How can l update the indicator column. In this case
when l have an open period which is represented by a
blank and they is no 'S' or 'R' in the closed column
and the duedate > getdate(). The open loan should have
a 'Y' status and the rest below that should be 'F'
for future and the date should be greater than getdate()
LOAN CLOSED PERIOD DUEDATE INDICATOR
========== ======== ====== ======= =========
PEZCF10668 C 23 20020228 N
PEZCF10668 C 24 20020331 N
PEZCF10668 C 25 20020430 N
PEZCF10668 C 26 20020531 N
PEZCF11184 38 20030531 Y
PEZCF11184 39 20030630 Y
This what l have so far and l need help to expand on this....
DROP TABLE #TEMP1
SELECT
DEA316_LOAN_REFERENCE
,Clp31600p_Closed_Period
,pRD316_PERIOD,DDT316_DUE_DATE
,(CASE
WHEN Clp31600p_Closed_Period = 'C' AND DDT316_DUE_DATE < CONVERT(Varchar(20),GETDATE(),112) THEN 'N'
WHEN Clp31600p_Closed_Period = 'C' AND DDT316_DUE_DATE > CONVERT(Varchar(20),GETDATE(),112) THEN 'Error'
WHEN Clp31600p_Closed_Period = 'S' AND DDT316_DUE_DATE < CONVERT(Varchar(20),GETDATE(),112) THEN 'N'
WHEN Clp31600p_Closed_Period = 'S' AND DDT316_DUE_DATE > CONVERT(Varchar(20),GETDATE(),112) THEN 'Error'
WHEN Clp31600p_Closed_Period = '' AND DDT316_DUE_DATE < CONVERT(Varchar(20),GETDATE(),112) THEN 'N'
ELSE 'F'
END) AS INDICATOR INTO #TEMP1
FROM ZA31600P1
ORDER BY DEA316_LOAN_REFERENCE
,Clp31600p_Closed_Period
,CAST(PRD316_PERIOD AS INT) ASC
-- UPDATE a
-- SET INDICATOR = 'Y'
-- FROM #TEMP1 a
-- WHERE CASE
-- WHEN Clp31600p_Closed_Period = '' AND DDT316_DUE_DATE > CONVERT(Varchar(20),GETDATE(),112)
-- WHEN
--
SELECT * FROM #TEMP1
l hope this is makes it more clear.l've attached small samples of the current
scenario and the desired result.
Situation 1:
LOAN CLOSED PERIOD DUEDATE
PIE3007520 C 1 20000430
PIE3007520 C 2 20000531
PIE3007520 C 3 20000630
PIE3007520 C 4 20000731
THE ABOVE RESULT SET IS SHOWING THE LOAN IS NO LONGER
ACTIVE BECAUSE THE CLOSED PERIOD IS FLAGGED WITH A 'C'
AND THE DUEDATE IS LESS THAN GETDATE() HENCE ITS CLOSED.
IF IT WAS '' FOR OPEN AND THE
DUEDATE WAS LESS THAN GETDATE() IT SHOULD BE UPDATED
WITH A 'C' TO CLOSE THE OPEN PERIODS.
LOAN CLOSED PERIOD DUEDATE
PIE3007520 C 1 20000731
PIE3007520 C 2 20000831
PIE3007520 C 3 20000930
PIE3007520 C 4 20001031
PIE3007520 5 20001130
PIE3007520 6 20001231
DESIRED RESULT :- the 'N' is to indicate the loan is not active
LOAN CLOSED PERIOD DUEDATE INDICATOR
PIE3007520 C 1 20000430 N
PIE3007520 C 2 20000531 N
PIE3007520 C 3 20000630 N
PIE3007520 C 4 20000731 N
PIE3007520 C 5 20000831 N
PIE3007520 C 6 20000930 N
Situation 2 :-
BUT IF THE GETDATE()
IS GREATER THAN DUEDATE AND IN THE CLOSED COLUMN THERE
IS NO 'S' FOR SETTLED OR 'R' FOR RESCHEDULLED THEN THE
CLOSED PERIOD SHOULD BE 'F' FOR FUTURE AND THE INDICATOR SET
TO 'Y' FOR THE CURRENT OPEN PERIOD AND FOR THE ONES BELOW
THE ONE WITH A 'Y' THE INDICATOR WILL BE 'N' AND ONLY
CHANGE TO 'Y' WHEN IT IS FOR THE CURRENT MONTH.
SEE EXAMPLE BELOW
LOAN CLOSED PERIOD DUEDATE
PIE3007520 C 1 20000430
PIE3007520 C 2 20000531
PIE3007520 3 20030630
PIE3007520 4 20030731
PIE3007520 5 20030831
DESIRED RESULT :-
LOAN CLOSED PERIOD DUEDATE INDICATOR
PIE3007520 C 1 20000430 N
PIE3007520 C 2 20000531 N
PIE3007520 3 20030630 Y
PIE3007520 4 20030731 N
PIE3007520 5 20030831 N
SITUATION 3:-
LOAN CLOSED PERIOD DUEDATE
PIE3007610 C 1 20000430
PIE3007610 C 2 20000531
PIE3007610 C 3 20000630
PIE3007610 4 20000731
PIE3007610 S 5 20000831
PIE3007610 6 20000930
PIE3007610 7 20001031
PIE3007610 8 20001130
PIE3007610 9 20001231
PIE3007610 10 20010131
IF THE LOAN HAS CLOSED PERIOD 'C' AND IF IT
ENCOUNTERS AN 'S' WHICH MEANS SETTLED
IT SHOULD CHECK TO SEE IF THE DUEDATE IS LESS THAN
GETDATE() IF IT IS, IT SHOULD THEN CLOSE ALL THE OTHER PERIODS
FOR THAT LOAN THAT ARE STILL OPEN '' AND SET THE INDICATOR
TO 'N' TO INDICATE THE LOAN IS NOT ACTIVE
SEE BELOW FOR DESIRED RESULT:-
LOAN CLOSED PERIOD DUEDATE INDICATOR
PIE3007610 C 1 20000430 N
PIE3007610 C 2 20000531 N
PIE3007610 C 3 20000630 N
PIE3007610 C 4 20000731 N
PIE3007610 S 5 20000831 N
PIE3007610 C 6 20000930 N
PIE3007610 C 7 20001031 N
PIE3007610 C 8 20001130 N
PIE3007610 C 9 20001231 N
PIE3007610 C 10 20010131 N
SO BASICALLY MY CONCERN AND PROBLEM IS AROUND THE CLOSED AND THE
INDICATOR FIELDS.
Though you make it very legible.I found many controvercy part.inclding
Situation 2 :-
The Indicator set to ‘Y’ for Current which is determined by min(duedate) where period is greater than getdate() and closed = ‘ ’ then the remaining ones are flaged with an ‘F’ for future
But your desired reslut doesnt have any 'F' in it.All you have is 'N'
According to your post,it's supposed to be
PIE3007520 4 20030731 F
PIE3007520 5 20030831 F
PIE3007520 6 20030931 F
----------------------------------------
and situation3
you didnt define how to get
PIE3007610 C 3 20030630
to
PIE3007610 C 3 20030630 N
Because in this case the duedate is > getdate() and also the closed column flaged as 'C'
Also this one
PIE3007610 S 5 20030831 N
doe closed = 'S' ,you didnt define how you want it to be.
How come suddenly you know it's indicator is 'S'