Query puzzle

Question

Query puzzle

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Howard,

No problem, we(at least I) pretty much come here for two things: to help and to have fun solving the problems people post

Cheers,

* Noel

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

Well that, and in my case, to get answers as well!

I wasn't born stupid - I had to study.

noeld SSC Guru Points: 96590 More actions · Answer 3

Sure I do too!!!

I have learnt a lot on this comunity

* Noel

RyanRandall SSChampion Points: 13623 More actions · Answer 4

Thanks Howard. Glad I could help

It's not such an easy skill to learn to define a problem well, but it looks like you're well on the way to learning because you clearly recognise the need. Good luck with future problems

Noel, Farrell - Yes, helping, learning and having fun. Luckily for me, the first two are often by-products of the last one... and it's hard to resist the fun of a 'query puzzle'!

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 5

Noel:

I believe that there is one problem with the query. In the example below the error with the first entry being an 'E' without a preceeding 'B' is not caught.

Also, you return a column 'EwithNoFollowingB' but this situation is okay.

--DROP table data

create table data (sequence_number int, Control_Code char(1) )

--truncate table data

insert into data( sequence_number, Control_Code) values (1, 'E') --E with no proceeding B - Error

insert into data( sequence_number, Control_Code) values (2, '2')

insert into data( sequence_number, Control_Code) values (3, 'X')

insert into data( sequence_number, Control_Code) values (4, 'W')

insert into data( sequence_number, Control_Code) values (5, 'B') --B

insert into data( sequence_number, Control_Code) values (6, '9')

insert into data( sequence_number, Control_Code) values (7, 'F')

insert into data( sequence_number, Control_Code) values (8, 'G')

insert into data( sequence_number, Control_Code) values (9, 'E') --E

insert into data( sequence_number, Control_Code) values (10, '1')

insert into data( sequence_number, Control_Code) values (11, '2')

insert into data( sequence_number, Control_Code) values (12, 'Y')

insert into data( sequence_number, Control_Code) values (13, '3')

insert into data( sequence_number, Control_Code) values (14, '4')

insert into data( sequence_number, Control_Code) values (15, 'X')

insert into data( sequence_number, Control_Code) values (16, 'B') --B

insert into data( sequence_number, Control_Code) values (17, 'E') --E

select

sum( case when Control_code ='B' and NextControl_Code <> 'E' then 1 else 0 end) as BwithNoFollowingE

, sum( case when Control_Code= 'E' and NextControl_Code <> 'B'and PrevControl_code <> 'B' then 1 else 0 end) as EwithNoFollowingB

, sum( case when Control_code + NextControl_Code = 'BB' then 1 else 0 end) as TwoConsecutivesB

, sum( case when Control_code + NextControl_Code = 'EE' then 1 else 0 end) as TwoConsecutivesE

from

(

select d1.sequence_number

, coalesce ((select top 1 d2.control_code

from data d2

where d2.sequence_number < d1.sequence_number

and d2.control_code in ('B','E')

order by d2.sequence_number desc ), '') as PrevControl_Code

, d1.Control_Code

, coalesce((select top 1 d2.control_code

from data d2

where d2.sequence_number > d1.sequence_number

and d2.control_code in ('B','E')

order by d2.sequence_number ),'') as NextControl_Code

from data d1

where d1.control_code in ('B','E')

) Q

I will understand if you do not want to put anymore time into this problem.

I was just trying to test for all situations.

Howard

noeld SSC Guru Points: 96590 More actions · Answer 6

Howard,

Sorry I couldn't get back to you sooner.

try:

select

sum( case when Control_code ='B' and NextControl_Code <> 'E' then 1 else 0 end) as BwithNoFollowingE

, sum( case when (Control_Code= 'E' and NextControl_Code <> 'B'and PrevControl_code <> 'B' )

or

(PrevControl_code <> 'B' and Control_Code= 'E' ) then 1 else 0 end) as EwithNoFollowingB

, sum( case when Control_code + NextControl_Code = 'BB' then 1 else 0 end) as TwoConsecutivesB

, sum( case when Control_code + NextControl_Code = 'EE' then 1 else 0 end) as TwoConsecutivesE

from

(

select d1.sequence_number

, coalesce ((select top 1 d2.control_code

from data d2

where d2.sequence_number < d1.sequence_number

and d2.control_code in ('B','E')

order by d2.sequence_number desc ), '') as PrevControl_Code

, d1.Control_Code

, coalesce((select top 1 d2.control_code

from data d2

where d2.sequence_number > d1.sequence_number

and d2.control_code in ('B','E')

order by d2.sequence_number ),'') as NextControl_Code

from data d1

where d1.control_code in ('B','E')

) Q

The reason I created the Prev, Current and Next Items was just to make it easier for you to find the cases that I could have missed ( as I obviously did )

I hope this helps you grasp the essence of the query you need.

You don't need to be sorry for asking questions this forum is just for that purpose

Cheers,

* Noel

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 7

Noel:

I have tested all variations that I can think of and your query appears to catch all situations.

I really do appreciate the help on this one. I would have never figured it out on my own.

Thank you and I just hope that somehow I can return the favor.

Howard