-1

Having some problems finding an answer to what I think is a simple query but I'm very green with SQL:

YR   MO  ID FLAG  RETURN
2001 01  1   1     3.00
2001 02  1   2     4.00
2001 03  1   3    -1.00
2001 04  1   4     1.00
2001 05  1   5     1.00
2001 06  1   6     1.00
2001 07  1   7     1.00
2001 08  1   8     1.00
2001 09  1   9     1.00
2001 10  1   10    1.00
2001 11  1   11    2.00
2001 12  1   12    1.00
2002 12  2   3     1.00
2002 04  2   0     0.05

I'd like a new column next to sum the previous 12 RETURN values WHERE FLAG = 12. Any help is greatly appreciated!

The data will be sorted by ID, then Year, and Month so it should be order sequentially.

The output would be (3+4+-1+1+1+1+1+1+1+1+2+1) = 16

I'd like the output (16) in the FLAG=12 row

user2977664
  • 97
  • 2
  • 11
  • Is your ordering for "Previous 12 RETURN values" done by year and month? Does `ID` figure into that ordering? – JNevill Dec 05 '16 at 15:21
  • 1
    Your question suggests that there is some sort of order to the data. What is the order? Also, you need to tag ONLY the DBMS you are actually using. The query will be greatly different for mysql and sql server. Which one are you actually using? And if you could provide enough sample data so we can see what you are trying to do AND the results you expect it would help us to know if we are even solving the problem you are asking. – Sean Lange Dec 05 '16 at 15:21
  • Add proper sample data and expected data. Most importantly the DBMS you are using `Mysql` <> `Sql Server` – Pரதீப் Dec 05 '16 at 15:39
  • @JNevill Ordering is done by ID first to group it together but then chronological order. – user2977664 Dec 05 '16 at 19:02
  • @SeanLange edited for SQL SERVER only and added sample data/output. Thanks! – user2977664 Dec 05 '16 at 19:02

2 Answers2

1

So, there are a couple issues with what you are attempting. First, you will need to either programmatically or administratively (through the UI) create the new column; the select call will not do this for you. Next, you need to be sure you want that data in your schema as it will be very 'odd' to have a column that sums flagged values. It seems as if you want to know that result but don't necessarily need to store it. If that is true (or can be made true), then I would suggest creating a select call that uses the 'sum', 'order by ... desc' (this means you need to know the ordering) and 'limit 12' functions. Given any row where the Flag is 12, you should be able get the result you want with a single call.

Just another note, since you've mentioned two different DBMSs, make sure you validate the SQL against both; I'm fairly certain you can find a generic request that will work in both systems. Good luck.

Gene
  • 534
  • 4
  • 5
  • Hi @Gene you are correct, I do not want to alter the existing DB. I'm also using SQL Server and have updated the post. Unfortunately there is no LIMIT function prior to 2012 (using 2008). I'm trying the following: SELECT [yr],[mo],[ID],[RETURN], SUM(RETURN) ORDER BY identifier, yr, mo LIMIT 12 – user2977664 Dec 05 '16 at 17:48
  • 1
    It looks like randcd's solution get's you pretty close (minus the LIMIT function). This post seems to have some good alternatives for you based on your SQL Server version. http://stackoverflow.com/questions/971964/limit-10-20-in-sql-server – Gene Dec 07 '16 at 16:19
1

Maybe a Windowed Function would fit the bill here:

SELECT *, CASE WHEN FLAG = 12 THEN SUM([RETURN]) OVER (PARTITION BY ID ORDER BY YR, MO ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) ELSE NULL END
FROM SomeTable
ORDER BY ID, YR, MO
randcd
  • 2,263
  • 1
  • 19
  • 21