We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to combine results of two queries that are both using a count and show by month?

Medium Priority
369 Views
Last Modified: 2012-05-11
I am trying to do a report to show how many companies were invoiced each month broken down into company size (large and small).

I have two queries that I use that work fine on their own but when I do a Union on them I get two rows per month if both a large and small company were invoiced.

What I'd like to have is just one row per month.

Here is an example of the queries I am using:

declare @start datetime
declare @end datetime

set @start = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
set @end = GETDATE()


select count(*) as LargeUpgrades, DATEADD(mm,DATEDIFF(mm,0,date_last_invoice),0) as MonthDate from Company
where member_type_id in (1,2) AND
member_size_id = 1
AND date_last_invoice > @start
group by DATEADD(mm,DATEDIFF(mm,0,date_last_invoice),0)


Select count(*) as SmallUpgrades, DATEADD(mm,DATEDIFF(mm,0,date_last_invoice),0) as MonthDate
from Company
where member_type_id in (1,2) AND
member_size_id = 2
AND date_last_invoice > @start
group by DATEADD(mm,DATEDIFF(mm,0,date_last_invoice),0)

When I do a Union I get an output similar to this:
large      small      MonthDate
0      4      2011-01-01 00:00:00.000
1      0      2011-01-01 00:00:00.000
0      3      2011-02-01 00:00:00.000
0      4      2011-03-01 00:00:00.000
0      6      2011-04-01 00:00:00.000


Is there a better way to write this so I can get the Large and Small totals and only one row per month?

Thanks!
Comment
Watch Question

0      4      2011-03-01 00:00:00.000
0      4      2011-01-01 00:00:00.000

The above result is fine as far your requirenet, One invlice for month 03 and other for month 01. No duplicates, right?

Author

Commented:
Yep, basically I just want a list of each month and totals for Large and Small which can be zero.
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
try this
declare @start datetime
declare @end datetime

set @start = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
set @end = GETDATE()

;with CTE
as
(
	select case when member_size_id = 1 then 
			 1
		   else
			 0
		   end as LargeUpgrades, 
		   case when member_size_id = 2 then 
			 1
		   else
			 0
		   end as SmallUpgrades, 
		   DATEADD(mm,DATEDIFF(mm,0,date_last_invoice),0) as MonthDate 
	from Company
	where member_type_id in (1,2) 
	AND (member_size_id = 1 or member_size_id = 2)
	AND date_last_invoice > @start
)

select SUM(LargeUpgrades) LargeUpgrades, SUM(SmallUpgrades) SmallUpgrades, MonthDate
from CTE A  
group by A.MonthDate

Open in new window

Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
Union
G Trurab KhanSnr. Development Manager
CERTIFIED EXPERT

Commented:
Try This

 sql1.sql
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.