Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have to create time sheet table .
I have table with filed
Personal no (Text)
Personal Name (text)
EventTime (Timestamp)
what i already have
Personal No | Personal name | EventTime |
---|---|---|
111 | Hana | 8.00 AM 12/7/2015 |
111 | Hana | 4.00 PM 12/7/2015 |
112 | Mohammed | 8.00 AM 12/7/2015 |
112 | Mohammed | 10.00 Am 12/7/2015 |
112 | Mohammed | 1.00 PM 12/7/2014 |
112 | Mohammed | 5.00 PM 12/7/2015 |
what i want to do , how can i make time in , time out
Personal No | Personal name | Time In | Time Out | Total Hours |
---|---|---|---|---|
111 | Hana | 8.00 AM | 4.0 PM | 8 hours |
112 | Mohammed | 8.00 AM | 5.00 PM | 6 Hours ( He left from 10 to 1 , take first in and last out per day ) |
and the search will be in date from filed Timestamp
See attached example. Note, if you have an odd number of records per user, i.e. the number of Time in events is different than the number of Time out events, then this can't work.
Its not work , because its take min , max for all
find attachment
Its not work , because its take min , max for all
What do you mean 'for all'? You get a min Time In and a max Time Out per user just like the example table you posted.
If you wanted a Date dimension you should have specified that.
You can create a Date dimension from the EventTime in the preceding load: floor(EventTime) as Date. You can then add that Date field as dimension in your chart.
Hi Hana,
See the attchment
Thank you Its work for me ,
i have other question ,
how can i got Avg in and Avg out ?
I try Avg(Min(Time(NewEventTime,'hh:mm')))
its not work .