ASP.NET - sql query

Asked By pradeep kumar on 05-Mar-12 05:54 AM

Earn up to 20 extra points for answering this tough question.

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