CODEWITHSUNDEEP
sqlsql-server
Brian
Brian

Reputation: 87

SQL Server How to combine 3 queries into one?

I want to condense the following three queries into one query and out put the totals into 3 columns. Oh and how do I make it so I don't have to declare the date. I want it to "know" the current date, month, and year. 

DECLARE @myDate as datetime
SET @myDate = '2015-01-1'
select SUM(Amount) as 'Day Total' 
from [Accounting].[dbo].[HandPay] 
where AccountingDate>=@myDate and AccountingDate<dateadd(day,1,@myDate)

select SUM(Amount) as 'Month Total' 
from [Accounting].[dbo].[HandPay] 
where AccountingDate>=@myDate and AccountingDate<dateadd(MONTH,1,@myDate)

select SUM(Amount) as 'Day Total' 
from [Accounting].[dbo].[HandPay] 
where AccountingDate>=@myDate and AccountingDate<dateadd(year,1,@myDate)

What is the best way to do this? Thanks!

Thanks for all the super fast responses! This is now solved.

Upvotes: 4

Views: 170

Answers (3)

Joe Farrell
Joe Farrell

Reputation: 3542

If I'm understanding the problem correctly, then something like this ought to work:

declare @today date = convert(date, getdate());
select
    [Day Total]   = sum(case when [AccountingDate] >= @today and [AccountingDate] < dateadd(day,   1, @today) then [Amount] else 0 end),
    [Month Total] = sum(case when [AccountingDate] >= @today and [AccountingDate] < dateadd(month, 1, @today) then [Amount] else 0 end),
    [Year Total]  = sum(case when [AccountingDate] >= @today and [AccountingDate] < dateadd(year,  1, @today) then [Amount] else 0 end)
from
    [Accounting].[dbo].[HandPay];

Note that [Month Total] and [Year Total] don't give the sums of the entries that occur within the current month/year, but rather the sum of the entries that occur within a month/a year of today's date. I'm not sure if that's what you want, but it seems consistent with the original queries.

UPDATE: As suggested by D Stanley below, you can simplify this a bit since you know that the date ranges that compose the [Day Total] and [Month Total] sums are enclosed entirely within the date range that composes the [Year Total] sum. Here's what this might look like:

declare @today date = convert(date, getdate());
select
    [Day Total]   = sum(case when [AccountingDate] < dateadd(day,   1, @today) then [Amount] else 0 end),
    [Month Total] = sum(case when [AccountingDate] < dateadd(month, 1, @today) then [Amount] else 0 end),
    [Year Total]  = sum([Amount])
from
    [Accounting].[dbo].[HandPay]
where
    [AccountingDate] >= @today and [AccountingDate] < dateadd(year, 1, @today);

Upvotes: 3

miltonb
miltonb

Reputation: 7385

One way is to write each as a sub-query with a surrounding select. I have often found this useful as it allows me to piece together the query one bit at a time. This will give you null in the column values when there is no records - which may not be what you want. Refer to @JoeFarrell's question as he has provided an example of using case to turn null into 0.

(Please excuse the strange layout, felt it was worth accentuating the ,)

DECLARE @myDate as datetime
SET @myDate = '2015-01-1'
select 
    (select SUM(sale_total)
    from [Accounting].[dbo].[HandPay]
    where AccountingDate>=@myDate and AccountingDate<dateadd(day,1,@myDate))  as 'Day Total' 
    ,
    (select SUM(sale_total) 
    from [Accounting].[dbo].[HandPay]
    where AccountingDate>=@myDate and AccountingDate<dateadd(MONTH,1,@myDate)) as 'Month Total' 
    ,
    (select SUM(sale_total) 
    from [Accounting].[dbo].[HandPay]
    where AccountingDate>=@myDate and AccountingDate<dateadd(year,1,@myDate)) as 'Day Total'

Upvotes: 1

Peter Smith
Peter Smith

Reputation: 5550

Make them all part of a 'super' select:

  DECLARE @myDate as datetime
  SET @myDate = '2015-01-1'
SELECT
(select SUM(Amount) 
from [Accounting].[dbo].[HandPay] 
where AccountingDate>=@myDate and AccountingDate<dateadd(day,1,@myDate) ) as 'Day Total',

(select SUM(Amount) 
from [Accounting].[dbo].[HandPay] 
where AccountingDate>=@myDate and AccountingDate<dateadd(MONTH,1,@myDate) )  as 'Month Total',

(select SUM(Amount)
from [Accounting].[dbo].[HandPay] 
where AccountingDate>=@myDate and AccountingDate<dateadd(year,1,@myDate) )  as 'Day Total'

Upvotes: 1

Related Questions