Getting 1 Row for same ID , diff values

1

Hello ,

I have the following table :

ID Ord_ID ORD_DT UPD_DT CODE TYPE ORD_ID
12 429 5/24/17 11/11/19 IND ORDER 1
12 3a8 10/7/19 11/11/19 PRM CHANGE 2
78 8b7 6/12/18 11/14/19 FAM ORDER 1
78 28e 11/3/19 11/14/19 TFM CHANGE 2

I want to get the following based on the Type = Order and Change.
ID PREV_DT CURR_DT PREV_V CURR_V
12 5/24/17 11/11/19 IND PRM
78 6/12/18 11/14/19 FAM TFAM

Any help will be appreciated.

Thank you in advance,
Pam

2

If you want help with your homework, you should organize the data as consumable SQL that loads the data into a table (there are many examples in these forums that do that). Give a full statement of the transformation rules to get from the table to the desired output. Then show what you've tried so far and indicate what you are stuck on. You may find out that by explaining it you get to the answer or pretty close. Good luck.

3

hi

i tried to this ..
for My SQL Solution .. its assumes there will always be only 2 records per ID
hope this helps :slight_smile: :slight_smile:

please click arrow to the left for drop create data script 
drop table #temp 
go 

create table #temp
(
ID int ,
Ord_ID varchar(100) ,
ORD_DT date,
UPD_DT date,
CODE varchar(100),
TYPE varchar(100),
ORD_ID_1 int
)
go 

insert into #temp select 12,'429', '5/24/17','11/11/19','IND','ORDER', 1
insert into #temp select 12,'3a8', '10/7/19','11/11/19','PRM','CHANGE',2
insert into #temp select 78,'87', '6/12/18','11/14/19','FAM','ORDER', 1
insert into #temp select 78,'28e', '11/3/19','11/14/19','TFM','CHANGE', 2

select * from #temp 
go
please click arrow to the left for SQL 
SELECT id, 
       Min(ord_dt) AS prev_dt, 
       Max(ord_dt) AS curr_dt, 
       Min(code)   AS prev_v, 
       Max(code)   AS curr_v 
FROM   #temp 
GROUP  BY id 

go

image
image734×163 6.62 KB

image

4

hi pam

i am trying this again

this time MORE THAN 2 records per ID

please click arrow to the left for drop create sample data 
drop table #temp 
go 

create table #temp
(
ID int ,
Ord_ID varchar(100) ,
ORD_DT date,
UPD_DT date,
CODE varchar(100),
TYPE varchar(100),
ORD_ID_1 int
)
go 

insert into #temp select 12,'429', '5/24/17','11/11/19','IND','ORDER', 1
insert into #temp select 12,'3a8', '10/7/19','11/11/19','PRM','CHANGE',2
insert into #temp select 12,'5st', '11/7/19','11/11/19','OOP','NICE',3
go 

insert into #temp select 78,'87' , '6/12/17','11/14/19','FAM','ORDER', 1
insert into #temp select 78,'28e', '11/4/18','11/14/19','TFM','CHANGE', 2
insert into #temp select 78,'qwe', '09/3/19','11/14/19','ccc','NICE', 3
insert into #temp select 78,'yyy', '11/7/19','11/14/19','def','kolp', 4
go 

select * from #temp 
go

image
image1545×503 14.8 KB

please click arrow to the left for SQL ... 
; with cte as 
(
select ID,
       lag(ORD_DT) over(partition by id order by id, ord_Dt) as PREV_DT, ORD_DT as CURR_DT,
       lag(code) over(partition by id order by id, ord_Dt) as PREV_V, CODE as CURR_V
  from #temp 
)
select 'SQL Output',* from cte where PREV_DT is not null 
go

image
image1207×398 9.47 KB