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
 Development Tools
 Reporting Services Development
 Help with Query for Visual Studios Report

Author  Topic 

mikecoleman407
Starting Member

13 Posts

Posted - 2015-04-07 : 21:42:33
I have some select statements that I would like to use for a report in VS2010. Is there a way to make this into one select statement so that I can create column output for each of the sites?

so for example, 'WSISBC' is a site. I would like to include all these sites in one select statement. WSISBC will have a count of 30 and WSISBH will have a count of 27 and so on.

Right now, if I put this in the query builder and run it in the report, it only gives me output for the first select statement. I am new to SQL so I appreciate your patience and excellence in knowledge.

select COUNT(EecEmplStatus) as 'WSISBC' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in (
'WSISBC')
select COUNT(EecEmplStatus) as 'WSISBH' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSISBH')
select COUNT(EecEmplStatus) as 'WSISBM' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSISBM')
select COUNT(EecEmplStatus) as 'WSISEL' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSISEL')
select COUNT(EecEmplStatus) as 'WSISWH' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSISWH')
select COUNT(EecEmplStatus) as 'WSIWSB' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSIWSB')
select COUNT(EecEmplStatus) as 'WSIGAO' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSIGAO')
select COUNT(EecEmplStatus) as 'WSIDOJ' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSIDOJ')
select COUNT(EecEmplStatus) as 'WSIHMM' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSIHMM')
select COUNT(EecEmplStatus) as 'WSIALC' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSIALC')
select COUNT(EecEmplStatus) as 'WSIBRF' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSIBRF')
select COUNT(EecEmplStatus) as 'WSIURE' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSIURE')
select COUNT(EecEmplStatus) as 'WSIFNR' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSIFNR')
select COUNT(EecEmplStatus) as 'WSIZSW' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSIZSW')
select COUNT(EecEmplStatus) as 'WSIFMI' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSIFMI')
select COUNT(EecEmplStatus) as 'WSIFWI' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSIFWI')
select COUNT(EecEmplStatus) as 'WSIFMN' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in
('WSIFMN')

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-04-08 : 09:14:40
[code]
SELECT
SUM(CASE WHEN EecOrgLvl2 = 'WSISBC' THEN 1 ELSE 0 END) AS WSISBC
,SUM(CASE WHEN EecOrgLvl2 = 'WSISBH' THEN 1 ELSE 0 END) AS WSISBH
,SUM(CASE WHEN EecOrgLvl2 = 'WSISBM' THEN 1 ELSE 0 END) AS WSISBM
,SUM(CASE WHEN EecOrgLvl2 = 'WSISEL' THEN 1 ELSE 0 END) AS WSISEL
,SUM(CASE WHEN EecOrgLvl2 = 'WSISWH' THEN 1 ELSE 0 END) AS WSISWH
,SUM(CASE WHEN EecOrgLvl2 = 'WSIWSB' THEN 1 ELSE 0 END) AS WSIWSB
,SUM(CASE WHEN EecOrgLvl2 = 'WSIGAO' THEN 1 ELSE 0 END) AS WSIGAO
,SUM(CASE WHEN EecOrgLvl2 = 'WSIDOJ' THEN 1 ELSE 0 END) AS WSIDOJ
,SUM(CASE WHEN EecOrgLvl2 = 'WSIHMM' THEN 1 ELSE 0 END) AS WSIHMM
,SUM(CASE WHEN EecOrgLvl2 = 'WSIALC' THEN 1 ELSE 0 END) AS WSIALC
,SUM(CASE WHEN EecOrgLvl2 = 'WSIBRF' THEN 1 ELSE 0 END) AS WSIBRF
,SUM(CASE WHEN EecOrgLvl2 = 'WSIURE' THEN 1 ELSE 0 END) AS WSIURE
,SUM(CASE WHEN EecOrgLvl2 = 'WSIFNR' THEN 1 ELSE 0 END) AS WSIFNR
,SUM(CASE WHEN EecOrgLvl2 = 'WSIZSW' THEN 1 ELSE 0 END) AS WSIZSW
,SUM(CASE WHEN EecOrgLvl2 = 'WSIFMI' THEN 1 ELSE 0 END) AS WSIFMI
,SUM(CASE WHEN EecOrgLvl2 = 'WSIFWI' THEN 1 ELSE 0 END) AS WSIFWI
,SUM(CASE WHEN EecOrgLvl2 = 'WSIFMN' THEN 1 ELSE 0 END) AS WSIFMN
FROM empcomp
WHERE eecemplstatus <> 'T';
[/code]
Go to Top of Page

mikecoleman407
Starting Member

13 Posts

Posted - 2015-04-08 : 09:49:26
Thanks so much works like a champ. Can you tell me what the THEN 1 ELSE 0 does to the statement?
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-04-08 : 10:03:39
It produces either a 1 or a 0 which is then SUMmed.
Go to Top of Page

mikecoleman407
Starting Member

13 Posts

Posted - 2015-04-09 : 13:33:22
Thanks again for all your help !
Go to Top of Page
   

- Advertisement -