0

I'm working on a SQL query trying to fetch sum data for the current day/date. Can anyone have a look at my query, and find me a working solution?

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

Here is the result

But I get data when mentioning a specific date in where condition like

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

The result

I want the a code to auto pick current date. Also I would like to fetch sum of ranged dates like a whole week, and a month!

Zhorov
  • 28,486
  • 6
  • 27
  • 52
Ameer Soomro
  • 48
  • 8
  • 1
    Run `select getdate()`. My guess is that it will output `2020-12-06` (yyyy-mm-dd) and not `2020-06-12` (yyyy-dd-mm). That would explain the difference. Also, have a look at [this question and answer](https://stackoverflow.com/questions/33228765/how-do-i-specify-date-literal-when-writing-sql-query-from-sql-server-that-is-lin). – Sander Dec 06 '20 at 10:20
  • the order doesn't matter, I have worked around with this, and casting, and conversion, but the results remained unchanged. I don't get why getdate would throw null value back. – Ameer Soomro Dec 06 '20 at 10:32
  • Please provide sample data for a [minimal, reproducable example](https://stackoverflow.com/help/minimal-reproducible-example). Also, if `select cast(getdate() as date), cast('2020-06-12' as date)` does not produce identical values, then your casting/conversion is flawed. – Sander Dec 06 '20 at 10:36
  • Please, [double check your conversion](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=2065a05ceb57e70b15bfbee63d6a2827). – Sander Dec 06 '20 at 10:44
  • Please see the second image showing data exists in the table, and the column names are mentioned in the question. By trying your suggested query I did get identical results. – Ameer Soomro Dec 06 '20 at 10:48
  • They all work fine individually showing same dates in each resulted column, but this is not my problem, when I put the getdate code into my query for fetching amount it throws null result. but using specific date manually does show data. – Ameer Soomro Dec 06 '20 at 10:52
  • Again, please provide _sample data_ that we can copy-paste and allows us to [attempt to reproduce](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d49df87d4452341d54289da283ed5dc3) your issue. – Sander Dec 06 '20 at 11:07
  • No need for sample data, I'm doing the same as you have done in the link, It works there , but not in my 2016 version of sql. this is very strange. – Ameer Soomro Dec 06 '20 at 11:12
  • What is the data type of the `dateonly` column? – Zhorov Dec 06 '20 at 11:25
  • date @Zhorov check Sander's link, it works fine there. – Ameer Soomro Dec 06 '20 at 11:28

1 Answers1

2
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;

Examples:

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);
lptr
  • 1
  • 2
  • 6
  • 16
  • I appreciate how much efforts you guys are giving in, My issue still persists after trying all these queries I'm still getting "Null" result :/ – Ameer Soomro Dec 06 '20 at 12:00
  • Bravo! It worked. Could you please help me understand what's going on here I've gone bonkers completely. – Ameer Soomro Dec 06 '20 at 12:05
  • ...there are no rows in the table that fall in the last week, or last month...check the last query..edited answer – lptr Dec 06 '20 at 12:06
  • 6months actually works, now my question is how will I get one week record and a month seperately? my table contains 2 days data – Ameer Soomro Dec 06 '20 at 12:12
  • thanks alot man, everyhting works perfect! plus got alot new stuff for learning -thumbs up- – Ameer Soomro Dec 06 '20 at 12:45
  • ..@AmeerSoomro .. another edit...for correct week boundaries [datediff(week,..) calculates difference between normal weeks, starting on sunday] – lptr Dec 06 '20 at 15:41
  • thanks again, I think I now have enough queries to work on. – Ameer Soomro Dec 06 '20 at 15:45
  • cool!!, when problems arise and if you get stuck, just ask a new question, the community is very helpful here. Success!!!! – lptr Dec 06 '20 at 15:48