Hello All,
So I am not a SQL GUY...but ive been given this query..
[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]
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.