Case Confusion - SQL Server Forums

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

Case Confusion

Author

Topic

samjhudson
Starting Member

3 Posts

Posted - 2009-12-14 : 09:44:44

Hey guys,
I'm a little confused as to how to use the case function correctly with a subquery. I have written the below code for using case with datediff and it works fine

CASE
WHEN Datediff(MONTH,cas.start_date,Getdate()) <= 6
AND cas.end_date IS NULL
THEN 'Less than 6 months'
WHEN Datediff(MONTH,cas.start_date,Getdate()) <= 12
AND cas.end_date IS NULL
THEN 'Less than 12 months'
WHEN Datediff(MONTH,cas.start_date,Getdate()) <= 18
AND cas.end_date IS NULL
THEN 'Less than 18 months'
WHEN Datediff(MONTH,cas.start_date,Getdate()) <= 24
AND cas.end_date IS NULL
THEN 'Less than 2 years'
WHEN Datediff(MONTH,cas.start_date,Getdate()) > 24
AND cas.end_date IS NULL
THEN 'More than 2 years'
ELSE NULL
END AS 'timetaken'

however, when I try to do a similar thing with a subquery I'm getting errors

CASE (SELECT TOP 1 md1.DESCRIPTION
FROM dbo.vw_meeting_dates_ccw md1
INNER JOIN vw_meeting_ccw m1
ON m1.fk_meeting_date_id = md1.internalid
WHERE m1.fk_conduct_case_id = ccase.internalid

ORDER BY md1.createdon DESC)
WHEN 'PPC1' and cas.end_date is null
THEN 'Pre-PPC2'
WHEN 'PPC2' and cas.end_date is null
THEN 'Pre-Conduct'
WHEN 'conduct' and cas.end_date is null
THEN 'Closed'
WHEN 'conduct committee' and cas.end_date is null
THEN 'closed'
ELSE 'Pre-PPC1'
END AS 'Case Stage',

any ideas?

Thanks in advance

Sam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-14 : 09:47:58

Post the full code you used

Madhivanan

Failing to plan is Planning to fail

samjhudson
Starting Member

3 Posts

Posted - 2009-12-14 : 09:50:47

DECLARE @startdate DATETIME

DECLARE @enddate DATETIME

DECLARE @stageclosed INT

DECLARE @conductoutcome INT

DECLARE @ppc1outcome INT

DECLARE @ppc2outcome INT

DECLARE @nt INT

DECLARE @regsubpart INT

DECLARE @meetings TABLE(
caseid INT,
meetingtype INT,
startdate DATETIME,
outcome VARCHAR(100)
)

INSERT INTO @meetings
SELECT m.fk_conduct_case_id,
m.fk_type_id,
md.date_from,
lut.long_description
FROM vw_meeting_ccw m
LEFT JOIN dbo.vw_meeting_dates_ccw md
ON m.fk_meeting_date_id = md.internalid
LEFT JOIN dbo.vw_meeting_outcome_ccw mo
ON m.internalid = mo.fk_meeting_id
LEFT JOIN lut_meeting_outcome lut
ON mo.fk_meeting_outcome_id = lut.internalid
WHERE m.fk_type_id IN (18,20,22,23)

SELECT DISTINCT ind.surname_dn,
ind.forenames_dn,
ind.scr_number,
lutg.long_description AS gender,
rsp.long_description AS registersubpart,
ccase.internalid,
cas.internalid AS internalid2,
cas.createdon AS daterecinfo,
(SELECT TOP 1 c.createdon
FROM vw_contact_ccw c
INNER JOIN vw_material_ccw m
ON c.internalid = m.fk_contact_id
INNER JOIN vw_case_material_ccw cm
ON m.internalid = cm.fk_material_id
WHERE cm.fk_case_id = cas.internalid
AND c.fk_direction = 1
AND c.createdon <= cas.createdon
AND m.fk_conduct = 1
ORDER BY 1) AS initialincomingcontact,
(SELECT TOP 1 c.createdon
FROM vw_contact_ccw c
INNER JOIN vw_material_ccw m
ON c.internalid = m.fk_contact_id
INNER JOIN vw_case_material_ccw cm
ON m.internalid = cm.fk_material_id
WHERE cm.fk_case_id = cas.internalid
AND c.fk_direction = 2
AND c.createdon >= cas.createdon
AND m.fk_conduct = 1
ORDER BY 1) AS initialoutgoingcontact,
CASE
WHEN (Datediff(dd,(SELECT TOP 1 c.createdon
FROM vw_contact_ccw c
INNER JOIN vw_material_ccw m
ON c.internalid = m.fk_contact_id
INNER JOIN vw_case_material_ccw cm
ON m.internalid = cm.fk_material_id
WHERE cm.fk_case_id = cas.internalid
AND c.fk_direction = 1
AND c.createdon <= cas.createdon
AND m.fk_conduct = 1
ORDER BY 1),(SELECT TOP 1 c.createdon
FROM vw_contact_ccw c
INNER JOIN vw_material_ccw m
ON c.internalid = m.fk_contact_id
INNER JOIN vw_case_material_ccw cm
ON m.internalid = cm.fk_material_id
WHERE cm.fk_case_id = cas.internalid
AND c.fk_direction = 2
AND c.createdon >= cas.createdon
AND m.fk_conduct = 1
ORDER BY 1)) <= 5)
THEN 'Y'
END AS acknowledgedwithin5days,
lut3.long_description AS conductcasesubtype,
(SELECT TOP 1 org.name
FROM vw_employment_ccw emp
INNER JOIN vw_organisation_address_ccw orgad
ON emp.fk_org_address_id = orgad.internalid
INNER JOIN vw_organisation_ccw org
ON orgad.fk_organisation_id = org.internalid
WHERE (emp.date_to IS NULL
OR emp.date_to > Getdate())
AND emp.fk_individual_id = ind.internalid) AS organisation,
(SELECT TOP 1 Isnull(ia.forenames_dn + ' ' + ia.surname_dn,users.ad_user_firstname + ' ' + users.ad_user_surname)
FROM vw_complaint_ccw comp
LEFT JOIN vw_individual_access_ccw ia
ON comp.fk_complainant_individual_id = ia.internalid
LEFT JOIN vw_user_ccw users
ON comp.fk_complainant_user_id = users.internalid
WHERE comp.fk_conduct_case_id = ccase.internalid) AS complainant,
(SELECT CASE
WHEN z.internalid IS NOT NULL
THEN 'Y'
END
FROM vw_complaint_ccw z
WHERE z.fk_conduct_case_id = ccase.internalid
AND current_employer = 1) AS current_employer,
(SELECT CASE
WHEN z.internalid IS NOT NULL
THEN 'Y'
END
FROM vw_complaint_ccw z
WHERE z.fk_conduct_case_id = ccase.internalid
AND past_employer = 1) AS past_employer,
(SELECT CASE
WHEN z.internalid IS NOT NULL
THEN 'Y'
END
FROM vw_complaint_ccw z
WHERE z.fk_conduct_case_id = ccase.internalid
AND employment_agency = 1) AS employment_agency,
(SELECT CASE
WHEN z.internalid IS NOT NULL
THEN 'Y'
END
FROM vw_complaint_ccw z
WHERE z.fk_conduct_case_id = ccase.internalid
AND member_of_public = 1) AS member_of_public,
(SELECT CASE
WHEN z.internalid IS NOT NULL
THEN 'Y'
END
FROM vw_complaint_ccw z
WHERE z.fk_conduct_case_id = ccase.internalid
AND relative_friend_carer = 1) AS relative_friend_carer,
(SELECT CASE
WHEN z.internalid IS NOT NULL
THEN 'Y'
END
FROM vw_complaint_ccw z
WHERE z.fk_conduct_case_id = ccase.internalid
AND self = 1) AS self,
(SELECT CASE
WHEN z.internalid IS NOT NULL
THEN 'Y'
END
FROM vw_complaint_ccw z
WHERE z.fk_conduct_case_id = ccase.internalid
AND service_user = 1) AS service_user,
(SELECT CASE
WHEN z.internalid IS NOT NULL
THEN 'Y'
END
FROM vw_complaint_ccw z
WHERE z.fk_conduct_case_id = ccase.internalid
AND social_service_worker = 1) AS social_service_worker,
(SELECT CASE
WHEN z.internalid IS NOT NULL
THEN 'Y'
END
FROM vw_complaint_ccw z
WHERE z.fk_conduct_case_id = ccase.internalid
AND university_college = 1) AS university_college,
(SELECT CASE
WHEN z.internalid IS NOT NULL
THEN 'Y'
END
FROM vw_complaint_ccw z
WHERE z.fk_conduct_case_id = ccase.internalid
AND other = 1) AS other,
lut2.long_description AS typeofreferral,
Isnull(u.ad_user_firstname + ' ' + u.ad_user_surname,
t.name) AS caseowner,
cd2.date_from AS firstcaseconferencedate,
cd.date_from AS lastcaseconferencedate,
(SELECT TOP 1 startdate
FROM @meetings
WHERE meetingtype = 22
AND caseid = ccase.internalid
ORDER BY startdate DESC) AS ppc1date,
(SELECT TOP 1 outcome
FROM @meetings
WHERE meetingtype = 22
AND caseid = ccase.internalid
ORDER BY startdate DESC) AS ppc1outcome,
(SELECT TOP 1 CASE
WHEN caseid IS NOT NULL
THEN 'Y'
END
FROM @meetings
WHERE meetingtype = 18
AND caseid = ccase.internalid
AND outcome = 'Impose Interim Suspension Order'
ORDER BY startdate DESC) AS isoimposed,
(SELECT TOP 1 startdate
FROM @meetings
WHERE meetingtype = 23
AND caseid = ccase.internalid
ORDER BY startdate DESC) AS ppc2date,
(SELECT TOP 1 outcome
FROM @meetings
WHERE meetingtype = 23
AND caseid = ccase.internalid
ORDER BY startdate DESC) AS ppc2outcome,
(SELECT TOP 1 startdate
FROM @meetings
WHERE meetingtype = 20
AND caseid = ccase.internalid
ORDER BY startdate DESC) AS conductdate,
(SELECT TOP 1 outcome
FROM @meetings
WHERE meetingtype = 20
AND caseid = ccase.internalid
ORDER BY startdate DESC) AS conductoutcome,
cas.end_date AS datecaseclosed,
CASE
WHEN cas.end_date IS NOT
NULL
THEN Isnull((SELECT TOP 1 lutmt.long_description
FROM vw_meeting_ccw m
LEFT JOIN dbo.vw_meeting_dates_ccw md
ON m.fk_meeting_date_id = md.internalid
LEFT JOIN dbo.vw_meeting_outcome_ccw mo
ON m.internalid = mo.fk_meeting_id
LEFT JOIN lut_meeting_type lutmt
ON m.fk_type_id = lutmt.internalid
WHERE m.fk_conduct_case_id = ccase.internalid
ORDER BY md.date_from DESC),'During Investigation')
END AS stageclosedat,
CASE (SELECT TOP 1 md1.DESCRIPTION
FROM dbo.vw_meeting_dates_ccw md1
INNER JOIN vw_meeting_ccw m1
ON m1.fk_meeting_date_id = md1.internalid
WHERE m1.fk_conduct_case_id = ccase.internalid
AND cas1.end_date IS NULL
ORDER BY md1.createdon DESC)
WHEN 'PPC1' AND cas.end_date IS NULL
THEN 'Pre-PPC2'
WHEN 'PPC2' AND cas.end_date IS NULL
THEN 'Pre-Conduct'
WHEN 'conduct' AND cas.end_date IS NULL
THEN 'Closed'
WHEN 'conduct committee' AND cas.end_date IS NULL
THEN 'closed'
ELSE 'Pre-PPC1'
END AS 'Case Stage',
CASE
WHEN Datediff(MONTH,cas.start_date,Getdate()) <= 6
AND cas.end_date IS NULL
THEN 'Less than 6 months'
WHEN Datediff(MONTH,cas.start_date,Getdate()) <= 12
AND cas.end_date IS NULL
THEN 'Less than 12 months'
WHEN Datediff(MONTH,cas.start_date,Getdate()) <= 18
AND cas.end_date IS NULL
THEN 'Less than 18 months'
WHEN Datediff(MONTH,cas.start_date,Getdate()) <= 24
AND cas.end_date IS NULL
THEN 'Less than 2 years'
WHEN Datediff(MONTH,cas.start_date,Getdate()) > 24
AND cas.end_date IS NULL
THEN 'More than 2 years'
ELSE NULL
END AS 'timetaken'
FROM dbo.vw_individual_access_ccw ind
INNER JOIN dbo.vw_conduct_case_ccw ccase
ON ind.internalid = ccase.fk_subject_individual_id
LEFT OUTER JOIN dbo.vw_conduct_case_only_council_ccw cas
ON ccase.fk_case_id = cas.internalid
LEFT OUTER JOIN dbo.lut_conduct_case_decision lut
ON ccase.fk_conduct_case_decision_id = lut.internalid
LEFT OUTER JOIN dbo.lut_case_investigation lut2
ON cas.fk_case_investigation = lut2.internalid
LEFT OUTER JOIN dbo.vw_team_ccw t
ON cas.fk_allocated_to_team = t.internalid
LEFT OUTER JOIN dbo.vw_user_ccw u
ON cas.fk_allocated_to_user = u.internalid
LEFT OUTER JOIN dbo.vw_conduct_case_only_council_ccw cas1
ON ccase.fk_case_id = cas1.internalid
AND cas1.internalid IN (SELECT TOP 1 cas2.internalid
FROM dbo.vw_conduct_case_only_council_ccw cas2
WHERE cas2.end_date IS NULL
ORDER BY cas2.start_date DESC)
LEFT OUTER JOIN dbo.vw_conduct_discussion_ccw cd
ON ccase.internalid = cd.fk_conduct_case_id
AND cd.fk_type_id = 4
AND cd.internalid IN (SELECT TOP 1 internalid
FROM vw_conduct_discussion_ccw
WHERE ccase.internalid = fk_conduct_case_id
AND fk_type_id = 4
ORDER BY date_from DESC)
LEFT OUTER JOIN dbo.vw_conduct_discussion_ccw cd2
ON ccase.internalid = cd2.fk_conduct_case_id
AND cd2.fk_type_id = 4
AND cd2.internalid IN (SELECT TOP 1 internalid
FROM vw_conduct_discussion_ccw
WHERE ccase.internalid = fk_conduct_case_id
AND fk_type_id = 4
ORDER BY date_from)
LEFT JOIN lut_case_subtype lut3
ON cas.fk_subtype = lut3.internalid
INNER JOIN vw_individual_ccw i
ON ind.internalid = i.internalid
LEFT JOIN lut_gender lutg
ON i.fk_sex = lutg.internalid
LEFT JOIN vw_registered_in_ccw ri
ON ind.internalid = ri.fk_individual_id
AND ri.internalid IN (SELECT TOP 1 z.internalid
FROM vw_registered_in_ccw z
INNER JOIN vw_clu_register_sub_part_ccw y
ON z.fk_register_sub_part_id = y.internalid
INNER JOIN vw_registered_in_status_ccw w
ON z.internalid = w.fk_registered_in
WHERE ind.internalid = z.fk_individual_id
AND w.fk_status IN (5,6)
ORDER BY w.date_from DESC)
LEFT JOIN vw_clu_register_sub_part_ccw rsp
ON ri.fk_register_sub_part_id = rsp.internalid
WHERE (@startdate IS NULL
OR @startdate <= cas.createdon)
AND (@enddate IS NULL
OR @enddate >= cas.createdon
AND (cas.end_date IS NULL
OR cas.end_date <= @enddate))
AND (@stageclosed IS NULL
OR (@stageclosed = -1
AND cas.end_date IS NULL)
OR @stageclosed = CASE
WHEN cas.end_date IS NOT NULL
THEN Isnull((SELECT TOP 1 lutmt.internalid
FROM vw_meeting_ccw m
LEFT JOIN dbo.vw_meeting_dates_ccw md
ON m.fk_meeting_date_id = md.internalid
LEFT JOIN dbo.vw_meeting_outcome_ccw mo
ON m.internalid = mo.fk_meeting_id
LEFT JOIN lut_meeting_type lutmt
ON m.fk_type_id = lutmt.internalid
WHERE m.fk_conduct_case_id = ccase.internalid
ORDER BY md.date_from DESC),0)
END)
AND (@conductoutcome IS NULL
OR @conductoutcome = (SELECT TOP 1 lut.internalid
FROM vw_meeting_ccw m
LEFT JOIN dbo.vw_meeting_dates_ccw md
ON m.fk_meeting_date_id = md.internalid
LEFT JOIN dbo.vw_meeting_outcome_ccw mo
ON m.internalid = mo.fk_meeting_id
LEFT JOIN lut_meeting_outcome lut
ON mo.fk_meeting_outcome_id = lut.internalid
WHERE m.fk_type_id = 20
AND m.fk_conduct_case_id = ccase.internalid))
AND cas.fk_subtype NOT IN (94,28,29,15,
18,22,30,25,
19,23,16,20,
31,26,17,21,
24,32,27,71)
AND (@ppc1outcome IS NULL
OR @ppc1outcome = (SELECT TOP 1 outcome
FROM @meetings
WHERE meetingtype = 22
AND caseid = ccase.internalid
ORDER BY startdate DESC))
AND (@ppc2outcome IS NULL
OR @ppc2outcome = (SELECT TOP 1 outcome
FROM @meetings
WHERE meetingtype = 23
AND caseid = ccase.internalid
ORDER BY startdate DESC))
AND (@nt IS NULL
OR @nt = 1
AND Datediff(mm,cas.createdon,(SELECT TOP 1 startdate
FROM @meetings
WHERE meetingtype = 22
AND caseid = ccase.internalid
ORDER BY startdate DESC)) < 3
OR @nt = 2
AND Datediff(mm,cas.createdon,(SELECT TOP 1 startdate
FROM @meetings
WHERE meetingtype = 23
AND caseid = ccase.internalid
ORDER BY startdate DESC)) < 6
OR @nt = 3
AND Datediff(mm,cas.createdon,(SELECT TOP 1 startdate
FROM @meetings
WHERE meetingtype = 20
AND caseid = ccase.internalid
ORDER BY startdate DESC)) < 12)
AND (@regsubpart IS NULL
OR @regsubpart = rsp.internalid)
ORDER BY ind.surname_dn,
ind.forenames_dn,
ind.scr_number

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-14 : 10:03:07

Hint

SELECT TOP 1 c.createdon
FROM vw_contact_ccw c
INNER JOIN vw_material_ccw m
ON c.internalid = m.fk_contact_id
INNER JOIN vw_case_material_ccw cm
ON m.internalid = cm.fk_material_id
WHERE cm.fk_case_id = cas.internalid
AND c.fk_direction = 1
AND c.createdon <= cas.createdon
AND m.fk_conduct = 1
ORDER BY 1) AS initialincomingcontact,

(SELECT TOP 1 c.createdon
FROM vw_contact_ccw c
INNER JOIN vw_material_ccw m
ON c.internalid = m.fk_contact_id
INNER JOIN vw_case_material_ccw cm
ON m.internalid = cm.fk_material_id
WHERE cm.fk_case_id = cas.internalid
AND c.fk_direction = 2
AND c.createdon >= cas.createdon
AND m.fk_conduct = 1
ORDER BY 1) AS initialoutgoingcontact,

can be simplified to

SELECT 
	min(case when c.fk_direction = 1 then c.createdon end) as initialincomingcontact,
	min(case when c.fk_direction = 2 then c.createdon end) as initialoutgoingcontact
FROM vw_contact_ccw c 
INNER JOIN vw_material_ccw m 
ON c.internalid = m.fk_contact_id 
INNER JOIN vw_case_material_ccw cm 
ON m.internalid = cm.fk_material_id 
WHERE cm.fk_case_id = cas.internalid 
AND c.createdon >= cas.createdon 
AND m.fk_conduct = 1

Madhivanan

Failing to plan is Planning to fail

samjhudson
Starting Member

3 Posts

Posted - 2009-12-14 : 10:06:44

Thanks that's great,

Could you also give me some advice on the initial question(how to use case with a subquery)? I've re-posted the code below

CASE (SELECT TOP 1 md1.DESCRIPTION
FROM dbo.vw_meeting_dates_ccw md1
INNER JOIN vw_meeting_ccw m1
ON m1.fk_meeting_date_id = md1.internalid
WHERE m1.fk_conduct_case_id = ccase.internalid

ORDER BY md1.createdon DESC)
WHEN 'PPC1' and cas.end_date is null
THEN 'Pre-PPC2'
WHEN 'PPC2' and cas.end_date is null
THEN 'Pre-Conduct'
WHEN 'conduct' and cas.end_date is null
THEN 'Closed'
WHEN 'conduct committee' and cas.end_date is null
THEN 'closed'
ELSE 'Pre-PPC1'
END AS 'Case Stage',

many thanks

Sam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-15 : 01:49:49

quote:
Originally posted by samjhudson

Thanks that's great,

Could you also give me some advice on the initial question(how to use case with a subquery)? I've re-posted the code below

CASE (SELECT TOP 1 md1.DESCRIPTION
FROM dbo.vw_meeting_dates_ccw md1
INNER JOIN vw_meeting_ccw m1
ON m1.fk_meeting_date_id = md1.internalid
WHERE m1.fk_conduct_case_id = ccase.internalid

ORDER BY md1.createdon DESC)
WHEN 'PPC1' and cas.end_date is null
THEN 'Pre-PPC2'
WHEN 'PPC2' and cas.end_date is null
THEN 'Pre-Conduct'
WHEN 'conduct' and cas.end_date is null
THEN 'Closed'
WHEN 'conduct committee' and cas.end_date is null
THEN 'closed'
ELSE 'Pre-PPC1'
END AS 'Case Stage',

many thanks

Sam

CASE WHEN  cas.end_date is null THEN
	CASE (SELECT TOP 1 md1.DESCRIPTION 
		FROM dbo.vw_meeting_dates_ccw md1 
		INNER JOIN vw_meeting_ccw m1 
		ON m1.fk_meeting_date_id = md1.internalid 
		WHERE m1.fk_conduct_case_id = ccase.internalid 

		ORDER BY md1.createdon DESC) 
		WHEN 'PPC1' THEN 'Pre-PPC2' 
		WHEN 'PPC2' THEN 'Pre-Conduct' 
		WHEN 'conduct' THEN 'Closed' 
		WHEN 'conduct committee'THEN 'closed' 
	END
ELSE 'Pre-PPC1' 
	END AS 'Case Stage',

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources