Nested Case statement in sql query

Question

My Query

Declare     @From       DateTime='01 Feb 2013'
Declare     @To         DateTime='28 Feb 2013 23:59:59'

Select 
            Case
                  When
                  (
                        Select TOP 1 f1.UpdatedOn
                        From TicketTypeFollowUp As f1 with(nolock)
                        Where f1.UpdatedOn<T.UpdatedOn And f1.Ticket=T.Ticket
                        Order By f1.UpdatedOn Desc
                  )IS Null
                  Then Ticket.TicketRaisedOn

                  Else
                  (
                       Select TOP 1 f1.UpdatedOn
                       From TicketTypeFollowUp As f1 with(nolock)
                       Where f1.UpdatedOn<T.UpdatedOn And f1.Ticket=T.Ticket
                       Order By f1.UpdatedOn Desc
                  ) End [Start Date]

      From dbo.TicketTypeFollowUp T with(nolock)
      --Some Tables Omitted          
      Where CAST(TicketRaisedOn As Date)Between ''+Convert(VarChar(19), @From, 100)+''       And ''+Convert(VarChar(19), @To, 100)+''

If the Column [Start Date] has values Greater than @To Then @To Value should print Else [Start Date] Column

How can i write it in the same query ?

What i tried

       Case When(
             Case
                  When
                  (
                        Select TOP 1 f1.UpdatedOn
                        From TicketTypeFollowUp As f1 with(nolock)
                        Where f1.UpdatedOn<T.UpdatedOn And f1.Ticket=T.Ticket
                        Order By f1.UpdatedOn Desc
                  )IS Null
                  Then Ticket.TicketRaisedOn

                  Else
                  (
                       Select TOP 1 f1.UpdatedOn
                       From TicketTypeFollowUp As f1 with(nolock)
                       Where f1.UpdatedOn<T.UpdatedOn And f1.Ticket=T.Ticket
                       Order By f1.UpdatedOn Desc
                  ) End [Start Date]
              )> @To Then @To Else [Start Date] End,

DB : SQL SERVER 2008

Answer 1

It looks to me like you need this:

Declare     @From       DateTime='01 Feb 2013'
Declare     @To         DateTime='28 Feb 2013 23:59:59'

Select CASE WHEN ISNULL(StartDates.[Start Date], Ticket.TicketRaisedOn) > @To THEN @To ELSE ISNULL(StartDates.[Start Date], Ticket.TicketRaisedOn) END AS ResultValue
From dbo.TicketTypeFollowUp T with(nolock)
    --Some Tables Omitted 
    OUTER APPLY
    (
        Select TOP 1 f1.UpdatedOn AS [Start Date]
        From TicketTypeFollowUp As f1 with(nolock)
        Where f1.UpdatedOn<T.UpdatedOn And f1.Ticket=T.Ticket
        Order By f1.UpdatedOn Desc
    ) StartDates
Where CAST(TicketRaisedOn As Date)Between ''+Convert(VarChar(19), @From, 100)+''       And ''+Convert(VarChar(19), @To, 100)+''

The OUTER APPLY lets you specify a computed value per row, referencing values from the joined tables above, but after that it's rather like data coming from a JOIN. So you give your APPLY an alias as if it were a table and then access its value as many times as needed above.

As you can see, this dramatically simplifies your query-- APPLY is extremely handy to have in your tool belt.)))

EDIT Added another ISNULL per comment.