SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Help in getting only data which can suffix the total Qty

    Hi...

    I have a
    table: bom
    fields:
    Comp = P28
    ReqQty = 31968

    and I have table
    table: dipping
    fields needed to get data
    LOT_CODE
    OUTPUT_QTY
    DATE_ENTRY

    now I only want to get LOT_CODE and OUTPUT_QTY where can suffix the ReqQty ORDER BY DATE_ENTRY

    I tried this query:

    Code:
    SELECT SUBSTR(LOT_CODE, 9,4) AS Comp, LOT_CODE, OUTPUT_QTY, DATE_ENTRY FROM dipping WHERE SUBSTR(LOT_CODE, 9,4) = 'P28'   ORDER BY DATE_ENTRY ASC;
    I attach the result of this query.

    I want only query or get is the LOT_CODE where the OUTPUT can suffix the ReqQty.


    Simple Example:

    ReqQty = 100
    Comp = P28

    LOT_CODE--OUTPUT_QTY---DATE_ENTRY
    00001P28--- 50------------2012-05-15
    00002P28----25------------2012-05-16
    00003P28----50------------2012-05-17
    00004P28----25-----------2012-05-18
    in query I need to get only the
    00001P28--- 50------------2012-05-15
    00002P28----25------------2012-05-16
    00003P28----50------------2012-05-17

    because it suffix my ReqQty as you can see in this LOT_CODE
    00003P28----50------------2012-05-17 i only need is 25 to suffix my Req
    Qty so I need to display only:

    00001P28--- 50------------2012-05-15
    00002P28----25------------2012-05-16
    00003P28----25------------2012-05-17

    Feel free to ask me if theirs any question for better understanding of my problem
    Thank you
    Attached Files Attached Files

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  4. #4
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi..

    I have updated query:

    Code:
     set @t = 0;
     SELECT LOT_CODE as code,  DATE_ENTRY, @t := @t + d.OUTPUT_QTY as cumulative,  CASE WHEN @t+OUTPUT_QTY > 31680 THEN @t - 31680 ELSE OUTPUT_QTY END as qty   FROM dipping d  WHERE SUBSTR(LOT_CODE, 9,4)='P28' AND (@t < 31680) ;
    and I attach sample output where i got an issue.

    Also I attach my sample documents for better understanding of my issue

    Thank you
    Attached Images Attached Images
    Attached Files Attached Files

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •