Need Assistance with SQL Query

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)

Need Assistance with SQL Query

Author

Topic

tfulmino
Starting Member

7 Posts

Posted - 2007-11-12 : 13:11:38

I am trying to get a running total. I need the query to reset the running total for each year/id. Below is the sample query. Any help would be greatly appreciated.

Code:
CREATE TABLE #valueset (k1 int, date datetime, groupByThis nvarchar(50), c1 int)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13024, '09/14/2007', '2007', 1)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13025, '09/15/2006', '2006', 1)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13025, '10/13/2006', '2006', 1)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13025, '09/14/2007', '2007', 2)

SELECT v.k1, v.date, v.groupByThis, v.c1, RunningTotal=SUM(a.c1)
FROM ( SELECT k1, date, groupByThis, c1, RANK() OVER (PARTITION BY k1 ORDER BY groupbythis, date) as Rank
FROM #valueset ) v

CROSS JOIN

( SELECT k1, date, groupByThis, c1, RANK() OVER (PARTITION BY k1 ORDER BY groupbythis, date) as Rank
FROM #valueset ) a
WHERE a.Rank <= v.Rank
AND a.groupByThis = v.groupByThis
GROUP BY v.k1, v.date, v.groupByThis, v.c1
ORDER BY v.groupByThis, v.date

Drop Table #valueset

Expected Results:
k1 date groupbythis c1 RunningTotal
13025 2006-09-15 00:00:00.000 2006 1 1
13025 2006-10-13 00:00:00.000 2006 1 2
13024 2007-09-14 00:00:00.000 2007 1 1
13025 2007-09-14 00:00:00.000 2007 2 2
13025 2007-11-09 00:00:00.000 2007 1 3

I am almost there - any help would be great. I need to reset the running total after every "groupbythis" for every "k1"

Thanks.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-12 : 13:30:43

Is this that you need?


SELECT V.*
	, RT = RANK() OVER(partition BY V.groupbythis ORDER BY groupbythis,date,k1)
FROM #valueSet V
ORDER BY v.groupByThis, v.date

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tfulmino
Starting Member

7 Posts

Posted - 2007-11-12 : 13:37:55

Dinakar:

The query works, but it is not displaying the correct results. The running total column should be summing the c1 column. In your query you are ranking the rows.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-12 : 14:23:17

Is there any PK on the table like an Identity column?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tfulmino
Starting Member

7 Posts

Posted - 2007-11-12 : 14:27:29

There is not a PK on the table, as the data is coming from a view.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-12 : 14:41:32

The view must be selecting from base table(s). Can the view return a unique value across the table?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tfulmino
Starting Member

7 Posts

Posted - 2007-11-12 : 14:50:53

I am sure I can expose something to make each row unique. What are you thinking? Can you show me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 15:20:32

[code]
SELECT k1,
Date,
GroupByThis,
c1,
ROW_NUMBER() OVER (PARTITION BY GroupByThis ORDER BY Date, c1) AS RunningTotal
FROM #ValueSet
ORDER BY GroupByThis,
Date,
c1[/code]

E 12°55'05.25"
N 56°04'39.16"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-12 : 15:35:52

quote:
Originally posted by tfulmino

Dinakar:

The query works, but it is not displaying the correct results. The running total column should be summing the c1 column. In your query you are ranking the rows.

If you need the total be summed on C1 shouldnt the results be:

k1 date groupbythis c1 RunningTotal
13025 2006-09-15 00:00:00.000 2006 1 1
13025 2006-10-13 00:00:00.000 2006 1 2
13024 2007-09-14 00:00:00.000 2007 1 1
13025 2007-09-14 00:00:00.000 2007 2 3
13025 2007-11-09 00:00:00.000 2007 1 4

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 15:58:38

[code]DECLARE @Sample TABLE (k INT, Date DATETIME, GroupByThis VARCHAR(50), c INT)

INSERT @Sample
SELECT 13024, '09/14/2007', '2007', 1 UNION ALL
SELECT 13025, '09/15/2006', '2006', 1 UNION ALL
SELECT 13025, '10/13/2006', '2006', 1 UNION ALL
SELECT 13025, '09/14/2007', '2007', 2 UNION ALL
SELECT 13025, '2007-11-09', '2007', 1

;WITH Yak (k, Date, GroupByThis, c, r)
AS (
SELECT k,
Date,
GroupByThis,
c,
ROW_NUMBER() OVER (PARTITION BY GroupByThis ORDER BY Date, c) AS RunningTotal
FROM @Sample
)

SELECT y1.k,
y1.Date,
y1.GroupByThis,
y1.c,
SUM(y2.c) AS RunningTotal
FROM Yak AS y1
INNER JOIN Yak AS y2 ON y2.GroupByThis = y1.GroupByThis
WHERE y2.r <= y1.r
GROUP BY y1.k,
y1.Date,
y1.GroupByThis,
y1.c,
y1.r
ORDER BY y1.Date,
y1.GroupByThis,
y1.c[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources