# Calculated Query Help

• 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

• 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

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

• 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

• 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`
• 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.

• 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

• 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);

• 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

• 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 |
+-----------+-------+------------+------------+

• 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]

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

• 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.

• 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]

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 - 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.

• 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);

• 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)