SQLTeam.com | Weblogs | Forums

Cardholders Report


#1

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.


#2

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.


#3

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.