Select Statement Query

Ramco Ramco

In below Select statement if it is false then i want to write another Select statement which should check that if indate exists between StartDate And Endate in Invoice it should also be Break else ''

CASE WHEN ISNULL((SELECT TOP 1 DG.[OnBreak] FROM [SessionDeliveryParticipant] DG WHERE DG.[StudentID]=GP.[StudentID] AND DG.[SessionDeliveryCalendarID]=C.[SessionDeliveryCalendarID] AND DG.[Deleted]=0),0)=1 THEN 'Break' ELSE '' END AS [OnBreak]

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago

Accepted answer

CASE 
    WHEN ISNULL(
        (SELECT TOP 1 DG.[OnBreak] FROM [SessionDeliveryParticipant] DG WHERE DG.[StudentID]=GP.[StudentID] AND DG.[SessionDeliveryCalendarID]=C.[SessionDeliveryCalendarID] AND DG.[Deleted]=0),0)=1 
    OR 
        ([InDate] BETWEEN [StartDate] AND [EndDate])
    THEN 'Break' 
    ELSE '' 
END AS [OnBreak]

Naimish Makwana3y ago

Try this.

SELECT
    CASE 
        WHEN ISNULL((SELECT TOP 1 DG.[OnBreak] FROM [SessionDeliveryParticipant] DG WHERE DG.[StudentID]=GP.[StudentID] AND DG.[SessionDeliveryCalendarID]=C.[SessionDeliveryCalendarID] AND DG.[Deleted]=0),0)=1 THEN 'Break' 
        WHEN indate BETWEEN StartDate AND EndDate THEN 'Invoice'
        ELSE 'Another Select Statement'
    END AS [OnBreak]
FROM 
    [YourTable]

Thanks