SQL query Dynamic Cummulative sum

Question

I have the following two tables called Tran and SPrices:

Tables

From the Tran table i know that tType = 1 means deposit, tType = 2 means buy, tType=3 means sell, cID is client ID, sID = Stock ID which is referred in SPrices, the table below.

From the SPrices table all that I am interested in is, sID which is the link with Tran, pDate and lPr.

I am trying to see if i can build a query that gives a output as below.

CID    pDate        sID  Qty  lPr     Value  

1004   09/07/2012   584  5    69.99   (69.99 * 5) 
1004   10/07/2012   584  5    69.87   (69.87 * 5)
...
...
1004   26/07/2012   584  5    72.4    (72.4 * 5)
1004   27/07/2012   584  9    71.2    (71.2 * 9)
1004   28/07/2012   584  9    72.2    (72.2 * 9)

I appreciate your help.

Thanks

Why do you mention `tType = 1 means deposit and tType = 2 means buy tType=3 means sell` — Conrad Frix, Sep 22 '12 at 05:17
I don't know ms-access, but there are a few ways to do it in mysql here: http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql — jmilloy, Sep 22 '12 at 05:19
@conradFrix cos if the tType is 3 then the AT&T has to be minus'd which i was planning to put in the earlier request. — cjv, Sep 22 '12 at 06:08
AT&T where did that come from ?? i was meaning to say @conradFrix cos if the tType is 3 then the Qty has to be minus'd which i was planning to put in the earlier request. — cjv, Sep 22 '12 at 10:14
@cjv you should edit your question to include that information. Also you should consider using [this webapp](http://www.sensefulsolutions.com/2010/10/format-text-as-table.html) to include sample data and sample output. Screenshots to data make it difficult to experiment — Conrad Frix, Sep 22 '12 at 22:32
@ConradFrix sure i did check that webapp. its very cool. I can send the mdb file if that will help. — cjv, Sep 23 '12 at 06:01

score 3 · Answer 1 · edited Sep 29 '12 at 16:37

Doubtless, there are typos and bugs in the sql below. You'll probably be unable to just paste it in and have it work perfectly, but that's why I've put in all my steps. Work your way through them and let me know which ones don't work and why and I'll try to fix them.

First, try this just to get the cumulative Qty:

SELECT Trans1.sID AS sID,
       Trans1.Qty AS Qty,
       (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=Trans1.tDate AND Trans1.sID=Trans2.sID) AS [sum]
FROM Tran AS Trans1
WHERE Trans1.sID = 584;

That should give

sID | Qty | sum
----+-----+----
584 |  5  |  5
584 |  4  |  9

If that works, we should be able to break the Qty out by type:

SELECT Trans1.sID AS sID,
       Trans1.Qty AS Qty,
       Trans1.tType AS tType,
       (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=Trans1.tDate AND Trans1.sID=Trans2.sID AND Trans2.tType=1) AS deposits,
       (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=Trans1.tDate AND Trans1.sID=Trans2.sID AND Trans2.tType=1) AS purchases,
       (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=Trans1.tDate AND Trans1.sID=Trans2.sID AND Trans2.tType=3) AS sales
FROM Tran AS Trans1
WHERE Trans1.sID = 584;

Since both transactions are type 2 in your example, that should give

sID | Qty | tType | deposits | purchases | sales
----+-----+-------+----------+-----------+------
584 |  5  |   2   |     0    |      5    |  0
584 |  4  |   2   |     0    |      9    |  0

Now let's try joining the two tables together. I'll join by sID and date, and use a RIGHT JOIN so that all of the dates and prices are included.

SELECT SPrices.pDate AS pDate,
       SPrices.sID AS sID,
       Trans1.Qty AS Qty,
       Trans1.tType AS tType,
       SPrices.lPr AS lPr
FROM Tran AS Trans1
    RIGHT JOIN SPrices ON Trans1.sID = SPrices.sID AND Trans1.tDate=SPrices.pDate
WHERE SPrices.sID = 584;

This should give a row for every row in SPrices for sID 584, with transactions listed on the appropriate days and NULL rows otherwyse:

pDate      | sID | Qty  | tType | lPr
-----------+-----+------+-------+------
09/07/2012 | 584 |  5   |   2   | 69.99
10/07/2012 | 584 | NULL | NULL  | 69.87
...
26/07/2012 | 584 | NULL | NULL  | 72.40
27/07/2012 | 584 |  4   |   2   | 71.20
28/07/2012 | 584 | NULL | NULL  | 72.20

Okay, if that's good, we'll add the two pieces together (the cumulative sums and the join). I've changed the cumulative sums to use SPrices.pDate, since we want the cumulative sum for each day, regardless if there's a transaction that day:

SELECT SPrices.pDate AS pDate,
       SPrices.sID AS sID
       Trans1.Qty AS Qty,
       Trans1.tType AS tType,
       SPrices.lPr AS lPr,
       (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=SPrices.pDate AND SPrices.sID=Trans2.sID AND Trans2.tType=1) AS deposits,
       (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=SPrices.pDate AND SPrices.sID=Trans2.sID AND Trans2.tType=1) AS purchases,
       (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=SPrices.pDate AND SPrices.sID=Trans2.sID AND Trans2.tType=3) AS sales
FROM Tran AS Trans1
    RIGHT JOIN SPrices ON Trans1.sID = SPrices.sID AND Trans1.tDate=SPrices.pDate
WHERE SPrices.sID = 584;



pDate      | sID | Qty  | tType | lPr   | deposits | purchases | sales
-----------+-----+------+-------+-------+----------+-----------+------
09/07/2012 | 584 |  5   |   2   | 69.99 |    0     |      5    |  0
10/07/2012 | 584 | NULL | NULL  | 69.87 |    0     |      5    |  0
...
26/07/2012 | 584 | NULL | NULL  | 72.40 |    0     |      5    |  0
27/07/2012 | 584 |  4   |   2   | 71.20 |    0     |      9    |  0
28/07/2012 | 584 | NULL | NULL  | 72.20 |    0     |      9    |  0

Finally, I'm going to wrap the whole thing in another select just to do the value calculation. I'm also going to drop the columns we don't actually need.

SELECT
    pDate,
    sID,
    lPr,
    deposits,
    purchases,
    sales,
    lPr * (IFNULL(deposits, 0) + IFNULL(purchases,0) - IFNULL(sales,0)) AS [Value]
FROM (
    SELECT SPrices.pDate AS pDate,
           SPrices.sID AS sID,
           SPrices.lPr AS lPr,
           (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=SPrices.pDate AND SPrices.sID=Trans2.sID AND Trans2.tType=1) AS deposits,
           (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=SPrices.pDate AND SPrices.sID=Trans2.sID AND Trans2.tType=1) AS purchases,
           (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=SPrices.pDate AND SPrices.sID=Trans2.sID AND Trans2.tType=3) AS sales
    FROM Tran AS Trans1
        RIGHT JOIN SPrices ON Trans1.sID = SPrices.sID AND Trans1.tDate=SPrices.pDate
    WHERE SPrices.sID = 584
) AS t;

The final result is:

pDate      | sID |  lPr  | deposits | purchases | sales | Value
-----------+-----+-------+----------+-----------+-------+------
09/07/2012 | 584 | 69.99 |    0     |      5    |  0    | 349.95
10/07/2012 | 584 | 69.87 |    0     |      5    |  0    | 349.34
...
26/07/2012 | 584 | 72.40 |    0     |      5    |  0    | 362.00
27/07/2012 | 584 | 71.20 |    0     |      9    |  0    | 640.80
28/07/2012 | 584 | 72.20 |    0     |      9    |  0    | 649.80

Can you indicate if this solved your problem, or how else I can help? On stackoverflow it's your responsibility to choose the best answer, and we are happy to revise and update answers that are not complete. — jmilloy, Sep 23 '12 at 23:18
Hello @jmilloy now question still not answered. I already stated the query does not give the required solution. If i can send/upload the mdb will that help? await advise. — cjv, Sep 26 '12 at 03:41
@cjv Okay I've broken down my query and included example outputs along the way. I noticed a few typos in my original example, so that would explain why it didn't work. Let me know what works here and what is still off. Good luck, and let's get this figured out! — jmilloy, Sep 27 '12 at 00:25
presently i have made the test table in access, and i guess nested select does not work in here. I will try to figure that out and let you know. as of now Query 3 looks fine .... checking for the rest. — cjv, Sep 28 '12 at 03:23
This article suggests that it should be doable in access just the same: http://www.techrepublic.com/article/how-do-i-generate-a-running-total-in-an-access-query/6140569. What happens when you try the first query? — jmilloy, Sep 28 '12 at 04:30
Ok all the queries now with the modifications do work .... but the last query does not give the desired result. Value column returns NULL — cjv, Sep 28 '12 at 05:02
and also it starts showing the Qty as 2 from 2/jul, 7 from 9/jul and 11 from 27/jul — cjv, Sep 28 '12 at 05:04
@cjv Okay, I made a simple edit that should fix the Qty! We needed to add `Trans1.sID=Trans2.sID` or `SPrices.sID=Trans2.sID` to the subquery so that it would only add up values for the correct stock id. Let me know if that works. — jmilloy, Sep 28 '12 at 13:17
ok i think i got something with a slight modification. I added a new transaction in the tran table that shows a -Qty on 30/07/2012. tried to run the query and i realised it does show the transaction but the purpose is to get a cummulative sum of the qty, and also identify the types. So i used a line from another query and got a new column called TotQty and i used that TotQty to multiply to lPr and now the value is working fine. — cjv, Sep 29 '12 at 03:46
out of academic interest it will be interesting to know why lPr * (deposits + purchases - sales) AS [Value] returns nothing. is it cos although purchases has value but others have NULL so everything turns out NULL. — cjv, Sep 29 '12 at 03:54

score 0 · Answer 2 · answered Sep 22 '12 at 05:17

0

This should help.

SELECT
      Tran.cID,
      sPrices.pDate,
      Tran.sID,
      Tran.Qty,
      sPrices.lPr,
      Tran.Qty * sPrices.lPr AS Value
FROM
      Tran INNER JOIN SPrices
        ON
           Tran.sID = SPrices.sID
WHERE
      Trans.tType = ?

Replace the ? with the proper transaction type (if needed).

answered Sep 22 '12 at 05:17

JoeFletch

3,820
1
27
36

What you're missing is that Qty needs to accumulate over time, either with a variable or a select. – jmilloy Sep 22 '12 at 05:21
@jmilloy, if that is the case, then I think that I am missing what the original purpose of the question. – JoeFletch Sep 22 '12 at 05:24
Notice that the Qty column in the Trans linked table for the two 584 transactions is 5 and then 4. So the cumulative quantity is 5 until 26/7/2012 and then 5+4 = 9 starting on 27/7/2012. – jmilloy Sep 22 '12 at 05:34
i am getting 258 rows for this query. while i should be getting only 55 rows, or in other words, in my sPrices table there are only 55 prices so i should be getting either 55 or less rows depending on the field tDate. So it should be grouped by cID, and also by sID – cjv Sep 22 '12 at 09:25
@jmilloy, I'm not sure how you get that from the original question! – JoeFletch Sep 22 '12 at 11:25
@cjv, I suggest that you update your question to give a more detailed scenario of what you are looking for. Unless the other answer is what you are looking for. – JoeFletch Sep 22 '12 at 11:27
I got it from the example desired output. – jmilloy Sep 22 '12 at 13:49
@JoeFletch the expected output is what i have already requested. That does not change. But the only thing to be taken care of is that, as there is a CID(Client ID) it means there can be more than one client, and one client can have relation with more than one sID from the Tran Table. That is the part that i have to be careful when i build the query. Do feel free to get back should this not be clear. Thanks. – cjv Sep 22 '12 at 15:35

score 0 · Accepted Answer · answered Sep 29 '12 at 03:51

Final Query that gives the desired result ... all credit goes to jmilloy for achieving this ..

SELECT
pDate,
sID,
lPr,
deposits,
purchases,
sales,
BalQty,
lPr * BalQty as [Value]
FROM (
SELECT SPrices.pDate AS pDate,
       SPrices.sID AS sID,
       SPrices.lPr AS lPr,
       (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=SPrices.pDate AND SPrices.sID=Trans2.sID AND Trans2.tType=1) AS deposits,
       (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=SPrices.pDate AND SPrices.sID=Trans2.sID AND Trans2.tType=2) AS purchases,
       (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=SPrices.pDate AND SPrices.sID=Trans2.sID AND Trans2.tType=3) AS sales,
       (SELECT SUM(Trans2.Qty) FROM Tran AS Trans2 WHERE Trans2.tDate<=SPrices.pDate AND SPrices.sID=Trans2.sID ) AS BalQty
FROM Tran AS Trans1
    RIGHT JOIN SPrices ON Trans1.sID = SPrices.sID AND Trans1.tDate=SPrices.pDate
WHERE SPrices.sID = 584
) AS t;

SQL query Dynamic Cummulative sum

3 Answers3