Sort Data based on user selection

Gulam Mohammad 1 Reputation point
2022-03-13T22:03:01.47+00:00

Dears,

I have 2 tables called Employees and Tickets

Employee Data
182714-empdata.png

Ticket Data
182705-ticketdata.png

When the user load the tickets I have to give a suggested employee who could be assigned this ticket based on some conditions

Conditions:

Nearest Location(The Employee who is near to the ticket location using Latitude and Longitude)

TypeId

AreaId

TodaysTickets (Get the employee who was assigned the least number of tickets to Top)

User can select all or some of the above criteria

1 If Nearest Location is selected then

Get the nearest employee

If TypeId is selected then

TypeId of Employee should match with ticket typeid

If TodaysTickets Selected

Get the employee who was assigned the least number of tickets to Top

If AreaId Selected then check the Employee Area Id with Tickets AreaId

I could able to sort based on location but after sorting location now I have to filter employee whose AreaId, TypeId matches the ticket records and Whoever have least todaystickets.

So for each ticket the system provides its suggestion based on the above criteria

Initially the Tickets will be like this
182722-emptyresult.png

Now i have to provide the Suggested employee to be assigned to the tickets based on above criteria explained
182732-result.png

CREATE TABLE [dbo].Tickets ON [PRIMARY]

GO

CREATE TABLE [dbo].Employee ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Employee] ON

GO
INSERT [dbo].[Employee] ([Id], [Name], [Latitude], [Longitude], [TypeId], [TicketsToday], [AreaId]) VALUES (1, N'Supervisor', 17.286848067916505, 78.24663132069098, 1, 5, 1)
GO
INSERT [dbo].[Employee] ([Id], [Name], [Latitude], [Longitude], [TypeId], [TicketsToday], [AreaId]) VALUES (2, N'Reader', 17.207415689708519, 78.253739445364374, 2, 5, 2)
GO
INSERT [dbo].[Employee] ([Id], [Name], [Latitude], [Longitude], [TypeId], [TicketsToday], [AreaId]) VALUES (3, N'Writer', 17.36978521081307, 78.200294359834473, 1, 9, 1)
GO
INSERT [dbo].[Employee] ([Id], [Name], [Latitude], [Longitude], [TypeId], [TicketsToday], [AreaId]) VALUES (4, N'New Join', 17.284559068738243, 78.499708338539662, 1, 5, 2)
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO
SET IDENTITY_INSERT [dbo].[Tickets] ON

GO
INSERT [dbo].[Tickets] ([Id], [TicketDescription], [Latitude], [Longitude], [TypeId], [AreaId],EmployeeId) VALUES (2, N'Emergency Ticket', 17.269480945138515, 78.3290484467444, 1, 1,null)
GO
INSERT [dbo].[Tickets] ([Id], [TicketDescription], [Latitude], [Longitude], [TypeId], [AreaId],EmployeeId) VALUES (3, N'Normal Ticket', 17.367466654347695, 78.206974995104844, 2, 1,null)
GO
INSERT [dbo].[Tickets] ([Id], [TicketDescription], [Latitude], [Longitude], [TypeId], [AreaId],EmployeeId) VALUES (4, N'Average Ticket', 17.178406092767386, 78.368524928263341, 1, 2,null)
GO
SET IDENTITY_INSERT [dbo].[Tickets] OFF
GO

Thanks & Regards

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 102.1K Reputation points MVP
    2022-03-15T22:28:32.793+00:00

    I've been lurking on this thread, as I also have difficulties to understand what Gulam really wants to achieve. And I did not on the top of my head how to compute the distance, and I was too shy to ask.

    Eventually, I got around to look at the spatial data types in SQL Server, which I don't know well at all. I don't know if Gulam has any better method to compute the distance, but here is a query that returns the employee nearest to the ticket:

    WITH empies AS (
        SELECT *, geography::Point(str(Latitude, 20, 4), str(Longitude, 20, 4), 4326)  AS point
        FROM Employee
     ), tickies AS (
        SELECT *,geography::Point(str(Latitude, 20, 4), str(Longitude, 20, 4), 4326) AS point
        FROM   Tickets
     ) 
     SELECT T.Id, T.TicketDescription, E.Name
     FROM   tickies T
     CROSS  APPLY (SELECT TOP (1) E.Name
                   FROM   empies E
                   ORDER  BY T.point.STDistance(E.point)) AS E
    

    This is not going to be fast at all if there is any volume, though.

    As for the rest of the requirement, I don't follow exactly. Are they to be combined with the condition on distance, or should they have separate queries?

    0 comments No comments