SQL Server Repeat number of rows based upon value

Question

I have a record which has following structure,

Item      ActiveFrom         ActiveTo
 A        1/01/2015           25/12/2018

I have a Where Condition with Year Value . So whenever i apply a year value ,the records active on that year should be obtained.

for ablove Structure I did,

Select Item,YEAR(ActiveFrom) From Test
UNION 
Select Item,YEAR(ActiveTo) From Test

So i get two line items as below,

Item    Year
A        2015
A        2018

Now when i Apply where Clause as 2017 this records are not fetched ,so i need output like below,

Item    Year
A        2015
A        2016
A        2017
A        2018

Any idea ?

Please explain weather you want all years or just that you want after where clause also you only want year 2015 and 2018? — Abhishek_Itachi, Nov 30 '18 at 09:47
How are you applying the where? Aren't you doing it only for one of the date columns? — Kim Lage, Nov 30 '18 at 09:47
I want the records to get repeated for years it has been active — Srini, Nov 30 '18 at 09:48
I have created the statement as a view so whenever i execute Select * from View Where Year=2018 it returns all the record active in that year. — Srini, Nov 30 '18 at 09:51

score 0 · Answer 1 · answered Nov 30 '18 at 09:49

Join with a calendar table:

WITH years AS (
    SELECT 2015 AS year UNION ALL
    SELECT 2016 UNION ALL
    SELECT 2017 UNION ALL
    SELECT 2018
)

SELECT
    t.Item,
    c.year
FROM years c
INNER JOIN Test t
    ON c.year BETWEEN YEAR(t.ActiveFrom) AND YEAR(t.ActiveTo);

SQL Server Repeat number of rows based upon value

1 Answers1