Reputation: 5830
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
Upvotes: 0
Views: 5730
Reputation: 2921
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.
Upvotes: 2