Calculated Query Help

  • gazy007

    Ten Centuries

    Points: 1298

    Hi,

    I am struggling to grasp on how to calculate in SQL where there is a fixed Qty in stock and minus sales order then it should minus from that QTY in stock where it previously taken off the order qty. I have tried mostly everything but I need help now.
    +------+-------+----------+------------+-----+--+--+--+--+--+
    | Prod | Desc | On Order | Qtyinstock | Bal | | | | | |
    +------+-------+----------+------------+-----+--+--+--+--+--+
    | ABC | Watch   | 10     | 100           | 100 | | | | | |
    | DEF | Strap   | 10     | 90                | 90 | | | | | |
    | EFG | Tie     |  40      | 50               | 50 | | | | | |
    |  |   |    |    |  | | | | | |
    +------+-------+----------+------------+-----+--+--+--+--+--+

    SELECT DISTINCT Product.ProductName, LEFT(ProductDescription, 30) AS Description,S.SalesOrderNumber, view_Product.QtyInStock,
    S.QtyOnOrder, S.OrderQuantity AS ReorderLevel, P.QtyInStock - S.OrderQuantity AS Balance, ( S.OrderQuantity - P.QtyInStock - S.OrderQuantity )
           
    FROM Product P INNER JOIN SalesOrderDueDateReport S ON P.ProductID = S.ProductID

  • J Livingston SQL

    SSC Guru

    Points: 51272

    gazy007 - Thursday, July 27, 2017 4:35 AM

    Hi,

    I am struggling to grasp on how to calculate in SQL where there is a fixed Qty in stock and minus sales order then it should minus from that QTY in stock where it previously taken off the order qty. I have tried mostly everything but I need help now.
    Product        Des                                                       Qty in Stock   100
                                                               On Order            Balance                     
    ABC             watch                                10                           90           
    TYE              Blue                                   5                            85
    TSG               Red                                 35                            50

    SELECT DISTINCT Product.ProductName, LEFT(ProductDescription, 30) AS Description,S.SalesOrderNumber, view_Product.QtyInStock,
    S.QtyOnOrder, S.OrderQuantity AS ReorderLevel, P.QtyInStock - S.OrderQuantity AS Balance, ( S.OrderQuantity - P.QtyInStock - S.OrderQuantity )
           
    FROM Product P INNER JOIN SalesOrderDueDateReport S ON P.ProductID = S.ProductID

    maybe easier for us to help if you posted as per this guide

    https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL

    SSC Guru

    Points: 51272

    you have posted in Sql 2005 forum....is this correct?  
     (I ask because it may restrict some solutions)

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • gazy007

    Ten Centuries

    Points: 1298

    I am using sql 2008 R2 standard .

    SELECT DISTINCT Product.ProductName,LEFT(ProductDescription, 30) AS Description,
    S.SalesOrderNumber,view_Product.QtyInStock,S.QtyOnOrder,S.OrderQuantity AS ReorderLevel,
    P.QtyInStock - S.OrderQuantity AS Balance,(S.OrderQuantity - P.QtyInStock - S.OrderQuantity)
    FROM Product PINNER JOIN SalesOrderDueDateReport S ON P.ProductID = S.ProductID
  • gazy007

    Ten Centuries

    Points: 1298

    Hi J Livingston,
    Do you think I should remove it from here and post it on 2008?
    I just don't want to upset the admin.

  • J Livingston SQL

    SSC Guru

    Points: 51272

    please post table create scripts for your two tables  Product & SalesOrderDueDateReport 
    some sample data insert scripts for both tables and your expected results for this sample data.

    someone may then be better prepared to assist you.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • gazy007

    Ten Centuries

    Points: 1298

    Does it help?

    CREATE TABLE mytable(
     ProductName  VARCHAR(7) NOT NULL PRIMARY KEY
    ,Description  VARCHAR(20) NOT NULL
    ,SalesOrderNumber INTEGER NOT NULL
    ,QtyInStock   INTEGER NOT NULL
    ,QtyOnOrder   INTEGER NOT NULL
    ,Balance    INTEGER NOT NULL
    );
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',92472,3000,50,2950);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',92473,3000,100,2850);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',93156,3000,50,2800);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',93157,3000,10,2790);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',93159,3000,10,2780);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',93160,3000,10,2770);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',93161,3000,10,2760);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',93162,3000,10,2750);

  • J Livingston SQL

    SSC Guru

    Points: 51272

    gazy007 - Thursday, July 27, 2017 9:30 AM

    Does it help?

    CREATE TABLE mytable(
     ProductName  VARCHAR(7) NOT NULL PRIMARY KEY
    ,Description  VARCHAR(20) NOT NULL
    ,SalesOrderNumber INTEGER NOT NULL
    ,QtyInStock   INTEGER NOT NULL
    ,QtyOnOrder   INTEGER NOT NULL
    ,Balance    INTEGER NOT NULL
    );
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',92472,3000,50,2950);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',92473,3000,100,2850);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',93156,3000,50,2800);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',93157,3000,10,2790);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',93159,3000,10,2780);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',93160,3000,10,2770);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',93161,3000,10,2760);
    INSERT INTO mytable(ProductName,Description,SalesOrderNumber,QtyInStock,QtyOnOrder,Balance) VALUES ('COS0010','Shape M60 7003/14 B',93162,3000,10,2750);

    if you remove PRIMARY KEY from this line "ProductName VARCHAR(7) NOT NULL PRIMARY KEY"...yeah it works ....
    great.....now based on this sample data set what results do you want to see please?

    EDIT...sheesh...this look like your expected results ...am I correct?
    seems like a "running total" scenario......many post out there on this subject  ....how you resolve will depend proabably on how big your expect your data set to grow to...

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • gazy007

    Ten Centuries

    Points: 1298

    I would like to get something like this .
    +-----------+-------+------------+------------+
    | Prod name | Desc | QtyonOrder | QtyinStock |
    +-----------+-------+------------+------------+
    | Coms  | Sheet |       123 |  130 |
    | Separate | Lamps    |    4 |    3 |
    | Bread  | Food |           4 |   -1 |
    +-----------+-------+------------+------------+

  • ChrisM@Work

    SSC Guru

    Points: 186023

    gazy007 - Tuesday, August 1, 2017 2:11 AM

    I would like to get something like this .
    +-----------+-------+------------+------------+
    | Prod name | Desc | QtyonOrder | QtyinStock |
    +-----------+-------+------------+------------+
    | Coms  | Sheet |       123 |  130 |
    | Separate | Lamps    |    4 |    3 |
    | Bread  | Food |           4 |   -1 |
    +-----------+-------+------------+------------+

    Can't see 'Coms', 'Separate' or 'Bread' anywhere in the sample data set.
    Can you either a) adjust the sample data to fit your expected results or b) adjust the expected results to fit the sample data?
    Cheers.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • gazy007

    Ten Centuries

    Points: 1298

    I would like to get something like this .
    +-----------+-------+------------+------------+
    | Prod name | Desc | QtyonOrder | QtyinStock |
    +-----------+-------+------------+------------+
    | Coms  | Sheet |       123 |  130 |
    | Separate | Lamps    |    4 |    3 |
    | Bread  | Food |           4 |   -1 |
    +-----------+-------+------------+------------+
    Hi Chris,
    The name or desc is only text fields. I am looking the solution to deduct Qty on order from qty in stock.

  • ChrisM@Work

    SSC Guru

    Points: 186023

    gazy007 - Tuesday, August 1, 2017 4:29 AM

    I would like to get something like this .
    +-----------+-------+------------+------------+
    | Prod name | Desc | QtyonOrder | QtyinStock |
    +-----------+-------+------------+------------+
    | Coms  | Sheet |       123 |  130 |
    | Separate | Lamps    |    4 |    3 |
    | Bread  | Food |           4 |   -1 |
    +-----------+-------+------------+------------+
    Hi Chris,
    The name or desc is only text fields. I am looking the solution to deduct Qty on order from qty in stock.

    I recommend that you adjust your sample data set so that the results exactly match what you've posted here. This removes ambiguity, gives people something to code against, and reduces the number of best guesses - which at best wastes time and at worst will deter folks from tackling your problem.
    Time well spent.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Thom A

    SSC Guru

    Points: 98023

    gazy007 - Tuesday, August 1, 2017 4:29 AM

    The name or desc is only text fields. I am looking the solution to deduct Qty on order from qty in stock.

    The problem is, like Chris says, we need to know what your original data looks like, and what you expect it t look like afterwards. We can then give you directions on how to get from "A to B". Giving us one set of sample data, and an expected output for something different doesn't help. It's like we have "A"and "C", but no idea where or what "B" is. We then can't direct you to "B" unless we know what it is.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • gazy007

    Ten Centuries

    Points: 1298

    Sorry for the confusion. Here is my best effort

    | | A | B | C | D | E | F |

    |---|-----------|---------------------|-----------|------------|------------|------------|

    | 1 | Prod name | Desc | ProdGroup | SalesOrder | QtyonOrder | QtyinStock |

    | 2 | Com10 | Comm M60 7003/14 B | COM | 200 | 1550 | 1552 |

    | 3 | Com10 | Comm M60 7003/14 B | COM | 300 | 3200 | 1552 |

    | 4 | Com10 | Comm M60 7003/14 B | COM | 400 | 3100 | 1552 |

    Here you see the Qtyinstock remains same where as It should deduct 1552-1550=2 on the next row 2-3200= -3198 and on 3rd row -3198-3100 = -6298 etc.

    CREATE TABLE mytable(

    ProductName VARCHAR(5) NOT NULL PRIMARY KEY

    ,ProductDescription VARCHAR(18) NOT NULL

    ,ProductGroup VARCHAR(3) NOT NULL

    ,SalesOrderNumber INTEGER NOT NULL

    ,QtyInStock INTEGER NOT NULL

    ,Out INTEGER NOT NULL

    );

    INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',200,1552,1550);

    INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',300,1552,3200);

    INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',400,1552,3100);

    INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',212,1552,3100);

    INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',235,1552,3100);

    INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',123,1552,3000);

    INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',298,1552,3000);

    INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',155,1552,1500);

  • Thom A

    SSC Guru

    Points: 98023

    gazy007 - Tuesday, August 1, 2017 5:36 AM

    Sorry for the confusion. Here is my best effort| | A | B | C | D | E | F ||---|-----------|---------------------|-----------|------------|------------|------------|| 1 | Prod name | Desc | ProdGroup | SalesOrder | QtyonOrder | QtyinStock || 2 | Com10 | Comm M60 7003/14 B | COM | 200 | 1550 | 1552 || 3 | Com10 | Comm M60 7003/14 B | COM | 300 | 3200 | 1552 || 4 | Com10 | Comm M60 7003/14 B | COM | 400 | 3100 | 1552 |Here you see the Qtyinstock remains same where as It should deduct 1552-1550=2 on the next row 2-3200= -3198 and on 3rd row -3198-3100 = -6298 etc.CREATE TABLE mytable( ProductName VARCHAR(5) NOT NULL PRIMARY KEY ,ProductDescription VARCHAR(18) NOT NULL ,ProductGroup VARCHAR(3) NOT NULL ,SalesOrderNumber INTEGER NOT NULL ,QtyInStock INTEGER NOT NULL ,Out INTEGER NOT NULL);INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',200,1552,1550);INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',300,1552,3200);INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',400,1552,3100);INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',212,1552,3100);INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',235,1552,3100);INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',123,1552,3000);INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',298,1552,3000);INSERT INTO mytable(ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('COM10','Comm M60 7003/14 B','COM',155,1552,1500);

    That INSERT statement doesn't work, as pointed out earlier, you need to remove the primary key (you can't have duplicate keys).

    So what is the expected outcome? Also, how do you tell what is the prior order in the table, as there seems to be no ID or date/time field we can sort by. How can we determine that the order containing 200 was the one prior to the one containing 300?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply