Solved

SQL - privot table creating from-to date columns

Posted on 2011-03-08
8
253 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
Comment Utility
You need a SELF join.
0
 
LVL 40

Expert Comment

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

Expert Comment

by:derekkromm
Comment Utility
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
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 300 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now