Query puzzle
-
I have columns named Control_Code and Sequence_Number in a table named Statement_Report.
For example:
Control_Code
1
2
3
W
B -- B
9
F
G
E -- E
The above results would be correct. However, if the ‘B’ was missing OR the ‘E’ was missing then that would be an error condition. There is not error if the there are no occurrences of both B and E.
I hope that this makes sense. If possible I would like to not only know if an error condition occurred but how many times it occurred.
Control_Code
1
2
3
W
B --B
9
F
G
E -- E with preceeding B - NO error
1
2
E -- E without preceeding B - error
3
4
B
A
S --Last row B without following E – error
In the above example an error occurred twice
I would appreciate any help that I can get. I tried using if and case but I apparently do have the skill set to get it right.
Howard
-
Is sequence number actually a sequential number, such as an identity field, so that the results can be sorted that way?
-
The results should be ordered by sequence_number.
-
Where is order of your codes defined?
From the example you displayed it's not possible to say if B is following E or E is following B.
_____________
Code for TallyGenerator -
I second Sergiy opinion you can't specify an order on stored data. Please post DDL and we may be able to help you.
* Noel -
I am sorry but I did not show the Sequence_Number column in my example. If you order by Sequence_Number the rows will be returned in the proper order.
What do you mean that you can't specify an order on stored data?
Example: select * from statement_report order by sequence_number
Howard
-
What do you mean that you can't specify an order on stored data?
Example: select * from statement_report order by sequence_number
That order, is on the retrieved data not on the the one that is stored
So you are saying that the table looks like this:
sequence_number Control_Code
1 1
2 2
3 3
4 W
5 B --B
6 9
7 F
8 G
9 E -- E with preceeding B - NO error
10 1
11 2
12 E -- E without preceeding B - error
13 3
14 4
15 B
16 A
17 S --Last row B without following E – error
?
* Noel -
So, show it.
_____________
Code for TallyGenerator -
Noel
Your example is what I should have included. I am sorry.
You are correct.
Howard
-
Hi Howard,
Maybe something like this?
--data
declare @t table (id int identity(1, 1), Control_Code char(1))
insert @t (Control_Code)
select '1'
union all select '2'
union all select '3'
union all select 'W'
union all select 'B' --B
union all select '9'
union all select 'F'
union all select 'G'
union all select 'E' -- E with preceeding B - NO error
union all select '1'
union all select '2'
union all select 'E' -- E without preceeding B - error
union all select '3'
union all select '4'
union all select 'B'
union all select 'A'
union all select 'S'
--calculation
select
id,
Control_Code,
case when (Control_Code = 'B' and bid is null) or (Control_Code = 'E' and eid is null) then 1
when Control_Code in ('B', 'E') then 0
end as IsError
from @t t left outer join ( --valid begin-end pairs
select b.id as bid, min(e.id) as eid
from @t b inner join @t e on b.id < e.id
where b.Control_Code = 'B' and e.Control_Code = 'E'
group by b.id
) p on t.id = bid or t.id = eid
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Ryan:
Thank you for your response. I got your query to execute without a problem however it does not give me what I need.
If possible I would like to not only know if an error condition occurred but how many times it occurred.
I will have to take some further action based on an error being found.
Thanks,
Howard
-
Then it appears we are still at the 'understanding your problem' part of the process. Defining a problem incompletely or ambiguously often puts people off helping, and is probably why you haven't got a useful response earlier.
What exactly do you need the result to be for your example? The slight modification below will give the total number of errors, but I'm still not sure if that's what you're asking for.
--data
declare @t table (id int identity(1, 1), Control_Code char(1))
insert @t (Control_Code)
select '1'
union all select '2'
union all select '3'
union all select 'W'
union all select 'B' --B
union all select '9'
union all select 'F'
union all select 'G'
union all select 'E' -- E with preceeding B - NO error
union all select '1'
union all select '2'
union all select 'E' -- E without preceeding B - error
union all select '3'
union all select '4'
union all select 'B'
union all select 'A'
union all select 'S'
--calculation
select
sum(case when (Control_Code = 'B' and bid is null)
or (Control_Code = 'E' and eid is null)
then 1 else 0 end) as NumberOfErrors
from @t t left outer join ( --valid begin-end pairs
select b.id as bid, min(e.id) as eid
from @t b inner join @t e on b.id < e.id
where b.Control_Code = 'B' and e.Control_Code = 'E'
group by b.id
) p on t.id = bid or t.id = eid
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
I do think that there are ambiguities in the results you expect but here is something to help you define better what you really need:
-- create table data (sequence_number int, Control_Code char(1) )
--
-- insert into data( sequence_number, Control_Code) values (1, '1')
-- insert into data( sequence_number, Control_Code) values (2, '2')
-- insert into data( sequence_number, Control_Code) values (3, '3')
-- 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 with preceeding B - NO error
-- 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, 'E') -- E without preceeding B - error
-- 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, 'B')
-- insert into data( sequence_number, Control_Code) values (16, 'A')
-- insert into data( sequence_number, Control_Code) values (17, 'S') --Last row B without following E – error
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
try running:
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')
so that you see the before, current and after values, then make sure you specify the correct conditions so that there is not overlap among them
Hope this helps,
* Noel -
Ryan:
You gave me exactly what I was trying to ask for. I am very sorry for the poor wording in my original question. You are exactly correct in that if I want any help I need to make it easier on the people that are willing to help me.
Thank you for the query and the advice.
Howard
-
Noel:
Thank you for taking my question a step further and giving me a more detailed result.
I am sorry for the poorly worded question and I thank you for your help.
Howard
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply