Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 complex sql statement(puzzle)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

donkerr
Starting Member

2 Posts

Posted - 05/27/2010 :  03:09:10  Show Profile  Reply with Quote
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

Posted - 05/27/2010 :  09:57:17  Show Profile  Reply with Quote
can you explain the logic of how you get your result from your sample data?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 05/27/2010 :  10:06:13  Show Profile  Reply with Quote

select [FROM] = min(day),
       [TO]   = max(day),
       leva,
       levb
from   onetable
group by leva, levb



KH
Time is always against us

Go to Top of Page

donkerr
Starting Member

2 Posts

Posted - 05/27/2010 :  10:32:47  Show Profile  Reply with Quote
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.vfd-1)
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.vfd-1)
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;
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 05/27/2010 :  10:50:44  Show Profile  Reply with Quote
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
Go to Top of Page

pawankkmr
Starting Member

4 Posts

Posted - 05/05/2015 :  07:45:29  Show Profile  Reply with Quote

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
Go to Top of Page

pawankkmr
Starting Member

4 Posts

Posted - 05/06/2015 :  02:54:35  Show Profile  Reply with Quote
Check out some interesting puzzles @

http://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/

Pawan Kumar Khowal



Thanks !
Pawan Kumar Khowal
MSBISkills.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000