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.

 

[odins701]

 

[lbass]

No, that's what is creating the faux group on empID. Not sure what that error means. Try fixing the cases for the field names and see if that makes a difference. I won't be able to look at this again for an hour or two.

-LB

 

[lbass]

Sorry, you need to insert the distinctcount on the earlier formula {@onsitenow}.

-LB

 

[odins701]

So I tried this to join the events table to itself.

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

the expression returns 667 and the distinct count on that is 1.

 

[odins701]

distinct count on {@onsitenow} is returning 7. you can see the results for OnSiteNow in that column. I'm guessing for this to work, Cliff needs to be null for both records to return 6 as a distinct count?

 

[odins701]

I tried something different, tell me what you think.

I changed the OnSiteNow formula to...
if {READER.TIMEATT} = 2 then
{emp.id} else
Tonumber({@null})

created a BeenOnsite formula...
DistinctCount ({EMP.ID})

created countOnsite formula...
ToText(DistinctCount ({@OnSiteNow}), 0)

Changed TotalOnsiteNow formula to...
Tonumber({@BeenOnSite}) - Tonumber({@countOnsite})

The created a TotalOnsiteNow2 formula to display the result...
"Total On-Site Now: " + ToText({@TotalOnsiteNow}, 0)

Screenshot below...

 

[lbass]

No, that doesn't work, since he could have a more recent "1". Please try to fix the SQL expression by adjusting case (see my recent post). Then plug that into the {@onsitenow} formula, which will return the emplID for anyone who is still on site. Then insert a distinctcount on {@onsitenow}.

-LB

 

[odins701]

I'm sorry, but what do you mean by fixing the case?

 

[odins701]

btw, good point about a more recent 1!

 

[odins701]

Changed everything back...
{OnSiteNow} is returning the empid for each record, no nulls.

If fixing the case means self joining the table, i tried this..
(
Select sum(timeatt)
From events a, EVENTS c, reader b
Where a.machine = b.panelID and
a.devID = b.readerID and
a.empID = c.empID
)

 

[lbass]

Sorry, but I see that my initial logic was wrong with the remainder(). Let me see if I can work out what the logic should be and then I'll get back to you.

-LB

 

[odins701]

Sure thing and thank you for all your help!

 

[lbass]

A couple of fixes. First, we need to get the SQL expression working. Try double clicking on the relevant fields to see how they appear in the SQL expression. I'm wondering whether you need to use the full punctuation (double quotes)and also add in the "AccessControl"."dbo"."EVENTS" , so it would look like this -- note that the last line is what creates the faux ID, and it must appear like an alias connecting to the table used in the report, as shown:

//{%sumee}:
(
Select sum("TIMEATT")
From "AccessControl"."dbo"."EVENTS" a, "AccessControl"."dbo"."READER" b
Where a."MACHINE" = b."PANELID" and
a."DEVID" = b."READERID" and
a."EMPID" = "AccessControl"."dbo"."EVENTS"."EMPID"
)

In addition, please post your record selection formula as this also needs to be built into the SQL expression. The SQL expression should be returning all 1's except a 3 for Colby for your sample data above. The large number you are currently seeing is because SQL expressions are calculated at the database level and not just based on the records already returned by your selection criteria.

Then create {@onsitenow}--please note the formula change:
{@onsitenow}:
if remainder({%sumee},3)=1 then //note the 3
{Events.EMPID} else //changed this to EMPID
{@null}

This will return an employee ID for anyone still onsite. Then insert a distinctcount on {@onsitenow} at the report level and move the result to the report header.

-LB

 

[odins701]

{@ETIME} = currentdate

 

[odins701]

Still saying it can't be bound

 

[odins701]

Basically, my record selection is based on current date

 

[odins701]

How is this?

Select sum(timeatt)
From events a, EVENTS c, reader b
Where a.machine = b.panelID and
a.devID = b.readerID and
a.empID = c.empID and
cast(a.EVENT_TIME_UTC as date) = cast(getdate() as date)

 

[lbass]

So what is the content of {@etime}?

I'm stumped on compiling the SQL expression. I can compile expressions like this here. I'll see if I can think of another way to do this. This would be simple if you could show the results in the report footer. Or if you used a subreport in the report header. Want to try that?

-LB

 

[lbass]

Our posts crossed. I don't think your sql expression will work properly with connecting two aliases, but see what the results are once you get the date working. I'm not sure what conversions work with your database--if the cast() approach doesn't work, check the functions list.

-LB

 

[lbass]

Maybe just use the subreport approach. Let me know.

-LB