Sql Query Issue

SELECT
    CASE
        WHEN (
            SELECT COUNT(*)
            FROM [View_StudentSubscription] V
            WHERE V.IsActive = 1
                AND V.StudentID = t.StudentId
                AND V.SubscriptionStatus = 'Active'
                AND V.DummySubscription = ''
        ) = 0 THEN
            (SELECT TOP 1 V.[LastSessionTentativeDate]
            FROM [View_StudentSubscription] V
            WHERE V.IsActive = 0
                AND V.StudentID = t.StudentId
                AND V.SubscriptionStatus = 'Active'
                AND V.DummySubscription = ''
            ORDER BY V.LastSessionTentativeDate DESC)
        ELSE
            (SELECT TOP 1 V.[LastSessionTentativeDate]
            FROM [View_StudentSubscription] V
            WHERE V.IsActive = 1
                AND V.StudentID = t.StudentId
                AND V.SubscriptionStatus = 'Active'
                AND V.DummySubscription = ''
            ORDER BY V.LastSessionTentativeDate DESC)
    END AS [LastSessionTentativeDate]
FROM YourTable t

Check this:

SELECT TOP 1 V.[LastSessionTentativeDate]
FROM [View_StudentSubscription] V
WHERE
    (CASE
        WHEN (SELECT COUNT(*) FROM [View_StudentSubscription] WHERE V.IsActive = 1 AND V.StudentID = t.StudentId AND V.SubscriptionStatus = 'Active' AND V.DummySubscription = '') = 0 THEN V.IsActive = 0
        ELSE V.IsActive = 1
    END)
    AND V.StudentID = t.StudentId
    AND V.SubscriptionStatus = 'Active'
    AND V.DummySubscription = ''
ORDER BY V.LastSessionTentativeDate DESC

Hello Ramco

You can get expected results using different ways like you can write a stored procedure or you can write conditions in a programming language like C#.

CREATE PROCEDURE [dbo].[PROC001]
AS        
BEGIN        
    SET NOCOUNT ON        
	DECLARE @Count int
    SELECT @Count = count(*) FROM [View_StudentSubscription] V WHERE V.IsActive = 1
	IF @Count = 0
		BEGIN
			SELECT @Count = count(*) FROM [View_StudentSubscription] V WHERE V.IsActive = 0
		END
	ELSE
		BEGIN
			SELECT @Count = count(*) FROM [View_StudentSubscription] V WHERE V.IsActive = 1
		END		
END

Thank you

Vishal

do you want do if else in the sql?
if count sql 1 have rows, do sql1.

else do sql2?