Query to Return rows that already exist

[Cobby1812]

Hi there,
I am struggling to write a query that does the following:-

Show all new cases entered today and check to see if the matter_desc (description) already exists in the database, if they do show the conflict, if not show n/a

Can anyone help.....

 

[bborissov]

Some data please.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Cobby1812]

OK,
I have a table called Matter. Within Matter I have a column called Matter_desc. Matter_desc holds a description of a name, for instance Bob the Builder Ltd.

What I want to do is say for all Matters Open Today show me if the Mastter_desc already exists in Table Matter. This is so that any conflicts (i.e Matter_desc Bob the Builder Ltd already exists)

I hope that makes sense.

 

[simian336]

select *
from matter
where date >= DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
and Matter_desc ='Bob the Builder Ltd'

Kinda assumes 'Bob the Builder Ltd' is spelled correctly

Simi

 

[Cobby1812]

Sorry I havent explained myself. I cant pass in the
Matter_desc ='Bob the Builder Ltd'

I just want to run a query that shows all Matters opened today and against those....if any exist already.


For instance

Matter Desc MATTER_ID DATE_OPENED
Bob the Builder Ltd 1234 08/09/11

o this was opened today, n ow I want to see of this Matter_desd exists anywhere else in the Matter Table:-

For Instance

Matter Desc MATTER_ID DATE_OPENED
Julie the Builder Ltd 1211 01/01/11
Jack Fireman Ltd 1266 02/07/11
Bob the Builder Ltd 1288 03/07/11
Bob the Builder Ltd 1234 08/09/11


So I want to return a match as Bob the Builder exists as ID 1288 and 12234 matched on the Matter_desc

 

[simian336]

This will give you the new ones...

select *
from matter where date >= DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

--This will give you any that already exist...

select * from matter
where MATTER_ID in (select MATTER_ID
from matterwhere date >= DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())))

Simi

 

[simian336]

typo

select * from matter
where MATTER_ID in (select MATTER_ID
from matter
where date >= DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())))

Simi

 

[Cobby1812]

Oh my...as easy as that eh!!!

many thanks

 

[markros]

declare @Today datetime
set @Today = dateadd(day,datediff(day,'19000101',CURRENT_TIMESTAMP),'19000101')

;with TodayData as (select Matter_Descrip
from Matter where [Date] >= @Today),

select M.* from Matter M
INNER JOIN TodayData ON M.Matter_Descrip = TodayData.Matter_Descrip
WHERE M.[Date] < @Today

This will show all the conflicts



PluralSight Learning Library

 

[bborissov]

Why not just add unique index that doesn't allow you to have duplicate data in that field?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.