Calculated Query Help

  • gazy007

    Ten Centuries

    Points: 1298

    Good question. I may be able to  add order by due date and minus from the date order?

  • Thom A

    SSC Guru

    Points: 98333

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

    Good question. I may be able to  add order by due date and minus from the date order?

    Considering that Due_Date isn't a column in your sample data, is this also something we're missing? You need to post full and comprehensive sample data, along with the output you would would like to get from it. Then we can help you.

    Thom~

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

  • gazy007

    Ten Centuries

    Points: 1298

    I have added the due date column.

    CREATE TABLE mytable(
     DueDate    DATE NOT NULL
    ,ProductName   VARCHAR(5) NOT NULL
    ,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(DueDate,ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('14/08/17','COM10','Comm M60 7003/14 B','COM',200,1552,1550);
    INSERT INTO mytable(DueDate,ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('31/08/17','COM10','Comm M60 7003/14 B','COM',300,1552,3200);
    INSERT INTO mytable(DueDate,ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('13/09/17','COM10','Comm M60 7003/14 B','COM',400,1552,3100);
    INSERT INTO mytable(DueDate,ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('29/09/17','COM10','Comm M60 7003/14 B','COM',212,1552,3100);
    INSERT INTO mytable(DueDate,ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('13/10/17','COM10','Comm M60 7003/14 B','COM',235,1552,3100);
    INSERT INTO mytable(DueDate,ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('30/10/17','COM10','Comm M60 7003/14 B','COM',123,1552,3000);
    INSERT INTO mytable(DueDate,ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('15/11/17','COM10','Comm M60 7003/14 B','COM',298,1552,3000);
    INSERT INTO mytable(DueDate,ProductName,ProductDescription,ProductGroup,SalesOrderNumber,QtyInStock,Out) VALUES ('30/11/17','COM10','Comm M60 7003/14 B','COM',155,1552,1500);

    | |  A  |  B  |    C    |  D  |  E  |  F  |  G  |
    |---|----------|-----------|---------------------|-----------|------------|------------|------------|
    | 1 | DueDate | Prod name | Desc      | ProdGroup | SalesOrder | QtyonOrder | QtyinStock |
    | 2 | 14/8/17 | Com10  | Comm M60 7003/14 B | COM   | 200   | 1550   | 1552  |
    | 3 | 31/8/17 | Com10  | Comm M60 7003/14 B | COM   | 300   | 3200   | 1552  |
    | 4 | 13/09/17 | Com10  | Comm M60 7003/14 B | COM   | 400   | 3100   | 1552  |

  • Thom A

    SSC Guru

    Points: 98333

    gazy007 - Tuesday, August 1, 2017 7:03 AM

    | |  A  |  B  |    C    |  D  |  E  |  F  |  G  |
    |---|----------|-----------|---------------------|-----------|------------|------------|------------|
    | 1 | DueDate | Prod name | Desc      | ProdGroup | SalesOrder | QtyonOrder | QtyinStock |
    | 2 | 14/8/17 | Com10  | Comm M60 7003/14 B | COM   | 200   | 1550   | 1552  |
    | 3 | 31/8/17 | Com10  | Comm M60 7003/14 B | COM   | 300   | 3200   | 1552  |
    | 4 | 13/09/17 | Com10  | Comm M60 7003/14 B | COM   | 400   | 3100   | 1552  |

    Your expected output appears to match the data in your sample table. Am I missing something?

    Thom~

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

  • Thom A

    SSC Guru

    Points: 98333

    Without an expected output, butusing a previous post, this is a guess:
    SELECT DueDate,
           ProductName, ProductDescription, ProductGroup,
           SalesOrderNumber,
           --FIRST_VALUE(QtyInStock) OVER (PARTITION BY ProductName ORDER BY DueDate ASC) - --Unfortunately First_Value is not available before SQL Server 2012.
           --SUM(Out) OVER (PARTITION BY ProductName ORDER BY DueDate ASC
                          --ROWS UNBOUNDED PRECEDING) AS QtyInStock, --UNBOUNDED PRECEDING also only available from 2012.
           (SELECT TOP 1 sq.QtyInStock
           FROM mytable sq
           WHERE sq.ProductName = MT.ProductName
           ORDER BY DueDate DESC) -
           (SELECT SUM(Out) FROM mytable sq
           WHERE sq.ProductName = MT.ProductName
            AND sq.DueDate <= MT.DueDate) AS QtyInStock,
           Out
    FROM mytable MT;

    I have included a commented out version, which works in 2012 onwards, which would be much faster. if you upgrade at any point, it would be worth considering updating your SQL.

    Thom~

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

  • gazy007

    Ten Centuries

    Points: 1298

    Thanks it gives me ideas to manipulate for few days.

  • gazy007

    Ten Centuries

    Points: 1298

    Hey Thom, 

    I seem to use your idea on my query but the balance but I have failed to acheive the desired result for example
    If I have got Qtyinstock 2330 and an order for 1450 then the balance should be 880 from the Qtyinstock - minus the order and should follow the next row when
    the Qtyinstock is 880 and Order is 2500 so the balance should be -1620 and so on

    SELECT PG.NAME,sl.ProductID,Convert(VARCHAR(12), sl.DueDate, 103) AS DateRequired,sto.QtyInStock,

    (sl.OrderQuantity - sl.QuantityDespatched) AS QuantityOut,

    (sl.OrderQuantity - sl.QuantityDespatched) - Qtyinstock AS Balance FROM SalesLine sl

    INNER JOIN Product P ON P.productid = sl.ProductIDINNER JOIN SalesOrder so ON sl.SalesOrderID = so.SalesOrderID

    INNER JOIN CustomerAccount ca ON so.CustomerAccountID = ca.CustomerAccountID

    INNER JOIN Company c ON ca.CompanyID = c.CompanyIDLEFT JOIN Depot d ON sl.DepotID = d.DepotID

    LEFT JOIN StockItemDefinition sto ON sl.ProductID = sto.ProductID

    INNER JOIN ProductGroup PG ON P.ProductGroupID = PG.ProductGroupIDWHERE so.closed = 0

    AND (sl.OrderQuantity > sl.QuantityDespatched)AND sl.ProductID BETWEEN '0'AND '999999'AND

    (sl.DueDate BETWEEN DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)AND '2099/12/31')
  • Thom A

    SSC Guru

    Points: 98333

    DDL, Sample data and Expected output is really important here. I'll need all of this, showing the problem you're having to be able to trouble shoot. You're returning data from 7 tables, so I'll need it for all of these, rather than just the one table I thought I had before.

    Then again, the SQL you posted above doesn't really use the SQL I gave at all (which had subqueries), so the two are not the same at all.

    Thom~

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

  • gazy007

    Ten Centuries

    Points: 1298

    Apologies Thom, Please see attached file.

  • Thom A

    SSC Guru

    Points: 98333

    gazy007 - Monday, August 7, 2017 8:54 AM

    Apologies Thom, Please see attached file.

    That's one table. Your above SQL has 7. Where are your expected results?

    Thom~

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

  • gazy007

    Ten Centuries

    Points: 1298

    I am trying to figure out how I could get the desired result.  If I have got 1552 qtyinstock and there is an order for 1550. It should minus the stockinqty - order=balance
    On the second row it should remember the available qtyinstock which is 2 in our example and minus the next order =-3198 and so on.
    | |  A  |  B  |    C    |  D  |  E  |  F  |  G  |  H  |
    |---|----------|-----------|---------------------|-----------|------------|------------|------------|---------|
    | 1 | DueDate | Prod name | Desc      | ProdGroup | SalesOrder | QtyonOrder | QtyinStock | Balance |
    | 2 | 14/8/17 | Com10  | Comm M60 7003/14 B | COM   | 200   | 1550   | 1552                     | 2   |
    | 3 | 31/8/17 | Com10  | Comm M60 7003/14 B | COM   | 300   | 3200   | 1552                    | -3198 |
    | 4 | 13/09/17 | Com10  | Comm M60 7003/14 B | COM   | 400   | 3100   | 1552                  | -6298 |

  • Thom A

    SSC Guru

    Points: 98333

    Ok, maybe I should elaborate a bit more.

    With the sample data you provided, and solution I gave, you received the correct answer (good). However, when applying it to your live/dev data, it didn't Looking at the SQL you posted previously, you reference several tables, not one, and you don't have any of the SQL that I previously provided in that query.

    Next, we have your new sample data. A quick look at it, and you 545 sample rows. That's not a bad thing, having a lot of Sample data is often really useful, as it can provide a more complete view of all the scenarios. The problem, however, is your expected output only has 3 lines, when I'm confident that you're going to be expecting more than 3 lines. There are also NO products in your sample data that have the product name "Com10", so where is that coming from?

    1. We have Sample data, however, your previous psot implies that you have 7 tables. If you have 7 tables, to reliably test and provide an answer, we need DDL with 7 tables, and data in each of those
    2. We need a full expected output that is relevant to the sample data you have provided.
    3. Explain your reasoning on how you get from your sample data to the expected result. you might just need to use one example, but it gives us an idea if your goal, and we can then work towards that.
    4. Have a look at the link in my signature, Jeff's Forum Etiquette topic, and see how he explains how to post as well. Try to avoid posting your sample data, expected results, etc, in word documents/Excel Workbooks etc.
    5.  Please, also, encapsulate your sample data, if posted it like you did in your previous post, in IF Code. code="plain" will be fine. That expected output above is very difficult to read. Making it easier for us to read and see what you need makes it easier for us to help you, and also will cause more people to.

    Have a look at these points and post back with a complete set.

    Thanks 🙂

    Thom~

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

  • Greg Edwards-268690

    SSC-Insane

    Points: 20562

    It appears to me you are trying to do a supply / demand type of report, combining a static QOH and by date of sales order line a running total showing when you drop below 0.
    I came from a shop that ran an ERP system that did this on screen, along with MRP messages to create purchase and mfg orders.

    I may be over thinking this, but you have order lines not shipped which are usually highest priority.

    And orders where they have the potential to be late in the near future (due to order line(s) using all available QOH).
    Although there may be mfg production or purchase orders that may satisfy that demand.

    Depending on if you are dealing with an ERP system that has some of this available, you might have other files that have some of this information already available in some form.
    And if your planners are going to filter this down to focus on the situations we addressed (lates and oversold items), you might possibly want to rethink things a bit.
    We had over 10k items in stock, and the system was designed for inquiry 1 item at a time.
    So creating these reports helped our planners quickly see items that needed to be looked at most.

    You may have already thought of this, or your business requirements are different that my thoughts.

Viewing 13 posts - 16 through 28 (of 28 total)

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