Validate Data

[sleezy]

SITUATION 1:-

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....

[Claire]

SITUATION 1:-

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

SITUATION 2:-

Under process~~~

[Claire]

I combined situation1 and 2 in a select statement


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

[sleezy]

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

[Claire]

I dont really get what you want.But it seems you are pretty much close.

Could you show the result you want.

And the result so far.

And how you want to achieve the goal?

[sleezy]

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.

[sleezy]

Sorry this is much clearer and is formated in word....

[Claire]

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'

[sleezy]

Sorry it was my mistake l' think it will be much clearer now

[Claire]

So do you still have doubt?Actually it's really not complicated.I think the doc. file I attached pretty much covered whatever you ask.

If anything else you need please specify.

Good luck!

[sleezy]

Thank You very much Claire for assisting me and taking time to analyse my problem.Keep up the good work

[Claire]

You are welcome.Anytime.