Geek Answers Handbook

SQL Server How to combine 3 queries into one?

I want to condense the following three queries into one query and display the totals in 3 columns. Oh, and how can I do this, so I don’t have to announce a date. I want him 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? Thank!

Thanks for all the quick answers! This is now allowed.

+4
source share
3 answers

If I understand the problem correctly, then something like this should 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];

Please note that [Month Total]they [Year Total]do not give the amount of records that occur during the current month / year, but rather the amount of records that occur during the month / year of today's date. I'm not sure if this is what you want, but it looks like the original queries.

:. D Stanley , , , , [Day Total] [Month Total], [Year Total]. :

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);
+3

"":

  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'
+1

, - . , . null , - , . @JoeFarrell, case, 0.

(, , , ,)

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'
+1

More articles:


All Articles