?
Solved

SQL - privot table creating from-to date columns

Posted on 2011-03-08
8
Medium Priority
?
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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 1200 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 800 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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

752 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