net SUM by date

Question

Post reply

net SUM by date

phingers

Ten Centuries

Points: 1041
More actions
January 17, 2014 at 6:48 am

#287382

Can anyone please advise how to write a query to return this data
date palletmovement qty
14/01/20141722608110
15/01/20141722608-110
14/01/20141722609110
14/01/20141722610110
14/01/2014172261155
15/01/20141722611-55
14/01/20141722612110
14/01/20141723975110
14/01/2014172397647
14/01/20141723977110
15/01/20141723977-110
14/01/2014172398063
in the format below? i.e. each combination of date and pallet has a net qty
date pallet qty
14/01/20141722608110
15/01/201417226080
14/01/20141722609110
14/01/20141722610110
14/01/2014172261155
15/01/201417226110
14/01/20141722612110
14/01/20141723975110
14/01/2014172397647
14/01/20141723977110
15/01/201417239770
14/01/2014172398063
Thanks for any advice

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

A simple SUM on the qty column combined with a GROUP BY on the others will do it:

select date, pallet_movement, sum(qty) as total_qty

from table_name

group by date, pallet_movement

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

pietlinden SSC Guru Points: 63503 More actions · Answer 2

Looks like a running total... if you're using 2012, this works:

sample data setup:

-- create table in tempdb (yes, it probably should have a PK)

CREATE TABLE #pMovement(

pDate DATE,

pallet int,

qty int

);

GO

-- populate it with data...

INSERT INTO #pMovement(pDate, pallet, qty)

VALUES

('01/14/2014',1722608,110),

('01/15/2014',1722608,-110),

('01/14/2014',1722609,110),

('01/14/2014',1722610,110),

('01/14/2014',1722611,55),

('01/15/2014',1722611,-55),

('01/14/2014',1722612,110),

('01/14/2014',1723975,110),

('01/14/2014',1723976,47),

('01/14/2014',1723977,110),

('01/15/2014',1723977,-110),

('01/14/2014',1723980,63);

SELECTpDate

, pallet

, qty

, ROW_NUMBER() OVER (ORDER BY pallet, pDate) As rn

, SUM(qty) OVER (PARTITION BY pallet ORDER BY pallet, pDate) as rt

FROM #pMovement;

phingers Ten Centuries Points: 1041 More actions · Answer 3

Thanks pietlinden,

I'm using 2008R2, what would the select statement be from the temp table please as your 2012 one doesn't parse?

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

I'm sorry. I have misread your the results in your Original post. My first solution didn't gave you the results you needed. The code below does it better. Keep in mind the performance will degrade if you need to calculate over a lot of rows. You should reed the article of Jeff Moden (http://www.sqlservercentral.com/articles/T-SQL/68467/) for another approach on this.

;WITH CTE_rownr

AS (

SELECT pDate

, pallet

, qty

, ROW_NUMBER() OVER (

PARTITION BY pallet ORDER BY pallet

, pDate

) AS rn

FROM #pMovement

)

SELECT *

, (

SELECT sum(qty)

FROM cte_rownr

WHERE pallet = base.pallet AND rn <= base.rn

) as running_total

FROM CTE_rownr base

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

phingers Ten Centuries Points: 1041 More actions · Answer 5

phingers

Ten Centuries

Points: 1041

January 20, 2014 at 7:00 am

#1681524

Thanks HanShi, that works great 🙂