More Help In Set Based Sql ...

Hi,

I have a problem that I can solve in a procedural way but I would rather solve with set based and I am having a hard time wrapping my mind around it.

First I will try an English description of problem:

The tables involved are (data insert statements are at end of post) … The ProgramData table:

create table #ProgramData
(clt_num int,
pgm_num int,
beg_dte datetime)

Following business rules listed below this table will be used to determine updates and inserts into the CafasData table:

create table #cafasData
(clt_num int,
log_on varchar(10))

pgm_num are associeated with log_on values in a one to many relationship defined in this lookup table:

create table #cafas_log_on_lookup
(pgm_num int,
log_on varchar(10))


There can be multiple rows for one client in the ProgramData table but only one row for clients in the CafasData table. As a client can be in two or more programs that match to different log_ons at the same time a weight has been assigned each log_on and the log_on with the greater weight is used in the CafasData Table. DDL for weight table:

create table #CafasLogOnWeights
(Log_on varchar(10),
weight int)


Programs have begin and end dates (end dates for this problem will always be in the future so I did not include them in the ddl).

The basic rules for assigning a log_on to a client is:
1.Once in a log_on it will not change until after the program has ended. Regardless if there is another current log_on with more weight.
2.Always look at the minimum beg_dte that has an end date in the future (assume all test data does have an end date in the future).
3.If two or more programs start on the same date and none of them are for the current log_on then change the log_on to the one with the greatest weight (see #CafasLogOnWeights table below).

Ok ... If that is completely confusing I will try some pseudo procedural code for explaining what needs to be done:

For a given clt_num:
Get all rows in the ProgramData table with a minimum beg_dte
If one row then
If clt_num in CafasData then
Update CafasData with log_on =
associated log_on for given pgm_num
Else
Create new row in CafasData with log_on =
associated log_on for given pgm_num
End if
Else
If clt_num in CafasData then
If one of the pgm_nums translates to the current log_on value then
Do nothing
Else
Pick log_on with 'heaviest' weight
End if
Else
Create new row in CafasData with log_on with 'heaviest' weight
End if
End if
End for


Sample data for the four tables defined above:

insert into #cafasData (clt_num, log_on)
select 1, 'SB' union all
select 2, 'Intake' union all
select 3, 'HB' union all
select 5, 'Wrap' union all
select 6, 'SB' union all
select 7, 'Intake'

insert into #cafas_log_on_lookup (pgm_num, log_on)
SELECT 1220, 'SB' UNION ALL
SELECT 1222, 'Wrap' UNION ALL
SELECT 1223, 'Wrap' UNION ALL
SELECT 1228, 'HB' UNION ALL
SELECT 1229, 'HB' UNION ALL
SELECT 1233, 'Intake' UNION ALL
SELECT 1234, 'Other' UNION ALL
SELECT 1238, 'Other' UNION ALL
SELECT 1239, 'Intake'

insert into #CafasLogOnWeights (log_on, weight)
SELECT 'HB', 5 UNION ALL
SELECT 'Wrap', 4 UNION ALL
SELECT 'SB', 3 UNION ALL
SELECT 'Intake', 2 UNION ALL
SELECT 'Other', 1

insert into #ProgramData (clt_num,pgm_num,beg_dte)
Select 1, 1222, '11/30/2006' union all
select 1, 1220, '11/30/2006' union all
select 2, 1222, '11/30/2006' union all
select 2, 1220, '11/30/2006' union all
select 3, 1222, '11/30/2006' union all
select 3, 1220, '11/30/2006' union all
select 4, 1222, '11/30/2006' union all
select 4, 1220, '11/30/2006' union all
select 5, 1222, '11/30/2006' union all
select 5, 1220, '11/30/2006' union all
select 6, 1234, '10/30/2006' union all
select 6, 1222, '11/30/2006' union all
select 6, 1220, '11/30/2006' union all
select 7, 1234, '10/30/2006' union all
select 7, 1222, '11/30/2006' union all
select 7, 1220, '11/30/2006' union all
select 8, 1234, '10/30/2006' union all
select 8, 1222, '11/30/2006' union all
select 8, 1220, '11/30/2006' union all
select 9, 1223, '1/15/2008' union all
select 10, 1222, '1/1/2007' union all
select 10, 1223, '1/1/2007'


Expected Output (if I didn't make any mistakes or typos):

Inserted Rows
Clt_numlog_on
4Wrap
8Intake
10Wrap

Changed Rows
Clt_numlog_on
2Wrap
3Wrap
5Wrap
6Intake

No Change
Clt_numlog_on
1SB
7Intake

clt_num = 9 is a trick row as the begin date is in the future and there fore should have no affect on the CafasData table

Please let me know if this does not make any sense and give me the opportunity to clarify any points as I really do need to solve this problem.

Thanks in advance for any help

Laurie

P.S. I will be leaving work at 4:30 est. but will check back first thing in the morning. Please do not think I am ignoring any responses.