How to combine results of two queries that are both using a count and show by month?
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,d
where member_type_id in (1,2) AND
member_size_id = 1
AND date_last_invoice > @start
group by DATEADD(mm,DATEDIFF(mm,0,d
Select count(*) as SmallUpgrades, DATEADD(mm,DATEDIFF(mm,0,d
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,d
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!
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,d
where member_type_id in (1,2) AND
member_size_id = 1
AND date_last_invoice > @start
group by DATEADD(mm,DATEDIFF(mm,0,d
Select count(*) as SmallUpgrades, DATEADD(mm,DATEDIFF(mm,0,d
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,d
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!
ASKER
Yep, basically I just want a list of each month and totals for Large and Small which can be zero.
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.MonthDateASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Union
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?