Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Dec 2009
    Thanked 0 Times in 0 Posts

    Question Creating a query to group by defined criteria - MS SQL 2005

    This query groups sick leave usage by day of week. So an administrator can see, at a glance, if they have a problem with people calling in sick on a particular day of the week.

    My problem is I have several administrators who will view this; I need to filter this by the location they are assigned to when they log in. I am filtering this on their 'employeeid' but when I do this, it adds that field to the group by clause, which ruins my roll-up on the report - instead of summarizing by day of week, it summarizes by employee, then by day of week.

    When I take that field out of the group by clause I get the following error:
    Msg 8121, Level 16, State 1, Line 2
    Column 'viwPREmployees.ID' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

    Any help would be greatly appreciated - I'm new to this - so any information I haven't provided that I need to.. Please just let me know.

    Thanks in advance!

    -- Sick Leave Usage - Certified - by DAC
    SELECT TOP (100) PERCENT dbo.HRDayofWeek(tblPREmpLeavePlanTrans.FromDate) AS DayofWeek, SUM(tblPREmpLeavePlanTrans.Units) AS Total
    FROM tblPREmpLeavePlanTrans INNER JOIN
    tblPREmpLeavePlans ON tblPREmpLeavePlanTrans.tblPREmpLeavePlansID = tblPREmpLeavePlans.ID INNER JOIN
    tblPRMasterLeave ON tblPREmpLeavePlans.tblPRMasterLeaveID = tblPRMasterLeave.ID INNER JOIN
    viwPREmployees ON tblPREmpLeavePlans.tblPREmployeesID = viwPREmployees.ID
    WHERE (tblPRMasterLeave.Description LIKE '%') AND (dbo.HRDayofWeek(tblPREmpLeavePlanTrans.FromDate) <> 'Sunday') AND
    (tblPREmpLeavePlanTrans.FromDate = tblPREmpLeavePlanTrans.ToDate) AND (tblPREmpLeavePlanTrans.TransType = 2)
    GROUP BY dbo.HRDayofWeek(tblPREmpLeavePlanTrans.FromDate), { fn DAYOFWEEK(tblPREmpLeavePlanTrans.FromDate) }, viwPREmployees.Classification,
    HAVING (dbo.HRDayofWeek(tblPREmpLeavePlanTrans.FromDate) <> 'Saturday') AND (viwPREmployees.Classification = 'Certified') AND
    (viwPREmployees.ID IN
    (SELECT tblPRPositions_2.tblPREmployeesID
    FROM tblPRPositions AS tblPRPositions_2 INNER JOIN
    tblAPReqLocations ON tblPRPositions_2.tblAPReqLocationsID = tblAPReqLocations.ID INNER JOIN
    tblAPUserParms ON tblAPReqLocations.ID = tblAPUserParms.tblReqLocationsID INNER JOIN
    tblPREmployees ON tblAPUserParms.tblPREmployeesID = tblPREmployees.ID
    WHERE (tblPREmployees.PortalUserName = '**UserName**')
    GROUP BY tblPRPositions_2.tblPREmployeesID
    HAVING (tblPRPositions_2.tblPREmployeesID <> 0)))
    ORDER BY { fn DAYOFWEEK(tblPREmpLeavePlanTrans.FromDate) }
    Last edited by ambrem33; 12-21-2009 at 03:55 AM. Reason: mark resolved.

  2. #2
    New to the CF scene
    Join Date
    Dec 2009
    Thanked 0 Times in 0 Posts
    I figured this one out - but thought I'd post in case it can help someone else in the future...

    I moved the code in the Having clause to the Where clause, and removed the viwPREmployeesID in the group by. Worked like a charm!


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts