question

Here's a small demo of a technique that might get you to what you need: USE tempdb ; DECLARE @DataTable TABLE ( event_date DATETIME , username VARCHAR(20) ) ; INSERT INTO @DataTable VALUES ( '20170101 10:30', 'Fred' ) , ( '20170101 10:45', 'Jim' ) , ( '20170102 09:00', 'Fred' ) , ( '20170102 12:45', 'Fred' ) , ( '20170102 23:45', 'Sheila' ) ; SELECT * FROM @DataTable AS dt ; WITH foo AS ( SELECT dt.event_date , dt.username , ROW_NUMBER() OVER ( PARTITION BY DATEADD(dd, 0, DATEDIFF(dd, 0, event_date)) , dt.username ORDER BY event_date ) AS RowNum FROM @DataTable AS dt ) SELECT * FROM foo WHERE foo.RowNum = 1 ; The key point here is the WITH statement that adds a ROW_NUMBER to your SELECT statement based on the grouping you require, and you can filter on that for the first row of each group.

so i modified my script and did the following: USE pwnt SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, evnt_dat)) 'Date' , evnt_descrp 'Event Type' , panel_descrp 'Panel' , q.LNAME 'Last Name' , q.FNAME 'First Name' , q.cardno 'Card Number' , bv.act_stat 'Employee Type' , DATEPART(mm, evnt_dat) 'Month' , DATEPART(wk, evnt_dat) 'Week' , DATEPART(dw, evnt_dat) 'Day' , bv.badge_country , bv.badge_department 'Department Name' , bv.badge_department_id 'Department ID' , bv.BADGE_EMERADDRESS2 'Region' , bv.BADGE_ADDRESS1 'Office' , bv.badge_location 'Panel Location' , bv.badge_city 'Location Name' , bv.BADGE_VEHICLE_LICENSE_PLATE 'Building Code' , bv.EMPLOYEE_NO 'Employee Number' FROM ( SELECT evnt_dat , evnt_descrp , panel_descrp , lname , fname , cardno , ROW_NUMBER() OVER ( PARTITION BY evnt_descrp, LEFT(panel_descrp, 10), lname, fname, DATEADD(dd, 0, DATEDIFF(dd, 0, evnt_dat)) ORDER BY evnt_dat ) rnum FROM ev_log ) q , badge_c bc , badge_v bv WHERE rnum = 1 AND evnt_descrp LIKE '%GRANT%' AND EVNT_DAT >= '9-1-2016' AND evnt_dat <= '10-28-2016' AND DATEPART(dw, evnt_dat) <> 1 AND DATEPART(dw, evnt_dat) <> 7 AND bv.badge_location <> 'unknown' AND LEFT(panel_descrp, 4) = LEFT(bv.badge_location, 4) AND RIGHT(badge_address1, 3) <> 'NSR' AND q.cardno = bc.cardno AND bv.id = bc.id ORDER BY evnt_dat ------------------------ I removed LEFT(panel_descrp, 10) ------------------------ and now my script is: USE pwnt SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, evnt_dat)) 'Date' , evnt_descrp 'Event Type' , panel_descrp 'Panel' , q.LNAME 'Last Name' , q.FNAME 'First Name' , q.cardno 'Card Number' , bv.act_stat 'Employee Type' , DATEPART(mm, evnt_dat) 'Month' , DATEPART(wk, evnt_dat) 'Week' , DATEPART(dw, evnt_dat) 'Day' , bv.badge_country , bv.badge_department 'Department Name' , bv.badge_department_id 'Department ID' , bv.BADGE_EMERADDRESS2 'Region' , bv.BADGE_ADDRESS1 'Office' , bv.badge_location 'Panel Location' , bv.badge_city 'Location Name' , bv.BADGE_VEHICLE_LICENSE_PLATE 'Building Code' , bv.EMPLOYEE_NO 'Employee Number' FROM ( SELECT evnt_dat , evnt_descrp , panel_descrp , lname , fname , cardno , ROW_NUMBER() OVER ( PARTITION BY evnt_descrp, lname, fname, DATEADD(dd, 0, DATEDIFF(dd, 0, evnt_dat)) ORDER BY evnt_dat ) rnum FROM ev_log ) q , badge_c bc , badge_v bv WHERE rnum = 1 AND evnt_descrp LIKE '%GRANT%' AND EVNT_DAT >= '9-1-2016' AND evnt_dat <= '10-28-2016' AND DATEPART(dw, evnt_dat) <> 1 AND DATEPART(dw, evnt_dat) <> 7 AND bv.badge_location <> 'unknown' AND LEFT(panel_descrp, 4) = LEFT(bv.badge_location, 4) AND RIGHT(badge_address1, 3) <> 'NSR' AND q.cardno = bc.cardno AND bv.id = bc.id ORDER BY evnt_dat

[code] USE pwnt SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, evnt_dat)) 'Date' , evnt_descrp 'Event Type' , panel_descrp 'Panel' , q.LNAME 'Last Name' , q.FNAME 'First Name' , q.cardno 'Card Number' , bv.act_stat 'Employee Type' , DATEPART(mm, evnt_dat) 'Month' , DATEPART(wk, evnt_dat) 'Week' , DATEPART(dw, evnt_dat) 'Day' , bv.badge_country , bv.badge_department 'Department Name' , bv.badge_department_id 'Department ID' , bv.BADGE_EMERADDRESS2 'Region' , bv.BADGE_ADDRESS1 'Office' , bv.badge_location 'Panel Location' , bv.badge_city 'Location Name' , bv.BADGE_VEHICLE_LICENSE_PLATE 'Building Code' , bv.EMPLOYEE_NO 'Employee Number' FROM ( SELECT evnt_dat , evnt_descrp , panel_descrp , lname , fname , cardno , ROW_NUMBER() OVER ( PARTITION BY evnt_descrp, LEFT(panel_descrp, 10), lname, fname, DATEADD(dd, 0, DATEDIFF(dd, 0, evnt_dat)) ORDER BY evnt_dat ) rnum FROM ev_log ) q , badge_c bc , badge_v bv WHERE rnum = 1 AND evnt_descrp LIKE '%GRANT%' AND EVNT_DAT >= '9-1-2016' AND evnt_dat <= '10-28-2016' AND DATEPART(dw, evnt_dat) <> 1 AND DATEPART(dw, evnt_dat) <> 7 AND bv.badge_location <> 'unknown' AND LEFT(panel_descrp, 4) = LEFT(bv.badge_location, 4) AND RIGHT(badge_address1, 3) <> 'NSR' AND q.cardno = bc.cardno AND bv.id = bc.id ORDER BY evnt_dat [/code] i removed: LEFT(panel_descrp, 10) from the ROW_NUMBER i now have: [code] USE pwnt SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, evnt_dat)) 'Date' , evnt_descrp 'Event Type' , panel_descrp 'Panel' , q.LNAME 'Last Name' , q.FNAME 'First Name' , q.cardno 'Card Number' , bv.act_stat 'Employee Type' , DATEPART(mm, evnt_dat) 'Month' , DATEPART(wk, evnt_dat) 'Week' , DATEPART(dw, evnt_dat) 'Day' , bv.badge_country , bv.badge_department 'Department Name' , bv.badge_department_id 'Department ID' , bv.BADGE_EMERADDRESS2 'Region' , bv.BADGE_ADDRESS1 'Office' , bv.badge_location 'Panel Location' , bv.badge_city 'Location Name' , bv.BADGE_VEHICLE_LICENSE_PLATE 'Building Code' , bv.EMPLOYEE_NO 'Employee Number' FROM ( SELECT evnt_dat , evnt_descrp , panel_descrp , lname , fname , cardno , ROW_NUMBER() OVER ( PARTITION BY evnt_descrp, lname, fname, DATEADD(dd, 0, DATEDIFF(dd, 0, evnt_dat)) ORDER BY evnt_dat ) rnum FROM ev_log ) q , badge_c bc , badge_v bv WHERE rnum = 1 AND evnt_descrp LIKE '%GRANT%' AND EVNT_DAT >= '9-1-2016' AND evnt_dat <= '10-28-2016' AND DATEPART(dw, evnt_dat) <> 1 AND DATEPART(dw, evnt_dat) <> 7 AND bv.badge_location <> 'unknown' AND LEFT(panel_descrp, 4) = LEFT(bv.badge_location, 4) AND RIGHT(badge_address1, 3) <> 'NSR' AND q.cardno = bc.cardno AND bv.id = bc.id ORDER BY evnt_dat [/code]