Group by Hour

Hello,
I have created a report which gets Clock In and Clock Out time for an
Employee and I would like to group this report by Hour so it would look
something like:
7:00-8:00
A
B
C
8:00-9:00
A
B
9:00-10:00
... AND so on.
So basically, I like to see who was here for each hour starting 7:00 am. My
query that I created is:
SELECT dbo.Employee.FirstName, dbo.Employee.LastName,
dbo.OrganizationUnit.Code AS Dept, dbo.OrganizationUnit.Description AS [Dept
Name],
dbo.EmployeeClocking.WhenCreated AS InClock,
EmployeeClocking_1.WhenCreated AS OutClock, dbo.JobClass.Code AS [Job Code],
dbo.JobClass.Description AS Title
FROM dbo.Employee INNER JOIN
dbo.EmployeeClocking ON dbo.Employee.ID = dbo.EmployeeClocking.EmployeeID INNER JOIN
dbo.OrganizationUnit ON
dbo.EmployeeClocking.OrganizationUnitID = dbo.OrganizationUnit.ID INNER JOIN
dbo.JobClass ON dbo.EmployeeClocking.JobClassID = dbo.JobClass.ID INNER JOIN
dbo.EmployeeClocking EmployeeClocking_1 ON
dbo.EmployeeClocking.OutClockingGuid = EmployeeClocking_1.Guid
WHERE (dbo.OrganizationUnit.Code = @.Department) AND
(dbo.EmployeeClocking.WhenCreated BETWEEN @.BeginDate AND @.EndDate)
And I need help to group it hourly and show employees who worked between
those hours. Thanks in advance for any assistance.kalindi,
Add a column to your dataset for the hour, and group by that field.
Here is a sample query.
select cast((cast(dateField as float) - cast(dateField as int)) * 24 as
int) as hour from temp4
Casting dateField as float gets you the julian date with time. Example:
right now it is 38973.4013944444.
Casting dateField as int gets you the julian date without time.
Example: right now it is 38973.
Subtracting the 2 gets you 0.4013944444. Multiply that result by 24 and
you get 9.6334666656. Just for fun, multiply 0.6334666656 by 60, and
you get 38. That means it is 9:38am.
Casting 9.6334666656 as int gets you 9.
Do whatever you need to do to get the group header to display the time
in the format that you mentioned (7:00-8:00).
Hope this helps!
-Josh
kalindi05 wrote:
> Hello,
> I have created a report which gets Clock In and Clock Out time for an
> Employee and I would like to group this report by Hour so it would look
> something like:
> 7:00-8:00
> A
> B
> C
> 8:00-9:00
> A
> B
> 9:00-10:00
> ... AND so on.
> So basically, I like to see who was here for each hour starting 7:00 am. My
> query that I created is:
> SELECT dbo.Employee.FirstName, dbo.Employee.LastName,
> dbo.OrganizationUnit.Code AS Dept, dbo.OrganizationUnit.Description AS [Dept
> Name],
> dbo.EmployeeClocking.WhenCreated AS InClock,
> EmployeeClocking_1.WhenCreated AS OutClock, dbo.JobClass.Code AS [Job Code],
> dbo.JobClass.Description AS Title
> FROM dbo.Employee INNER JOIN
> dbo.EmployeeClocking ON dbo.Employee.ID => dbo.EmployeeClocking.EmployeeID INNER JOIN
> dbo.OrganizationUnit ON
> dbo.EmployeeClocking.OrganizationUnitID = dbo.OrganizationUnit.ID INNER JOIN
> dbo.JobClass ON dbo.EmployeeClocking.JobClassID => dbo.JobClass.ID INNER JOIN
> dbo.EmployeeClocking EmployeeClocking_1 ON
> dbo.EmployeeClocking.OutClockingGuid = EmployeeClocking_1.Guid
> WHERE (dbo.OrganizationUnit.Code = @.Department) AND
> (dbo.EmployeeClocking.WhenCreated BETWEEN @.BeginDate AND @.EndDate)
> And I need help to group it hourly and show employees who worked between
> those hours. Thanks in advance for any assistance.|||Couldn't you just group by =Datepart(h,InClock) ? It seems like that
should work so long as your field is datetime datatype.
bell.joshua@.gmail.com wrote:
> kalindi,
> Add a column to your dataset for the hour, and group by that field.
> Here is a sample query.
> select cast((cast(dateField as float) - cast(dateField as int)) * 24 as
> int) as hour from temp4
> Casting dateField as float gets you the julian date with time. Example:
> right now it is 38973.4013944444.
> Casting dateField as int gets you the julian date without time.
> Example: right now it is 38973.
> Subtracting the 2 gets you 0.4013944444. Multiply that result by 24 and
> you get 9.6334666656. Just for fun, multiply 0.6334666656 by 60, and
> you get 38. That means it is 9:38am.
> Casting 9.6334666656 as int gets you 9.
> Do whatever you need to do to get the group header to display the time
> in the format that you mentioned (7:00-8:00).
> Hope this helps!
> -Josh
>
> kalindi05 wrote:
> > Hello,
> >
> > I have created a report which gets Clock In and Clock Out time for an
> > Employee and I would like to group this report by Hour so it would look
> > something like:
> >
> > 7:00-8:00
> > A
> > B
> > C
> > 8:00-9:00
> > A
> > B
> > 9:00-10:00
> > ... AND so on.
> >
> > So basically, I like to see who was here for each hour starting 7:00 am. My
> > query that I created is:
> > SELECT dbo.Employee.FirstName, dbo.Employee.LastName,
> > dbo.OrganizationUnit.Code AS Dept, dbo.OrganizationUnit.Description AS [Dept
> > Name],
> > dbo.EmployeeClocking.WhenCreated AS InClock,
> > EmployeeClocking_1.WhenCreated AS OutClock, dbo.JobClass.Code AS [Job Code],
> > dbo.JobClass.Description AS Title
> > FROM dbo.Employee INNER JOIN
> > dbo.EmployeeClocking ON dbo.Employee.ID => > dbo.EmployeeClocking.EmployeeID INNER JOIN
> > dbo.OrganizationUnit ON
> > dbo.EmployeeClocking.OrganizationUnitID = dbo.OrganizationUnit.ID INNER JOIN
> > dbo.JobClass ON dbo.EmployeeClocking.JobClassID => > dbo.JobClass.ID INNER JOIN
> > dbo.EmployeeClocking EmployeeClocking_1 ON
> > dbo.EmployeeClocking.OutClockingGuid = EmployeeClocking_1.Guid
> > WHERE (dbo.OrganizationUnit.Code = @.Department) AND
> > (dbo.EmployeeClocking.WhenCreated BETWEEN @.BeginDate AND @.EndDate)
> >
> > And I need help to group it hourly and show employees who worked between
> > those hours. Thanks in advance for any assistance.|||kalindi,
I always hate finding out that I took the long way. =)
The datepart function is a much better approach. I prefer to do stuff
like that in my SQL, and the SQL Server 2005 syntax for the hour
datepart looks like this:
select datepart(hh,dateField) as theHour from temp4
So, whether or not you add this calculation to the datasource or just
use it as the group by expression is up to you.
-Josh
toolman wrote:
> Couldn't you just group by =Datepart(h,InClock) ? It seems like that
> should work so long as your field is datetime datatype.
> bell.joshua@.gmail.com wrote:
> > kalindi,
> >
> > Add a column to your dataset for the hour, and group by that field.
> > Here is a sample query.
> >
> > select cast((cast(dateField as float) - cast(dateField as int)) * 24 as
> > int) as hour from temp4
> >
> > Casting dateField as float gets you the julian date with time. Example:
> > right now it is 38973.4013944444.
> >
> > Casting dateField as int gets you the julian date without time.
> > Example: right now it is 38973.
> >
> > Subtracting the 2 gets you 0.4013944444. Multiply that result by 24 and
> > you get 9.6334666656. Just for fun, multiply 0.6334666656 by 60, and
> > you get 38. That means it is 9:38am.
> >
> > Casting 9.6334666656 as int gets you 9.
> >
> > Do whatever you need to do to get the group header to display the time
> > in the format that you mentioned (7:00-8:00).
> >
> > Hope this helps!
> >
> > -Josh
> >
> >
> > kalindi05 wrote:
> > > Hello,
> > >
> > > I have created a report which gets Clock In and Clock Out time for an
> > > Employee and I would like to group this report by Hour so it would look
> > > something like:
> > >
> > > 7:00-8:00
> > > A
> > > B
> > > C
> > > 8:00-9:00
> > > A
> > > B
> > > 9:00-10:00
> > > ... AND so on.
> > >
> > > So basically, I like to see who was here for each hour starting 7:00 am. My
> > > query that I created is:
> > > SELECT dbo.Employee.FirstName, dbo.Employee.LastName,
> > > dbo.OrganizationUnit.Code AS Dept, dbo.OrganizationUnit.Description AS [Dept
> > > Name],
> > > dbo.EmployeeClocking.WhenCreated AS InClock,
> > > EmployeeClocking_1.WhenCreated AS OutClock, dbo.JobClass.Code AS [Job Code],
> > > dbo.JobClass.Description AS Title
> > > FROM dbo.Employee INNER JOIN
> > > dbo.EmployeeClocking ON dbo.Employee.ID => > > dbo.EmployeeClocking.EmployeeID INNER JOIN
> > > dbo.OrganizationUnit ON
> > > dbo.EmployeeClocking.OrganizationUnitID = dbo.OrganizationUnit.ID INNER JOIN
> > > dbo.JobClass ON dbo.EmployeeClocking.JobClassID => > > dbo.JobClass.ID INNER JOIN
> > > dbo.EmployeeClocking EmployeeClocking_1 ON
> > > dbo.EmployeeClocking.OutClockingGuid = EmployeeClocking_1.Guid
> > > WHERE (dbo.OrganizationUnit.Code = @.Department) AND
> > > (dbo.EmployeeClocking.WhenCreated BETWEEN @.BeginDate AND @.EndDate)
> > >
> > > And I need help to group it hourly and show employees who worked between
> > > those hours. Thanks in advance for any assistance.|||If you are not going over multiple days, the datepart is definitely the
easiest way to go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bell.joshua@.gmail.com> wrote in message
news:1158341470.346786.90070@.i3g2000cwc.googlegroups.com...
> kalindi,
> I always hate finding out that I took the long way. =)
> The datepart function is a much better approach. I prefer to do stuff
> like that in my SQL, and the SQL Server 2005 syntax for the hour
> datepart looks like this:
> select datepart(hh,dateField) as theHour from temp4
> So, whether or not you add this calculation to the datasource or just
> use it as the group by expression is up to you.
> -Josh
>
> toolman wrote:
>> Couldn't you just group by =Datepart(h,InClock) ? It seems like that
>> should work so long as your field is datetime datatype.
>> bell.joshua@.gmail.com wrote:
>> > kalindi,
>> >
>> > Add a column to your dataset for the hour, and group by that field.
>> > Here is a sample query.
>> >
>> > select cast((cast(dateField as float) - cast(dateField as int)) * 24 as
>> > int) as hour from temp4
>> >
>> > Casting dateField as float gets you the julian date with time. Example:
>> > right now it is 38973.4013944444.
>> >
>> > Casting dateField as int gets you the julian date without time.
>> > Example: right now it is 38973.
>> >
>> > Subtracting the 2 gets you 0.4013944444. Multiply that result by 24 and
>> > you get 9.6334666656. Just for fun, multiply 0.6334666656 by 60, and
>> > you get 38. That means it is 9:38am.
>> >
>> > Casting 9.6334666656 as int gets you 9.
>> >
>> > Do whatever you need to do to get the group header to display the time
>> > in the format that you mentioned (7:00-8:00).
>> >
>> > Hope this helps!
>> >
>> > -Josh
>> >
>> >
>> > kalindi05 wrote:
>> > > Hello,
>> > >
>> > > I have created a report which gets Clock In and Clock Out time for an
>> > > Employee and I would like to group this report by Hour so it would
>> > > look
>> > > something like:
>> > >
>> > > 7:00-8:00
>> > > A
>> > > B
>> > > C
>> > > 8:00-9:00
>> > > A
>> > > B
>> > > 9:00-10:00
>> > > ... AND so on.
>> > >
>> > > So basically, I like to see who was here for each hour starting 7:00
>> > > am. My
>> > > query that I created is:
>> > > SELECT dbo.Employee.FirstName, dbo.Employee.LastName,
>> > > dbo.OrganizationUnit.Code AS Dept, dbo.OrganizationUnit.Description
>> > > AS [Dept
>> > > Name],
>> > > dbo.EmployeeClocking.WhenCreated AS InClock,
>> > > EmployeeClocking_1.WhenCreated AS OutClock, dbo.JobClass.Code AS [Job
>> > > Code],
>> > > dbo.JobClass.Description AS Title
>> > > FROM dbo.Employee INNER JOIN
>> > > dbo.EmployeeClocking ON dbo.Employee.ID =>> > > dbo.EmployeeClocking.EmployeeID INNER JOIN
>> > > dbo.OrganizationUnit ON
>> > > dbo.EmployeeClocking.OrganizationUnitID = dbo.OrganizationUnit.ID
>> > > INNER JOIN
>> > > dbo.JobClass ON dbo.EmployeeClocking.JobClassID
>> > > =>> > > dbo.JobClass.ID INNER JOIN
>> > > dbo.EmployeeClocking EmployeeClocking_1 ON
>> > > dbo.EmployeeClocking.OutClockingGuid = EmployeeClocking_1.Guid
>> > > WHERE (dbo.OrganizationUnit.Code = @.Department) AND
>> > > (dbo.EmployeeClocking.WhenCreated BETWEEN @.BeginDate AND @.EndDate)
>> > >
>> > > And I need help to group it hourly and show employees who worked
>> > > between
>> > > those hours. Thanks in advance for any assistance.
>

Group based on hourly datetime

I have a report field that shows clock in and out for an employee. For example,

Date Classification Name
8/1/2006 6:30:26am IN A
8/1/2006 3:04:15PM OUT A
8/1/2006 7:30:26am IN B
8/1/2006 3:04:15PM OUT B

and so on...

I would like to have my report to show employees that were here from 7:00am -8:00am, 8:00am-9:00am, etc...

So my report would look like:
6:00AM-7:00AM
A
7:00AM-8:00AM
A
B

8:00AM-9:00AM
A
B
9:00AM-10:00AM
A
B

I'm not sure how to create time/hourly group and how i would achieve this. Please help!! Thanks,create a formula to extract hour
and then group by that formula

datepart('h',datetimefield)|||I created the formula:

datepart('h',{EmployeeClocking.WhenCreated})

and then grouped it on that forumula but it says:

The formula result must be a string!!

Thanks,|||That formula worked. But my results are not what I expected. For example, table:

Date Classification Name
8/1/2006 6:30:26am IN A
8/1/2006 3:04:15PM OUT A
8/1/2006 7:30:26am IN B
8/1/2006 3:04:15PM OUT B

gives me report:

6:00AM
A
7:00AM
B (I should get A because A worked until 3pm, but I get the ones that clocked in or out)
3PM
A
B|||You've not replied to my post on the other forum where you posted this question. :)

It was a simple request for what database you are running the report against, but I'll expand on why here.

If you use the clock in/out times to drive the report then you will only get hour intervals reported when someone actually clocks in/out within that hour, which is why you only got

6:00AM
A
7:00AM
B (I should get A because A worked until 3pm, but I get the ones that clocked in or out)
3PM
A
B

As you wrote '7:00am -8:00am, 8:00am-9:00am, etc...' I think you want output something like this instead:

6AM - 7AM
A
7AM - 8AM
A
B
9AM - 10AM
A
B
10AM - 11AM
A
B

...

3PM - 4PM
A
B

where you display all hours from the first clock in to the last clock out.

In which case I think you are going to need to generate a report with at least 24 rows (24 hours per day!) and then run a subreport for each hour interval to display those employees who clocked in before/during that hour and clocked out during/after it, on any given day.
There will be a slight complication if the clock in / out crosses the midnight boundary, but this can be overcome if necessary.

The 'problem' here is generating a report with at least 24 rows, preferrably exactly 24 rows, but not too many more than 24 rows. Which is why I asked what database you are running on.|||I'm running on SQL Server. I really need helplwith this coz I tried several things and it's not working. How would I do:

"In which case I think you are going to need to generate a report with at least 24 rows (24 hours per day!) and then run a subreport for each hour interval to display those employees who clocked in before/during that hour and clocked out during/after it, on any given day.
There will be a slight complication if the clock in / out crosses the midnight boundary, but this can be overcome if necessary.

The 'problem' here is generating a report with at least 24 rows, preferrably exactly 24 rows, but not too many more than 24 rows. Which is why I asked what database you are running on."

Thank you so much for your help.|||A report with 24 rows:

a) In Oracle, I might have written an 'Add Command' query in CR to get the first 24 rows from all_objects, using rownum. Can a similar thing be done in SQL Server?
b) create a specific table for this purpose with 24 rows of anything in it.
c) Use a data table that you know will always have at least 24 rows of data.

c's not the best one 'cos you'd need to suppress all records after the 24th one, and I think the subreport would still be run for all the extra records even though the detail is suppressed.

In the main report, create a formula to make a datetime out of the record number, something like
dateadd('h', recordnumber -1, today)

Add something from your main query to the supressed header section. (Anywhere really, it's just got to be used for the report to do anything.)
Create a subreport in the details and pass the formula to it as a parameter.
In the subreport, select the day's data from the employee clocking table with a formula like

// restrict to one day
date({EmployeeClocking.WhenCreated}) = date({?Pm-@.hour})
and
// clocked in before/during the hour
( {EmployeeClocking.Classification = 'IN'
and {EmployeeClocking.WhenCreated} < dateadd('h', 1, {?Pm-@.hour})
)
and
// clocked out during/after the hour
( {EmployeeClocking.Classification = 'OUT'
and {EmployeeClocking.WhenCreated} >= {?Pm-@.hour}
)

Then you should just need to fiddle with the format of the subreport and (back in the main report) the suppress blank sections / suppress blank subreport options etc. to get the format you want.

Note that the logic of your record selection might need tweeking. For instance, what if they clock in/out twice in a day or over a midnight boundary? Are your clock in/out records linked to pair them together? Can someone forget to clock in or out?