CODEWITHSUNDEEP
sqlsqlite
bryan sammon
bryan sammon

Reputation: 7441

sql running total math current quarter

Im trying to figure out the total for the quarter when the only data shown is a running total for the year:

Id      Amount      Periods     Year        Type        Date
-------------------------------------------------------------
1       65          2           2014        G           4-1-12
2       75          3           2014        G           7-1-12
3       25          1           2014        G           1-1-12
4       60          1           2014        H           1-1-12
5       75          1           2014        Y           1-1-12
6       120         3           2014        I           7-1-12
7       30          1           2014        I           1-1-12
8       90          2           2014        I           4-1-12

In the data shown above. The items in type G and I are running totals for the period (in qtrs). If my query returns period 3, is there a sql way to get the data for the qtr? The math would involve retrieving the data for the 3rd period - 2nd period.

Right now my sql is something like:

SELECT * FROM data WHERE Date='4-1-12';

In this query, it will return row #1, which is a total for 2 periods. I would like it to return just the total for the 2nd period. Im looking to make this happen with SQLite.

Any help would be appreciated.

Thank alot

Upvotes: 0

Views: 150

Answers (1)

CL.
CL.

Reputation: 180210

You want to subtract the running total of the previous quarter:

SELECT Id,
       Year,
       Type,
       Date,
       Amount - IFNULL((SELECT Amount
                        FROM data AS previousQuarter
                        WHERE previousQuarter.Year = data.year
                          AND previousQuarter.Type = data.Type
                          AND previousQuarter.Periods = data.Periods - 1
                       ), 0) AS Amount
FROM data

The IFNULL is needed to handle a quarter that has no previous quarter.

Upvotes: 1

Related Questions