SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 pivot between two dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 04/05/2013 :  10:54:27  Show Profile  Reply with Quote
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

USA
649 Posts

Posted - 04/07/2013 :  11:51:47  Show Profile  Reply with Quote
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.
http://www.sqlservercentral.com/articles/Best%20Practices/61537/

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.
http://www.sqlservercentral.com/articles/Crosstab/65048/

--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."

Edited by - Jeff Moden on 04/07/2013 11:56:38
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 04/08/2013 :  05:28:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/08/2013 :  05:49:00  Show Profile  Reply with Quote
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

USA
649 Posts

Posted - 04/08/2013 :  07:48:08  Show Profile  Reply with Quote
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

United Kingdom
548 Posts

Posted - 04/08/2013 :  11:32:16  Show Profile  Reply with Quote
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

United Kingdom
548 Posts

Posted - 04/09/2013 :  04:00:02  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/09/2013 :  04:05:57  Show Profile  Reply with Quote
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

United Kingdom
548 Posts

Posted - 04/09/2013 :  04:34:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/09/2013 :  04:43:43  Show Profile  Reply with Quote
;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


EDIT: use dynamic pivot query in the case of dynamic Years

Edited by - bandi on 04/09/2013 04:47:25
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/09/2013 :  04:54:54  Show Profile  Reply with Quote
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

United Kingdom
548 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/09/2013 :  05:48:44  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000