July 27, 2017 at 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.
+------+-------+----------+------------+-----+--+--+--+--+--+
| 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
July 27, 2017 at 5:05 am
gazy007 - Thursday, July 27, 2017 4:35 AMHi,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 50SELECT 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
July 27, 2017 at 5:15 am
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
July 27, 2017 at 5:16 am
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
July 27, 2017 at 6:17 am
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.
July 27, 2017 at 8:38 am
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
July 27, 2017 at 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);
July 27, 2017 at 9:37 am
gazy007 - Thursday, July 27, 2017 9:30 AMDoes 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
August 1, 2017 at 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 |
+-----------+-------+------------+------------+
August 1, 2017 at 3:54 am
gazy007 - Tuesday, August 1, 2017 2:11 AMI 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.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2017 at 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.
August 1, 2017 at 4:35 am
gazy007 - Tuesday, August 1, 2017 4:29 AMI 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.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2017 at 4:47 am
gazy007 - Tuesday, August 1, 2017 4:29 AMThe 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.
Larnu.uk
August 1, 2017 at 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);
August 1, 2017 at 5:47 am
gazy007 - Tuesday, August 1, 2017 5:36 AMSorry 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.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply