sql-server - 如何在 SQL 中使用 sum 获取当前日期的数据？

Question

我正在处理一个 SQL 查询，试图获取当天/日期的总和数据。任何人都可以看看我的查询，并为我找到一个可行的解决方案吗？

SELECT SUM(amount) 
FROM tbl_expense_record 
WHERE dateonly = CAST(GETDATE() AS Date)

但是当我提到特定日期时，我会得到数据，比如

SELECT SUM(amount) AS total 
FROM tbl_expense_record 
WHERE dateonly = '2020-06-12'

我希望代码自动选择当前日期。另外我想获取整个一周和一个月的范围日期的总和！

标签： sql-server

Answer 1

select datename(month, '2020-06-12'), datename(month, getdate());


--1week
SELECT SUM(amount) AS total 
FROM tbl_expense_record 
WHERE dateonly >= dateadd(week, -1, cast(getdate() as date))
and dateonly <= cast(getdate() as date)

--1month
SELECT SUM(amount) AS total 
FROM tbl_expense_record 
WHERE dateonly >= dateadd(month, -1, cast(getdate() as date))
and dateonly <= cast(getdate() as date)

--build muscle memory (it is always safe to check for < date+1 instead of <= date)

--1month
SELECT SUM(amount) AS total 
FROM tbl_expense_record 
WHERE dateonly >= dateadd(month, -1, cast(getdate() as date))
and dateonly < dateadd(day, 1, cast(getdate() as date));


--6months
SELECT SUM(amount) AS total 
FROM tbl_expense_record 
WHERE dateonly >= dateadd(month, -6, cast(getdate() as date))
and dateonly < dateadd(day, 1, cast(getdate() as date));


if not exists
(
select *
FROM tbl_expense_record 
WHERE dateonly >= dateadd(month, -1, cast(getdate() as date))
and dateonly < dateadd(day, 1, cast(getdate() as date))
)
begin
    select 'no rows within the last month'
end
else
begin
    select 'there are rows within the last month';
end;

例子：

declare @tbl_expense_record table(dateonly date, amount decimal(9,2));

insert into @tbl_expense_record
values ('20200501', 10), ('20200612', 10), ('20200613', 11), ('20200614', 12),
('20200710', 5), ('20200720', 6), ('20200820', 20), ('20200825', 30),
('20201102', 1), ('20201110', 2), ('20201120', 3);

--aggregation per month, for all rows
select year(dateonly) as _year, month(dateonly) as _month, sum(amount) as sum_amount_per_month, count(*) as rows_per_month
from @tbl_expense_record
group by year(dateonly), month(dateonly);

--aggregation per iso-week
select year(dateonly) as _year, datepart(iso_week, dateonly) as _isoweek, sum(amount) as sum_amount_per_isoweek, count(*) as rows_per_isoweek
from @tbl_expense_record
group by year(dateonly), datepart(iso_week, dateonly);



--aggregation per month, for all rows with a dateonly that falls in the last month
--check the difference between aggregation per month earlier and this..
--filter rows first == where .... and then aggregate
--there are two rows with dateonly > 06 november (the row at 05 is filtered out by the where clause)
select year(dateonly) as _year, month(dateonly) as _month, sum(amount) as sum_amount_per_month, count(*) as rows_per_month
from @tbl_expense_record
where dateonly >= dateadd(month, -1, cast(getdate() as date))
and dateonly < dateadd(day, 1, cast(getdate() as date))
group by year(dateonly), month(dateonly);



--aggregate per week diff from today/getdate()
select 
datediff(week, getdate(), dateonly) as week_diff_from_today, 
dateadd(day,
        --datepart(weekday..) is used...account for @@datefirst setting / set datefirst
        1-(@@datefirst+datepart(weekday, dateadd(week, datediff(week, getdate(), dateonly), cast(getdate() as date))))%7, 
        dateadd(week, datediff(week, getdate(), dateonly), cast(getdate() as date)))
 as startofweek, 
dateadd(day, 6, --add 6 days to startofweek
dateadd(day, 
        --datepart(weekday..) is used...account for @@datefirst setting / set datefirst
        1-(@@datefirst+datepart(weekday, dateadd(week, datediff(week, getdate(), dateonly), cast(getdate() as date))))%7, 
        dateadd(week, datediff(week, getdate(), dateonly), cast(getdate() as date)))
) as endofweek,

sum(amount) as sum_amount, count(*) as rows_within_week
from @tbl_expense_record
group by datediff(week, getdate(), dateonly);