troubleshooting Question

removing rows from sql result set where some column values are repeated

Avatar of blossompark
blossomparkFlag for Ireland asked on
Microsoft SQL Server
1 Comment1 Solution98 ViewsLast Modified:
Hi all,
This question has grown from  a previous question answered by PortletPaul. The solution supplied by PortletPaul was perfect but I now need a further tweak to the solution.
the previous query can be seen at
I have the following  table
rate_change-table.PNGthe table tracks the rate  history of accounts
I want to to return  from this table details of rate change increases or decreases over time.
If  the rate did not change from one date to another I do not need this  in the result set.
NOTE: although the table above shows only decreases, it is possible in the full table there are increases also.

portletPaul produced the following code
    , t.TranDate
    , t.rate
    , case when t.TranDate = D.tranDate then 'Base'
           when t.rate > d.rate then 'Increase'
           when t.rate < d.rate then 'Decrease'
           else 'Equal'
      end as Movement
from YourTable t
inner join (
        select acc1, TranDate, rate, row_number() over(partition by acc1 order by TranDate ASC) as rn
        from YourTable
        ) d ON t.acc1 = d.acc1 and d.rn=1
where (
        t.TranDate = d.TranDate
        t.rate < d.rate
order by acc1, TranDate
which produces the following result set
portletpaul_output.PNGthe out put i now require is as follows.
whenever  a rate for a specific account does not change,
i want that row removed. see below for required output
i have also uploaded the original table contents in xls file
any guidance appreciated

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros