Sql Query problem

Question

Post reply

Sql Query problem

Shaun2012

Hall of Fame

Points: 3214
More actions
February 14, 2009 at 9:36 pm

#218469

i'm writing this query to display
select id, service, datediff(d, date1, date2) as 'total_days' from cost group by id, service.
id service total_days
1 abc 5
2 def 9
3 ghi 15
Now i want to add 4th column to display total_days diff
id service total days tot_days_diff
1 abc 5 0 (first value should always be zero)
2 def 9 4 (9-5)
3 ghi 15 6 (15-9)
Can yo help me out, no clue how to do it.
thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

There are lot of articles on SSC on running totals. See the below articles..

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

http://www.sqlteam.com/article/calculating-running-totals

http://www.sqlservercentral.com/articles/T-SQL/65522/

And also, with SQL 2005, you can do the same using recursive CTE method....

--Ramesh

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Be very careful... the recursive CTE method will be slower than even a poorly formed cursor.

--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)

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 3

hi,

it is working but will it work in sql 2008 using same function for coalesce.

thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

"IT is working..."

What is working?

--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)

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 5

i had tested this code in sql 2000

SELECT id,

days,

days - COALESCE((SELECT max(days)

FROM v_test1 b

WHERE b.id < a.id),0)

AS RunningTotal

FROM v_test1 a

order BY id

it displays

id days runningtotal

1 5 5

2 12 7

3 21 9

im not sure will this function(coalesce) will work in sql 2008 ?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

sumit-saini (2/15/2009)
i had tested this code in sql 2000
SELECT id,
days,
days - COALESCE((SELECT max(days)
FROM v_test1 b
WHERE b.id < a.id),0)
AS RunningTotal
FROM v_test1 a
order BY id
it displays
id days runningtotal
1 5 5
2 12 7
3 21 9
im not sure will this function(coalesce) will work in sql 2008 ?

That has what's known as a "Triangular Join" in it. It seemingly works fine for small numbers of rows but it actually isn't... it's VERY resource intenensive and will drive both the CPU and the disk system nuts. It grows at a rate of ((x-1)²+(x-1))/2 for duration where x = number of rows. Please see the following article on what a "Triagular Join" actually is...

http://www.sqlservercentral.com/articles/T-SQL/61539/

If you need to do a running total on more than, say, 1,000 rows, post back and I'll show you how to do it with great speed. I'd point you to the article I wrote on the subject, but it's currently being rewritten.

--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)

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 7

Hi Jeff,

Yes, i'm gonna have more than 1000 could be from 5000 to no limit.

So could you please show me the better way in sql 2008

thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Ok... the following should work... read the comments... the advantage of this particular set based method is that you can create a view from it at the expense of just a bit of performance.

--===== This is not part of the solution...

-- This just creates some test data.

CREATE TABLE #Cost

(

ID INT,

Service CHAR(3),

Date1 DATETIME,

Date2 DATETIME

)

INSERT INTO #Cost

(ID, Service, Date1, Date2)

SELECT 10,'abc','20090101','20090106' UNION ALL

SELECT 20,'def','20090101','20090110' UNION ALL

SELECT 100,'ghi','20090101','20090106' UNION ALL

SELECT 21,'jkl','20090201','20090206' UNION ALL

SELECT 60,'mno','20090206','20090206' UNION ALL

SELECT 30,'pqr','20090204','20090224'

;WITH

cteAddRowNum AS

(--==== This is your query with a RowNum added

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum,

ID, Service, DATEDIFF(d, Date1, Date2) as 'Total_Days'

FROM #Cost

)

--===== And this uses the above twice to create the solution

-- without a triangular join.

SELECT hi.ID, hi.Service, hi.Total_Days,

hi.Total_Days - ISNULL(lo.Total_Days,0)

FROM cteAddRowNum hi

LEFT OUTER JOIN cteAddRowNum lo

ON hi.RowNum -1 = lo.RowNum

I will tell you that, if you have no requirements for it to be in a view and you have some extreme performance requirements, there is a very fast method (1 million row update in 7 seconds or less) that we could use. It does require the use of a temp table with a clustered index.

--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)

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 9

Hi Jeff,

Thanks for the code, but i will be test the code tommorrow @ my work and i will be able to tell you the performance of this logic with my query.

thanks

Sumit

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

sumit-saini (2/16/2009)
Hi Jeff,
Thanks for the code, but i will be test the code tommorrow @ my work and i will be able to tell you the performance of this logic with my query.
thanks
Sumit

So... how did it work out?

--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)

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 11

Hi Jeff,

I never got the time to test it, got involved some other project. So as i test it i will definitely let u know!!

Thanks!!!