I supposed that your table data is sorted by
Route , Days criteria as you listed above, and I named it route
So here it is :
with a(r, d, cnt)
as
(
select route, days , COUNT(*) from route
group by route, Days
union all
select r , d , cnt-1 from a where cnt >1
)
select * from a
order by r,d, cnt
Thanks to CTE's :)
EDIT :
a = output table name of our CTE which have 3 fields r,d,cnt
r = is route id from your route table
d = is days field from your route table
cnt = a decreasing counter
our anchor statement in a recursive CTE :
select route, days , COUNT(*) from route
group by route, Days
our recursive part of our CTE :
select r , d , cnt-1 from a where cnt >1
after these two parts we can use our CTE output in another CTE or in an ordinary SQL statement
so this is our ordinary SQL statement that uses our CTE output :
select * from a
order by r,d, cnt
Try to make a test table with your mentioned data in the question and run this query against it :)
EDIT 2 :
After seeing your data , try this one :
SELECT DepotCode, RouteCode, RouteName , ArbRouteName, TotalSale, DayNames, DayNumber ,ROW_NUMBER() OVER(PARTITION BY DepotCode, DayNumber ORDER BY DepotCode, DayNumber) AS 'record'
FROM routes
If it was solved your problem do not forget to accept it as an answer or vote it up ;)
Submit an article or tip