• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

How to limit rs results using GROUP BY

Hey all,

I'm on W2K, SQL Server 7...

I've got this view:

SELECT VD.intFormVacationDaysID LeaveRequestID, VD.intStaffId,
    VD.dtmCreateDate CreateDate, ST.strFullName StaffName,
    CAST(VDD.strRequestedTime AS Decimal(18, 2))
    AS decRequestedTime,
    CASE WHEN VD.intSuperVisorPIN IS NOT NULL
    THEN ST.intSuperStaffID ELSE NULL
    END AS ApprovalSupervisor,
    CASE WHEN VD.intManagerPIN IS NOT NULL
    THEN ST.intManagerStaffID ELSE NULL
    END AS ApprovalManager,
    CASE WHEN VD.intDirectorPIN IS NOT NULL
    THEN ST.intDirectorStaffID ELSE NULL
    END AS ApprovalDirector
FROM tblFormVacationDaysDetail VDD INNER JOIN
    tblFormVacationDays VD ON
    VDD.intFormVacationDaysID = VD.intFormVacationDaysID INNER
     JOIN
    tblStaff St ON VD.intStaffID = ST.intStaffID
WHERE ST.intStatus = 1 AND (VD.intEmpPIN IS NOT NULL) AND
    (VD.intSuperVisorPIN IS NOT NULL OR
    VD.intManagerPIN IS NOT NULL OR
    VD.intDirectorPIN IS NOT NULL OR
    VD.intProxyPIN IS NOT NULL) AND VD.intDenied <> 1


I'd like the results set to be grouped by VD.intFormVacationDaysID and limited to one record per ID...am I going to have to remove all references to VDD here for that to happen?

thx,
A
0
headbump
Asked:
headbump
1 Solution
 
rafranciscoCommented:
>> am I going to have to remove all references to VDD here for that to happen? <<

Not necessarily.  You can still perform group by and just get the minimum (earliest) strRequestedTime from the VDD table, like this:

SELECT VD.intFormVacationDaysID LeaveRequestID, VD.intStaffId,
    VD.dtmCreateDate CreateDate, ST.strFullName StaffName,
    MIN(CAST(VDD.strRequestedTime AS Decimal(18, 2)))
    AS decRequestedTime,
    CASE WHEN VD.intSuperVisorPIN IS NOT NULL
    THEN ST.intSuperStaffID ELSE NULL
    END AS ApprovalSupervisor,
    CASE WHEN VD.intManagerPIN IS NOT NULL
    THEN ST.intManagerStaffID ELSE NULL
    END AS ApprovalManager,
    CASE WHEN VD.intDirectorPIN IS NOT NULL
    THEN ST.intDirectorStaffID ELSE NULL
    END AS ApprovalDirector
FROM tblFormVacationDaysDetail VDD INNER JOIN
    tblFormVacationDays VD ON
    VDD.intFormVacationDaysID = VD.intFormVacationDaysID INNER
     JOIN
    tblStaff St ON VD.intStaffID = ST.intStaffID
WHERE ST.intStatus = 1 AND (VD.intEmpPIN IS NOT NULL) AND
    (VD.intSuperVisorPIN IS NOT NULL OR
    VD.intManagerPIN IS NOT NULL OR
    VD.intDirectorPIN IS NOT NULL OR
    VD.intProxyPIN IS NOT NULL) AND VD.intDenied <> 1
GROUP BY VD.intFormVacationDaysID, VD.intStaffId,
    VD.dtmCreateDate, ST.strFullName,
    CASE WHEN VD.intSuperVisorPIN IS NOT NULL
    THEN ST.intSuperStaffID ELSE NULL
    END,
    CASE WHEN VD.intManagerPIN IS NOT NULL
    THEN ST.intManagerStaffID ELSE NULL
    END,
    CASE WHEN VD.intDirectorPIN IS NOT NULL
    THEN ST.intDirectorStaffID ELSE NULL
    END
0
 
Scott PletcherSenior DBACommented:
Not necessary.  How specifically do you decide which of the multiple VD.intFormVacationDaysID will be listed?
0
 
headbumpAuthor Commented:
rafrancisco,

Thanks, this is exactly what I was lookking for...also I notice that it's cool to use SUM instead of MIN to
get total time amount.

thx,
A
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now