CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jay
Jay

Reputation: 465

SQL Loop through table by date and sum a value

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!

Upvotes: 0

Views: 6070

Answers (4)

A ツ
A ツ

Reputation: 1267

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

Upvotes: 1

sarin
sarin

Reputation: 5307

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

Upvotes: 0

Christian Phillips
Christian Phillips

Reputation: 18749

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)

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726509

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.

Upvotes: 2

Related Questions