[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

SQL - privot table creating from-to date columns

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
wppiexperts
Asked:
wppiexperts
2 Solutions
 
devlab2012Commented:
You need a SELF join.
0
 
SharathData EngineerCommented:
Can you provide some sample data with expected result?
0
 
derekkrommCommented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
derekkrommCommented:
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
 
LowfatspreadCommented:
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
 
wppiexpertsAuthor Commented:
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
 
derekkrommCommented:
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
 
LowfatspreadCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now