Calculate who is onsite now 1

[odins701]

Hi all! I need some help with my report. In the example screenshot below, there are 2 employees. Cliff has entered the site but has not left. Jesse has entered the site and has left. This means that 2 have been on site for the day, but 1 is on-site now. A distinct count of the employee id gives me the total of who has been on-site for the day. I need to calculate who is still on site. In the Entry/Exit column, 1 = entry and 2 = exit. Note: it is possible that someone may come and go multiple times throughout the day. If Jesse were to come and go twice in a day, he would have 4 records returned. I'm grouping by employee name. Could someone help me with this? I am using crystal reports 2020 patch 1 version 14.3.0.3569.

 

[lbass]

Does the result have to be at the beginning of the report?

What type of database?

-LB

 

[odins701]

Yes, it does. MS SQL.

 

[lbass]

First create a SQL expression {%sumee}. I'm not sure of the punctuation for your database, but it might look something like this. Please note also that whether the expression successfully compiles can be a function of the version of CR you are using. If it doesn't compile, remove the 'a.' in sum(a."entry/exit"). Substitute your table name for "table" and correct the field names to match your own. I used the cardholderID as I thought it would compile faster than the name field. SQL expression {%sumee}:

(
select sum(a."entry/exit")
from "table" a
where a."CardholderID"="table"."CardholderID"
)

Next, create a new formula by opening and saving a new formula without entering anything. Name it "null".

Then create a second formula:

{@onsitenow}:
if remainder({%sumee},2)=1 then
{table.CardholderID} else
{@null}

Then insert a distinctcount on {@onsitenow} and move the result to the report header.

-LB

 

[odins701]

Thank you, I will give that a try.

 

[odins701]

On the second formula, i get a compile error that a number is required and {@null} is highlighted

 

[lbass]

I guess you are using the badge number? You just need to wrap it in tonumber():

Tonumber({@null})

-LB

 

[odins701]

It is working great, thank you so much.

 

[odins701]

Actually it is not working correctly, and I need more help. In added the formulas to the report to see the results.
I've been out for medical reasons and just getting back to this. Screenshot below:

I've tried the formulas different ways, here is what i currently have...

Summee
(select sum(timeatt) subcount -- timeatt defines exit (2) and entry (1)
from reader
JOIN EVENTS a ON READER.READERID = a.DEVID AND READER.PANELID = a.MACHINE -- reader and panel must match
join EMP b on a.EMPID = b.ID)

OnSiteNow
if remainder({%%sumee},2)=1 then
{emp.id} else -- tried cardnum, same difference
Tonumber({@null})

TotalOnsiteNow
"Total On-Site Now: " + ToText(DistinctCount ({@OnSiteNow}), 0)

Tables:
Events -- these are the recordings of the entry and exits

Devid -- the reader fk to Reader
Machine -- the panel that the reader is connected to fk to Reader
Cardnum -- card number fk to Badge
Empid -- employees number fk to Emp
Eventid -- fk to event
DateTime -- when user entered or exited

Event -- defines the event types

evid -- pk
evdesc -- describes the event (access granted or denied)

Emp -- defines the employees

ID -- pk = empid
SSNO -- CardholderId
First name
last name

Badge -- defines the access cards

ID -- pk is the card number
EmpId -- fk to Emp (employee number)

Reader

PanelId -- pk the panel the reader is connected to
ReaderId -- pk the reader
Timeatt -- defines exit (2) and entry (1)
ReaderDesc

Of course there are more fields in these tables, but not really relevant to this.
Thanks again for your help!

 

[odins701]

Also consider this...

If the report is ran for today as in the example above, there are 9 events. However, id the report is ran over the last month, there will be many more events, thus the Summee will be greater. Just FYI.

 

[lbass]

This is very hard to follow. Is the sample data you show at the detail level? I need to see detail level data. Also please label columns by their actual field names and then show the actual SQL expression and actual formulas.

Your expected result would be 6, correct?

Why do you have missing cardholder ID data?

-LB

 

[odins701]

Not sure what you mean by detail level. The below screenshot shows the actual field names. Card holder name is a formula to concat the first, last, and middle names. The formulas above are the actual with the actual field names. 6 would be the correct count of people onsite now. Not sure why that user is missing a cardholder Id, but i can add it. Would rather use the badge number or emp id as they are numbers instead of varchar.

 

[odins701]

Would it help to attach the report and a database back-up? Although, i'm not sure if i should put the db out to the public. Maybe email it to you, if you need it?

 

[odins701]

This is the query the report makes to get the data...

SELECT "EMP"."LASTNAME"
,"EMP"."FIRSTNAME"
,"EMP"."MIDNAME"
,"UDFEMP"."USERFIELD01"
,"EVENTS"."CARDNUM"
,"EMP"."SSNO"
,"READER"."READERDESC"
,"EVENTS"."EVENT_TIME_UTC"
,"DEPT"."NAME"
,"EMP"."ID"
,"READER"."TIMEATT"
,(
SELECT sum(timeatt) subcount
FROM reader
JOIN EVENTS a ON READER.READERID = a.DEVID
AND READER.PANELID = a.MACHINE
JOIN EMP b ON a.EMPID = b.ID
)
FROM "AccessControl"."dbo"."DEPT" "DEPT"
INNER JOIN (
(
(
"AccessControl"."dbo"."EVENTS" "EVENTS" INNER JOIN "AccessControl"."dbo"."READER" "READER" ON ("EVENTS"."MACHINE" = "READER"."PANELID")
AND ("EVENTS"."DEVID" = "READER"."READERID")
) LEFT OUTER JOIN "AccessControl"."dbo"."EMP" "EMP" ON "EVENTS"."EMPID" = "EMP"."ID"
) LEFT OUTER JOIN "AccessControl"."dbo"."UDFEMP" "UDFEMP" ON "EVENTS"."EMPID" = "UDFEMP"."ID"
) ON "DEPT"."ID" = "UDFEMP"."DEPT

 

[odins701]

I've added the missing cardholder id...

 

[lbass]

Can you please show a screen shot of the linking expert that shows the links among the three tables? Also please post the actual SQL expression as you wrote it in the SQL expression expert.

-LB

 

[odins701]

 

[odins701]

Events was scrolled down, so i took another screenshot...

 

[lbass]

Change the SQL expression to the following exactly:

(
Select sum(timeatt)
From events a, reader b
Where a.machine = b.panelID and
a.devID = b.readerID and
a.empID = events.empID
)

Not sure whether the case matters, but it appears not. Then insert a distinctcount on the SQL Expression. Please report back with results. You do not need the employee table in the SQL expression.

-LB

 

[odins701]

Should i change the last line to a.empid = a.empid?