Query Help

I have a table <ClearanceORDER>
The Fields of interest are <ClearDate> , <ClearanceType>, <ClearanceOrderID>
The Problem:
When an order is Canceled (CNCL) or Change of address (COA) there is an
entry in this table for it. The entry should look like this:
ClearDate ClearanceType ClearanceOrderID
09/14/2007 COA 212
canceled order
07/09/2007 CNCL 547
Change of address
A person can NOT be 'canceled' and 'change of address' in the same
clearanceorderid and the same cleardate!
IN an other way.
How can I list all the records that have COA and CNCL with the same
cleardate and clearanceorderid?
I dont know how else to explain this.
Scott BurkeScott,
To prevent this from happening in the future, you could add an unique
index/constraint on ClearDate and ClearanceOrderID, so there will be only
one entry per day and per OrderID, no matter what type that is.
To display what entries in the table break that rule, use this:
select ClearanceOrderID, ClearDate
from ClearanceOrder
group by ClearanceOrderID, ClearDate
having count(*) > 1
Andrei.
"Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
news:5497C818-B7C0-4A17-835E-2AA139899281@.microsoft.com...
>I have a table <ClearanceORDER>
> The Fields of interest are <ClearDate> , <ClearanceType>,
> <ClearanceOrderID>
> The Problem:
> When an order is Canceled (CNCL) or Change of address (COA) there is an
> entry in this table for it. The entry should look like this:
> ClearDate ClearanceType ClearanceOrderID
> 09/14/2007 COA 212
> canceled order
> 07/09/2007 CNCL 547
> Change of address
> A person can NOT be 'canceled' and 'change of address' in the same
> clearanceorderid and the same cleardate!
> IN an other way.
> How can I list all the records that have COA and CNCL with the same
> cleardate and clearanceorderid?
> I dont know how else to explain this.
> Scott Burke
>|||Thanks Andrei !
For some dam reason I thought I had to generate a list of COA and CNCL then
compare them.
Tunnal vission I guess. :)
Thanks again.
Just for giggles......
is is possibe to do the above?
Scott Burke
"Andrei" wrote:
> Scott,
> To prevent this from happening in the future, you could add an unique
> index/constraint on ClearDate and ClearanceOrderID, so there will be only
> one entry per day and per OrderID, no matter what type that is.
> To display what entries in the table break that rule, use this:
> select ClearanceOrderID, ClearDate
> from ClearanceOrder
> group by ClearanceOrderID, ClearDate
> having count(*) > 1
>
> Andrei.
> "Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
> news:5497C818-B7C0-4A17-835E-2AA139899281@.microsoft.com...
> >I have a table <ClearanceORDER>
> > The Fields of interest are <ClearDate> , <ClearanceType>,
> > <ClearanceOrderID>
> >
> > The Problem:
> > When an order is Canceled (CNCL) or Change of address (COA) there is an
> > entry in this table for it. The entry should look like this:
> > ClearDate ClearanceType ClearanceOrderID
> > 09/14/2007 COA 212
> > canceled order
> > 07/09/2007 CNCL 547
> > Change of address
> >
> > A person can NOT be 'canceled' and 'change of address' in the same
> > clearanceorderid and the same cleardate!
> >
> > IN an other way.
> > How can I list all the records that have COA and CNCL with the same
> > cleardate and clearanceorderid?
> >
> > I dont know how else to explain this.
> > Scott Burke
> >
>
>sql