subt

[jphillips]

Hello,
building a time report.
table structure
---
userid varchar2(10)
punchtime date
typeofpunch varchar2(20)
rec_id number
---
Data for time is entered
as sysdate for punchtime and typeofpunch shows if they are (login, out_to_lunch, in_from_lunch, end_of_day). Can anyone help and show me how I can subtract these values so I can basically do this
(punchtime, out_to_lunch) - (punchtime, login) +
(punchtime, in_from_lunch) - (punchtime, login)
to get the total time for the day...
JP

 

[synapsevampire]

Group by userid.

In the Group header place something like:

whileprintingrecords;
numbervar TheSum:=0;

In the details use:
whileprintingrecords;
numbervar TheSum;
if typeofpunch in ["in_from_lunch", "end_of_day"] then
TheSum := TheSum+ datediff("s",{table.puinchtime},previous({table.puinchtime}))

In the Group Footer use:
whileprintingrecords;
numbervar TheSum

This will give you the time in seconds. To use a HH:MM:SS format, please refer to my FAQ in this forum on that topic, otherrwise state how it should appear.

faq767-3543

In general you'll get the best results by supplying version information, database information, example data and expected output.

-k