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
Go to Top of Page

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.
Go to Top of Page

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/
Go to Top of Page

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 13:51:06
How would you write the lead query? I
Go to Top of Page

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 14:08:58
Or lag query
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 16:55:36
Current record to previous record if income changed
Go to Top of Page

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?
Go to Top of Page

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)
Go to Top of Page

staceyw34
Starting Member

14 Posts

Posted - 2015-04-24 : 18:21:46
Its ranking the second record as 1. Why not 2?
Go to Top of Page

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.
Go to Top of Page

staceyw34
Starting Member

14 Posts

Posted - 2015-04-27 : 09:35:23
So do you think we can avoid dupes?
Go to Top of Page

staceyw34
Starting Member

14 Posts

Posted - 2015-04-28 : 09:26:21
I incorporated the having clause where count = 1. Think this works. Thanks for all your help!!
Go to Top of Page
   

- Advertisement -