May 4, 2009 at 6:51 am
CMP Ref# |Item No.|Ordered Qty| Remaining Open| Received|Sum itemquantity|Needed
610551 ****121910 ****400 ********400 *******400 ********800 ****-400
610551 ****301308 ****400 ********400 *******400 ********800 ****-400
610552 ****121910 ****400 ********400 *******400 ********800 ****-400
610552 ****301308 ****400 ********400 *******400 ********800 ****-400
The problem with the above is that im doing remainingopen - sum(itemquantity) over (partition by itemcode),
ie. remaining open = 400 - sumitemqauantiy = 800 = -400
the actual remaining amount is 0 but since it looks at the two sales order the calculation is therefore incorrect.
so as you can see above the item 121910 appears on two sales orders, so when i sum on that item it gives me the wrong quantity needed, which should be 0.
basically 400 of 121910 is assigned to 610551 and same for 610552 but the sum ignores the sales order number.
the reason why i need a sum of items received is because they came in more than 1 receipt. for example the 400 are 100 +100 +200 than that really throws the calculation off. so I take the sum of the receipts and subtract it from the qty remaining open to give me the needed amount.
But as you can see the sum looks globally at all the sales orders.
if I use where salesordernum = '610551' then I have no problem, however I want to look at a list of all the sales order and what's still open for each one
is there a way that I could do sum(t1.itemcode.salesorder) or something similar?
Appreciate your help guys
May 4, 2009 at 9:43 am
I'm having trouble following your question. Are you looking for something that does a running total? Like for inventory management?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 4, 2009 at 9:59 am
Yes, its for inventory management.
Basically i need to know what was received on a sales order and what's still needed?
May 4, 2009 at 10:04 am
For what you are trying to accomplish the SUM() OVER (PARTITION BY ...) does not work as you have found. These window functions haven't been fully implemented for that as they have been in Oracle.
For what you are trying to accomplish you need to do something similiar to a running total process. If you could provide us with the DDL for the tables, sample data (in a readily consumable format), expected results based on the sample data we could come up with a possible solution for your problem.
For more information on how to post the above, please read the first article i reference below in my signature block.
May 4, 2009 at 12:15 pm
Let me set up the data so you're able to see it.
By the way I appreciate your help.
May 4, 2009 at 2:15 pm
--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
--===== Create the test table with CREATE TABLE #mytable
(
ID SALESORDER IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DateValue SALESORDERDATE,
ITEM INT,
RECEIPTNUMBER INT,
QUANTITYORDERED INT,
QUANTITYRECEIVED,
QUANTITYSHIPPED INT)
--===== Setup any special required conditions especially where dates are concerned SET DATEFORMAT DMY
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(SALESORDER, SalesOrderDate, Item, ReceiptNumber, QuantityOrdered,QuantityReceived,QuantityShipped)
SELECT '1','Oct 17 2007 12:00AM','120100','1','10','5','0' UNION ALL
SELECT '1','Oct 17 2007 12:00AM','120100','2','10','2','0' UNION ALL
SELECT '2','Oct 17 2007 12:00AM','120100','3','20','10','0' UNION ALL
SELECT '2','Oct 17 2007 12:00AM','120100','4','20','10','0' UNION ALL
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable ON
QUERY
select salesorder, salesorderdate, item, quantityreceived,receiptnumber, quantityreceived,quantityshipped,
(quantityordered-quantityreceived) - sum(quantityreceived) over (partition by Item)
From #mytable
order by salesorder
expected output
i would like to see the sum of the items received sorted by each sales order.
May 4, 2009 at 4:08 pm
Did you perchance read the article I suggested? I don't see any sample data or expected results.
May 4, 2009 at 7:17 pm
I updated my post with the info
May 4, 2009 at 8:59 pm
slins4ever (5/4/2009)
I updated my post with the info
Okay, part way there on the expected results, you described what you wanted, but you didn't show it. By showing the expected results based on the sample data you provide something that we can use to verify the code written. This requires you to manually generate what our code should do on its own.
May 4, 2009 at 9:15 pm
UPDATED BY THE WAY
May 5, 2009 at 6:16 am
slins4ever (5/4/2009)
UPDATED BY THE WAY
Yelling at me doesn't help. I have looked at the previous posts, and I'm sorry but I don't see the expected results detailed for us. I see where you verbally explained the expected results, but that doesn't show me what the output from any query I may write should return based on the data provided.
Is it really that much to ask for you to put together the results that should be returned in a format that is easily comparable?
I humbly suggest you read this blog entry I wrote, The Flip Side.
May 5, 2009 at 6:51 am
Also, there is a slight problem with your table and the data that you are trying to enter that needs to be addressed.
This is the code for your table:
CREATE TABLE #mytable
(
ID SALESORDER IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DateValue SALESORDERDATE,
ITEM INT,
RECEIPTNUMBER INT,
QUANTITYORDERED INT,
QUANTITYRECEIVED,
QUANTITYSHIPPED INT)
The data you are trying to insert:
INSERT INTO #mytable
(SALESORDER, SalesOrderDate, Item, ReceiptNumber, QuantityOrdered,QuantityReceived,QuantityShipped)
SELECT '1','Oct 17 2007 12:00AM','120100','1','10','5','0' UNION ALL
SELECT '1','Oct 17 2007 12:00AM','120100','2','10','2','0' UNION ALL
SELECT '2','Oct 17 2007 12:00AM','120100','3','20','10','0' UNION ALL
SELECT '2','Oct 17 2007 12:00AM','120100','4','20','10','0' UNION ALL
If you notice in the table DDL, SALESORDER is declared as the PRIMARY KEY. If you then look at your data, you are attempting to enter two records each for SALESORDER numbers 1 and 2. This won't work as you will get a primary key error. This has nothing to do with the fact you also declared SALESORDER as an IDENTITY column because you did include code to allow an identity insert to occur.
May 5, 2009 at 7:53 am
First, your sample data doesn't work. Lynn already pointed out that the PK can't have duplicate values, but the whole idea for this is that you provide a script that someone can copy-and-paste into SSMS so they can start helping you. You obviously didn't test your script, or you'd have found the errors on the duplicate values in the PK, the extra "UNION ALL" after the last line of the insert, and the fact that you turn Identity Insert "ON" twice, where the second one should obviously be "OFF". Also, the table creation is flawed, because QUANTITYRECEIVED doesn't have a data type and will error-out. You also have two misdefined columns in the table creation, ID and DateValue.
And, while it works, why force the script to do all those implicit conversions from string to int? You have single-quotes around all the integer values.
Here's a script that seems to create valid test data, cleaned up for you. Please run it and verify that it actually does what you wanted as far as creating test data goes. If it does, then I can probably help on the running total thing you want, but I need to make sure I have the right table structure first.
CREATE TABLE #mytable
(
SALESORDER int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SALESORDERDATE datetime,
ITEM INT,
RECEIPTNUMBER INT,
QUANTITYORDERED INT,
QUANTITYRECEIVED INT,
QUANTITYSHIPPED INT);
--
SET IDENTITY_INSERT #mytable ON;
--
--===== Insert the test data into the test table
INSERT INTO #mytable
(SALESORDER, SalesOrderDate, Item, ReceiptNumber, QuantityOrdered,QuantityReceived,QuantityShipped)
SELECT 1,'Oct 17 2007 12:00AM',120100,1,10,5,0 UNION ALL
SELECT 2,'Oct 17 2007 12:00AM',120100,2,10,2,0 UNION ALL
SELECT 3,'Oct 17 2007 12:00AM',120100,3,20,10,0 UNION ALL
SELECT 4,'Oct 17 2007 12:00AM',120100,4,20,10,0;
--
SET IDENTITY_INSERT #mytable OFF;
--
select *
from #mytable;
Test that and let me know if it actually gives a correct test-table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 5, 2009 at 8:29 am
I'm thinking that SalesOrder and ReceiptNumber should be reversed allowing SalesOrder to be duplicated. Two receipts for each order. If you make the ReceiptNumber the PRIMARY KEY instead, then you could have order number 1 and 2 twice as originally posted.
Not sure, I could be wrong.
May 5, 2009 at 9:00 am
Lynn Pettis (5/5/2009)
I'm thinking that SalesOrder and ReceiptNumber should be reversed allowing SalesOrder to be duplicated. Two receipts for each order. If you make the ReceiptNumber the PRIMARY KEY instead, then you could have order number 1 and 2 twice as originally posted.Not sure, I could be wrong.
I thought of that too. Also wasn't sure, which is why I asked about it as a test harness.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply