ASP.NET - sql query - Asked By pradeep kumar on 05-Mar-12 05:54 AM

when i selecting the minimum TotalLeave Balance from Different FacultyID

select Name1,FacultyID,Gender,AnnualLeaves,(Select  min(TotalLeaveBalance) from Leave_status  ) as tlb,LeaveType,SpecificLeave,min(RemainingLeave) as rl from Leave_status where Status='Approved' group by  Name1,FacultyID,Gender,AnnualLeaves,LeaveType,SpecificLeave

here in the above query TotalLeaveBalance will taken for min value from the table not a Different faculty ID ... How to dynamically taken the value from Min TotalLeave balance
[)ia6l0 iii replied to pradeep kumar on 05-Mar-12 10:06 AM
You do not need the Sub Query at all:

SELECT 
Name1,FacultyID,Gender,AnnualLeaves,
MIN(TotalLeaveBalance) ,
LeaveType,
SpecificLeave,
MIN(RemainingLeave)
FROM 
Leave_status 
WHERE
Status='Approved' 
GROUP BY
Name1,FacultyID,Gender,AnnualLeaves,LeaveType,SpecificLeave
Sandeep Mittal replied to pradeep kumar on 05-Mar-12 02:07 PM
Try this below query.

SELECT
  Name1, FacultyID, Gender, AnnualLeaves
  , (SELECT MIN(TotalLeaveBalance) FROM Leave_status B WHERE A.FacultyID = B.FacultyID) as tlb
  , LeaveType, SpecificLeave, MIN(RemainingLeave) as rl
FROM    Leave_status A where Status='Approved'
GROUP BY  Name1,FacultyID,Gender,AnnualLeaves,LeaveType,SpecificLeave
kalpana aparnathi replied to pradeep kumar on 05-Mar-12 02:23 PM
hi,

try this way:

Select Name1,FacultyID,gender,AnnualLeaves,min(TotalLeaveBalance),tlb,LeaveType,SpecificLeave,min(remainingLeave) from Leave_Status
where Status='Approved' group by  Name1,FacultyID,Gender,AnnualLeaves,LeaveType,SpecificLeave

regards,
pradeep kumar replied to Sandeep Mittal on 06-Mar-12 06:16 AM
thank you
Sandeep Mittal replied to pradeep kumar on 06-Mar-12 07:42 AM
you are welcome