CODEWITHSUNDEEP
sqlsql-servert-sql
Thomas
Thomas

Reputation: 63

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

enter image description here

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!!

Upvotes: 1

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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.

Upvotes: 1

China Syndrome
China Syndrome

Reputation: 993

Try using UNION and adding the missing columns then do group by and ordering

select * from (
 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',
0 as 'WA to OR'
    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

UNION

select convert(varchar,Origin_Date,101) as 'Date',
0 as 'OR to WA'
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

        ) group by [Date] 
        order by [Date] desc

Upvotes: 0

Related Questions