Combine queries to show one set of results

I have two separate queries where I need to match up and combine together based on a date value.

select convert(varchar,Delivery_Date,101) as 'Date',
sum(case when billing_group = '3' and delivery_date >= dateadd(day,datediff(day, 0, GetDate()) - 30, 0) then 1 else 0 end) as 'OR to WA'
    from orders 
    where delivery_Date >= dateadd(day, datediff(day, 0, GetDate()) - 30, 0) and billing_group in ('3') 
    group by delivery_date
    order by date desc


select convert(varchar,Origin_Date,101) as 'Date',
sum(case when billing_group = '4' and origin_date >= dateadd(day, datediff(day, 0, GetDate()) - 30, 0)then 1 else 0 end) as 'WA to OR'
        from orders 
        where origin_Date >= dateadd(day, datediff(day, 0, GetDate()) - 30, 0) and billing_group in ('4') 
        group by origin_Date
        order by date desc

Please note that I am using a different date value (delivery_date) in the first query and a different for the second (origin_date)

Thanks for the help!!

Answer

I think you just want conditional aggregation, but your query offers other room for improvement:

select convert(varchar(255), v.thedate, 101) as [Date],
       sum(case when o.billing_group = 3 then 1 else 0 end) as [OR to WA],
       sum(case when o.billing_group = 4 then 1 else 0 end) as [WA to OR]
from orders o cross apply
     (values (case when o.billing_group = 3 then delivery_date else o.origin_date end)
     ) v(the_date)
where v.thedate >= dateadd(day, -30, cast(getdate() as date)) and
      o.billing_group in (3, 4) 
group by convert(varchar(255), v.thedate, 101)
order by v.thedate desc

Notes:

• Never use varchar() without a length parameter. The length varies by context and it might not do what you expect.
• I am guessing that billing_group is a number. If so, don’t use single quotes. Do use single quotes if I’m wrong.
• I cannot tell what the data type of delivery_date is. It is safer to aggregate by the full expression, rather than just the column.
• Do not use single quotes for column aliases. Only use single quotes for string and date constants.
• There is no reason to use the “trick” of adding days to 0 to remove a time component. Instead, just convert to date.
• The condition on delivery_date does not have to appear in both the case expression and the where clause.