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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 pivot between two dates

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 endDate
66709 2010-10-11 2016-11-01
66709 2010-10-11 2016-11-01

I want to pivot the above but based on year between startdate and endDate.

ie


select ref, [2010], [2011], [2012], [2013] from(


select ref, year(startdate)as 'StartYear', year(enddate) as 'EndYear'
from members where ref = 66709
) as query

pivot (

not sure about this part but...


count (ref) for (between [startYear] and [endYear] in
([2010], [2011], [2012], [2013])) as result

could 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."
Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-04-08 : 05:28:12
Hello Jeff

thank you for your reply,

I am sorry for the poor post,

please see my example below

declare @example table (

ref int,
startdate date,
enddate date)


insert @example

select '66709', '2010-10-11', '2016-11-01' union all
select '66709', '2010-10-11', '2016-11-01'

select * from @example
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-08 : 05:49:00
Check the link for Dynamic Pivot
http://beyondrelational.com/modules/2/blogs/28/posts/10362/getting-started-with-pivot-queries-in-sql-server-20052008.aspx
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-04-08 : 07:48:08
quote:
Originally posted by masterdineen

Hello Jeff

thank you for your reply,

I am sorry for the poor post,

please see my example below

declare @example table (

ref int,
startdate date,
enddate date)


insert @example

select '66709', '2010-10-11', '2016-11-01' union all
select '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."
Go to Top of Page

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 2013
then there will be 1, else 0.
Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-04-09 : 04:00:02
Hello Jeff

would you be able to give me some code for an example please.

Rob
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 04:05:57
quote:
Originally posted by masterdineen

Hello Jeff

would 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 out

Why 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-04-09 : 04:34:37
ok. please see better example below

declare @example table (

ref int,
startdate date,
enddate date)


insert @example

select '21547', '2008-10-11', '2013-11-01' union all
select '85404', '2006-10-11', '2013-11-01' union all
select '25874', '2011-10-11', '2012-11-01' union all
select '36254', '2003-10-11', '2011-11-01' union all
select '85975', '2004-10-11', '2013-11-01' union all
select '14785', '2003-10-11', '2010-11-01' union all
select '33547', '2012-10-11', '2013-11-01' union all
select '77412', '2008-10-11', '2010-11-01' union all
select '97541', '2006-10-11', '2013-11-01' union all
select '25478', '2011-10-11', '2013-11-01' union all
select '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 2010

ref 25478 has years startdate and enddate range from 2011 to 2013

ref 66709 has years startdate and enddate range from 2010 to 2013

I 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 all
select '25478', '0', '0','0','0','0','0','0','0','1','1', '1' union all
select '66709','0', '0','0','0','0','0','0','1','1','1', '1'
Go to Top of Page

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 query
pivot (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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 04:54:54
you could use the below logic for generating year list dynamically in pivot

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-04-09 : 05:44:25
thank you for that, so basically, your using a simple pivot
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -