table iteration. - 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

table iteration.

Author

Topic

mikelasouris
Starting Member

3 Posts

Posted - 2008-09-11 : 12:38:24

I have this table that has age and ethnicity.
now i need to report on that table based on agegroup and etnicity group.

ethinicity group: white, spanish, black etc.
age group: 51-60,61-70,71-80

attempt: i created 3 temporary tables. ran insert in each of them.
table 1 resulted in:
number| ethnicity
4 White
3 Unreported
Table 2:
2 White
Table 3:
1 Asian

My problem is that I want to have a query returning
(sixties) (seventies) (eighties)
4 2 0 White
3 0 0 Unreported
0 0 1 asian

is this doable any one?

thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-09-11 : 15:01:06

Can you show us the actual DDL of your table? Why would you make 3 temp tables?

Anyways, making asumptions:

create table agenic ( age int, eth varchar(20))

insert into agenic (age, eth)
select 63,'WHITE' union all select
67,'WHITE' union all select
61,'WHITE' union all select
64,'WHITE' union all select
73,'WHITE' union all select
77,'WHITE' union all select
63,'UNREPORTED' union all select
65,'UNREPORTED' union all select
67,'UNREPORTED' union all select
87,'ASIAN' 



select 
	sum (case when age  between 60 and 69 then 1 else 0 end) as 'Sixties' 
	,sum (case when age  between 70 and 79 then 1 else 0 end) as 'Seventies' 
	,sum (case when age  between 80 and 89 then 1 else 0 end) as 'Eighties' 
	,eth
FROM
	agenic
group by 
	eth

Drop Table agenic

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mikelasouris
Starting Member

3 Posts

Posted - 2008-09-11 : 15:32:01

[quote]Originally posted by mikelasouris

[quote]Originally posted by mikelasouris

I have this table that has age and ethnicity.
now i need to report on that table based on agegroup and etnicity group.

ethinicity group: white, spanish, black etc.
age group: 51-60,61-70,71-80

attempt: i created 3 temporary tables. ran insert in each of them.
table 1 resulted in:
number| ethnicity
4 White
3 Unreported
Table 2:
2 White
Table 3:
1 Asian

My problem is that I want to have a query returning
(sixties) (seventies) (eighties)
4 2 0 White
3 0 0 Unreported
0 0 1 asian

is this doable any one?

thanks

table:
CREATE TABLE [dbo].[Clients](
[ClientId] [int] NOT NULL,
[LastName] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[MiddleName] [varchar](1) NULL,
[Gender] [varchar](1) NULL,
[DateOfBirth] [int] NULL,
[EthnicityId] [int] NULL
)
ethnicity is described in another table
/*---------------------------*/

DECLARE @Sixties TABLE (
patients int,
ethnicityid int,
explain char(30)
);

/*=========================*/
INSERT INTO @Sixties (patients, ethnicityid,explain)
SELECT COUNT(derivedtbl_1.dob) AS patients, derivedtbl_1.EthnicityId, Ethnicity.Description
FROM (SELECT CAST(SUBSTRING(CAST(DateOfBirth AS VARCHAR(8)), 1, 4) + '-' + SUBSTRING(CAST(DateOfBirth AS VARCHAR(8)), 5, 2)
+ '-' + SUBSTRING(CAST(DateOfBirth AS VARCHAR(8)), 7, 2) AS DATETIME) AS dob, Gender, EthnicityId
FROM Clients
WHERE (DateOfBirth > 19000101)) AS derivedtbl_1 INNER JOIN
Ethnicity ON derivedtbl_1.EthnicityId = Ethnicity.EthnicityId
WHERE (DATEDIFF(YEAR, derivedtbl_1.dob, GETDATE()) BETWEEN 60 AND 74)
GROUP BY derivedtbl_1.EthnicityId, Ethnicity.Description

/*------------------Finally I have this -----------*/
IF EXISTS(SELECT description FROM ethnicity)
select _SX.patients as six,_st.patients AS seven,_et.patients AS eight
FROM @Sixties AS _SX
LEFT JOIN @SEVENTIES _ST ON
_sx.explain= _st.explain

LEFT JOIN @EIGHTIES _ET ON
_st.explain= _et.explain OR _sx.explain= _et.explain
/* this doesn't give all the available results.----------*/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-09-12 : 12:29:03

With the DateOfBirth field being INT, this is beyond ugly. Any possibility of changing it to a datetime?


/*
CREATE TABLE [dbo].[Clients](
[ClientId] [int] identity(1,1),
[LastName] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[MiddleName] [varchar](1) NULL,
[Gender] [varchar](1) NULL,
[DateOfBirth] [datetime] NULL,
[EthnicityId] [int] NULL
)

create table ethnicity (EthnicityID int identity(1,1), [Description] varchar (20))

insert into ethnicity ([description])
select 'White' union all select
'Unreported' union all select
'Asian'

insert into clients (lastname,firstname,middlename,Gender,DateOfBirth,ethnicityID)

select 'Smith','Fred',null,'m','1943-01-02',1 union all select
'Smith','Fred',null,'m','1940-01-02',1 union all select
'Smith','Fred',null,'m','1941-01-02',1 union all select
'Smith','Fred',null,'m','1942-01-02',1 union all select
'Smith','Fred',null,'m','1934-01-02',1 union all select
'Smith','Fred',null,'m','1933-01-02',1 union all select
'Smith','Fred',null,'m','1940-01-02',2 union all select
'Smith','Fred',null,'m','1943-01-02',2 union all select
'Smith','Fred',null,'m','1920-01-02',3 


*/
select 
	sum (case when datediff(yy,c.dateofbirth,getdate())  between 60 and 69 then 1 else 0 end) as 'Sixties' 
	,sum (case when datediff(yy,c.dateofbirth,getdate())   between 70 and 79 then 1 else 0 end) as 'Seventies' 
	,sum (case when datediff(yy,c.dateofbirth,getdate())   between 80 and 89 then 1 else 0 end) as 'Eighties' 
	,et.[Description]
FROM
	clients c
inner join
	ethnicity et
on
	c.ethnicityID = et.ethnicityID
group by 
	et.[Description]


/*

drop table clients

drop table ethnicity

*/

otherwise you will have to change your birthdate to a datetime on the fly.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources