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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 complex sql statement(puzzle)

Author  Topic 

donkerr
Starting Member

2 Posts

Posted - 2010-05-27 : 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 - 2010-05-27 : 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/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)

17689 Posts

Posted - 2010-05-27 : 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]

Go to Top of Page

donkerr
Starting Member

2 Posts

Posted - 2010-05-27 : 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.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

2937 Posts

Posted - 2010-05-27 : 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
Go to Top of Page

pawankkmr
Starting Member

4 Posts

Posted - 2015-05-05 : 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]
Go to Top of Page

pawankkmr
Starting Member

4 Posts

Posted - 2015-05-06 : 02:54:35
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
   

- Advertisement -