Solved

SQL - privot table creating from-to date columns

Posted on 2011-03-08
8
257 Views
Last Modified: 2012-05-11
I have a table of records that consist of customer_no, activity_date and sequence_no.

I need to create a query that results in customer_no, activity_from_date, activity_to_date so that essentially the from/to dates consist of the to date being the current record's date, and the from date being the prior record's date.

Not sure if this involves a pivot or throwing it into a temp table and then pivoting??

0
Comment
Question by:wppiexperts
8 Comments
 
LVL 13

Expert Comment

by:devlab2012
ID: 35071194
You need a SELF join.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35071207
Can you provide some sample data with expected result?
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35071475
declare @t table (
	customer_no int,
	activity_date datetime,
	sequence_no int)

insert into @t
select 1,'1/1/2011',1
union select 1,'1/2/2011',2
union select 1,'1/3/2011',3
union select 2,'1/1/2011',1
union select 2,'1/5/2011',2

select
	t1.customer_no, t2.activity_date as 'activity_from_date', t3.activity_date as 'activity_to_date'
from
	(
		select 
			t.customer_no, max(sequence_no) 'maxseq' 
		from 
			@t t
		group by
			t.customer_no) t1
	inner join @t t2
		on	t1.customer_no = t2.customer_no
			and t1.maxseq -1 = t2.sequence_no 
	inner join @t t3
		on	t1.customer_no = t3.customer_no
			and t1.maxseq = t3.sequence_no

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 15

Expert Comment

by:derekkromm
ID: 35071492
actually, here's a small change to account for times when there is only 1 record for a given customer (from and to dates will be the same date)
declare @t table (
	customer_no int,
	activity_date datetime,
	sequence_no int)

insert into @t
select 1,'1/1/2011',1
union select 1,'1/2/2011',2
union select 1,'1/3/2011',3
union select 2,'1/1/2011',1
union select 2,'1/5/2011',2
union select 3, '1/1/2011',1

select
	t1.customer_no, isnull(t2.activity_date, t3.activity_date) as 'activity_from_date', t3.activity_date as 'activity_to_date'
from
	(
		select 
			t.customer_no, max(sequence_no) 'maxseq' 
		from 
			@t t
		group by
			t.customer_no) t1
	left outer join @t t2
		on	t1.customer_no = t2.customer_no
			and t1.maxseq-1 = t2.sequence_no 
	inner join @t t3
		on	t1.customer_no = t3.customer_no
			and t1.maxseq = t3.sequence_no

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 300 total points
ID: 35071837
a join should be sufficent , however you do need to tell us how your data is sequenced and what you mean be current and prior records....


if you mean that sequence number was an independant incremented value specifying the order of the rows....
(per customer)

then something like this

select a.customer_number,a.activity_date as from,B.activity_date as to,a.sequence_number
from yourtable as a
left join yourtable as b
on a.customer_number=b.customer_number
and a.sequence_number=b.sequence_number - 1
order by a.customer_number,a,sequence_number


0
 

Author Comment

by:wppiexperts
ID: 35071885
the sequence number is based on year and month, so while they can be sorted based on that, there will be an issue where the year changes:

201102
201101
201012
201011

so an incremental join based on the field won't work in instances such as 201101 and 201012. I may need to put in a autonumber type of field with which to do the sequence join.
0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 200 total points
ID: 35071933
this should handle that

declare @t table (
	customer_no int,
	activity_date datetime,
	sequence_no int)

insert into @t
select 1,'1/1/2011',1
union select 1,'1/2/2011',2
union select 1,'1/3/2011',500
union select 2,'1/1/2011',1
union select 2,'1/5/2011',2
union select 3, '1/1/2011',1

select
	t1.customer_no, isnull(t_prev.activity_date, t_curr.activity_date) as 'activity_from_date', t_curr.activity_date as 'activity_to_date'
from
	(
		select 
			t.customer_no, max(sequence_no) 'maxseq' 
		from 
			@t t
		group by
			t.customer_no) t1
	left join (
		select 
			t.customer_no, max(sequence_no) 'prevseq' 
		from 
			@t t
		where
			sequence_no <> (select max(sequence_no) from @t where customer_no = t.customer_no)
		group by
			t.customer_no) t2
		on	t1.customer_no = t2.customer_no

	left outer join @t t_curr
		on	t1.customer_no = t_curr.customer_no
			and t1.maxseq = t_curr.sequence_no 
	left outer join @t t_prev
		on	t2.customer_no = t_prev.customer_no
			and t2.prevseq = t_prev.sequence_no

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35073535
use the windowing functions perhaps.
;with cte as (select x.*
                    ,row_number() over (partition by customer_number order by sequence_number) as rn
                 from yourtable as x
             ) 
select a.customer_number,a.activity_date as from,B.activity_date as to,a.sequence_number
from cte as a
left join cte as b
on a.customer_number=b.customer_number
and a.rn=b.rn - 1
order by a.customer_number,a,sequence_number

Open in new window

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question