Get the Suggested employee to assign ticket
-
Dears,
I have 2 tables called Employees and Tickets
Employee Data
Tickets 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 based on above criteria explained
CREATE TABLE [dbo].[Tickets](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TicketDescription] [nvarchar](500) NOT NULL,
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[TypeId] [int] NULL,
[AreaId] [int] NULL,
[EmployeeId] [int] NULL
CONSTRAINT [PK_Tickets] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NULL,
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[TypeId] [int] NULL,
[TicketsToday] [int] NULL,
[AreaId] [int] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) 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
GOThanks
-
I cannot see any questions in your post. What, specifically, do you need help with?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
This looks like you are trying to create a catch-all query. You are on a slippery slope.
I have never worked with geometry data types before, but after a quick google, I cam up with the following function to assist with the final query
CREATE FUNCTION dbo.fn_GetDistance(
@srcLng decimal(18,15)
, @srcLat decimal(18,15)
, @dstLng decimal(18,15)
, @dstLat decimal(18,15)
)
/*
USAGE:
SELECT *
FROM dbo.fn_GetDistance( -95.45612258030434, 30.20491677226107 /* Source */, -95.44187468725596, 30.120021042878015 /* Destination */ );
*/RETURNS table WITH SCHEMABINDING AS
RETURN
SELECT Kilometers = CAST( geography::Point(@srcLat, @srcLng, 4326).STDistance(geography::Point(@dstLat, @dstLng, 4326)) / 1000.0 AS decimal(28, 6) )
, Miles = CAST( geography::Point(@srcLat, @srcLng, 4326).STDistance(geography::Point(@dstLat, @dstLng, 4326)) / 1609.344 AS decimal(28, 6) );
GOBelow is how I would use the above to get all tickets that match employees on TypeID and AreaId, as well as the employee being the closest.
WITH cteData AS (
SELECT t.Id
, t.TicketDescription
, t.Latitude
, t.Longitude
, t.TypeId
, t.AreaId
, t.EmployeeId
, SuggestedEmployeeId = e.Id
, DistanceRank = DENSE_RANK() OVER (PARTITION BY t.Id
ORDER BY dst.Kilometers)
FROM dbo.Tickets AS t
INNER JOIN dbo.Employee AS e
ON t.TypeId = e.TypeId
AND t.AreaId = e.AreaId
CROSS APPLY dbo.fn_GetDistance( t.Longitude, t.Latitude, e.Longitude, e.Latitude ) AS dst
)
SELECT d.Id
, d.TicketDescription
, d.Latitude
, d.Longitude
, d.TypeId
, d.AreaId
, d.EmployeeId
, d.SuggestedEmployeeId
FROM cteData AS d
WHERE d.DistanceRank = 1;As far as the Employee with the least number of calls for the day, there is no date data to even begin looking for a solution
-
This gives my answer
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply