Seems Basic but Difficult Sql Query 1

[Khanson82]

I have a table with
1.date
2.casenumber
3.errortype
4.pagenumber columns

I want to pull all records in the table .
but, if an error_type of '2' is present for a pagenumber/case_number on the same day that there are other error types for that page, I only want to pull the error type 2 log and ignore the rest for that page.
Any ideas?
Sounds like maybe an exising clause?
This one has stumped a few people here today, so I come to you all!

So if for example a casenumber on 2/24 for page 13, had errortypes of 1,2,3,4,5 logs. I would only want to see the log for the error type 2.

 

[simian336]

Are errortypes rows or comma seperated values. Please show some example data as it might appear in the table.

Simi

 

[Khanson82]

Rows..

Here is random example.
Date casenumber error page
2011-02-18 00:00:00.000 0000045404 1 12
2011-02-18 00:00:00.000 0000045404 1 12
2011-02-18 00:00:00.000 0000045404 1 12
2011-02-21 00:00:00.000 22820767 3 3
2011-02-21 00:00:00.000 0000062666 3 1
2011-02-21 00:00:00.000 22567224 3 10
2011-02-21 00:00:00.000 22860784 5 3

Here is an example of one case/one page, where the query will help.All page 13's, but I only want the first log which has error type of 2 since they are all on same day.
date casenumber error page
2011-02-24 00:00:00.000 22803666 2 13
2011-02-24 00:00:00.000 22803666 3 13
2011-02-24 00:00:00.000 22803666 4 13
2011-02-24 00:00:00.000 22803666 4 13
2011-02-24 00:00:00.000 22803666 7 13
2011-02-24 00:00:00.000 22803666 5 13
2011-02-24 00:00:00.000 22803666 6 13

 

[simian336]

is Casenumber unique?

Simi

 

[simian336]

Meaning could you have 2 different cases with the same case number?

Simi

 

[Khanson82]

Casenumber is unique.

 

[simian336]

Here is the test data for anybody that wants to look at it.

Simi


Create table list (
Date datetime,
casenumber varchar(30),
error int,
page int)

insert into list values ('2011-02-18 00:00:00.000','0000045404',1,12)
insert into list values ('2011-02-18 00:00:00.000','0000045404',1,12)
insert into list values ('2011-02-21 00:00:00.000','22820767',3,3)
insert into list values ('2011-02-21 00:00:00.000','0000062666',3,1)
insert into list values ('2011-02-21 00:00:00.000','22567224',3,10)
insert into list values ('2011-02-21 00:00:00.000','22860784',5,3)
insert into list values ('2011-02-24 00:00:00.000','22803666',2,13)
insert into list values ('2011-02-24 00:00:00.000','22803666',3,13)
insert into list values ('2011-02-24 00:00:00.000','22803666',4,13)
insert into list values ('2011-02-24 00:00:00.000','22803666',4,13)
insert into list values ('2011-02-24 00:00:00.000','22803666',7,13)
insert into list values ('2011-02-24 00:00:00.000','22803666',5,13)
insert into list values ('2011-02-24 00:00:00.000','22803666',6,13)
insert into list values ('2011-02-25 00:00:00.000','22803667',1,13)
insert into list values ('2011-02-25 00:00:00.000','22803667',2,13)
insert into list values ('2011-02-25 00:00:00.000','22803667',3,13)

 

[simian336]

This is close... But the union kills the duplicates.

select *
from list
where casenumber not in (
select casenumber
from list
where error=2
group by casenumber)
Union
select *
from list
where error=2

If you change it to Union all it gives you the other casenumbers with 2...


Seperately I think they give you what you want...

select *
from list
where casenumber not in (
select casenumber
from list
where error=2
group by casenumber);


select *
from list
where error=2

Simi

 

[Khanson82]

Only thing now is it only pulls the error_type 2 for my test case when on one page it doesnt have an errortype 2 so I would want it to show.

So with the data below, it should pull the page 3 error and the error 2 type errors for page 13 and 14 only.
So 3 total rows in the results
There will need to be some sort of date check to, since a casenumber can have all this same info for different days.And I only want it to look at one day at a time.
Date error page
2011-02-24 00:00:00.000 22803666 5 3
2011-02-24 00:00:00.000 22803666 2 13
2011-02-24 00:00:00.000 22803666 3 13
2011-02-24 00:00:00.000 22803666 4 13
2011-02-24 00:00:00.000 22803666 4 13
2011-02-24 00:00:00.000 22803666 7 13
2011-02-24 00:00:00.000 22803666 5 13
2011-02-24 00:00:00.000 22803666 6 13
2011-02-24 00:00:00.000 22803666 2 14
2011-02-24 00:00:00.000 22803666 3 14
2011-02-24 00:00:00.000 22803666 7 14
2011-02-24 00:00:00.000 22803666 3 14
2011-02-24 00:00:00.000 22803666 7 14

 

[simian336]

Yes, that complicates things..

insert into list values ('2011-02-24 00:00:00.000','22803668',5,3)
insert into list values ('2011-02-24 00:00:00.000','22803668',2,13)
insert into list values ('2011-02-24 00:00:00.000','22803668',3,13)
insert into list values ('2011-02-24 00:00:00.000','22803668',4,13)
insert into list values ('2011-02-24 00:00:00.000','22803668',4,13)
insert into list values ('2011-02-24 00:00:00.000','22803668',7,13)
insert into list values ('2011-02-24 00:00:00.000','22803668',5,13)
insert into list values ('2011-02-24 00:00:00.000','22803668',6,13)
insert into list values ('2011-02-24 00:00:00.000','22803668',2,14)
insert into list values ('2011-02-24 00:00:00.000','22803668',3,14)
insert into list values ('2011-02-24 00:00:00.000','22803668',7,14)
insert into list values ('2011-02-24 00:00:00.000','22803668',3,14)
insert into list values ('2011-02-24 00:00:00.000','22803668',7,14)

Simi

 

[Khanson82]

You might add this too so you can get the date check too..and another case too.

insert into list values ('2011-02-23 00:00:00.000','22803668',5,3)
insert into list values ('2011-02-23 00:00:00.000','22803668',2,3)
insert into list values ('2011-02-24 00:00:00.000','22803789',5,13)

 

[Khanson82]

So out of my insert and your insert above the desired results should be:

'2011-02-23 00:00:00.000' '22803668' 2 3
'2011-02-24 00:00:00.000' '22803789' 5 13
'2011-02-24 00:00:00.000' '22803668' 2 14
'2011-02-24 00:00:00.000' '22803668' 2 13
'2011-02-24 00:00:00.000' '22803668' 5 3

Thanks again.

 

[gmmastros]

Is this right?

Declare @list Table(
Date datetime,
casenumber  varchar(30),
error int,
page int)

insert into @list values ('2011-02-18 00:00:00.000','0000045404',1,12)
insert into @list values ('2011-02-18 00:00:00.000','0000045404',1,12)
insert into @list values ('2011-02-21 00:00:00.000','22820767',3,3)
insert into @list values ('2011-02-21 00:00:00.000','0000062666',3,1)
insert into @list values ('2011-02-21 00:00:00.000','22567224',3,10)
insert into @list values ('2011-02-21 00:00:00.000','22860784',5,3)
insert into @list values ('2011-02-24 00:00:00.000','22803666',2,13)
insert into @list values ('2011-02-24 00:00:00.000','22803666',3,13)
insert into @list values ('2011-02-24 00:00:00.000','22803666',4,13)
insert into @list values ('2011-02-24 00:00:00.000','22803666',4,13)
insert into @list values ('2011-02-24 00:00:00.000','22803666',7,13)
insert into @list values ('2011-02-24 00:00:00.000','22803666',5,13)
insert into @list values ('2011-02-24 00:00:00.000','22803666',6,13)
insert into @list values ('2011-02-25 00:00:00.000','22803667',1,13)
insert into @list values ('2011-02-25 00:00:00.000','22803667',2,13)
insert into @list values ('2011-02-25 00:00:00.000','22803667',3,13) 
insert into @list values ('2011-02-24 00:00:00.000','22803668',5,3)
insert into @list values ('2011-02-24 00:00:00.000','22803668',2,13)
insert into @list values ('2011-02-24 00:00:00.000','22803668',3,13)
insert into @list values ('2011-02-24 00:00:00.000','22803668',4,13)
insert into @list values ('2011-02-24 00:00:00.000','22803668',4,13)
insert into @list values ('2011-02-24 00:00:00.000','22803668',7,13)
insert into @list values ('2011-02-24 00:00:00.000','22803668',5,13)
insert into @list values ('2011-02-24 00:00:00.000','22803668',6,13)
insert into @list values ('2011-02-24 00:00:00.000','22803668',2,14)
insert into @list values ('2011-02-24 00:00:00.000','22803668',3,14)
insert into @list values ('2011-02-24 00:00:00.000','22803668',7,14)
insert into @list values ('2011-02-24 00:00:00.000','22803668',3,14)
insert into @list values ('2011-02-24 00:00:00.000','22803668',7,14) 
insert into @list values ('2011-02-23 00:00:00.000','22803668',5,3)
insert into @list values ('2011-02-23 00:00:00.000','22803668',2,3)
insert into @list values ('2011-02-24 00:00:00.000','22803789',5,13) 

Select  *
From    @List L
        Left Join (
           Select Date, CaseNumber, Page, Error
           From   @List 
           Where  error = 2
           ) As Error2
           On L.Date = Error2.Date
           And L.CaseNumber = Error2.CaseNumber
           And L.Page = Error2.Page
           And L.Error <> 2
Where	Error2.CaseNumber Is NULL
Order By L.Date, L.CaseNumber, L.Page, L.Error

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Khanson82]

At quick glance it looks like it works with my test..I will test on the real database and let you know.

Thanks again!