September 21, 2010 at 2:21 pm
Hi,
I have a business case where a member selects a teacher on a monthly basis (but not every month)
Below is a sample table
if object_id('dbo.TEST_TEACHER') is not null drop table dbo.TEST_TEACHER
create table dbo.TEST_TEACHER (
MEMB_IDvarchar(10),
SELECTION_DTdatetime,
TEACHER_IDvarchar(10))
insert into TEST_TEACHER select '123', '2009-09-01', 'P654'
insert into TEST_TEACHER select '123', '2009-10-01', 'P987'
insert into TEST_TEACHER select '123', '2009-12-01', 'P321'
insert into TEST_TEACHER select '123', '2010-01-01', 'P987'
insert into TEST_TEACHER select '123', '2010-02-01', 'P987'
NOTE: The member '123' has selected three different teachers from '2009-09-01' to '2010-02-01'
There are two goals
1. get the TEACHER_ID that the member selected most recently (in this case 'P987' from '2010-02-01')
ex)
select a.MEMB_ID,
substring(a.MAX_TEACHER_ID,9,10) as TEACHER_ID
from (select MEMB_ID,
max(convert(char(8),SELECTION_DT,112) + cast(TEACHER_ID as char(10)) as MAX_TEACHER_ID
from TEST_TEACHER group by MEMB_ID) a
2. get the earliest selection date for the teacher selected in step 1 (in this case 'P987').
However, this date must be the earliest date that the member selected without switching to a different teacher. (in this case, it's '2010-01-01' not ''2009-10-01')
Can anyone help me how to construct a query to get the answer for step 2?
Any help will be appreciated.
Thanks
September 21, 2010 at 2:50 pm
Sounds like a homework question...
Did you consider using ROW_NUMBER()?
You can use this function to number rows in a specific order (ORDER BY) and, if needed, restart numbering based on values in specific column(s) (PARTITION BY) clause.
If you play around with that function and the PARTITION BY clause I'm sure you'll find the nice trick to group contiguous "teacher sessions". If you need some more info, google for "sql server contiguous date range" to get a "strong hint". Once you figure how it works it'll be easy to apply the concept to your scenario.
If you get stuck, please post what you've tried so far and what you're struggling with.
We'll be glad to help you understand how it works or what you might need to do different and why. But we won't simply provide you with an answer. Sorry.
September 21, 2010 at 3:08 pm
create table dbo.TEST_TEACHER (
MEMB_ID varchar(10),
SELECTION_DT datetime,
TEACHER_ID varchar(10))
insert into TEST_TEACHER select '123', '2009-10-01', 'P654'
insert into TEST_TEACHER select '123', '2009-11-01', 'P987'
insert into TEST_TEACHER select '123', '2009-12-01', 'P321'
insert into TEST_TEACHER select '123', '2010-01-01', 'P987'
insert into TEST_TEACHER select '123', '2010-02-01', 'P987'
;with numbered as(SELECT rowno=row_number() over
(partition by MEMB_ID order by SELECTION_DT ),MEMB_ID,SELECTION_DT,TEACHER_ID from TEST_TEACHER)
select * from numbered WHERE rowno = 1
Result:
rownoMEMB_IDSELECTION_DTTEACHER_ID
11232009-10-01 00:00:00.000P654
In order to better understand what this is doing,:
1. Run the code without the WHERE clause
2. Change the order by SELECTION_DT) to order by SELECTION_DT DESC)
Once you do this with a larger sample, test, test and test again
Now the next time you post a question to a forum please follow the guide to how to post (click the first link in my signature block).
September 22, 2010 at 8:10 am
Thanks for your replies.
If you look carefully, you will notice that row_number() or rank() will not work in this case. A recursion may work, but I just used a few subqueries to solve the problem.
I will post the code if anyone wants, I just don't have time now to read the "how to post a query" guide.
Thanks anyway.
September 22, 2010 at 1:09 pm
Max-498379 (9/22/2010)
Thanks for your replies.If you look carefully, you will notice that row_number() or rank() will not work in this case. A recursion may work, but I just used a few subqueries to solve the problem.
I will post the code if anyone wants, I just don't have time now to read the "how to post a query" guide.
Thanks anyway.
I disagree. It will work when used the way I described in my previous post. It's still not clear whether it's a homework question or not.
September 22, 2010 at 2:39 pm
Max-498379
I will post the code if anyone wants, I just don't have time now to read the "how to post a query" guide.
Look you are in the window posting to a form. Notice in the frame on the left of the frame you are typing in. There is a smaller (lightly outlined frame) titled "IFCode Sortcuts" Look down the list unitl you see the item with the following text code="sql". Have your cursor in the position in this frame on a blank line where you want to post iyr code then move cursor to that location in the 'IFCode Shortcuts frame" and click over on tge item with the following text code="sql" Guess what you have done what we requested. Now that is not too hard to do is it?
Here I will make it easy for you - this is what you should click on in the "IFCode Shortcuts" frame
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply