[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

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
0
Jimmy_inc
Asked:
Jimmy_inc
3 Solutions
 
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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
lwadwellCommented:
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
 
DBAduck - Ben MillerPrincipal 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
 
Scott PletcherSenior 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now