Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filling in the years between start and end dates Expand / Collapse
Author
Message
Posted Thursday, November 7, 2013 1:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 5:55 AM
Points: 15, 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.
Post #1512441
Posted Thursday, November 7, 2013 2:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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
)
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)
Post #1512448
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse