Adding a column on output - 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

Adding a column on output

Author

Topic

rpettigr
Starting Member

4 Posts

Posted - 2005-09-27 : 17:27:47

I have two queries I would like to combine the output on. they are as follows:

Select substring(WrkSta.[Name],1,2) 'Location'
,count (aexe.ReturnCode) as '# Patched'
from WrkSta, AeXEvt_AeX_SWD_Execution aexe
where WrkSta.WrkStaId=aexe.WrkStaId
and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%'
or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%'
or WrkSta.[Name] like 'ET%')
and (aexe.returncode='0' or
aexe.returncode ='3010')
and aexe.AdvertisementName like 'MS05-035-043%'
group by substring (WrkSta.[Name], 1,2)

---
Returns :
Location # Patched
EP 102
ES 1986
ET 19
AB 174
SU 6
EM 506

and the second one:

Select substring(WrkSta.[Name],1,2) 'Location'
,count (coll.WrkStaId ) as '# Workstation'
from WrkSta join AeXNSCollectionMembership coll on WrkSta.WrkStaId=Coll.WrkStaId
where coll.CollectionGuid = '38F5DAFC-E09D-49A5-A0FD-370983CA7596'
and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%'
or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%'
or WrkSta.[Name] like 'ET%')
group by substring (WrkSta.[Name], 1,2)

---
returns:

Location # Workstations
EP 178
ES 2299
ET 24
AB 215
SU 13
EM 582

What I need is :

Location # Workstations # Patched
EP 178 102
EI 2299 1986
ET 24 19
AB 215 174
SU 13 6
EM 582 582

No mater how I try to do a join to do this in a single query I end up with what looks like a cross-join and # workstations and # Patched jump to huge numbers. I obviously am having a problem understanding how to set up the select statements so that I can do this in one query or am I following the wrong direction and should be trying something else?
Once again I appreciate your help in advance....

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-27 : 20:04:31

There may be a single query, but without table structures, relationships and sample data, I'll just use the two queries you provided..



SELECT X.Location, X.[# Patched], Y.[# Workstation]
  FROM (
    Select  substring(WrkSta.[Name],1,2) 'Location' ,count (aexe.ReturnCode) as '# Patched'
      from WrkSta, AeXEvt_AeX_SWD_Execution aexe
      where WrkSta.WrkStaId = aexe.WrkStaId
      and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%' 
      or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%' 
      or WrkSta.[Name] like 'ET%')
      and (aexe.returncode='0' or  aexe.returncode ='3010')
      and aexe.AdvertisementName like 'MS05-035-043%'
      group by substring (WrkSta.[Name], 1,2)
  ) X
  INNER JOIN ( 
    Select  substring(WrkSta.[Name],1,2) 'Location' ,count (coll.WrkStaId ) as '# Workstation'
      from WrkSta join AeXNSCollectionMembership coll on WrkSta.WrkStaId=Coll.WrkStaId
      where coll.CollectionGuid = '38F5DAFC-E09D-49A5-A0FD-370983CA7596'
      and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%' 
      or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%' 
      or WrkSta.[Name] like 'ET%')
      group by substring (WrkSta.[Name], 1,2)
  ) Y ON Y.Location = X.Location

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources