question

palum avatar image
palum asked

Complex-CTE

I have a problem with the following query: WITH CTE_1 (stu_id ,meet_doc_id ,doc_name ,stu_name ,dob ,done ,date_meet_doc) AS (SELECT stu_id ,meet_doc_id ,doc_name ,stu_name ,dob ,CASE WHEN (PATINDEX('%SMOKING%',act.VALUE)) THEN 'LMDO' WHEN (PATINDEX('%NOT SMOKING%',act.VALUE)) THEN 'LMD1' WHEN (ISNULL(CAST(act.VALUE as varchar(max)),'')='') THEN 'CLEAR' ELSE 'CLEAR' END done ,date_meet_doc FROM abc INNER JOIN INNER JOIN INNER JOIN WHERE multiple conditions ) SELECT * FROM CTE_1 one WHERE date =(SELECT MAX(DATE) FROM CTE_1 two WHERE two.stu_id=one.stu_id AND one.doc_name=two.doc_name) ORDER BY stu_name,dob ; The result set of the query(CTE_1) for three student(for eg) is something like stu_id meet_doc_id doc_name stu_name dob value date 101 0104 AD AM 15/06/1950 LMDO 2011-02-15 101 0105 AD AM 15/06/1950 CLEAR 2011-02-18 101 0106 AD AM 15/06/1950 CLEAR 2011-02-25 102 0107 AD AK 12/08/1987 CLEAR 2011-03-28 102 0108 AD AK 12/08/1987 LDMO 2011-04-29 103 0109 PK LMP 13/07/1970 CLEAR 2011-03-28 103 0110 PK LMP 13/07/1970 CLEAR 2011-05-12 AND when i execute the whole query my result set is below. Since I have the condition > WHERE date =(SELECT MAX(DATE) FROM > CTE_1 two WHERE two.stu_id=one.stu_id > AND one.doc_name=two.doc_name) stu_id meet_doc_id doc_name stu_name dob value date 101 0106 AD AM 15/06/1950 CLEAR 2011-02-25 102 0108 AD AK 12/19/1987 LDMO 2011-04-29 103 0110 PK LMP 13/07/1970 CLEAR 2011-05-12 It selects only the MAX DATE of that particular stu_id.I need to change the query where the result set will look something like stu_id meet_doc_id doc_name stu_name dob value date 101 0104 AD AM 15/06/1950 LMDO 2011-02-15 102 0108 AD AK 12/19/1987 LDMO 2011-04-29 103 0110 PK LMP 13/07/1970 CLEAR 2011-05-12 The logic behind it is if the stu_id is same and the doc_name is same and if there exists a value either LMDO or LMD1 then show that record if not show the record which has CLEAR. Simply i want to remove the MAX(date) and place a condition for the whole reporting period of that particular stu_id with the same doc_name.
t-sqlcte
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
Are you now trying to get the min date? I'm trying to work out the criteria you require for your final select statement.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
Your requirements are conflicting here. The final example output goes against what you wrote. Going by the written requirement, then the result for student 102 should be the LDMO row, not the CLEAR row. Please clarify.
0 Likes 0 ·
palum avatar image palum commented ·
Yes, It was my mistake. I am sorry for it. I am changing the resultset. yes,student 102 should be LDMO row, not the CLEAR row.
0 Likes 0 ·
palum avatar image palum commented ·
AND also a student cannot have both LDMO and LDM1. So there is no records which conflit between LDMO and LDM1.A student can either smoke or not smoke. :)
0 Likes 0 ·

1 Answer

·
WilliamD avatar image
WilliamD answered
It turns out to be a simple fix if I understand the requirements right (and have created correct test data - you had some errors if I read it right - L**D**MO and L**M**DO and d.o.b for stu_id 102 don't seem to match up). Anyhow, you just need to identify the smoking/non-smoking entries and get those to the top of the list, then sort via date desc. ROW_NUMBER() is your friend :o) declare @TestTable as table (stu_id int, meet_doc_id char(4), doc_name char(2), stu_name varchar(3), dob date, value varchar(5), date_meet_doc date) insert into @TestTable (stu_id,meet_doc_id,doc_name,stu_name,dob,value,date_meet_doc) values (101,'0104','AD','AM', '19500615','LDMO' ,'2011-02-15'), (101,'0105','AD','AM', '19500615','CLEAR','2011-02-18'), (101,'0106','AD','AM', '19500615','CLEAR','2011-02-25'), (102,'0107','AD','AK', '19870812','CLEAR','2011-03-28'), (102,'0108','AD','AK', '19870812','LDMO' ,'2011-04-29'), (103,'0109','PK','LMP','19700713','CLEAR','2011-03-28'), (103,'0110','PK','LMP','19700713','CLEAR','2011-05-12'); WITH CTE_1 (stu_id ,meet_doc_id ,doc_name ,stu_name ,dob ,done ,date_meet_doc) AS (SELECT stu_id ,meet_doc_id ,doc_name ,stu_name ,dob ,value ,date_meet_doc FROM @TestTable ), CTE_2 as( SELECT *,row_number() over (partition by stu_id order by case when done in ('LDMO','LDM1') then 0 else 1 end, date_meet_doc desc) rn FROM CTE_1) select stu_id ,meet_doc_id ,doc_name ,stu_name ,dob ,value ,date_meet_doc from CTE_2 where rn=1 ; The magic is happening in `CTE_2`. I use the case statement to assign entries with LDMO and LDM1 a 0 and everything else a 1, this pushes those up to the top of the `ROW_NUMBER()` sort. I then sort via date_meet_doc descending to push the newest entry that is LDMO or LDM1 to the top of the list. The final select filters to only take the ones we want (top of the sort list in `CTE_2`) Give that a try and let me know if it works.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

palum avatar image palum commented ·
awesome!. it worked out.I just had to tweak a litlle bit with (partition by stu_id,doc_name) Genious .Thanks a lot for taking out some time and undetanding my senario. Thanks Again! :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.