?
Solved

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

Posted on 2011-04-28
6
Medium Priority
?
337 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!
0
Comment
Question by:Ike23
6 Comments
 
LVL 9

Expert Comment

by:radcaesar
ID: 35486146
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?
0
 
LVL 4

Author Comment

by:Ike23
ID: 35486166
Yep, basically I just want a list of each month and totals for Large and Small which can be zero.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35486192
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

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 40

Accepted Solution

by:
mrjoltcola earned 2000 total points
ID: 35486199
Try this:

select
 sum(case member_size_id when 2 then 1 else 0 end) as SmallUpgrades,
 sum(case member_size_id when 1 then 1 else 0 end) 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)

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35489540
Union
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 35489562
Try This

 sql1.sql
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question