Cardholders Report

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 =

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...


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.

Something doesn't seem right here. The Where clause starts with a test for "rnum < 1" but this will never be true; the row_number() function starts at 1 by definition.

You are correct

I changed it to = but it still doesn't work

When I do run it

It returns
8/1/2016 bldg 7 smith jones blah blah
8/1/2016 bldg 8 smith jones blah blah

I just need it to return it once if the date is the same and the name is the same.