plz write a query for me ? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

plz write a query for me ?

Author

Topic

feeka
Starting Member

2 Posts

Posted - 2007-04-20 : 03:56:55

i have following schemas
Client_Department(departmentid,name)
Profile (profileid,username,departmentid)
Attribute(attributeid,attributename,isactive,iscorrect)
ProfileAttribute(attributeid,profileid)

my required query is

i want following records grouped by client_department.name

profile.username(total number of users in each department)
users which are more then 20 times in profileAttribute "As Completed"
Regards
Feeka

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-20 : 04:19:55

Please post some sample data and desired results

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-20 : 04:22:18

Post your DDL, sample data and expected result

feeka
Starting Member

2 Posts

Posted - 2007-04-20 : 04:34:04

quote:
Originally posted by Vinnie881

Please post some sample data and desired results

here is sample data
client_department(1,HR 2,mgm 3,HRC 4,HEAD)
Profile(1,naveed,1 2,jhon,1 3,alen,4 4,xe,3)
Attribute(11,question1,0,0 12,answer1,1,0 13,answer1,1,1)
profileAttribute(11,1 11,2 12,3 13,2)

my desired results are
1.all the records should be grouped by client_department.name
2.total number of users in profile
3.number of users > 1 in profileattribute
Regards
feeka

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-20 : 06:25:50

i am not clear about the your requirement 1 which says all records should be grouped by department name always give the desired output u need...check the following helps u

declare @Client_Department table (departmentid int ,name varchar(30))
declare @Profile table (profileid int ,username varchar(30) ,departmentid int )
declare @Attribute table (attributeid int,attributename varchar(30),isactive int ,iscorrect int)
declare @ProfileAttribute table (attributeid int ,profileid int )

insert @client_department
select 1,'HR' union
select 2,'mgm' union
select 3,'HRC' union
select 4,'HEAD'
insert @Profile
select 1,'naveed',1 union
select 2,'jhon',1 union
select 3,'alen',4 union
select 4,'xe',3

Insert @Attribute
select 11,'question1',0,0 union
select 12,'answer1',1,0 union
select 13,'answer1',1,1

insert @profileAttribute
select 11,1 union
select 11,2 union
select 12,3 union
select 13,2

--1.all the records should be grouped by client_department.name

--2.total number of users in profile
select cd.departmentid, cd.name, No_user = case when a.cc is null then 0 else a.cc end from @Client_Department cd left outer join
(select departmentid, cc = count(departmentid) from @Profile group by departmentid) a on
cd.departmentid = a.departmentid

--3.number of users > 1 in profileattribute
select username from @Profile p join
(select profileid, cc = count(profileid) from @profileAttribute group by profileid) a on p.profileid = a.profileid and a.cc > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources