merge to queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

merge to queries

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2014-04-29 : 01:19:27

Hi I have the following queries which I need to merge into one, not sure the best way to do this, any help would be great

declare @Period nvarchar(50) = 'month',
@DateFrom datetime = '2012-01-01',
@DateTo datetime = '2016-01-01'

select sum(o.Tax) as TotalTax, SUM(o.Total) as NetTotal,
case when @Period = 'year' then DATENAME(year, o.DateCreated) when @Period = 'month' then DATENAME(month, o.DateCreated) when @Period = 'week' then DATENAME(week, o.DateCreated) when @Period = 'day' then DATENAME(day, o.DateCreated) end as Period
from Orders o
where o.StatusId = 4 AND o.DateCreated >= ISNULL(@DateFrom, DATEADD(month,-1,getdate())) and o.DateCreated < ISNULL(@DateTo, getdate())
group by case when @Period = 'year' then DATENAME(year, o.DateCreated) when @Period = 'month' then DATENAME(month, o.DateCreated) when @Period = 'week' then DATENAME(week, o.DateCreated) when @Period = 'day' then DATENAME(day, o.DateCreated) end

select sum(oi.BuyingCost) as TotalCost,
case when @Period = 'year' then DATENAME(year, o.DateCreated) when @Period = 'month' then DATENAME(month, o.DateCreated) when @Period = 'week' then DATENAME(week, o.DateCreated) when @Period = 'day' then DATENAME(day, o.DateCreated) end as Period
from OrderItems oi
inner join Orders o ON o.Id = oi.OrderId
where o.StatusId = 4 AND o.DateCreated >= ISNULL(@DateFrom, DATEADD(month,-1,getdate())) and o.DateCreated < ISNULL(@DateTo, getdate())
group by case when @Period = 'year' then DATENAME(year, o.DateCreated) when @Period = 'month' then DATENAME(month, o.DateCreated) when @Period = 'week' then DATENAME(week, o.DateCreated) when @Period = 'day' then DATENAME(day, o.DateCreated) end

Tusharp86
Starting Member

9 Posts

Posted - 2014-04-29 : 07:40:34

What do you mean by merge query.. do you mean that u want to two query result into single table result?

craigmacca
Posting Yak Master

142 Posts

Posted - 2014-04-29 : 09:57:32

Yes, so the result should be, TotalCost | TotalTax | NetTotal | Period

if I run TotalCost in the top query the inner join causes the TotalCost to be incorrect as the results are duplicated, it sums all the duplicated items, i think i need a select in select but not sure the best way

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources