September 20, 2010 at 5:16 pm
Hi, I was wondering if anyone could help me out on this issue. I have a table of employees with associated ages and I want to count the amount of people in certain age ranges. The difficulty comes when there is no one in a certain range, and I need that range to show up.
Here would be some test data
-- These are the bins that peoples range has to fall into
-- Its a CTE cause I tried to delcare it outside of my select for readibility
WITH CTE_Range (RangeID,RangeLabel,Lower,Upper)
AS
(
SELECT 1 ,'0 - 5',0,5 UNION ALL
SELECT 2 ,'6 - 10',6,10 UNION ALL
SELECT 3 ,'11 - 15',11,15 UNION ALL
SELECT 4 ,'16 - 20',16,20 UNION ALL
SELECT 5 ,'21 - 25',21,25
)
My table results look something like this, they include an age, nationalityID, and Sex. Below is fake test data.
DECLARE @mydata TABLE
(
PersonID INT,
Age INT,
NationalityID INT,
Sex char(1)
)
INSERT INTO @mydata (PersonID,Age,NationalityID,Sex)
SELECT 100,5 ,9000,'M' UNION ALL
SELECT 101,13,1000,'M' UNION ALL
SELECT 102,2,1000,'M' UNION ALL
SELECT 103,20,9000,'F' UNION ALL
SELECT 104,17,3000,'M' UNION ALL
SELECT 105,14,2000,'M' UNION ALL
SELECT 106,9,9000,'M' UNION ALL
SELECT 107,5,9000,'M' UNION ALL
SELECT 108,2,9000,'F'
So my end goal would be to get a data set I can send to a report which will aggregate it based on the age range running down the left side, and the nationalityID running along the top. And the user has to be able to select to run the report for either males or females. The issue that really kinda gets me is how to make sure I can display all possible ranges, without having a CASE statement for each one? I would rather use a CTE so I can add more in the future or a table with ranges I could modify without touching the code.
Note this is not my actual data set just a test set to get my question across. This is what I have tried as a solution with no success. Note I need to be able to pull from this as a view based on male and female. If you look at the results from my attemp, if you query based on sex you wouldn't get the range corresponding to 21-25...
DECLARE @mydata TABLE
(
PersonID INT,
Age INT,
NationalityID INT,
Sex char(1)
)
INSERT INTO @mydata (PersonID,Age,NationalityID,Sex)
SELECT 100,5 ,9000,'M' UNION ALL
SELECT 101,13,1000,'M' UNION ALL
SELECT 102,2,1000,'M' UNION ALL
SELECT 103,20,9000,'F' UNION ALL
SELECT 104,17,3000,'M' UNION ALL
SELECT 105,14,2000,'M' UNION ALL
SELECT 106,9,9000,'M' UNION ALL
SELECT 107,5,9000,'M' UNION ALL
SELECT 108,2,9000,'F'
;WITH CTE_Range (RangeID,RangeLabel,Lower,Upper)
AS
(
SELECT 1 ,'0 - 5',0,5 UNION ALL
SELECT 2 ,'6 - 10',6,10 UNION ALL
SELECT 3 ,'11 - 15',11,15 UNION ALL
SELECT 4 ,'16 - 20',16,20 UNION ALL
SELECT 5 ,'21 - 25',21,25
)
SELECT
Rangelabel,
sex,
nationalityID,
AgesInRange = Count(PersonID)
FROM
(
SELECT
PersonID,Age,NationalityID,Sex,rangelabel
FROM
CTE_Range LEFT JOIN @mydata mydata
ON mydata.Age BETWEEN CTE_Range.Lower AND CTE_Range.Upper
)as temp
GROUP BY
temp.sex,
temp.RangeLabel,
temp.nationalityID
September 20, 2010 at 5:24 pm
I'm thinking I may have to use a stored proceedure so I can pass in the parameters in the lowest part of the query, but I don't know that I should be using stored procedures for reports. Kinda seems like over kill. But maybe this case warrents them?
September 20, 2010 at 8:30 pm
Your problem is that you need a male and female for every range, which basically means even with an outer join to your CTE you cant create the M and F zero count records. To do that, you need to seperate the query to get M and F in their own outer joins, default the value of Sex when no records are found, and then merge the results.
Quick and ugly solution:
DECLARE @mydata TABLE
(
PersonID INT,
Age INT,
NationalityID INT,
Sex char(1)
)
INSERT INTO @mydata (PersonID,Age,NationalityID,Sex)
SELECT 100,5 ,9000,'M' UNION ALL
SELECT 101,13,1000,'M' UNION ALL
SELECT 102,2,1000,'M' UNION ALL
SELECT 103,20,9000,'F' UNION ALL
SELECT 104,17,3000,'M' UNION ALL
SELECT 105,14,2000,'M' UNION ALL
SELECT 106,9,9000,'M' UNION ALL
SELECT 107,5,9000,'M' UNION ALL
SELECT 108,2,9000,'F'
;WITH CTE_Range (RangeID,RangeLabel,Lower,Upper)
AS
(
SELECT 1 ,'0 - 5',0,5 UNION ALL
SELECT 2 ,'6 - 10',6,10 UNION ALL
SELECT 3 ,'11 - 15',11,15 UNION ALL
SELECT 4 ,'16 - 20',16,20 UNION ALL
SELECT 5 ,'21 - 25',21,25
)
(SELECT
Rangelabel,
Nonullsex,
nationalityID,
AgesInRange = Count(PersonID)
FROM
(
SELECT
PersonID,Age,NationalityID,Coalesce(Sex,'F') as NoNullSex,rangelabel
FROM
CTE_Range LEFT JOIN @mydata mydata
ON mydata.Age BETWEEN CTE_Range.Lower AND CTE_Range.Upper
AND Sex='F'
)as temp
GROUP BY
Nonullsex,
temp.RangeLabel,
temp.nationalityID)
UNION ALL
(SELECT
Rangelabel,
nonullsex,
nationalityID,
AgesInRange = Count(PersonID)
FROM
(
SELECT
PersonID,Age,NationalityID,Coalesce(Sex,'M') as NonullSex ,rangelabel
FROM
CTE_Range LEFT JOIN @mydata mydata
ON mydata.Age BETWEEN CTE_Range.Lower AND CTE_Range.Upper
AND Sex='M'
)as temp2
GROUP BY
NoNullSex,
temp2.RangeLabel,
temp2.nationalityID)
May be a prettier way, this is just off the top of my head.
If you have the same issue with nationality as sex, you have a problem.
September 20, 2010 at 9:14 pm
Speaking of a prettier way, you can just add sex to the temp table:
DECLARE @mydata TABLE
(
PersonID INT,
Age INT,
NationalityID INT,
Sex char(1)
)
INSERT INTO @mydata (PersonID,Age,NationalityID,Sex)
SELECT 100,5 ,9000,'M' UNION ALL
SELECT 101,13,1000,'M' UNION ALL
SELECT 102,2,1000,'M' UNION ALL
SELECT 103,20,9000,'F' UNION ALL
SELECT 104,17,3000,'M' UNION ALL
SELECT 105,14,2000,'M' UNION ALL
SELECT 106,9,9000,'M' UNION ALL
SELECT 107,5,9000,'M' UNION ALL
SELECT 108,2,9000,'F'
;WITH CTE_Range (RangeID,RangeLabel,Sex,Lower,Upper)
AS
(
SELECT 1 ,'0 - 5','M',0,5 UNION ALL
SELECT 2 ,'6 - 10','M',6,10 UNION ALL
SELECT 3 ,'11 - 15','M',11,15 UNION ALL
SELECT 4 ,'16 - 20','M',16,20 UNION ALL
SELECT 5 ,'21 - 25','M',21,25 UNION ALL
SELECT 1 ,'0 - 5','F',0,5 UNION ALL
SELECT 2 ,'6 - 10','F',6,10 UNION ALL
SELECT 3 ,'11 - 15','F',11,15 UNION ALL
SELECT 4 ,'16 - 20','F',16,20 UNION ALL
SELECT 5 ,'21 - 25','F',21,25
)
SELECT
Rangelabel,
sex,
nationalityID,
AgesInRange = Count(PersonID)
FROM
(
SELECT
PersonID,Age,NationalityID,cte_range.Sex,rangelabel
FROM
CTE_Range LEFT JOIN @mydata mydata
ON mydata.Age BETWEEN CTE_Range.Lower AND CTE_Range.Upper
AND CTE_Range.Sex = mydata.Sex
)as temp
GROUP BY
temp.sex,
temp.RangeLabel,
temp.nationalityID
The advantage of this way is it works for nationality too if you need it to (and know the possible values)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply