Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-04-05 : 10:54:27
|
Hello there,I have the below data, (small example.)ref startdate endDate66709 2010-10-11 2016-11-0166709 2010-10-11 2016-11-01I want to pivot the above but based on year between startdate and endDate.ieselect ref, [2010], [2011], [2012], [2013] from(select ref, year(startdate)as 'StartYear', year(enddate) as 'EndYear' from members where ref = 66709) as querypivot (not sure about this part but...count (ref) for (between [startYear] and [endYear] in([2010], [2011], [2012], [2013])) as resultcould this be achievable ? |
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-04-07 : 11:51:47
|
I suspect that you've been around this forum long enough to know that you'll get much better answers much more quickly if you provide some data in a readily consumable format. Please see the following article for how to do that correctly.[url]http://www.sqlservercentral.com/articles/Best%20Practices/61537/[/url]As to your question, you're going to need a bit of dynamic SQL. If I had some readily consumable data, I'd post actual code. Since I don't, you'll have to settle for an article on the subject. [url]http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-04-08 : 05:28:12
|
Hello Jeffthank you for your reply, I am sorry for the poor post,please see my example belowdeclare @example table (ref int,startdate date,enddate date)insert @example select '66709', '2010-10-11', '2016-11-01' union allselect '66709', '2010-10-11', '2016-11-01'select * from @example |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-08 : 05:49:00
|
Check the link for Dynamic Pivothttp://beyondrelational.com/modules/2/blogs/28/posts/10362/getting-started-with-pivot-queries-in-sql-server-20052008.aspx |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-04-08 : 07:48:08
|
quote: Originally posted by masterdineen Hello Jeffthank you for your reply, I am sorry for the poor post,please see my example belowdeclare @example table (ref int,startdate date,enddate date)insert @example select '66709', '2010-10-11', '2016-11-01' union allselect '66709', '2010-10-11', '2016-11-01'select * from @example
I was hoping for a bit more than two rows, for sure. What do you want the actual output to look like from those two rows? Your original post only lists 2010-2013 as output. Do you want just a horizontal list of the years 2010 thru 2016 with a count of "2" in each?--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-04-08 : 11:32:16
|
well I would only have years from 2010 to 2013.but like my blinding example. as long as some has years between year(startdate) and year(enddate) from 2010 and 2013then there will be 1, else 0. |
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-04-09 : 04:00:02
|
Hello Jeffwould you be able to give me some code for an example please.Rob |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-09 : 04:05:57
|
quote: Originally posted by masterdineen Hello Jeffwould you be able to give me some code for an example please.Rob
unless we understand whats your actual requirement we wont be able to help you outWhy dont you put some good amount of sample data and show us what you want?I still didnt understand why you've only years till 2013 when your end date is somewhere in 2016.is it always like from start till current year?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-04-09 : 04:34:37
|
ok. please see better example belowdeclare @example table (ref int,startdate date,enddate date)insert @example select '21547', '2008-10-11', '2013-11-01' union allselect '85404', '2006-10-11', '2013-11-01' union allselect '25874', '2011-10-11', '2012-11-01' union allselect '36254', '2003-10-11', '2011-11-01' union allselect '85975', '2004-10-11', '2013-11-01' union allselect '14785', '2003-10-11', '2010-11-01' union allselect '33547', '2012-10-11', '2013-11-01' union allselect '77412', '2008-10-11', '2010-11-01' union allselect '97541', '2006-10-11', '2013-11-01' union allselect '25478', '2011-10-11', '2013-11-01' union allselect '66709', '2010-10-11', '2013-11-01' select * from @example So what I want to achieve is, pivoting on years Min(Year(startdate)) to max(year(enddate))for example, ref 14785 has years startdate and enddate range from 2003 to 2010ref 25478 has years startdate and enddate range from 2011 to 2013ref 66709 has years startdate and enddate range from 2010 to 2013I would want the result to be declare @result table (ref int,[2003] int,[2004] int,[2005] int,[2006] int,[2007] int,[2008] int,[2009] int,[2010] int,[2011] int,[2012] int,[2013] int)insert @result select '14785', '1', '1','1','1','1','1','1','1','0','0', '0' union allselect '25478', '0', '0','0','0','0','0','0','0','1','1', '1' union allselect '66709','0', '0','0','0','0','0','0','1','1','1', '1' |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-09 : 04:43:43
|
[code];WITH RecCte AS ( SELECT ref, MIN(YEAR(startdate)) StartYear, MAX(YEAR(enddate)) EndYear FROM @example GROUP BY ref union all SELECT ref, StartYear+1, EndYear FROM RecCte WHERE StartYear < EndYear)select ref, [2003],[2004],[2005],[2006],[2007],[2008],[2009], [2010], [2011], [2012], [2013] from( SELECT ref, StartYear, EndYear FROM RecCte ) as querypivot (count(EndYear) for StartYear IN([2003],[2004],[2005],[2006],[2007],[2008],[2009], [2010], [2011], [2012], [2013])) as result[/code]EDIT: use dynamic pivot query in the case of dynamic Years |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-04-09 : 05:44:25
|
thank you for that, so basically, your using a simple pivot |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-09 : 05:48:44
|
quote: Originally posted by masterdineen thank you for that, so basically, your using a simple pivot
yep. but you can extend it to generate yearlist dynamically as per link------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|