calculate trend of a column

Question

calculate trend of a column

jaya

SSCrazy

Points: 2109
More actions
August 22, 2012 at 1:08 pm

#260981

I have a temp table where Iam inserting data from original table
temp_id, temp_test_id, temp_status
1 2 failed
2 2 failed
3 2 passed
4 17 failed
5 17 passed
4 17 failed
I want to display trend in a new column that has following values.
temp_id, temp_test_id, temp_status, temp_trend
1 2 failed -1
2 2 failed -2
3 2 passed 1
4 17 failed -1
5 17 passed 1
6 17 passed 2
7 17 failed -1
When staus is failed I wan trend to be neagative. When status is passed I want trend to be positive.
How do I do that??

Viewing 15 posts - 1 through 15 (of 15 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

lallu_jaya (8/22/2012)
I have a temp table where Iam inserting data from original table
temp_id, temp_test_id, temp_status
1 2 failed
2 2 failed
3 2 passed
4 17 failed
5 17 passed
4 17 failed
I want to display trend in a new column that has following values.
temp_id, temp_test_id, temp_status, temp_trend
1 2 failed -1
2 2 failed -2
3 2 passed 1
4 17 failed -1
5 17 passed 1
6 17 passed 2
7 17 failed -1
When staus is failed I wan trend to be neagative. When status is passed I want trend to be positive.
How do I do that??

You can accomplish fairly easily with ROW_NUMBER().

Your "sample data" does not match the number of rows in your desired output.

I think this matches what you are looking for...

;with cte (temp_id, temp_test_id, temp_status)

as

(

select 1, 2, 'failed' union all

select 2, 2, 'failed' union all

select 3, 2, 'passed' union all

select 4, 17, 'failed' union all

select 5, 17, 'passed' union all

select 6, 17, 'failed'

)

select *, ROW_NUMBER() over(partition by temp_test_id, temp_status order by temp_test_id, temp_id) * Case temp_status when 'failed' then -1 else 1 end as RowNumber

from cte

Notice how I provided data in a format that makes it easy to consume. You should do something like this in the future.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jaya SSCrazy Points: 2109 More actions · Answer 2

@sean Lange

Iam having multiple values for test_status which I didnt mention in my 1st post.

I am having other status like rerun, non completed, investigate etc which I want to assign value '-1'

But if I partition it by status I am having separate sections for different status. But I want to view only 2 sections of data

1 passed,

2 failed, rerun,not completed, investigate etc

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

lallu_jaya (8/22/2012)
@Sean Lange
Iam having multiple values for test_status which I didnt mention in my 1st post.
I am having other status like rerun, non completed, investigate etc which I want to assign value '-1'
But if I partition it by status I am having separate sections for different status. But I want to view only 2 sections of data
1 passed,
2 failed, rerun,not completed, investigate etc

ddl and sample data please. If you are unsure of how to post that, please see the first link in my signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sandeep rawat Ten Centuries Points: 1317 More actions · Answer 4

sandeep rawat

Ten Centuries

Points: 1317

August 22, 2012 at 11:01 pm

#1529030

provide full detail

jaya SSCrazy Points: 2109 More actions · Answer 5

The script does not work for this case

;with cte (temp_id, temp_test_id, temp_status)

as

(

select 1, 2, 'failed' union all

select 2, 2, 'failed' union all

select 3, 2, 'passed' union all

select 4, 17, 'failed' union all

select 5, 17, 'passed' union all

select 6, 17, 'failed' union all

select 7, 17, 'failed' union all

select 8, 17, 'passed'

)

select *, ROW_NUMBER() over(partition by temp_test_id, temp_status order by temp_test_id, temp_id) * Case temp_status when 'failed' then -1 else 1 end as RowNumber

from cte

order by temp_id asc

[/code]

I have the following result set

temp_idtemp_test_idtemp_statusRowNumber

12failed-1

22failed-2

32passed1

417failed-1

517passed1

617failed-2

717failed-3

817passed2 ---- which should be (1) because it passed after failing.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Sean's query is close, but I think it will need a difference calculation in there to get the "reset to 1" trick working.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

jaya SSCrazy Points: 2109 More actions · Answer 7

How do I open the post. Need help on this "Reset to 1" trick to work.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 8

;with cte (temp_id, temp_test_id, temp_status)

as

(

select 1, 2, 'failed' union all

select 2, 2, 'failed' union all

select 3, 2, 'passed' union all

select 4, 17, 'failed' union all

select 5, 17, 'passed' union all

select 6, 17, 'failed' union all

select 7, 17, 'failed' union all

select 8, 17, 'passed'

),

cte1 as (

select *,

row_number() over(partition by temp_test_id order by temp_id) as rn1,

row_number() over(partition by temp_test_id, temp_status order by temp_id) as rn2

from cte)

select temp_id, temp_test_id, temp_status,

ROW_NUMBER() over(partition by temp_test_id, temp_status, rn2-rn1 order by rn1) * Case temp_status when 'failed' then -1 else 1 end as RowNumber

from cte1

order by temp_id asc

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

jaya SSCrazy Points: 2109 More actions · Answer 9

@Mark-101232

The above script worked. Thank you.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Mark has it. That's what a Difference Query is... 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 11

RBarryYoung (8/23/2012)
Mark has it. That's what a Difference Query is... 🙂

Have a look a Jeff Modens excellent article here[/url] for an explanation

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Mark-101232 (8/23/2012)
RBarryYoung (8/23/2012)
Mark has it. That's what a Difference Query is... 🙂
Have a look a Jeff Modens excellent article here[/url] for an explanation

Actually, Jeff and I learned about this trick together, on a thread in this very site: Here. 😎

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 13

RBarryYoung (8/23/2012)
Mark-101232 (8/23/2012)
RBarryYoung (8/23/2012)
Mark has it. That's what a Difference Query is... 🙂
Have a look a Jeff Modens excellent article here[/url] for an explanation
Actually, Jeff and I learned about this trick together, on a thread in this very site: Here. 😎

You'll find it goes back to at least here. 4th post was mine btw.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Mark-101232 (8/23/2012)
RBarryYoung (8/23/2012)
Mark-101232 (8/23/2012)
RBarryYoung (8/23/2012)
Mark has it. That's what a Difference Query is... 🙂
Have a look a Jeff Modens excellent article here[/url] for an explanation
Actually, Jeff and I learned about this trick together, on a thread in this very site: Here. 😎
You'll find it goes back to at least here. 4th post was mine btw.

Very cool. I was pretty sure that it had been around for a long time, but I was never able to find any information on its history.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]