need advise on this complex sql

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

SQL Server 2000 Forums

Transact-SQL (2000)

need advise on this complex sql

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-27 : 22:23:36

hi friends
I’ve built this query which works beautifully but for one thing – I can’t seem to make it return a count of 0. I’ve tried isnull as well as a couple of other things but if for instance, if there are no pacific islander children who have had an immunisation in Year 2 Cohort – it will return no line at all. (i.e below)

HL7Count|Ethnicity|ImmAge

39 Other Year 2 Cohort

21 Other Year 5 Cohort

2 Pacific Island Year 5 Cohort

95 Maori Year 5 Cohort

99 Maori Year 2 Cohort

25 Other Year 1 Cohort

1 Pacific Island Year 1 Cohort

4 Pacific Island Other

101 Other Other

107 Maori Year 1 Cohort

282 Maori Other

What I need it to do is return the following..(see the one zero(0))

HL7Count|Ethnicity|ImmAge

39 Other Year 2 Cohort

21 Other Year 5 Cohort

2 Pacific Island Year 5 Cohort

95 Maori Year 5 Cohort

99 Maori Year 2 Cohort

25 Other Year 1 Cohort

1 Pacific Island Year 1 Cohort

4 Pacific Island Other

101 Other Other

107 Maori Year 1 Cohort

0 Pacific Island Year 2 Cohort

282 Maori Other

Select query below – any ideas for me?

SELECT COUNT(*) AS HL7Count,
Ethnicity =
CASE WHEN Ethnicity_1 = 21 THEN 'Maori'
WHEN Ethnicity_1 BETWEEN 30 AND 39 THEN 'Pacific Island'
ELSE 'Other'
END,
ImmAge = CASE ASR.dbo.fn_Age(DOB, '2006-03-30')
WHEN 1 THEN 'Year 1 Cohort'
WHEN 2 THEN 'Year 2 Cohort'
WHEN 5 THEN 'Year 5 Cohort'
ELSE 'Other'
END
FROM membership.dbo.PHO pho INNER JOIN
membership.dbo.Companies c ON pho.id = c.PHO INNER JOIN
dbo.tmp_hl7import su ON c.Practice# = su.practiceid
WHERE PHOID = 583084 AND su.ErrorCode = 3002
AND ASR.dbo.fn_Age(DOB, '2006-03-30') < 6
GROUP BY
CASE WHEN Ethnicity_1 = 21 THEN 'Maori'
WHEN Ethnicity_1 BETWEEN 30 AND 39 THEN 'Pacific Island'
ELSE 'Other'
END,
CASE ASR.dbo.fn_Age(DOB, '2006-03-30')

WHEN 1 THEN 'Year 1 Cohort'

WHEN 2 THEN 'Year 2 Cohort'

WHEN 5 THEN 'Year 5 Cohort'

ELSE 'Other'

END

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-27 : 22:28:47

btw the function ASR.dbo.fn_Age(DOB, '2006-03-30')
It works out the age in years by looking at the date of birth vs the date entered (submission date)
it returns age in years.
and "dbo.tmp_hl7import " table has "ethnicity" field and "DOB" fields

Cheers

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-27 : 23:12:51

The count cannot be zero in that query, since there would have to be at least one row for the combination of Pacific Island Year 2 Cohort to show up in the result set.

You would have to create a table of all combinations you want a result for, and left join your current query result against that.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources