| Author |
 Topic  |
|
|
mike0913
Starting Member
7 Posts |
 Posted - 01/27/2008 : 11:49:45
|
| sdcsdcsdc |
Edited by - mike0913 on 02/23/2008 14:47:09
|
|
|
mike0913
Starting Member
7 Posts |
Posted - 01/27/2008 : 12:21:05
|
| P.S. If you guys have any other ways to implement what I'm trying to implement, that would be helpful too. Thanks guys! |
 |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 01/27/2008 : 13:29:55
|
moved from script library.
Script library is for posting working scripts only.
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
mike0913
Starting Member
7 Posts |
Posted - 01/27/2008 : 14:09:49
|
| I apologize.. newbie to this forum... :D |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 01/27/2008 : 14:15:02
|
Perhaps something like this:
select replace(replace(replace(LetterText,
'[FirstName]', a.FirstName)
,'[ClaimNumber]', convert(varchar, a.ClaimNumber))
,'[ServiceStartDate]', convert(varchar, ServiceStartDate, 101))
--,...
from testLetter tl
join Applicant a on a.ClaimStatus =
case
when tl.lettertype = 'Rejection Letter' then 'REJECTED'
when tl.lettertype = 'Approval Letter' then 'Approved'
end
Be One with the Optimizer
TG |
|
|
|
mike0913
Starting Member
7 Posts |
Posted - 01/27/2008 : 14:23:49
|
| xssdsd |
Edited by - mike0913 on 02/23/2008 14:47:34 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 01/27/2008 : 15:21:57
|
>>I'm trying to avoid to having hard code everything
Well you would just need one REPLACE nesting for each replaceable column in [Applicant]. That would be six according to your DDL of applicant table. Then just one WHEN expession for each ClaimStatus. How many distinct value are there? An alternative to the CASE statement in JOIN clause would be to create a Mapping table that associates LetterType to ClaimStatus then you wouldn't need a CASE statement at all.
Be One with the Optimizer
TG |
|
|
|
mike0913
Starting Member
7 Posts |
Posted - 01/27/2008 : 18:33:59
|
| sdsdfsd |
Edited by - mike0913 on 02/23/2008 14:48:02 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 01/27/2008 : 19:10:20
|
Here is a complete example of what I was suggesting. SqlQuery column is not needed. Notice that the different letter types all work even though they have different [replacement] codes. If you want to you can select all ClaimStatus types at once.
CREATE TABLE #testLetter(
LetterID int IDENTITY(1,1) NOT NULL,
LetterType nvarchar(50),
LetterText nvarchar(4000),
SQLQuery nvarchar(3000) null
)
insert #testLetter (LetterType, LetterText)
values ('Rejection Letter'
,'Dear [FirstName], The application you filed against our office with claim ID [ClaimNumber]' +
' was not approved. The provided service date [ServiceStartDate] and [ServiceEndDate] indicates' +
' that you are not eligible for application.')
insert #testLetter (LetterType, LetterText)
values ('Approval Letter'
,'Dear [FirstName], Congratulations, the application you filed against our office with claim ID ' +
'[ClaimNumber] has been approved.')
create table #Applicant
(ClaimNumber int
,Firstname varchar(20)
,LastName varchar(20)
,ServiceStartDate datetime
,ServiceEndDate datetime
,ClaimStatus varchar(20))
insert #Applicant
select 10001
,'John'
,'Doe'
,'1/9/2001'
,'6/10/2005'
,'REJECTED'
union all
select 10002
,'Jane'
,'Doe'
,'5/4/1997'
,'12/22/1999'
,'APPROVED'
union all
select 10003
,'Joe'
,'Young'
,'11/4/2005'
,'2/22/2006'
,'REJECTED'
select replace(replace(replace(replace(LetterText,
'[FirstName]', a.FirstName)
,'[ClaimNumber]', convert(varchar, a.ClaimNumber))
,'[ServiceStartDate]', convert(varchar, ServiceStartDate, 101))
,'[ServiceEndDate]', convert(varchar, ServiceEndDate, 101))
from #testLetter tl
join #Applicant a on a.ClaimStatus =
case
when tl.lettertype = 'Rejection Letter' then 'REJECTED'
when tl.lettertype = 'Approval Letter' then 'Approved'
end
drop table #applicant
drop table #testLetter
EDIT:
I changed the datatype of a nvarchar(max) because here at home I only have sql 2000
Here is the output:
Dear John, The application you filed against our office with claim ID 10001 was not approved. The provided service date 01/09/2001 and 06/10/2005 indicates that you are not eligible for application.
Dear Joe, The application you filed against our office with claim ID 10003 was not approved. The provided service date 11/04/2005 and 02/22/2006 indicates that you are not eligible for application.
Dear Jane, Congratulations, the application you filed against our office with claim ID 10002 has been approved.
Be One with the Optimizer
TG |
Edited by - TG on 01/27/2008 19:14:25 |
|
|
|
mike0913
Starting Member
7 Posts |
Posted - 01/27/2008 : 20:04:10
|
| fgdfgdfg |
Edited by - mike0913 on 02/23/2008 14:47:50 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 01/27/2008 : 20:37:08
|
You're welcome. 
I see your point but I'm still not convinced passing the query is the right way. I think it depends on the answer to two more quesitons:
How many different [replacement] values do you anticipate needing total? And how many different tables will they come from? So far you've identified 2 tables (applicant and address) with 7 possible [replacements]. It would be very easy to add [address] to the query thus making any columns from address available for replacment.
A custom query for each letterType seems to go against your desire not to "hardcode everything". Another reason I don't like the sqlQuery idea is that passing executable sql to a process raises a lot of difficult issues including: varying column counts, dynamic sql which can have security ramifications and error handling for "un-managed" sql code. So if you can avoid that I think you'll be happier.
Be One with the Optimizer
TG |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/24/2008 : 01:44:54
|
| Why has OP edited all his posts? I cant understand what these people gain from doing this though! |
|
|
|
mike0913
Starting Member
7 Posts |
Posted - 04/16/2008 : 20:19:57
|
Because i want to.. =)
quote:
Originally posted by visakh16
Why has OP edited all his posts? I cant understand what these people gain from doing this though!
|
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 05/16/2008 : 16:17:14
|
Because you're a posterior orafice.
An infinite universe is the ultimate cartesian product. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 05/16/2008 : 16:35:06
|
quote:
Originally posted by cat_jesus
Because you're a posterior orafice.
An infinite universe is the ultimate cartesian product.
Yes he is. Such a cheap guy. |
|
|
| |
Topic |
|
Mimic Mail Merge Functionality
