SQL Loop through table by date and sum a value

Question

I have a table with just a few columns:

BUS_DATE      VALUE         EXP_DATE
6/29/2015     60            6/29/2015
6/30/2015     100           6/30/2015
6/30/2015     50            6/30/2015
6/30/2015     25            7/1/2015
7/1/2015      75            7/1/2015

I'm just looking how to loop through each [BUS_DATE] in the table and SUM the [VALUE] with some [EXP_DATE] logic

FOR EACH @BUS_DATE
     INSERT BUS_DATE, SUM(VALUE) 
     INTO #tmp 
     FROM TABLE 
     WHERE (
           BUS_DATE = @BUS_DATE 
           OR 
          (@BUS_DATE > BUS_DATE AND @BUS_DATE <= EXP_DATE)
          ) 
NEXT

Ultimately, I'd like the output to look like this:

BUS_DATE     VALUE
6/29/2015    60 
6/30/2015    175
7/1/2015     100

Thank you so much in advance!

are you aware that for all the rows in your example, EXP_DATE >= BUS_DATE? In other words, none of the rows in your example would be excluded. Also, the 7/1/2015 row should total 75, right? — Beth, Jul 02 '15 at 15:07
I want the 7/1/2015 SUM to include both of these entries because of the EXP_DATE: 6/30/2015 25 7/1/2015 7/1/2015 75 7/1/2015 This is where the 100 comes from for 7/1/2015 — Jay, Jul 02 '15 at 15:15
I seem to be struggling to ask it correctly. I want to loop through each BUS_DATE in table, and SUM the VALUE. However, there are VALUES that have a later EXP_DATE (expiration_date) that I want to use for each day between that BUS_DATE and EXP_DATE sum. I've edited the WHERE clause above to I believe what I;m looking for. thanks again for bearing with me here — Jay, Jul 02 '15 at 15:30

score 2 · Answer 1 · edited May 23 '17 at 12:29

2

This is a simple GROUP BY query:

SELECT BUS_DATE, SUM(VALUE) AS VALUE
FROM MyTable
GROUP BY BUS_DATE

If you would like to store the results in #tmp table, use insert from select syntax.

edited May 23 '17 at 12:29

Community

1
1

answered Jul 02 '15 at 14:39

Sergey Kalinichenko

714,442
84
1,110
1,523

Thank you but I wish it was this simple. I need to add EXP_DATE logic to it, so I don't think this would work. For example, BUS_DATE 7/1/2015 if grouped only has 1 entry in my sample table. It wouldn't be able to include the BUS_DATE 6/30/2015 entry in it that has an EXP_DATE of 7/1/2015. Maybe I should have just asked for the EXP_DATE logic to be included to keep everybody from being confused? thoughts? thanks so much – Jay Jul 02 '15 at 14:52
@Jay If there is additional logic, please do include it. If the logic changes the question significantly, consider writing up a separate question, because a big change would render the current answers obsolete. The question should describe what needs to happen in as much details as you can provide, perhaps with a sqlfiddle.com example. – Sergey Kalinichenko Jul 02 '15 at 14:59
I just editted my original post so the output I'm looking for should make sense now. I just want to grab each unique bus_date, sum the value and include other bus_date entries if the exp_date is >= the bus_date. Please have a look, hopefully this will make more sense. thank you so much in advance!! – Jay Jul 02 '15 at 15:01

score 1 · Accepted Answer · answered Jul 03 '15 at 08:50

1

if i understand your problem correctly:

you calculate a list of BUS_DATE (SELECT DISTINCT BUS_DATE FROM TABLE). each existing BUS_DATE will appear once.

you join your original table with that list according your EXP_DATE logic.

SELECT lst.BUS_DATE
     , val = SUM(VALUE) 
INTO #tmp 
FROM ( SELECT DISTINCT BUS_DATE FROM TABLE ) lst
JOIN TABLE dat
  ON ( lst.BUS_DATE = dat.BUS_DATE )
       OR 
     ( lst.BUS_DATE > dat.BUS_DATE AND lst.BUS_DATE <= dat.EXP_DATE )
GROUP BY lst.BUS_DATE

answered Jul 03 '15 at 08:50

A ツ

1,267
2
9
14

YES! This worked! I had to tweek my date operators a little but this JOIN did it for me and returns my results in great time. Thank you very much for sticking with my issue and helping me figure out a solution! It's much appreciated! – Jay Jul 06 '15 at 18:50

sarin · Answer 3 · 2015-07-02T15:08:40.540

0

UPDATED: This now only sums the values when EXP_DATE > BUS_DATE

Select BUS_DATE, Sum(CASE WHEN EXP_DATE >= BUS_DATE THEN VALUE ELSE 0 END) AS [VALUE]
FROM MyTable
GROUP BY BUS_DATE

edited Jul 02 '15 at 15:08

answered Jul 02 '15 at 14:39

sarin

5,227
3
34
63

Thank you for the response but I must have led onto the wrong solution. I've edited my original post. I don't believe a simple GROUP would work in this case because for some rows with EXP_DATE's in the future I'd like to use for multiple BUS_DATES. Please have a look if you have time and thank you very much in advance!! – Jay Jul 02 '15 at 15:05
@Jay I think this is what you are after? – sarin Jul 02 '15 at 15:08

score 0 · Answer 4 · answered Jul 02 '15 at 14:39

0

SELECT BUS_DATE, SUM(VALUE) FROM tblName Group By BUS_DATE

In your case here...

SELECT BUS_DATE, SUM(VALUE) INTO #tmp FROM TABLE (where exp_date logic here)

answered Jul 02 '15 at 14:39

Christian Phillips

18,399
8
53
82

Thank you for the response but I must have led onto the wrong solution. I've edited my original post. I don't believe a simple GROUP would work in this case because for some rows with EXP_DATE's in the future I'd like to use for multiple BUS_DATES. Please have a look if you have time and thank you very much in advance!! – Jay Jul 02 '15 at 15:05

SQL Loop through table by date and sum a value

4 Answers4