17
Views
3
Comments
Solved
Getting the number of people ahead of you in the queue
Question

Hi Community,

I am looking to use the UserCreatedDate input to extract data from my ticket database.

The objective is to get the number of people infront of the user in a queue. 

I am using the user's Created date for referencing. Will count the number of tickets who are

1) <= User's created date and time

2) having the status of 92418

3) the ticket must be of the same date

But not sure how I can write the query in the Left Join. 


UPDATE:

Tried again but still not working out

Please help :')

Rank: #5818
Solution

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)
Rank: #510

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.

mvp_badge
MVP
Rank: #19

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!

Rank: #5818
Solution

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)