Using Rank - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Using Rank

Author

Topic

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 13:17:19

I have a query that ranks. Once I get the ranked fields is there a way to compare the 2 total_income fields?

Here's the query:

select * from
(
select cardholderid, appcnum, total_income ,Rank() over (PARTITION BY A.APPCNUM
ORDER BY A.EFFSTARTDATE DESC) as Rank
from
TBL_EPIC_BILLSTATUS A
) tmp
where Rank in (2,3) and CARDHOLDERID = '704355'
--------------and rank ((2),total_income) <> rank (3),total_income))looking to do something like this to see if the income is different

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-24 : 13:24:21

>= sql 2012, use lag function
<= sql 2008 or earlier, put select in a cte then self-join the cte

with cte as
(
your query
)


select *
from cte c1
left join cte c2
  on c1.rank = c2.rank-1
where c1.rank is not null
and c1.total_income <> c2.total_income

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 13:31:15

I'm trying to create the query in TOAD. What is cte?
I attempted the lead function but couldnt get it working.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-24 : 13:47:20

I don't use TOAD. Just SSMS.

CTE: http://www.sqlservercentral.com/articles/Stairway+Series/122606/

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 13:51:06

How would you write the lead query? I

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 14:08:58

Or lag query

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-24 : 14:14:15

basic idea (could have syntax errors!)


select *
from 
(
	select cardholderid, appcnum, total_income ,Rank() over (PARTITION BY A.APPCNUM
	ORDER BY A.EFFSTARTDATE DESC) as Rank
	, LAG(Total_Income) over (PARTITION BY A.APPCNUM
	ORDER BY A.EFFSTARTDATE DESC) as Lag_Total_Income
	from
	TBL_EPIC_BILLSTATUS A
) _

where total_income <> Lag_Total_Income

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 16:20:22

Can I compare only the second and third rank....NOT whenever the income changed at any rank

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 16:35:55

Ultimately I want to just look at rank 1 and 2 to see if they are different.

This 3 and 4 rank is just testing one historical issue.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-24 : 16:41:27

Yes, but its probably time to post some sample data and desired results . be sure to post as INSERT INTO statements

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 16:54:56

I want to compare the latest record to previous record if income changed. The query now seems to look at any change in income whenever there is a change in income. This query will end up being a view.

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 16:55:36

Current record to previous record if income changed

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 17:08:45

I think all i need to do is look at:

where total_income <> Lag_Total_Income
and rank = 1

That sound right to see if current record is different from previous record?

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 17:28:23

Even with rank = 1 it pulls in duplicates. I only want to look at two records in the same table.

Here's the table:
EFFSTARTDATE CARDHOLDERID TOTAL_INCOME
4/1/2015 12:00:00 AM EP0966695 33363
4/1/2015 12:00:00 AM EP0966695 29455
4/1/2015 12:00:00 AM EP0966695 30713

Here's the query results:

(CARDHOLDERID, APPCNUM, TOTAL_INCOME, RANK, LAG_TOTAL_INCOME) VALUES
('EP0966695', 5411868, 30713, 1, 33363),
('EP0966695', 5411868, 29455, 1, 30713)

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 18:21:46

Its ranking the second record as 1. Why not 2?

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 18:40:01

I guess because the effstartdate is the same for all records on the table. Can this approach still work with duplicate records? I thought it could.

staceyw34
Starting Member

14 Posts

Posted - 2015-04-27 : 09:35:23

So do you think we can avoid dupes?