June 24, 2015 at 2:24 pm
The 2 tables have data for one patient whose registration number ( reg_num ) is = 1
--Has data when the patient came to hospital
create table #cases ( reg_num int , case_date datetime, case_num int ) ;
INSERT INTO #cases ( reg_num, case_date, case_num ) VALUES ( 1, '6/1/2010', 100 );
INSERT INTO #cases ( reg_num, case_date, case_num ) VALUES ( 1, '7/1/2010', 200);
INSERT INTO #cases ( reg_num, case_date, case_num ) VALUES ( 1, '8/1/2010', 300);
--Has data when a exam was performed
create table #rad ( reg_num int , exam_date datetime , case_num int ) ;
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '6/2/2010' );--1
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '6/21/2010' );--2
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '8/2/2010' );--3
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '9/2/2010' );--4
-- NOW I need to update the case_num column in table #rad
-- Here's the logic
-- If done properly the first row in the table #rad will get a value of 100 for case_num
--Why ? Because exam date 6/2/2010 falls between 6/1/2010 and 7/1/2010
--rows 1 and 2 will get value 100
--row 3 will be 300
--row 4 will get 300 because it was done after 8/1/2010 and after that there are no more cases for patient 1
June 24, 2015 at 2:45 pm
mw112009 (6/24/2015)
The 2 tables have data for one patient whose registration number ( reg_num ) is = 1--Has data when the patient came to hospital
create table #cases ( reg_num int , case_date datetime, case_num int ) ;
INSERT INTO #cases ( reg_num, case_date, case_num ) VALUES ( 1, '6/1/2010', 100 );
INSERT INTO #cases ( reg_num, case_date, case_num ) VALUES ( 1, '7/1/2010', 200);
INSERT INTO #cases ( reg_num, case_date, case_num ) VALUES ( 1, '8/1/2010', 300);
--Has data when a exam was performed
create table #rad ( reg_num int , exam_date datetime , case_num int ) ;
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '6/2/2010' );--1
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '6/21/2010' );--2
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '8/2/2010' );--3
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '9/2/2010' );--4
-- NOW I need to update the case_num column in table #rad
-- Here's the logic
-- If done properly the first row in the table #rad will get a value of 100 for case_num
--Why ? Because exam date 6/2/2010 falls between 6/1/2010 and 7/1/2010
--rows 1 and 2 will get value 100
--row 3 will be 300
--row 4 will get 300 because it was done after 8/1/2010 and after that there are no more cases for patient 1
Great job posting ddl and sample data. Makes this a LOT easier.
Something like this should work.
update r
set case_num = x.case_num
from #rad r
cross apply (select top 1 case_num from #cases c where c.case_date < r.exam_date order by case_date desc) x
select *
from #rad
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2015 at 2:52 pm
Cool!
I like it that worked!
Great JOB!
Lets close the case. It is is 5PM time to set home.
June 24, 2015 at 2:59 pm
mw112009 (6/24/2015)
Cool!I like it that worked!
Great JOB!
Lets close the case. It is is 5PM time to set home.
Make sure you understand it. Feel free to ask questions if there is something in there that doesn't make sense.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2015 at 3:14 pm
Sean Lange (6/24/2015)
mw112009 (6/24/2015)
The 2 tables have data for one patient whose registration number ( reg_num ) is = 1--Has data when the patient came to hospital
create table #cases ( reg_num int , case_date datetime, case_num int ) ;
INSERT INTO #cases ( reg_num, case_date, case_num ) VALUES ( 1, '6/1/2010', 100 );
INSERT INTO #cases ( reg_num, case_date, case_num ) VALUES ( 1, '7/1/2010', 200);
INSERT INTO #cases ( reg_num, case_date, case_num ) VALUES ( 1, '8/1/2010', 300);
--Has data when a exam was performed
create table #rad ( reg_num int , exam_date datetime , case_num int ) ;
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '6/2/2010' );--1
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '6/21/2010' );--2
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '8/2/2010' );--3
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '9/2/2010' );--4
-- NOW I need to update the case_num column in table #rad
-- Here's the logic
-- If done properly the first row in the table #rad will get a value of 100 for case_num
--Why ? Because exam date 6/2/2010 falls between 6/1/2010 and 7/1/2010
--rows 1 and 2 will get value 100
--row 3 will be 300
--row 4 will get 300 because it was done after 8/1/2010 and after that there are no more cases for patient 1
Great job posting ddl and sample data. Makes this a LOT easier.
Something like this should work.
update r
set case_num = x.case_num
from #rad r
cross apply (select top 1 case_num from #cases c where c.case_date < r.exam_date order by case_date desc) x
select *
from #rad
Shouldn't this:
...
cross apply (select top 1 case_num from #cases c where c.case_date < r.exam_date order by case_date desc) x
Be this?
...
cross apply (select top 1 case_num from #cases c where c.case_date <= r.exam_date order by case_date desc) x
June 24, 2015 at 3:29 pm
Lynn Pettis (6/24/2015)
Sean Lange (6/24/2015)
mw112009 (6/24/2015)
The 2 tables have data for one patient whose registration number ( reg_num ) is = 1--Has data when the patient came to hospital
create table #cases ( reg_num int , case_date datetime, case_num int ) ;
INSERT INTO #cases ( reg_num, case_date, case_num ) VALUES ( 1, '6/1/2010', 100 );
INSERT INTO #cases ( reg_num, case_date, case_num ) VALUES ( 1, '7/1/2010', 200);
INSERT INTO #cases ( reg_num, case_date, case_num ) VALUES ( 1, '8/1/2010', 300);
--Has data when a exam was performed
create table #rad ( reg_num int , exam_date datetime , case_num int ) ;
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '6/2/2010' );--1
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '6/21/2010' );--2
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '8/2/2010' );--3
INSERT INTO #rad ( reg_num, exam_date ) VALUES ( 1, '9/2/2010' );--4
-- NOW I need to update the case_num column in table #rad
-- Here's the logic
-- If done properly the first row in the table #rad will get a value of 100 for case_num
--Why ? Because exam date 6/2/2010 falls between 6/1/2010 and 7/1/2010
--rows 1 and 2 will get value 100
--row 3 will be 300
--row 4 will get 300 because it was done after 8/1/2010 and after that there are no more cases for patient 1
Great job posting ddl and sample data. Makes this a LOT easier.
Something like this should work.
update r
set case_num = x.case_num
from #rad r
cross apply (select top 1 case_num from #cases c where c.case_date < r.exam_date order by case_date desc) x
select *
from #rad
Shouldn't this:
...
cross apply (select top 1 case_num from #cases c where c.case_date < r.exam_date order by case_date desc) x
Be this?
...
cross apply (select top 1 case_num from #cases c where c.case_date <= r.exam_date order by case_date desc) x
Yes indeed it should!! Thanks for the catch Lynn!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2015 at 4:09 pm
Forgot to tell u but there can be many patients. So I had t modify as follows:
cross apply (select top 1 case_num from #cases c where convert(char(8),c.case_date,112) <= convert(char(8),r.exam_date,112) and ( c.reg_num = r.reg_num ) ) order by case_date desc) x
Anyhow, it works! Thanks!
Credit to you.
June 24, 2015 at 5:53 pm
mw112009 (6/24/2015)
Forgot to tell u but there can be many patients. So I had t modify as follows:cross apply (select top 1 case_num from #cases c where convert(char(8),c.case_date,112) <= convert(char(8),r.exam_date,112) and ( c.reg_num = r.reg_num ) ) order by case_date desc) x
Anyhow, it works! Thanks!
Credit to you.
Why are you converting dates to character strings?? All you needed to add was the comparision reg_num to reg_num between the tables in the select in the cross apply.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply