November 7, 2013 at 1:54 pm
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_IDCategory_IDStart_Date End_Date
G7000521 2011-01-01 2015-12-31 00:00:00.000
G7000631 2011-01-01 2015-12-31 00:00:00.000
G7000891 2012-01-01 2016-12-31 00:00:00.000
G8001541 2013-01-01 2017-12-31 00:00:00.000
LU10002237 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.
November 7, 2013 at 2:19 pm
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/[/url]
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply