Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filling in the years between start and end dates


Filling in the years between start and end dates

Author
Message
Scott Nicholson-254313
Scott Nicholson-254313
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 31
I need the following data to return something like

Table_ID Category_ID Year Start_Date End_Date
G700052 1 2011 2011-01-01 2015-12-31
G700052 1 2012 2011-01-01 2015-12-31
G700052 1 2013 2011-01-01 2015-12-31
G700052 1 2014 2011-01-01 2015-12-31
G700052 1 2015 2011-01-01 2015-12-31
G700063 1 2011 2011-01-01 2015-12-31
G700063 1 2012 2011-01-01 2015-12-31
G700063 1 2013 2011-01-01 2015-12-31
G700063 1 2014 2011-01-01 2015-12-31
G700063 1 2015 2011-01-01 2015-12-31
etc...

The datasource is a table that looks similar to

Table_ID   Category_ID   Start_Date    End_Date
G700052   1    2011-01-01    2015-12-31 00:00:00.000
G700063   1    2011-01-01    2015-12-31 00:00:00.000
G700089   1    2012-01-01    2016-12-31 00:00:00.000
G800154   1    2013-01-01    2017-12-31 00:00:00.000
LU100022   37    2012-02-03    2016-12-31 00:00:00.000

I want to be able to display detailed information grouping by Year or Category. I am not very familiar with Recursive CTEs and am looking for any assistance.

Thanks.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16539 Visits: 16993
Hi and welcome to the forums. It is generally preferred if you can post ddl and sample data in a consumable format. Since you are brand new around here I did this for you so you can see an example. You can take a look at the first link in my signature for all the best practices when posting questions.


create table #Something
(
   Table_ID varchar(10),
   Category_ID int,
   Start_Date datetime,
   End_Date datetime
)

insert #Something
select 'G700052', 1, '2011-01-01', '2015-12-31 00:00:00.000' union all
select 'G700063', 1, '2011-01-01', '2015-12-31 00:00:00.000' union all
select 'G700089', 1, '2012-01-01', '2016-12-31 00:00:00.000' union all
select 'G800154', 1, '2013-01-01', '2017-12-31 00:00:00.000' union all
select 'LU100022', 37, '2012-02-03', '2016-12-31 00:00:00.000'



You don't need a recursive cte or any kind of looping mechanism for this type of thing. You need to use a tally table. You can read more about the tally table here.

http://www.sqlservercentral.com/articles/62867/

I like to use a cte for a tally table because it is lightning fast.

Something like this.


WITH
   E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
   E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
   E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
   cteTally(N) AS
   (
      SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
   Wink
select *
from #Something s
join cteTally t on t.N >= YEAR(start_date) and t.N <= YEAR(End_Date)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search