question

Morskain avatar image
Morskain asked

Cardholders Report

Hello All, So I am not a SQL GUY...but ive been given this query.. 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 what this currently does it brings in data for example date, event type, panel, last name first name card number etc... I needs it to just return 1 Transaction for the said person a day... example... keep the headers... keep the information... but if i appear 6 times on 10-1-2016 it should only return me once for 1-1-2016 etc. etc... this is basically being used as a check in... for the dates above...i just need one occurance.. you can use date, last name, firstname, and card number as your filter to return one occurance.
sql-server-2008sql-server-2008-r2sql-server-2012query
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nidheesh.r.pillai avatar image nidheesh.r.pillai commented ·
Welcome non-SQL guy! Good that you wish to learn some SQL stuff. Happy learning!
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Morskain avatar image Morskain commented ·
I kinda understand. But in my script above, how do I get it to return 1 occurance for a day vs 15 occurances for a day?
0 Likes 0 ·
Morskain avatar image
Morskain answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Morskain avatar image
Morskain answered
[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]
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.