Author 
Topic 

donkerr
Starting Member
2 Posts 
Posted  05/27/2010 : 03:09:10

The problem is simple, I got one table which looks like this
day leva levb  18 A B 19 A B 20 B A 21 A B 22 A B 23 A B
Now I need a query which results in
FROM TO leva levb  18 19 A B 20 20 B A 21 23 A B
a simple group by on the columns leva and levb will not work, does someone now a solution 

DonAtWork
Flowing Fount of Yak Knowledge
2167 Posts 

khtan
In (Som, Ni, Yak)
Singapore
17689 Posts 
Posted  05/27/2010 : 10:06:13

select [FROM] = min(day),
[TO] = max(day),
leva,
levb
from onetable
group by leva, levb
KH Time is always against us



donkerr
Starting Member
2 Posts 
Posted  05/27/2010 : 10:32:47

I already found a solution as I said before a group by over the columns leva and levb will not work because then I will not get the result as said in the table below.
lev_a = lv_alloc lev_b = lv_recon day = vfd
temp is the table day leva levb  18 A B 19 A B 20 B A 21 A B 22 A B 23 A B
CREATE GLOBAL TEMPORARY TABLE ar_own.temp_result ON COMMIT PRESERVE ROWS AS select ar_own.temp_seq.nextval, vfd, lv_alloc, lv_recon from ((select t1.vfd, t1.lv_alloc, t1.lv_recon from AR_OWN.temp t1, ar_own.temp t2 where t1.lv_alloc <> t2.lv_alloc and t1.lv_recon <> t2.lv_recon and t1.vfd = t2.vfd1) union all (select t2.vfd, t2.lv_alloc, t2.lv_recon from AR_OWN.temp t1, ar_own.temp t2 where t1.lv_alloc <> t2.lv_alloc and t1.lv_recon <> t2.lv_recon and t1.vfd = t2.vfd1) union all (select t1.vfd, t1.lv_alloc, t1.lv_recon from AR_OWN.temp t1 where t1.vfd = (select min(vfd) from ar_own.temp)) union all (select t1.vfd, t1.lv_alloc, t1.lv_recon from AR_OWN.temp t1 where t1.vfd = (select max(vfd) from ar_own.temp)) order by vfd)
select t1.vfd, t2.vfd, t1.lv_alloc, t2.lv_recon from ar_own.temp_result t1, AR_OWN.temp_result t2 where t1.lv_alloc = t2.lv_alloc and t1.lv_recon = t2.lv_recon and t1.nextval = t2.nextval 1; 


Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts 
Posted  05/27/2010 : 10:50:44

Much simpler
declare @tbl as table(day int,leva varchar(40),levb varchar(40))
insert into @tbl
select 18 ,'A' ,'B' union all
select 19, 'A', 'B' union all
select 20, 'B', 'A' union all
select 21, 'A', 'B' union all
select 22 ,'A' ,'B' union all
select 23, 'A', 'B'
select min(day)as [from],max(day) as [to],MIN(leva)as leva,MAX(levb)as levb from
(
select *,ROW_NUMBER()over(order by day)DENSE_RANK()over(partition by leva,levb order by day)as rid from @tbl
)t group by rid order by min(day)
PBUH 


pawankkmr
Starting Member
4 Posts 
Posted  05/05/2015 : 07:45:29

declare @tbl as table(day int,leva varchar(40),levb varchar(40)) insert into @tbl select 18 ,'A' ,'B' union all select 19, 'A', 'B' union all select 20, 'B', 'A' union all select 21, 'A', 'B' union all select 22 ,'A' ,'B' union all select 23, 'A', 'B'
SELECT MIN(day) [FROM] , MAX(day) [TO] , MIN(Leva) Leva , MAX(Levb) Levb FROM ( SELECT * , DAY  ROW_NUMBER() OVER (PARTITION BY leva,levb ORDER BY %%Physloc%%) rnk FROM @tbl ) A GROUP BY rnk ORDER BY [FROM]

Edited by  pawankkmr on 05/05/2015 07:51:36 


pawankkmr
Starting Member
4 Posts 


Topic 


