August 1, 2017 at 6:36 am
Good question. I may be able to add order by due date and minus from the date order?
August 1, 2017 at 6:48 am
gazy007 - Tuesday, August 1, 2017 6:36 AMGood 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.
Larnu.uk
August 1, 2017 at 7:03 am
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 |
August 1, 2017 at 7:17 am
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.
Larnu.uk
August 1, 2017 at 7:29 am
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.
Larnu.uk
August 1, 2017 at 8:56 am
Thanks it gives me ideas to manipulate for few days.
August 7, 2017 at 7:48 am
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')
August 7, 2017 at 8:06 am
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.
Larnu.uk
August 7, 2017 at 8:54 am
Apologies Thom, Please see attached file.
August 8, 2017 at 1:46 am
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 |
August 8, 2017 at 2:19 am
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?
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.
Larnu.uk
August 8, 2017 at 7:54 am
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 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply