writing stock letters from a databse

[jamesflowers1974]

hi

I have a sql statement that extracts from our system all the names , addresses and dates of tenancy for a client.
Based upon that tenancy date , I have to write a letter to him informing of certain events.
ie if tenancy is 4.5 years old , in next six months they are due a kitchen refurb.

we have the wording of the letter in a database field , and based upon the timescale a different worded letter is sent.

how can I link the length of time from a sql statement

ie select DOT , (trunc(sysdate) - TRUNC(DOT)) as days from x

x can be banded from 0-30 , 31-60 , 61-180 , 181+

days as a guide to which text should be returned .

TIA

James

 

[taupirho]

I'm not 100% sure what you are asking here but I'll give it a shot and please correct me
if I'm wrong. It sounds like you need to use the case statement


select
DOT ,
case
when trunc(sysdate) - TRUNC(DOT) > 180 then 0
when trunc(sysdate) - TRUNC(DOT) between 61 and 180 then 1
when trunc(sysdate) - TRUNC(DOT) between 31 and 60 then 2
etc ....

end letter_type
from your_table


In order to understand recursion, you must first understand recursion.