I have two separate queries where I need to match up and combine together based on a date value.
x
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!!
Advertisement
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 todate
. - The condition on
delivery_date
does not have to appear in both thecase
expression and thewhere
clause.