Submit My Question - Recent Questions - Recent Answer

Case Statment - is this possible?

the current statment reads as follows -

SELECT @NonConsent as NonConsent, @NonConsentPAS as NonConsentPAS,
SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) <=25 THEN 1 ELSE 0 END) As Under25,
SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) >25 THEN 1 ELSE 0 END) as Over25

FROM.........

this works fine but now ive been asked to expand upon this for the following age groups

18-25
26-35
36-45
46-55
56-65
66-75
>75

so i was just going to do the following

SUM(CASE WHEN (Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) >=18 AND Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) <=25) THEN 1 ELSE 0 END) As 18_25,

but it appears T-SQL doesnt like this - is there another way round it?

Cheers,
Craig

[920 byte] By [CraigG] at [2008-2-17]

«« Error 2091- Table corrupted.
»» how would I call the logout function in the code behind file if the user navigates away from the

# 1

It would help if you post the error message you are getting and also a script that has sample schema and data. You most probably have a syntax error in your code.

UmachandarJayachandran-MS at 2007-9-9 >

top of Msdn Tech,SQL Server,Transact-SQL...

# 2

the schema & data are fine as its works for the over 25 & under 25 - but now that i need the new age groups it will not work

here's the whole shabang

SELECT @NonConsent as NonConsent, @NonConsentPAS as NonConsentPAS,

SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) >=18 AND Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) <=25 THEN 1 ELSE 0 END) As 18_25,

SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) >=26 AND Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) <=35 THEN 1 ELSE 0 END) As 26_35,

SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) >=36 AND Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) <=45 THEN 1 ELSE 0 END) As 36_45,

SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) >=46 AND Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) <=55 THEN 1 ELSE 0 END) As 46_55,

SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) >=56 AND Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) <=65 THEN 1 ELSE 0 END) As 56_65,

SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) >=66 AND Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) <=75 THEN 1 ELSE 0 END) As 66_75,

SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) >75 THEN 1 ELSE 0 END) as Over75

--these 2 lines work fine but i dont use them anymore
--SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) <=25 THEN 1 ELSE 0 END) As Under25,
--SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) >25 THEN 1 ELSE 0 END) as Over25

FROM PAS.dbo.[PAS Admission Details] [PAD] INNER JOIN
PAS.dbo.[PAS Patient Details] PP ON [PAD].[PAS key 1] = PP.[PAS key] INNER JOIN
dbo.tblStudyServices ES INNER JOIN
dbo.tblPatient EP ON ES.PatientID = EP.PatientID AND ES.PatientID = EP.PatientID ON PP.[PAS key] = EP.PatientInternalNumber INNER JOIN
PAS.dbo.[PAS Consultant Episode] PCE ON PP.[PAS key] = PCE.[PAS key 1]

WHERE (ES.ConsentDate BETWEEN [PAD].[Admission Date] AND [PAD].[Discharge Date]) AND (ES.ConsentDate BETWEEN PCE.[Episode start date] AND
PCE.[Episode end date]) AND (ES.Consent = 0)

and the error message is "incorrect syntax near 18"

CraigG at 2007-9-9 >

# 3

turned out it didnt like the As 18_25

so i just changed it to field18_25 and all is fine

CraigG at 2007-9-9 >

SQL Server

Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech