Group cumulative data

Question

Group cumulative data

nam-811192

SSC Rookie

Points: 27
More actions
February 13, 2009 at 12:02 am

#130970

Hi everyone,
I have to write a query that group data follow that :
sample data:
weekdateSKUOpeningDeliveryClosed
11/1/09a1002080
12/1/09a801070
13/1/09a70-30100
14/1/09a1002080
15/1/09a80575
the closing of 1/1109 will be opening of 2/1/09 (cumulative data)
and this data will be grouped by week:
week SKUopeningdelieveryclosed
1a1002575
in the result:
opening value will get from opening of min(date of week)
Delevery value will be sum(delivery of week)
Closed value will be closed of max(date of week)
I think we can do if make 3 queries:
+ 1 query get opening
+ 1 query get delivery
+ 1 qeury get closing
and join 3 queries to get the final result
but it think this is commplicated and too slow to query.
So any ideas to help me on this queries ? thanks

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

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Your example result line has no date on it and, maybe I'm being a bit thick, but I can't actually figure out what you want from your description. It would be helpful if you took your full input example and showed the full output example you're looking for.

It would also be helpful if you posted table creation code and the data in the form of INSERT statements so that we can actually test our solution(s). It'll help you get a better answer quicker.

I'd also suggest that you read the article at the link in my signature for this and future posts. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Milla-1016924 SSC Veteran Points: 227 More actions · Answer 2

Please try:

DECLARE @test-2 TABLE (week TINYINT

,date DATETIME

,sku CHAR(1)

,opening INTEGER

,delivery INTEGER

,closed INTEGER)

INSERT INTO @test-2 (week,date,SKU,opening,delivery,closed)

SELECT 1,'1/1/09','a',100,20,80 UNION ALL

SELECT 1,'2/1/09','a',80,10,70 UNION ALL

SELECT 1,'3/1/09','a',70,-30,100 UNION ALL

SELECT 1,'4/1/09','a',100,20,80 UNION ALL

SELECT 1,'5/1/09','a',80,5,75

SELECT a.week

,a.sku

,(SELECT opening

FROM @test-2 b

WHERE b.week = a.week

AND b.sku = a.sku

AND b.date = a.minDate) opening

,a.delivery

,(SELECT closed

FROM @test-2 b

WHERE b.week = a.week

AND b.sku = a.sku

AND b.date = a.maxDate) closed

FROM (SELECT week

,SUM(delivery) delivery

,sku

,max(date) maxDate

,min(date) minDate

FROM @test-2

GROUP BY week, sku) a

or you can use CTE

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Nice eye, Milla. Guess I need more coffee.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nam-811192 SSC Rookie Points: 27 More actions · Answer 4

That's great, Milla. Thanks so much.:) I learnt a lot from your script.

But sorry, what's CTE?

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 5

[font="Verdana"]CTE: Common Table Expression. Introduced in SQL Server 2005. Well worthwhile learnng! They make complicated SQL statements so much clearer.[/font]

nam-811192 SSC Rookie Points: 27 More actions · Answer 6

nam-811192

SSC Rookie

Points: 27

February 15, 2009 at 7:20 pm

#944307

Thanks Bruce.