How to select aggregate of values within a date range from a table

Question

Let me just give a simplified example to illustrate what I mean.

Let's say we have a table named accounts with columns deposits, dmonth, and dyear. Select sum(deposits) will get the sum of ALL values under the deposits column. What is the SQL query to get sum of deposits for, say, period earlier than July 2016

`SELECT SUM(deposits) as Deposits_Sum WHERE CONVERT(int, dyear+dmonth) < 201607` - Depending on how year and month are stored, you'll have to change the `201607` part. My example assumes year is stored as 4-digit and month as 2-digit. — Tyler Roper, Sep 13 '16 at 17:37
Months less than october are stored as single digits e.g as 7 not 07, so will 20167 be correct? — bodesam, Sep 13 '16 at 17:54
Comparing Dec 2015 and Sept 2016, is 201512 < 20169 ? This one is a bit tricky. — bodesam, Sep 13 '16 at 18:13
We could tell you for sure if you provide some sample data. There is a lot of guessing going on below — kjmerf, Sep 13 '16 at 18:13
Duh @bodesam, not sure how I did not think of that. You could convert single digit months to double digit, however truthfully I much prefer Aaron D's answer to my own. I'd suggest going that route instead! — Tyler Roper, Sep 13 '16 at 18:15

score 2 · Accepted Answer · answered Sep 13 '16 at 18:10

2

Here is another way:

SELECT SUM(Deposits)
FROM   Accounts
WHERE  (dYear = 2016 AND dMonth < 7)
OR     dYear < 2016

answered Sep 13 '16 at 18:10

Aaron Dietz

10,137
1
14
26

Thanks. Nice, but it wont capture years earlier than 2016 – bodesam Sep 13 '16 at 18:19
Sorry. OR dYear < 2016 should take care of that. – bodesam Sep 13 '16 at 18:24
@bodesam Yup, exactly. – Aaron Dietz Sep 13 '16 at 18:26

score 1 · Answer 2 · answered Sep 13 '16 at 17:44

Depending on how your dyear and dmonth or stored, you may need to do something like this:

SELECT SUM(deposits) as Deposits_Sum WHERE CONVERT(int, dyear+dmonth) < 201607

What i've done here is combined your dyear and dmonth to create a value that's chronologically sortable. I then convert this to an integer for comparison purposes.

Using this logic, we can find all records where this value is less than July 2016 by doing < 201607 in the WHERE clause. This final piece may require some modification if your years are not stored as yyyy and/or your months are not MM.

score 0 · Answer 3 · edited May 23 '17 at 10:33

0

Assuming dmonth contains something date time like you could do this with a where clase

Select sum(deposits) as Sum from accounts where dmonth < 07-01-16 00:00:000.000

How to select aggregate of values within a date range from a table

3 Answers3