Grouping Question

HI there all

Here is my question for the day.

I am creating a report based of a table where i have employee information and hours that he has worked, I need to have the total of all hours, and also the total of Billable hours. there is a field called Billable hours.

Anyways, In my layout section, I get the correct total hours, due to the groupin g which is easy. I also added a new field to my Query for the field hours with an alias of billable hours. I need to put some sort of filter for this field. so that This will add up the hours that are billable, (bullable =true.). I tried to do this with the query and I had no success. I tried thought the report layout, on the properties of that field something along the lines of

=Fields!Billable_Hours.Value where Fields!billable.Value = 1,

but it does not like my where clause and it errors out so I can not run the report.

I I remove the where clause it does not error out, but it prints error on the field on the report.

Any ideas anyone ?

Thanks

Armela,

Try

Sum(iif(Fields!Billable.value>1,Fields!Billable_hours.value,nothing))

I think this should work.

Ham

|||That worked.
Thank you very much

Grouping by week starting on Sunday

I have a matrix report where I group employee hours by week. I have the
grouping working using the following expressions:
=DatePart(DateInterval.WeekOfYear,Fields!businessdate.Value)
=DatePart(DateInterval.Year, Fields!shift_date.Value)
It looks like the week of year starts on monday. Is there a way to change
this to start on Sunday? Or is there a better way to group by week starting
on Sunday? Any help is appreciated.I tried expression:
=DatePart("ww", Fields!shift_date.Value, FirstDayofWeek.Sunday)
and
=DatePart("ww", Fields!shift_date.Value, vbSunday)
neither of these work either.
"TBraun" wrote:
> I have a matrix report where I group employee hours by week. I have the
> grouping working using the following expressions:
> =DatePart(DateInterval.WeekOfYear,Fields!businessdate.Value)
> =DatePart(DateInterval.Year, Fields!shift_date.Value)
> It looks like the week of year starts on monday. Is there a way to change
> this to start on Sunday? Or is there a better way to group by week starting
> on Sunday? Any help is appreciated.

Group Filter doesn't work as expected

Employee (Group H1) AVG( of all Ratings)
Job # (Group H2) Rating
Rating is a "Calculated" field in the Dataset.
I put a filter on Job# (Group H2) to only include Ratings > 25%
Results:
Job#'s are filtered properly and every job < 25% does NOT show. HOWEVER,
the AVG (of all Ratings) ignores the Group 2 filter and includes every job
even those with a rating < 25%.
How can I get the "Avg Rating" to also only average those ratings according
to the group 2 filter I setup?
mythreadsHi,
Try setting up th filter this way. Write the expression return a true/false
value:
For expression use this (or similar): Job# > 25%
For Operator use: =For Value use: True
HTH!
Kind regards - Fred|||Hi,
Try setting up th filter this way. Write the expression return a true/false
value:
For expression use this (or similar): Job# > 25%
For Operator use: =For Value use: =True (*** CORRECTED FROM LAST POST***)
HTH!
Kind regards - Fred|||Thanks Fred for responding.
Your suggestion produced the same results. After further digging we solved
this original problem by putting the Filter on the "Dataset" and not the
report. I didn't know you could do this.
When I put the filter on "Group 2" header, "Group 2" detail was correct, but
"Group 1" header ignored the filtering in "Group 2" and used the raw Dataset
information.
When we put the filter on the dataset, it applied to both Group 1 and Group
2.
Thanks again.
"Fred Block" wrote:
> Hi,
> Try setting up th filter this way. Write the expression return a true/false
> value:
> For expression use this (or similar): Job# > 25%
> For Operator use: => For Value use: =True (*** CORRECTED FROM LAST POST***)
> HTH!
> Kind regards - Fred
>
>

GROUP BY Question

Given the following tables, I would like to figure out employee (employeeID)
leads the most assignments.
Doing it in two steps works (after a fashion) with a view (as listed below)
but I would like the Assignmentleader ID and the number of assignments that
person leads only for the leader that leads the most assignments and do it
in one query if possible.
CREATE VIEW MostNum AS
SELECT AssignmentLeader,COUNT(AssignmentID) AS NumOF
FROM Assignments
GROUP BY AssignmentLeader
CREATE TABLE Employees
(
EmployeeID INT NOT NULL PRIMARY KEY,
FirstName CHAR (20) NULL,
LastName CHAR (20) NULL,
HireDate DATETIME NULL,
TerminationDate DATETIME NULL,
EmployeeComments CHAR (300) NULL,
SupervisorsID INT NULL,
FOREIGN KEY (SupervisorsID) REFERENCES Employees (EmployeeID)
)
--Employees sample data
INSERT INTO Employees VALUES(101,'James','Hinkle','2006-01-01
00:00:00.000',NULL,'The Big Boss',NULL)
INSERT INTO Employees VALUES(102,'Dave','McCracken','2006-01-04
00:00:00.000',NULL,'VP',101)
INSERT INTO Employees VALUES(103,'Phil','Jensen','2006-01-05
00:00:00.000',NULL,'Accounting Supervisor',101)
INSERT INTO Employees VALUES(104,'Melinda','Carol','2006-01-05
00:00:00.000',NULL,'Finance Supervisor',101)
INSERT INTO Employees VALUES(105,'Daniel','Humphreys','2006-01-06
00:00:00.000',NULL,'MIS Support',101)
INSERT INTO Employees VALUES(106,'Lisa','Nugen','2006-01-06
00:00:00.000',NULL,'Shipping Supplier',101)
INSERT INTO Employees VALUES(107,'Henry','Wallace','2006-01-06
00:00:00.000',NULL,'Shipping PC',106)
INSERT INTO Employees VALUES(108,'Karen','Berrett','2006-01-07
00:00:00.000',NULL,'Secretary',103)
INSERT INTO Employees VALUES(109,'Ben','Pratt','2006-01-08
00:00:00.000','2006-01-09 00:00:00.000','Grunt',104)
INSERT INTO Employees VALUES(110,'Chump','Wegion','2006-01-08
00:00:00.000',NULL,'Warehouse PC',101)
CREATE TABLE Assignments
(
AssignmentID INT NOT NULL PRIMARY KEY,
AssignmentDescription CHAR(60) NULL ,
AssignmentLeader INT NULL
FOREIGN KEY (AssignmentLeader) REFERENCES Employees (EmployeeID)
)
--Assignments sample data
INSERT INTO Assignments VALUES(1,'Run operations of company',105)
INSERT INTO Assignments VALUES(2,'East Coast Marketing Plan',101)
INSERT INTO Assignments VALUES(3,'West Coast Marketing Plan',103)
INSERT INTO Assignments VALUES(4,'Build a new assembly line for Mark V',101)
INSERT INTO Assignments VALUES(5,'Research and develop a new pump
sprayer',102)
INSERT INTO Assignments VALUES(6,'Build new engineering facility',109)SELECT TOP 1 EmployeeID, COUNT(*) FROM Assignments GROUP BY EmployeeID ORDER
BY 2 DESC
"David Olsen" <david.olsen@.usu.edu> wrote in message
news:%23Dn2%23n7QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Given the following tables, I would like to figure out employee
> (employeeID) leads the most assignments.
>
> Doing it in two steps works (after a fashion) with a view (as listed
> below) but I would like the Assignmentleader ID and the number of
> assignments that person leads only for the leader that leads the most
> assignments and do it in one query if possible.
>
>
>
> CREATE VIEW MostNum AS
>
> SELECT AssignmentLeader,COUNT(AssignmentID) AS NumOF
> FROM Assignments
> GROUP BY AssignmentLeader
>
>
> CREATE TABLE Employees
> (
> EmployeeID INT NOT NULL PRIMARY KEY,
> FirstName CHAR (20) NULL,
> LastName CHAR (20) NULL,
> HireDate DATETIME NULL,
> TerminationDate DATETIME NULL,
> EmployeeComments CHAR (300) NULL,
> SupervisorsID INT NULL,
>
> FOREIGN KEY (SupervisorsID) REFERENCES Employees (EmployeeID)
>
> )
>
>
> --Employees sample data
> INSERT INTO Employees VALUES(101,'James','Hinkle','2006-01-01
> 00:00:00.000',NULL,'The Big Boss',NULL)
> INSERT INTO Employees VALUES(102,'Dave','McCracken','2006-01-04
> 00:00:00.000',NULL,'VP',101)
> INSERT INTO Employees VALUES(103,'Phil','Jensen','2006-01-05
> 00:00:00.000',NULL,'Accounting Supervisor',101)
> INSERT INTO Employees VALUES(104,'Melinda','Carol','2006-01-05
> 00:00:00.000',NULL,'Finance Supervisor',101)
> INSERT INTO Employees VALUES(105,'Daniel','Humphreys','2006-01-06
> 00:00:00.000',NULL,'MIS Support',101)
> INSERT INTO Employees VALUES(106,'Lisa','Nugen','2006-01-06
> 00:00:00.000',NULL,'Shipping Supplier',101)
> INSERT INTO Employees VALUES(107,'Henry','Wallace','2006-01-06
> 00:00:00.000',NULL,'Shipping PC',106)
> INSERT INTO Employees VALUES(108,'Karen','Berrett','2006-01-07
> 00:00:00.000',NULL,'Secretary',103)
> INSERT INTO Employees VALUES(109,'Ben','Pratt','2006-01-08
> 00:00:00.000','2006-01-09 00:00:00.000','Grunt',104)
> INSERT INTO Employees VALUES(110,'Chump','Wegion','2006-01-08
> 00:00:00.000',NULL,'Warehouse PC',101)
>
>
> CREATE TABLE Assignments
> (
> AssignmentID INT NOT NULL PRIMARY KEY,
> AssignmentDescription CHAR(60) NULL ,
> AssignmentLeader INT NULL
> FOREIGN KEY (AssignmentLeader) REFERENCES Employees (EmployeeID)
> )
>
> --Assignments sample data
> INSERT INTO Assignments VALUES(1,'Run operations of company',105)
> INSERT INTO Assignments VALUES(2,'East Coast Marketing Plan',101)
> INSERT INTO Assignments VALUES(3,'West Coast Marketing Plan',103)
> INSERT INTO Assignments VALUES(4,'Build a new assembly line for Mark
> V',101)
> INSERT INTO Assignments VALUES(5,'Research and develop a new pump
> sprayer',102)
> INSERT INTO Assignments VALUES(6,'Build new engineering facility',109)
>
>|||"David Olsen" <david.olsen@.usu.edu> wrote in message
news:%23Dn2%23n7QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Given the following tables, I would like to figure out employee
> (employeeID) leads the most assignments.
>
> Doing it in two steps works (after a fashion) with a view (as listed
> below) but I would like the Assignmentleader ID and the number of
> assignments that person leads only for the leader that leads the most
> assignments and do it in one query if possible.
>
>
>
> CREATE VIEW MostNum AS
>
> SELECT AssignmentLeader,COUNT(AssignmentID) AS NumOF
> FROM Assignments
> GROUP BY AssignmentLeader
>
>
> CREATE TABLE Employees
> (
> EmployeeID INT NOT NULL PRIMARY KEY,
> FirstName CHAR (20) NULL,
> LastName CHAR (20) NULL,
> HireDate DATETIME NULL,
> TerminationDate DATETIME NULL,
> EmployeeComments CHAR (300) NULL,
> SupervisorsID INT NULL,
>
> FOREIGN KEY (SupervisorsID) REFERENCES Employees (EmployeeID)
>
> )
>
>
> --Employees sample data
> INSERT INTO Employees VALUES(101,'James','Hinkle','2006-01-01
> 00:00:00.000',NULL,'The Big Boss',NULL)
> INSERT INTO Employees VALUES(102,'Dave','McCracken','2006-01-04
> 00:00:00.000',NULL,'VP',101)
> INSERT INTO Employees VALUES(103,'Phil','Jensen','2006-01-05
> 00:00:00.000',NULL,'Accounting Supervisor',101)
> INSERT INTO Employees VALUES(104,'Melinda','Carol','2006-01-05
> 00:00:00.000',NULL,'Finance Supervisor',101)
> INSERT INTO Employees VALUES(105,'Daniel','Humphreys','2006-01-06
> 00:00:00.000',NULL,'MIS Support',101)
> INSERT INTO Employees VALUES(106,'Lisa','Nugen','2006-01-06
> 00:00:00.000',NULL,'Shipping Supplier',101)
> INSERT INTO Employees VALUES(107,'Henry','Wallace','2006-01-06
> 00:00:00.000',NULL,'Shipping PC',106)
> INSERT INTO Employees VALUES(108,'Karen','Berrett','2006-01-07
> 00:00:00.000',NULL,'Secretary',103)
> INSERT INTO Employees VALUES(109,'Ben','Pratt','2006-01-08
> 00:00:00.000','2006-01-09 00:00:00.000','Grunt',104)
> INSERT INTO Employees VALUES(110,'Chump','Wegion','2006-01-08
> 00:00:00.000',NULL,'Warehouse PC',101)
>
>
> CREATE TABLE Assignments
> (
> AssignmentID INT NOT NULL PRIMARY KEY,
> AssignmentDescription CHAR(60) NULL ,
> AssignmentLeader INT NULL
> FOREIGN KEY (AssignmentLeader) REFERENCES Employees (EmployeeID)
> )
>
> --Assignments sample data
> INSERT INTO Assignments VALUES(1,'Run operations of company',105)
> INSERT INTO Assignments VALUES(2,'East Coast Marketing Plan',101)
> INSERT INTO Assignments VALUES(3,'West Coast Marketing Plan',103)
> INSERT INTO Assignments VALUES(4,'Build a new assembly line for Mark
> V',101)
> INSERT INTO Assignments VALUES(5,'Research and develop a new pump
> sprayer',102)
> INSERT INTO Assignments VALUES(6,'Build new engineering facility',109)
>
>
Transact-SQL proprietary version:
SELECT TOP 1 WITH TIES
assignmentleader, COUNT(*) AS numof
FROM Assignments
GROUP BY assignmentleader
ORDER BY COUNT(*) DESC ;
ANSI/ISO Standard SQL version:
SELECT assignmentleader, COUNT(*) AS numof
FROM Assignments
GROUP BY assignmentleader
HAVING COUNT(*)>= ALL
(SELECT COUNT(*)
FROM Assignments
GROUP BY assignmentleader);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23hw5rz7QGHA.1096@.TK2MSFTNGP11.phx.gbl...
> "David Olsen" <david.olsen@.usu.edu> wrote in message
> news:%23Dn2%23n7QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Transact-SQL proprietary version:
> SELECT TOP 1 WITH TIES
> assignmentleader, COUNT(*) AS numof
> FROM Assignments
> GROUP BY assignmentleader
> ORDER BY COUNT(*) DESC ;
>
> ANSI/ISO Standard SQL version:
> SELECT assignmentleader, COUNT(*) AS numof
> FROM Assignments
> GROUP BY assignmentleader
> HAVING COUNT(*)>= ALL
> (SELECT COUNT(*)
> FROM Assignments
> GROUP BY assignmentleader);
>
Thanks dude (and Aaron), you guys rock.

> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

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 by help!

I have the following SQL query:

SELECT DEPARTMENT.dnumber, COUNT(*)
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.dno = DEPARTMENT.dnumber
GROUP BY DEPARTMENT.dnumber

I had tested this query on the following table instances:

department
+-------+----+----+----+
| dname | dnumber | mgrssn | mgrstartdate |
+-------+----+----+----+
| R & D | 5 | 333445555 | 1988-05-22 |
| Administration | 4 | 987654321 | 1995-01-01 |
| Headquarters | 1 | 888665555 | 1981-06-19 |
| security | 2 | 123456789 | 1990-07-15 |
+-------+----+----+-----+

employee
+----+--+----+------+------+------------+--+----+------+--+
| fname | mint | lname | ssn | bdate | address | sex | salary | superssn | dno |
+----+--+----+------+------+------------+--+----+------+--+
| John | B | Smith | 123456789 | 1965-01-09 | 231 Fondren, Houston, TX | M | 30000 | 333445555 | 5 |
| Franklin | T | Wong | 333445555 | 1965-12-18 | 638 Voss, Houston, TX | M | 40000 | 888665555 | 5 |
| Alicia | J | Zelaya | 999887777 | 1968-07-19 | 3321 Castle, Spring, TX | F | 25000 | 987654321 | 4 |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire, TX | F | 43000 | 888665555 | 4 |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak, Humble, TX | M | 38000 | 333445555 | 5 |
| Joyce | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX | F | 25000 | 333445555 | 5 |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 960 Dalls, Houston, TX | M | 25000 | 987654321 | 4 |
| James | E | Borg | 888665555 | 1937-11-10 | 450 Stone, Houston, TX | M | 55000 | NULL | 1 |
| Larry | W | Clinton | 777225555 | 1972-03-15 | 100 Main, Houston, TX | M | 50000 | 888665555 | 1 |
+----+--+----+------+------+------------+--+----+------+--+
(Note: the field dno is the department number of department the employee woks for)

I got following result:
+----+-----+
| dnumber | COUNT(*) |
+----+-----+
| 1 | 2 |
| 4 | 3 |
| 5 | 4 |
+----+-----+

Although department 2 has no employee. It should show up on the result with the value of count(*) to be 0 according my professor. But it did not show up. I tested the above query in MySQL. Do I need to config something in MySQL in order to let department 2 to show up. Or this is the universal result I will get no matter whether I use MySQL, Oracal, or Microsoft Access. Thanks.You did an inner join to employee. Your query asked to count how many employees were in departments that had employees. You will have to do a query that will list all departments and then go find how many employees in each. Since this is for homework I will just provide a suggestion or suffer the wrath of others. A sum on a case statement would work for this or you could union the departments with no employees to the departments with employees.|||No, this is not for homework. This is a sample in lecture slide. I swell. The original problem statement is "For each department, find the department number and the number of employees of the department. It does not matter whether the department has employee or not".

Professor tried to show that by using SELECT, FROM, WHERE, and GROUP BY alone, the above problem could be solved. But I tested his query (see the first post), the result only contains the number of employees for the departments which have employees. The results does not contain the number of employees for the departments which do not have employees.

See if you can come out a SQL query for above problem. The SQL query should contain SELECT, FROM, WHERE, and GROUP BY only. Or you tell me this is impossible.|||of course it's possible

hint: use either a LEFT OUTER JOIN or RIGHT OUTER JOIN

(you can look up which one would be appropriate in your situation)|||But we can not use JOIN. The only SQL constructS professor used are SELECT, FROM, WHERE, and GROUP BY.|||if you cannot use JOIN, use a subquery

by the way, in the real world, "cannot use JOIN" is a bogus requirement

that's what's wrong with homework assigments, often they are unrealistic|||FYI the query you posted in #1 is a join|||OK, then show me your SQL query for this problem. THIS IS NOT A HOMEWORK QUESTION.|||Did you try to write a query by yourself using the hing Rudy gave you in post #4? If so, how does it look like and what did you get as a result?|||I did not try to write a query by myself using the hint Rudy gave me in post #4?|||to do it without joins is interesting challenge

you do realize that the query you posted in #1 DOES use a join, don't you?
select dnumber, sum(emps) as employees
from (
select dnumber, 0 as emps
from department
union all
select dno, count(*)
from employee
group by dno
)
group by dnumber look ma, no WHERE clause :) :)|||to do it without joins is interesting challenge

you do realize that the query you posted in #1 DOES use a join, don't you?
select dnumber, sum(emps) as employees
from (
select dnumber, 0 as emps
from department
union all
select dno, count(*)
from employee
group by dno
)
group by dnumber look ma, no WHERE clause :) :)

Yes, it is working. But can you remove UNION from query? With UNION, I can come out my own SQL query without any help.|||okay, then do it

good luck with your assignment|||Without neither UNION nor JOIN:select dnumber,
(select count(*) from employee where dno=dnumber)
from departmentEven without GROUP BY !|||I used Peter's coding to retrieve only those departments without employees.
select dnumber,
(select count(*) from employee where dno=dnumber) counter
from department
where counter = 0

The database returns an Oracle error. ORA-00904: "COUNTER": invalid identifier. However, when the WHERE keyword is followed by the full code of the made-up column, the SQL is executed okay.
select dnumber,
(select count(*) from employee where dno=dnumber)
from department
where (select count(*) from employee where dno=dnumber) = 0
This above is okay. Very curious too me. Any comments?|||Ikviens, what are you trying to do? departments without employees? use a LEFT OUTER JOIN with an IS NULL check|||The explanation is simple:
the order of interpretation of an SQL query is as follows:
FROM
WHERE
GROUP BY
HAVING
SELECT
[UNION]
[ORDER BY]

Hence, an alias defined in the SELECT subclause is not available in the other subclauses (except in the ORDER BY subclause).

You may however write what you want as follows:SELECT *
FROM (select dnumber,
(select count(*) from employee where dno=dnumber) counter
from department) T
WHERE counter = 0since now the alias is visible in the outer FROM subclause and hence available in the outer WHERE subclause.|||peter, that was an excellent explanation, excellent

:)|||A SQL statement is interpreted in an order and, in that order, SELECT clause is placed after FROM, WHERE, GROUP BY and HAVING clauses.
Two magic facts I have never been aware of. I feel my cognizance is widened so much. Thank you Peter!|||Without neither UNION nor JOIN:select dnumber,
(select count(*) from employee where dno=dnumber)
from departmentEven without GROUP BY !

Now, UNION was gone. It is good. But if you use GROUP BY, it will make the SQL query more readable. See if you can come out the SQL query by using SELECT, FROM, WHERE, and GROUP BY.|||no, wingstech, let's see you do it

please, let's see your solution, we've given you lots of options, and you don't seem to be satisfied with any of them, so let's see yours

please|||See if you can come out the SQL query by using SELECT, FROM, WHERE, and GROUP BY.select dnumber,
(select count(*) from employee where dno=dnumber)
from department
group by dnumber|||Let me see.|||no, wingstech, let's see you do it

please, let's see your solution, we've given you lots of options, and you don't seem to be satisfied with any of them, so let's see yours

please|||select dnumber,
(select count(*) from employee where dno=dnumber)
from department
group by dnumber

What a wonderful solution! See if you can remove the embedded SQL query from SELECT clause. All SQL queries I saw in professor's lecture slides do not have embedded SQL query in SELECT clause. I saw him using embedded SQL query in WHERE clause.|||What a wonderful solution! it's not that wonderful -- for one thing, the GROUP BY is superfluous!!|||it's not that wonderful -- for one thing, the GROUP BY is superfluous!!

But if professor request you to use it? e.g. in exam|||as i said earlier in this very same thread, that would be bogus

any professor that says you should use GROUP BY when you don't need to is ... well, let's just say i don't expect this to ever happen in the real world

please, why don't you just use the LEFT OUTER JOIN as i suggested earlier, and let's move on to some other problem|||to do it without joins is interesting challenge

you do realize that the query you posted in #1 DOES use a join, don't you?
select dnumber, sum(emps) as employees
from (
select dnumber, 0 as emps
from department
union all
select dno, count(*)
from employee
group by dno
)
group by dnumber look ma, no WHERE clause :) :)Rudy - did you not fall on the "a UNION is a join" side of the fence in a discussion on these hallowed boards some time ago? I imagine that would be the sort of analysis a dedicated student (such as young wingstech here) would lap up.

Assuming the professor did stipulate that a FROM clause should be used without a JOIN then it was a rather fiendish challenge (assuming CROSS joins are counted and the UNION(esque) operators are not).|||pootle, you Audacious Flump Cuddler you, your kind words do not go unappreciated

if you would kindly please get in touch with me via my contact page or email address thereon, i want to ask you a private question

a UNION is a "join" only in the most generous interpretations, which, i hasten to add, should always be tried -- it is helpful to realize that to a newbie, the distinctions between a cross join and a join and a union are difficult to master

as for this thread, i trust that my aggravation has managed to peek through my usual carefully neutral responses, so i shall just leave it at that

:)|||I think you are concious of the esteem in which I hold you rudeboy :)

It is laudable that wingstech has had the wherewithal to follow up a slide he* was presented with in a lecture. It is equally a shame that he* has not had the courage to attempt a solution of his* own. That is all that you and Peter are asking of him*. Go on wingstech - have a stab at it!

replace with she\ her if appropriate.|||outer queries shud be used for this purpose

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?