Help with query

• maxlezious

  SSCommitted

  Points: 1648

  More actions

  June 15, 2017 at 8:40 am

  #326686

  Hi All,

  I have a Background checks table and I am trying to find all the employee's (EMPLOY_REF column) who has never had Check_type called 'DBS' before

  Dummy data:

  
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EMPLOYEE_BACKGROUND](
    [EMPLOYEE_BACKGROUND_ID] [int] IDENTITY(1,1) NOT NULL,
    [EMPLOY_REF] [varchar](10) NOT NULL,
    [CHECK_DATE] [datetime] NOT NULL,
    [CHECK_TYPE] [varchar](10) NULL,
    [CHECK_RESULT] [varchar](10) NULL,
    [NEXT_CHECK_DATE] [datetime] NULL,
CONSTRAINT [PK__EMPLOYEE_BACKGRO__5BD950A8] PRIMARY KEY CLUSTERED
(
    [EMPLOYEE_BACKGROUND_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]

  GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[EMPLOYEE_BACKGROUND] ON

  INSERT [dbo].[EMPLOYEE_BACKGROUND] ([EMPLOYEE_BACKGROUND_ID], [EMPLOY_REF], [CHECK_DATE], [CHECK_TYPE], [CHECK_RESULT], [NEXT_CHECK_DATE]) VALUES (2, N'101', CAST(N'2014-09-19 00:00:00.000' AS DateTime), N'DBS', N'PASS', CAST(N'2017-09-19 00:00:00.000' AS DateTime))
INSERT [dbo].[EMPLOYEE_BACKGROUND] ([EMPLOYEE_BACKGROUND_ID], [EMPLOY_REF], [CHECK_DATE], [CHECK_TYPE], [CHECK_RESULT], [NEXT_CHECK_DATE]) VALUES (3, N'101', CAST(N'2014-12-09 00:00:00.000' AS DateTime), N'R2W', N'PASS', CAST(N'2017-12-09 00:00:00.000' AS DateTime))
INSERT [dbo].[EMPLOYEE_BACKGROUND] ([EMPLOYEE_BACKGROUND_ID], [EMPLOY_REF], [CHECK_DATE], [CHECK_TYPE], [CHECK_RESULT], [NEXT_CHECK_DATE]) VALUES (4, N'101', CAST(N'2010-02-02 00:00:00.000' AS DateTime), N'CRB', N'PASS', CAST(N'2013-02-20 00:00:00.000' AS DateTime))
INSERT [dbo].[EMPLOYEE_BACKGROUND] ([EMPLOYEE_BACKGROUND_ID], [EMPLOY_REF], [CHECK_DATE], [CHECK_TYPE], [CHECK_RESULT], [NEXT_CHECK_DATE]) VALUES (5, N'102', CAST(N'2013-03-27 00:00:00.000' AS DateTime), N'R2W', N'PASS', CAST(N'2016-03-27 00:00:00.000' AS DateTime))
INSERT [dbo].[EMPLOYEE_BACKGROUND] ([EMPLOYEE_BACKGROUND_ID], [EMPLOY_REF], [CHECK_DATE], [CHECK_TYPE], [CHECK_RESULT], [NEXT_CHECK_DATE]) VALUES (6, N'103', CAST(N'2014-08-14 00:00:00.000' AS DateTime), N'DBS', N'PASS', CAST(N'2017-08-14 00:00:00.000' AS DateTime))
INSERT [dbo].[EMPLOYEE_BACKGROUND] ([EMPLOYEE_BACKGROUND_ID], [EMPLOY_REF], [CHECK_DATE], [CHECK_TYPE], [CHECK_RESULT], [NEXT_CHECK_DATE]) VALUES (7, N'103', CAST(N'2013-03-28 00:00:00.000' AS DateTime), N'CRB', N'PASS', CAST(N'2016-03-28 00:00:00.000' AS DateTime))
INSERT [dbo].[EMPLOYEE_BACKGROUND] ([EMPLOYEE_BACKGROUND_ID], [EMPLOY_REF], [CHECK_DATE], [CHECK_TYPE], [CHECK_RESULT], [NEXT_CHECK_DATE]) VALUES (8, N'104', CAST(N'2012-10-25 00:00:00.000' AS DateTime), N'CRB', N'PASS', CAST(N'2015-10-25 00:00:00.000' AS DateTime))
INSERT [dbo].[EMPLOYEE_BACKGROUND] ([EMPLOYEE_BACKGROUND_ID], [EMPLOY_REF], [CHECK_DATE], [CHECK_TYPE], [CHECK_RESULT], [NEXT_CHECK_DATE]) VALUES (9, N'105', CAST(N'2011-08-30 00:00:00.000' AS DateTime), N'DBS', N'PASS', CAST(N'2014-08-30 00:00:00.000' AS DateTime))
INSERT [dbo].[EMPLOYEE_BACKGROUND] ([EMPLOYEE_BACKGROUND_ID], [EMPLOY_REF], [CHECK_DATE], [CHECK_TYPE], [CHECK_RESULT], [NEXT_CHECK_DATE]) VALUES (10, N'105', CAST(N'2013-04-16 00:00:00.000' AS DateTime), N'R2W', N'PASS', CAST(N'2016-04-16 00:00:00.000' AS DateTime))
INSERT [dbo].[EMPLOYEE_BACKGROUND] ([EMPLOYEE_BACKGROUND_ID], [EMPLOY_REF], [CHECK_DATE], [CHECK_TYPE], [CHECK_RESULT], [NEXT_CHECK_DATE]) VALUES (11, N'105', CAST(N'2013-09-07 00:00:00.000' AS DateTime), N'CRB', N'PASS', CAST(N'2016-09-07 00:00:00.000' AS DateTime))
INSERT [dbo].[EMPLOYEE_BACKGROUND] ([EMPLOYEE_BACKGROUND_ID], [EMPLOY_REF], [CHECK_DATE], [CHECK_TYPE], [CHECK_RESULT], [NEXT_CHECK_DATE]) VALUES (12, N'106', CAST(N'2010-07-30 00:00:00.000' AS DateTime), N'CRB', N'PASS', CAST(N'2013-07-30 00:00:00.000' AS DateTime))
SET IDENTITY_INSERT [dbo].[EMPLOYEE_BACKGROUND] OFF

  

  How would I write a query to show me employ_ref's 102,104 and 106 as they never had check_type = DBS in past. 

  Many Thanks

  
  

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  June 15, 2017 at 8:47 am

  #1946953

  Something like this should work:

  SELECT   EMPLOY_REF
FROM     EMPLOYEE_BACKGROUND
GROUP BY EMPLOY_REF
HAVING   MAX(CASE WHEN CHECK_TYPE='DBS' THEN 1 ELSE 0 END)=0;

  Cheers!

• maxlezious

  SSCommitted

  Points: 1648

  More actions

  June 15, 2017 at 8:52 am

  #1946959

  Jacob Wilkins - Thursday, June 15, 2017 8:47 AM

  Something like this should work:

  SELECT   EMPLOY_REF
FROM     EMPLOYEE_BACKGROUND
GROUP BY EMPLOY_REF
HAVING   MAX(CASE WHEN CHECK_TYPE='DBS' THEN 1 ELSE 0 END)=0;

  Cheers!

  That is exactly what I wanted - Thank you

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 15, 2017 at 9:29 am

  #1946968

  Here are a couple of options that are equivalent to each other, but different to the previous posted option.
  They read the table twice, but might be able to take better advantage of indexes and can show more information.
  
SELECT *
FROM [EMPLOYEE_BACKGROUND]
WHERE [EMPLOY_REF] NOT IN(SELECT i.[EMPLOY_REF]
        FROM [EMPLOYEE_BACKGROUND] i
        WHERE i.[CHECK_TYPE] = 'DBS'
        /*AND i.[EMPLOY_REF] IS NOT NULL*/);

  SELECT *
FROM [EMPLOYEE_BACKGROUND] eb
WHERE NOT EXISTS(SELECT 1
      FROM [EMPLOYEE_BACKGROUND] i
      WHERE i.[CHECK_TYPE] = 'DBS'
      AND i.[EMPLOY_REF] = eb.[EMPLOY_REF]);

  And here's an extra one that might be worth the shot if you only need the Employ_ref column
  
SELECT EMPLOY_REF
FROM  EMPLOYEE_BACKGROUND
EXCEPT
SELECT EMPLOY_REF
FROM  EMPLOYEE_BACKGROUND
WHERE  CHECK_TYPE = 'DBS';


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• maxlezious

  SSCommitted

  Points: 1648

  More actions

  June 15, 2017 at 9:41 am

  #1946972

  Luis Cazares - Thursday, June 15, 2017 9:29 AM

  Here are a couple of options that are equivalent to each other, but different to the previous posted option.
  They read the table twice, but might be able to take better advantage of indexes and can show more information.
  
  SELECT *
  FROM [EMPLOYEE_BACKGROUND]
  WHERE [EMPLOY_REF] NOT IN(SELECT i.[EMPLOY_REF]
          FROM [EMPLOYEE_BACKGROUND] i
          WHERE i.[CHECK_TYPE] = 'DBS'
          /*AND i.[EMPLOY_REF] IS NOT NULL*/);

  SELECT *
FROM [EMPLOYEE_BACKGROUND] eb
WHERE NOT EXISTS(SELECT 1
      FROM [EMPLOYEE_BACKGROUND] i
      WHERE i.[CHECK_TYPE] = 'DBS'
      AND i.[EMPLOY_REF] = eb.[EMPLOY_REF]);

  And here's an extra one that might be worth the shot if you only need the Employ_ref column
  
SELECT EMPLOY_REF
FROM  EMPLOYEE_BACKGROUND
EXCEPT
SELECT EMPLOY_REF
FROM  EMPLOYEE_BACKGROUND
WHERE  CHECK_TYPE = 'DBS';


  Thanks Luis, Before writing this post I did try NOT IN operator but it didn't work for me. However, after looking at your query I have realised the mistake I made in my query - many thanks for providing different options.