How to get Top 1 with below statement

SELECT TOP 1 [Attendance]
FROM SessionDeliveryParticipant sdp
WHERE EXISTS (
    SELECT 1
    FROM SessionDeliveryParticipant sub
    WHERE sub.[SessionDeliveryCalendarID] = sdp.[SessionDeliveryCalendarID]
    GROUP BY sub.[SessionDeliveryCalendarID]
    HAVING COUNT(*) = SUM(CASE WHEN sub.PresentInSession = 0 THEN 1 ELSE 0 END)
)

I used the EXISTS keyword to check for the existence of records in the subquery result. The subquery groups the records by SessionDeliveryCalendarID and calculates the count of records where PresentInSession is 0. Then, it compares this count with the total count for each SessionDeliveryCalendarID. If they match, it means all the participants for that calendar session have PresentInSession set to 0.

Try this

SELECT TOP 1 [Attendance]
FROM SessionDeliveryParticipant
WHERE [Attendance] = (
    SELECT CASE
        WHEN (
            SELECT COUNT(*)
            FROM SessionDeliveryParticipant AS sub
            WHERE sub.[SessionDeliveryCalendarID] = SessionDeliveryParticipant.[SessionDeliveryCalendarID]
            AND sub.PresentInSession = 0
        ) = COUNT(*)
        THEN 1
        ELSE 0
    END
    FROM SessionDeliveryParticipant
)

Hi Amit

  I have written below statement but on Case it is giving message - An expression of non boolean type specified in a context where condition is expected.

,(SELECT TOP 1 [Attendance]
FROM SessionDeliveryParticipant where CASE
    WHEN (
        SELECT COUNT(*)
        FROM SessionDeliveryParticipant AS sub
        WHERE sub.[SessionDeliveryCalendarID] = [SessionDeliveryCalendarID]
        AND sub.PresentInSession = 0
    ) = COUNT(*) THEN 1
    ELSE 0 End) [Attendance]

Thanks

SELECT TOP 1 *
FROM YourTable
WHERE CASE
        WHEN (
            SELECT COUNT(*)
            FROM SessionDeliveryParticipant AS sub
            WHERE sub.[SessionDeliveryCalendarID] = YourTable.[SessionDeliveryCalendarID]
            AND sub.PresentInSession = 0
        ) = COUNT(*) THEN 1
        ELSE 0
      END = 1;