More help in set based sql ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

More help in set based sql ...

Author

Topic

LaurieCox

158 Posts

Posted - 2007-01-08 : 15:52:19

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_num	log_on
4	Wrap
8	Intake
10	Wrap

Changed Rows
Clt_num	log_on
2	Wrap
3	Wrap
5	Wrap
6	Intake

No Change
Clt_num	log_on
1	SB
7	Intake

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.

LaurieCox

158 Posts

Posted - 2007-01-08 : 15:57:46

So you don't have to cut and paste the ddl and sample data from two different places in the post here it is as one script:


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

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' 


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

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'

create table #CafasLogOnWeights
	(Log_on varchar(10),
	 weight int)
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

create table #ProgramData
	(clt_num int,
	 pgm_num int,
	 beg_dte datetime)
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/2007' union all
	select 10, 1222, '1/1/2007' union all
	select 10, 1223, '1/1/2007'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 18:27:34

[code]-- Show the inserted records
select x.clt_num,
w.log_on
from (
select p.clt_num,
max(w.weight) as maxw
from #programdata as p
inner join #cafas_log_on_lookup as l on l.pgm_num = p.pgm_num
inner join #CafasLogOnWeights as w on w.log_on = l.log_on
left join #cafasData as d on d.clt_num = p.clt_num
where d.clt_num is null
and p.beg_dte <= dateadd(day, datediff(day, 0, getdate()), 1)
group by p.clt_num
) as x
inner join #CafasLogOnWeights as w on w.weight = x.maxw
order by x.clt_num

-- Show changed rows
select x.clt_num,
w.log_on,
d.log_on
from (
select p.clt_num,
max(w.weight) as maxw
from #programdata as p
inner join #cafas_log_on_lookup as l on l.pgm_num = p.pgm_num
inner join #CafasLogOnWeights as w on w.log_on = l.log_on
where p.beg_dte <= dateadd(day, datediff(day, 0, getdate()), 1)
group by p.clt_num
) as x
inner join #CafasLogOnWeights as w on w.weight = x.maxw
inner join #cafasData as d on d.clt_num = x.clt_num
where w.log_on <> d.log_on

-- Show unchanged rows
select x.clt_num,
w.log_on
from (
select p.clt_num,
max(w.weight) as maxw
from #programdata as p
inner join #cafas_log_on_lookup as l on l.pgm_num = p.pgm_num
inner join #CafasLogOnWeights as w on w.log_on = l.log_on
where p.beg_dte <= dateadd(day, datediff(day, 0, getdate()), 1)
group by p.clt_num
) as x
inner join #CafasLogOnWeights as w on w.weight = x.maxw
inner join #cafasData as d on d.clt_num = x.clt_num
where w.log_on = d.log_on[/code]

Peter Larsson
Helsingborg, Sweden

LaurieCox

158 Posts

Posted - 2007-01-09 : 09:41:56

Peso,

First of all: Thank you very much for your help. Unfortunately there are a couple of problems in the SQL.

The insert is perfect the other two don't do exactly as I want. Your results were different in three places from my expected results (clt_num 1, 5 and 7). Your result for 5 was correct it should have been in the No Changes results, but 1 and 7 should have also.

They were both a little tricky.

Clt_num 1:


ProgramData Table:

clt_num	pgm_num	beg_dte	log_on
1	1222	11/30/2006	wrap
1	1220	11/30/2006	SB
2	1222	11/30/2006	wrap
2	1220	11/30/2006	SB

CafasData Table:
Clt_num	Log_on
1	SB
2	Intake

This condition falls into the first business rule listed in the OP:

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.

Even though it was associated with wrap on 11/30/2006 it was currently associated with SB in CafasData Table. As SB was still a valid association in the ProgramData table it should not change. Even though wrap has more weight than SB.

This differs from clt_num = 2, which had the same data in ProgramData, but currently is associated with Intake. As the only two associations in ProgramData available are wrap and sb, clt_num 2 will have Log_on Intake changed to wrap (the one with the most weight). Note: even if clt_num in CafasData table was HB the 'heaviest' log_on it would still change because HB is not currently associated with it in the ProgramData table.

Clt_num 7:


ProgramData Table:
clt_num	pgm_num	beg_dte	log_on
7	1234	10/30/2006	Intake
7	1222	11/30/2006	wrap
7	1220	11/30/2006	SB

CafasData Table:
Clt_num	Log_on
7	Intake

Note that the minimum begin date is 10/30/2006 (line one in the ProgramData table above) and therefore the two entries for 11/30/2006 should not even be considered.

I will be pouring over your code to see how it works and how to change it for this fix, but your help would also be appreciated. I am trying to learn so that I can do these on my own, but learning by example seems to work best for me. I also want to learn the correct way to do things. I have seen some horrendous code written by people that only read the manuals and get no direction for the best way to do things.

Again, thanks for all your help.

Laurie

p.s. My boss leant me two of Joe Celko books: SQL for Smarties (recommended by a couple of people in my thread How to think in set based SQL) and Puzzles and Games and but I haven't had any time to read, at home or at work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 09:49:28

I think I have provided enough information how to make you change to query to work as you want.

Peter Larsson
Helsingborg, Sweden

LaurieCox

158 Posts

Posted - 2007-01-09 : 12:03:25

Hi Peso,

Ok … my mind is spinning … but this is what I came up with so far:

I started by working with the problem with clt_num 1. First I created a temp table (#ChangedRows) with your changes result set and then wrote this sql:


select ch.clt_num,
       ch.Newlog_on,
       ch.OldLog_on
  from #changedRows ch
 where ch.oldLog_On  not in  (select l.log_on 
                             from #ProgramData p join #cafas_log_on_lookup as l on l.pgm_num = p.pgm_num
                            where p.clt_num = ch.clt_num
                              and l.log_on = ch.OldLog_on)

Then took the where clause from the above stuff and threw it back into your sql on it's where clause:


select		x.clt_num,
		w.log_on,
		d.log_on
from		(
			select 		p.clt_num,
					max(w.weight) as maxw
			from		#programdata as p
			inner join	#cafas_log_on_lookup as l on l.pgm_num = p.pgm_num
			inner join	#CafasLogOnWeights as w on w.log_on = l.log_on
			where		p.beg_dte <= dateadd(day, datediff(day, 0, getdate()), 1)
			group by	p.clt_num
		) as x
inner join	#CafasLogOnWeights as w on w.weight = x.maxw
inner join	#cafasData as d on d.clt_num = x.clt_num
where		w.log_on <> d.log_on
   and 		d.Log_On  not in  (select l.log_on 
                                     from #ProgramData p join #cafas_log_on_lookup as l on l.pgm_num = p.pgm_num
                                    where p.clt_num = x.clt_num
                                      and l.log_on = d.Log_on)

This fixes the problem with clt_num 1, by removing it from result set. Is it acceptable code?

… But it also removed 6 from the result set which it shouldn't have. But I think 6 has the same problems as 7. Your code:


select 		p.clt_num,
					max(w.weight) as maxw
			from		#programdata as p
			inner join	#cafas_log_on_lookup as l on l.pgm_num = p.pgm_num
			inner join	#CafasLogOnWeights as w on w.log_on = l.log_on
			where		p.beg_dte <= dateadd(day, datediff(day, 0, getdate()), 1)
			group by	p.clt_num

… Looks at all records for a given client in ProgramData, but it should only look at the records that have the minimum beg_dte.

I am not really sure how to modify this. I know I want the record(s) with the maximum weight and the minimum beg_dte, but I am not sure how to write this query. I think you are just looking for beg_dte < current date, but that is not sufficient.

Thanks again for all your help,

Laurie

LaurieCox

158 Posts

Posted - 2007-01-09 : 14:54:58

Hi Peso,

I have been thinking … The table ProgramData is actually a view of a handful of tables:


CREATE VIEW dbo.v_cafas_eligible  AS
select distinct 
       a.clt_num, 
       c.lst_nme, 
       c.fst_nme, 
       d.bth_dte, 
       d.sex,
       a.autbeg_dte,
       a.pgm_num
from cmh_aut a join cltdmo d on a.clt_num=d.clt_num
     join cltctl c on a.clt_num=c.clt_num
where a.autsts=1
  and a.autend_dte>getdate()
  and a.pgm_num between 1200 and 1229
  and population='SED CHILD' 
  and datediff(mm,bth_dte,getdate()) between 84 and 202

So I thought I could change the view so that it only returns the records that meet all the conditions but have only min autbeg_dte. This would make the code you wrote work (I could just remove any references to beg_dte as I would have already excluded the records causing the problem.

So I went searching for example code on how to do this and I found this thread and it had this code from Harsh Athalye:


 declare @dept table
(
	deptno int,
	dept varchar(100)
)

declare @emp table
(
	eno int,
	ename varchar(100),
	salary numeric(9,2),
	deptno int
)

insert @dept select 1, 'sales' union all select 2, 'IT'  union all select 3, 'Production'

insert @emp
select 1, 'aa', 17790, 1 union all
select 2, 'bb', 7575, 2 union all
select 3, 'cc', 5290, 3 union all
select 4, 'dd', 8760, 1 union all
select 5, 'ee', 6340, 2 union all
select 6, 'ff', 10000, 3 union all
select 7, 'gg', 8672, 1 union all
select 8, 'hh', 1563, 2 union all
select 9, 'jj', 5854, 3 

select d.dept, e.ename, e.salary
from @emp e join @dept d
on e.deptno = d.deptno
where salary = (select max(salary) from @emp e1 where e1.deptno = d.deptno)



OR alternatively,

select d.dept, e.ename, e.salary
from @emp e
join
(
	select d.deptno, d.dept, max(salary) salary
	from @emp e join @dept d on e.deptno = d.deptno
	group by d.deptno, d.dept
) d
on e.deptno = d.deptno
and e.salary = d.salary

and after I modified the data to include two employees in the same department with the max salary and ran … I saw that it does exactly what I want to do with beg_dte. But … I cannot wrap my mind around it enough to merge the concept into my view. I think I might need a pk in the view. The table cmh_aut does have a primary key (aut_num), so I could include it in the view … and then what do I do???

By the way from what I could tell by doing a quick browse thru all 179274 rows in the cmh_aut table, all dates in autbeg_dte have a zero (00:00:00.000) as the time part of the date, so I don't think I have to do anything fancy to use it in a min function.

Thanks,

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources