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.
>