You have a table that contains rows identifying start and end values for a range and you want to convert this to rows i.e. one row for every number between the start of the range and the end of the range. You also need to do it in a view.
An example is :
Which you want represented as :
Moving along, your view would look something like this, a Common Table Expression embedded in a view…
CREATE VIEW [dbo].[RangeBuster_VW] AS WITH RangeBuster (Id, SomeDate, StartRange, EndRange, RangeValue) AS ( SELECT [Id] ,[SomeDate] ,CAST([StartRange] as int) AS StartRange ,CAST([EndRange] as int) AS EndRange ,CAST([StartRange] as int) AS RangeValue FROM [dbo].[SomeTable] UNION ALL SELECT [Id] ,[SomeDate] ,[StartRange] ,[EndRange] ,[RangeValue] + 1 FROM RangeBuster WHERE [RangeValue] < [EndRange] ) SELECT * FROM RangeBuster GO
So this is good but say you also want to rank the rows in each grouping :
…then add the following to your SELECT clause within the view…
SELECT *, row_number() over ( partition by Id order by RangeValue desc) as [Rank] FROM RangeBuster