Thread: Step a counter.

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0

    Step a counter.


    Hi there,

    I been struggling all day with this and i would appreciate your help.

    I got three columns. ART, DATE, BOUGHT, SOLD and the data looks as following.

    12-12323, 2012-01-01,1500,45
    12-12323, 2012-02-01,0,78
    12-12323, 2012-03-01,0,109
    12-12323, 2012-04-01,450,43

    I want som compare the BOUGHT counter with the following row.so that the output would look something like this.

    12-12323, 2012-01-01,1500,45
    12-12323, 2012-02-01,1500,78
    12-12323, 2012-03-01,1500,109
    12-12323, 2012-04-01,1950,43

    Is this even possible? The Bought counter + previous period/row.

    Kind of a fuzzy explaination, i know. It's not my native language.

    Take care!
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,191
    Rep Power
    9398
    It would be really easy to do this in the regular code you're writing. Do you have to have a pure (My)SQL solution?
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,248
    Rep Power
    4279
    Originally Posted by jazzarn
    Is this even possible? The Bought counter + previous period/row.
    relational database table rows do not have a sequence

    so how do you find "previous row" ??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0
    I think i need to explain a little bit better. I'm sure this could be achieved.

    Imagine if you have a webstore. You bye products in stock and you sell it. One month you decide to buy 100 products and you managed to sell 20.

    Date, Product, Bought, Sold
    2012-01,P1,100,20

    Next month, you don't buy anything into stock but you sell 70 products.

    Date, Product, Bought, Sold
    2012-01,P1,100,20
    2012-02,P1,0,70

    The month after that, you need to buy more in stock and you sell 30.

    ..and so on..

    Date, Product, Bought, Sold
    2012-01,P1,100,20
    2012-02,P1,0,70
    2012-03,P1,100,30

    Here's the situation i got. I need to be able to query history data based on product in stock. I wish i could get my output something like this.

    Date, Product, Bought, Sold, stock
    2012-01,P1,100,20, 80 (100-20)
    2012-02,P1,0,70,10 (80 allready in stock - sold)
    2012-03,P1,100,30,80 (10 allready in stock + bought - sold)
  8. #5
  9. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,191
    Rep Power
    9398
    In other words you define sequence by date.

    What I said still stands: can't you do this in code? In fact I might suggest you store the current stock count somewhere rather than calculate it every single time you need it.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0
    HI requinix,

    I appreciate you help.

    Howeverm I don't need the current stock count. What i need is to be able to see the stock count at the end of each month. Could you please give me a push in the right direction?

    Here's the sql :

    SELECT tmonth, artnr, ifnull(SUM(inköp),0) inköp, SUM(sålt) as sålt FROM (
    SELECT DATE_FORMAT(dat,"%Y-%m") as tmonth, artnr,
    sum(antal1) as inköp,
    null as sålt
    FROM 24mx.artradtmp1
    where artnr='TW-520XG' and (typ ='MI' or typ='LS') AND DAT BETWEEN '2011-09-01' AND '2012-12-31'
    group by tmonth, artnr
    union all
    SELECT DATE_FORMAT(dat,"%Y-%m") as tmonth,artnr,
    null as inköp,
    sum(antal1) as sålt
    FROM 24mx.artradtmp1
    where artnr='TW-520XG' and typ ='F' AND DAT BETWEEN '2011-09-01' AND '2012-12-31'
    group by tmonth, artnr
    ) AS a
    group by tmonth, artnr

    Output :

    2011-09 TW-520XG 1500 68
    2011-10 TW-520XG 0 245
    2011-11 TW-520XG 0 152
    2011-12 TW-520XG 460 130
  12. #7
  13. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,191
    Rep Power
    9398
    For the third time:

    Can you do this in code instead of in SQL?
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by requinix
    For the third time:

    Can you do this in code instead of in SQL?
    Sorry!

    I would prefer a doing it in sql, if possible.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Code:
    SELECT x.*
         , SUM(y.bought) running 
      FROM fuzzy x 
      JOIN fuzzy y 
        ON y.art=x.art 
       AND y.date <= x.date 
     GROUP 
        BY art
         , date;
    [This assumes a compound PK on (art,date)]
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0
    No PK, unfortunately. I'm running it againts a view.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    You need a way of uniquely identifying rows in your table/view.
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by cafelatte
    You need a way of uniquely identifying rows in your table/view.
    What about the date column? It will be unique?
    one row per month.

IMN logo majestic logo threadwatch logo seochat tools logo