removing rows from sql result set where some column values are repeated
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 the 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 Movementfrom YourTable tinner 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=1where ( t.TranDate = d.TranDate or t.rate < d.rate )order by acc1, TranDate
which produces the following result set the 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 table-contents.xlsx