Hi Alvin,

To use an input variable you have to add an @ symbol in front, like @UserCreatedDate, and not using the {Ticket}.[CreatedDate] as UserCreatedDate.

Hi Alvin,

You will need to help us out here... Is the CreatedDate a DateTime attribute? If so, in your query you will need to split it into the date part (the same for all tickets on the queue that day) and the time part (that you will use to determine the tickets ahead of yours). Alternatively you can change your data model to separate the two of them into separate attributes of Date and Time datatypes respectively.

In either case you will need to use a subquery.

Both queries below should return the number of tickets, on that date that were created before yours:

WITH MyTicket(CreatedDate, CreatedTime) AS (
    SELECT
        CAST(MyTicket.[CreatedDate] AS DATE),
        CAST(MyTicket.[CreatedDate] AS TIME)
    FROM
        {Ticket} MyTicket
    WHERE
        MyTicket.[TicketNumber] = @UserTicketNumber
    )

SELECT COUNT(1)
FROM {Ticket}
WHERE
    CAST({Ticket}.[CreatedDate] AS DATE) = MyTicket.CreatedDate
AND CAST({Ticket}.[CreatedDate] AS TIME) < MyTicket.CreatedTime

NOTE: This query uses a CTE to obtain the ticket you want to use as basis of comparison.

SELECT
    (SELECT COUNT(1)
     FROM
         {Ticket} Other
     WHERE
         CAST(Other.[CreatedDate] AS DATE) = CAST({Ticket}.[CreatedDate] AS DATE)
     AND CAST(Other.[CreatedDate] AS TIME) < CAST({Ticket}.[CreatedDate] AS TIME)
    )
FROM
    {Ticket}
WHERE
    {Ticket}.[TicketNumber] = @UserTicketNumber

NOTE: This query relies on a correlated subquery to obtain the same information, given the way it is designed, if you remote the outer WHERE clause, it would return, for each ticket, how many tickets are ahead of it in the queue. I'm expecting it to be less efficient than the previous query. 

Let me know if this helped!

Hi all 

I did some testing and problem is resolve using the following.

SELECT COUNT(1)
FROM {Ticket} WITH(NOLOCK)
WHERE ( {Ticket}.[TicketTypeID] = 92416 AND {Ticket}.[AppointmentDateTime] <= @UserCreatedDate
        AND {Ticket}.[TicketStatusID] = 92418 AND {Ticket}.[LocationID] = @UserLocationId) 
        OR ( {Ticket}.[TicketTypeID] = 92467 AND {Ticket}.[CreatedDate] <= @UserCreatedDate 
        AND {Ticket}.[TicketStatusID] = 92418 AND {Ticket}.[LocationID] = @UserLocationId)