asked on

Checking if a value exists in prior records SQL SERVER

Hi,
Please run:

CREATE TABLE [dbo].[EETest3](
       [EmployeeID] [int] NULL,
[Row] [int] NULL,
[Test] [nvarchar](15) NULL
)

INSERT INTO dbo.EETest3 VALUES
(200 ,1, 'Test1')
,(200 ,2,'Test2')
,(200 ,3,'TestZZ')
,(3000,1,'TestZZ')
,(400 ,1,'Test5')
,(400 ,2,'TestZZ')
,(500 ,1,'Test1')
,(500 ,2,'Test2');

SELECT * FROM EETEST3

I need to look test field and only if the last entry is 'TestZZ' it needs to look at previous entries in Test column, within the context of Employeeid and check if there are other tests < > TestZZ

so the results would look like this:

CREATE TABLE [dbo].[EETest3Results](
       [EmployeeID] [int] NULL,
[Row] [int] NULL,
[Test] [nvarchar](15) NULL,
       [Check] [nvarchar] (3) NULL
)

INSERT INTO dbo.EETest3Results VALUES
(200,1, 'Test1','yes')
,(200 ,2,'Test2','yes')
,(200 ,3,'TestZZ','yes')
,(3000,1,'TestZZ','no')
,(400 ,1,'Test5','yes')
,(400 ,2,'TestZZ','yes')
,(500 ,1,'Test1','no')
,(500 ,2,'Test2','no');

select * from EETest3Results

keyu

this is just rare idea syntax might be wrong...

for above checking you might use below query

select count(*) from EETest3Results where row>=1 and Test<>'TestZZ' group by employeeid

so your update query will be something like this

update EETest3Results set [Check]=case when (select count(*) from EETest3Results where row>=1 and Test<>'TestZZ' group by employeeid)>0 then 'yes' else 'no' end

Raj Shekhar

Hi,

For this type of working, We mostly do these step.

First store all the data into the temporary table and then apply a loop or cursor for iteratration for each record using temporatry table

Inside the iteration, you can apply any logic before inserting the data into final table.

Hope this logic, will resolve your problem.

Jimmy_inc

ASKER

time is not on my side so I dont really have time to work through these suggestions but thanks for your comments. I'm sure i could eventually get it working with .. along the lines of case when (select count(*) from EETest3Results where row>=1 and Test<>'TestZZ' group by employeeid)>0 then 'yes' else 'no' end...

SOLUTION