Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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
http://rextester.com/MXJU32426
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
select
      t.acc1
    , 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
      or 
        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
output-required.PNG
i have also uploaded the original table contents in xls file
thanks,
any guidance appreciated
table-contents.xlsx
ASKER CERTIFIED SOLUTION
Avatar of NanaTechie
NanaTechie

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

Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answers