How to create a report with two columns from different queries?

A question was posted to the SMS mailing list.

 

I created 2 queries

·         One counts how many clients there are

·         One counts how many are missing the client

 

I want my results to look like this.

 

Domain   Clients installed    Clients missing

 

XXX                   50                     2

 

 

How do can I do this? [Since the text above is out of context, I have paraphrased the question]

 

This is how I have done this task in the past, it might not be the best way to do, but it works. This is also an example of how to create a temporary table.

 

(SELECT  Resource_Domain_OR_Workgr0 AS ‘Domain’, COUNT(*) as ‘NOTok’ into #SMSCliNot FROM v_R_System WHERE (Operating_System_Name_and0 LIKE ‘%server%’) and (Client0 IS NULL) GROUP BY Resource_Domain_OR_Workgr0)

(SELECT  Resource_Domain_OR_Workgr0 AS ‘Domain’, COUNT(*) as ‘CliOK’ into #SMSCli FROM v_R_System WHERE (Operating_System_Name_and0 LIKE ‘%server%’) and (Client0 = ‘1’) GROUP BY Resource_Domain_OR_Workgr0)

Select #SMSCli.Domain as ‘Domain’, CliOk as ‘Clients installed’, NOTok as ‘Clients missing’  from

#SMSCli Full outer join #SMSCliNot on #SMSCli.Domain = #SMSCliNot.Domain

order by #SMSCli.Domain

drop table #SMSCli

drop table #SMSCliNot

 

Leave a Reply

Your email address will not be published. Required fields are marked *