Sort Data based on user selection

Question

Dears,

I have 2 tables called Employees and Tickets

Employee Data

Ticket Data

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

Now i have to provide the Suggested employee to be assigned to the tickets based on above criteria explained

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

Answer

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?

Share via

Sort Data based on user selection

1 answer