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
Jimmy_incAsked:
Who is Participating?
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
Better Yet: Try this one.  This one leverages the MAX function to get the max Row so that you know if it is > 1 or 1 and then you can get the Test value by joining the table to the max row to see if it is TestZZ.  Let me know if it does not make sense.

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');

CREATE TABLE #temp (
   EmployeeId int NOT NULL,
   [MaxRow] int NOT NULL,
   [Test] nvarchar(15) NOT NULL,
   [Check] nvarchar(3) NOT NULL
)

INSERT INTO #temp
SELECT EmployeeId, MAX([Row]), '', ''
FROM dbo.[EETest3]
GROUP BY EmployeeId

SELECT *
FROM #temp

UPDATE T
SET [Test] = E.[Test], 
	[Check] = CASE WHEN E.[Test] = 'TestZZ' AND T.[MaxRow] > 1 THEN 'yes' 
				   ELSE 'no' 
			  END
FROM #temp AS T
INNER JOIN dbo.[EETest3] E ON T.EmployeeId = E.EmployeeId AND T.MaxRow = E.[Row]

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

INSERT INTO dbo.EETest3Results 
SELECT T.EmployeeId, 
	E.[Row],
	E.[Test],
	T.[Check]
FROM #temp T
INNER JOIN dbo.[EETest3] E ON T.EmployeeId = E.EmployeeId

SELECT *
FROM dbo.EETest3Results

Open in new window

0
 
keyuCommented:
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
0
 
rajshekherCommented:
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Jimmy_incAuthor Commented:
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...
0
 
lwadwellConnect With a Mentor Commented:
something along the lines of:
select e.[EmployeeID], e.[Row], e.[Test],
       case when v.[Test] = 'TestZZ' and testZZ_count <> total_rows then 'YES' else 'NO' end as [Check]
from (select [EmployeeID], [Row], [Test],
             row_number() over(partition by [EmployeeID] order by [Row] desc) rn,
             sum(case when [Test] = 'TestZZ' then 1 else 0 end) over(partition by [EmployeeID]) testZZ_count,
             count(*) over(partition by [EmployeeID])  as total_rows
      from eetest3) v
join eetest3 e on v.[EmployeeID] = e.[EmployeeID]
where rn = 1

Open in new window

0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
SELECT
    EETEST3.*,
    CASE WHEN empids_testzz.LastNonTestZZ > 0 THEN N'Yes' ELSE N'No' END AS [Check]
FROM EETEST3
LEFT OUTER JOIN (
    SELECT
        [EmployeeID],
        MAX(CASE WHEN [Test] = 'TestZZ' THEN Row ELSE 0 END) AS LastTestZZ,
        MAX(CASE WHEN [Test] <> 'TestZZ' THEN Row ELSE 0 END) AS LastNonTestZZ
    FROM dbo.EETEST3
    GROUP BY
        [EmployeeID]
    -- limit results to only emps with LAST row having 'TestZZ'
    HAVING
        MAX(CASE WHEN [Test] = 'TestZZ' THEN Row ELSE 0 END) = MAX(Row)
) AS empids_testzz ON
    empids_testzz.[EmployeeID] = EETEST3.[EmployeeID]
--ORDER BY
    --EETest3.[EmployeeID], EETest3.[Row]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.