Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author 
Topic 
donkerr
Starting Member
2 Posts 
Posted  20100527 : 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
Master Smack Fu Yak Hacker
2167 Posts 
Posted  20100527 : 09:57:17

can you explain the logic of how you get your result from your sample data?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/questionneedednotanswer.aspx How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx For ultra basic questions, follow these links. http://www.sqltutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp 


khtan
In (Som, Ni, Yak)
17689 Posts 
Posted  20100527 : 10:06:13

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


donkerr
Starting Member
2 Posts 
Posted  20100527 : 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
2937 Posts 
Posted  20100527 : 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  20150505 : 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] 


pawankkmr
Starting Member
4 Posts 





