[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL - privot table creating from-to date columns

Posted on 2011-03-08
8
Medium Priority
?
262 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

650 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