Sum - Partition question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sum - Partition question

Author

Topic

slinshot
Starting Member

3 Posts

Posted - 2009-05-01 : 10:09:16

Hello guys
I have a problem with the sum partition, I want it to only add the following per sales order as follows

sum(quantity) over ( partition by itemcode)

but i also only want it to add the quantities in each sales order, problem is is adding globally so something like this hopefully you guys will understand

sum(quantity) over (partition by itemcode and salesorder)

I basically want to only sum within each sales order, and at the same time i wan to see all the sales order. It works if I do
where salesorder = '#'.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-01 : 10:35:36

Please provide sample data and expected output.

slinshot
Starting Member

3 Posts

Posted - 2009-05-01 : 10:59:17

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.

if i do where salesorder = '610551' than the query is correct.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-01 : 11:23:06

why are you doing a SUM on itemquantity which already appears to be the sum. Maybe you need to sum up the Remaining Open to match with the itemqauntity.

is this what you need?

declare @t table (cmprefno int, itemno int,ordqty int,remopen int, qtyneed int,sumqty int)
insert @t
select 610551, 121910, 400, 400, -400,800 union all
select 610551, 301308, 400, 400, -400,800 union all
select 610552, 121910, 400, 400, -400,800 union all
select 610552, 301308, 400, 400, -400,800

select itemno, sum(remopen) - sumqty as remamt from @t
group by itemno,sumqty

If not, please explain how "the actual remaining amount is 0 " based on your sample data and provide expected output

slinshot
Starting Member

3 Posts

Posted - 2009-05-01 : 11:38:21

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

I've updated the above results to have more details. 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 sales orders.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources