Sort Data based on user selection

Transact-SQL
1

Dears,

I have one issue and I am not able to write proper query for this.

I have 2 tables called Employees and Tickets

  1. /****** Object: Table [dbo].[Tickets] Script Date: 3/14/2022 12:30:28 AM ******/
  2. SET ANSI_NULLS ON
  3. GO
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  8. CREATE TABLE [dbo].[Tickets](
  9. [Id] [int] IDENTITY(1,1) NOT NULL,
  10. [TicketDescription] [nvarchar](500) NOT NULL,
  11. [Latitude] [float] NULL,
  12. [Longitude] [float] NULL,
  13. [TypeId] [int] NULL,
  14. [AreaId] [int] NULL,
  15. CONSTRAINT [PK_Tickets] PRIMARY KEY CLUSTERED
  16. (
  17. [Id] ASC
  18. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  19. ) ON [PRIMARY]
  21. GO
  23. CREATE TABLE [dbo].[Employee](
  24. [Id] [int] IDENTITY(1,1) NOT NULL,
  25. [Name] [nvarchar](100) NULL,
  26. [Latitude] [float] NULL,
  27. [Longitude] [float] NULL,
  28. [TypeId] [int] NULL,
  29. [TicketsToday] [int] NULL,
  30. [AreaId] [int] NULL,
  31. CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
  32. (
  33. [Id] ASC
  34. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  35. ) ON [PRIMARY]
  38. GO
  39. SET IDENTITY_INSERT [dbo].[Employee] ON
  41. GO
  42. INSERT [dbo].[Employee] ([Id], [Name], [Latitude], [Longitude], [TypeId], [TicketsToday], [AreaId]) VALUES (1, N'Supervisor', 17.286848067916505, 78.24663132069098, 1, 5, 1)
  43. GO
  44. INSERT [dbo].[Employee] ([Id], [Name], [Latitude], [Longitude], [TypeId], [TicketsToday], [AreaId]) VALUES (2, N'Reader', 17.207415689708519, 78.253739445364374, 2, 5, 2)
  45. GO
  46. INSERT [dbo].[Employee] ([Id], [Name], [Latitude], [Longitude], [TypeId], [TicketsToday], [AreaId]) VALUES (3, N'Writer', 17.36978521081307, 78.200294359834473, 1, 9, 1)
  47. GO
  48. INSERT [dbo].[Employee] ([Id], [Name], [Latitude], [Longitude], [TypeId], [TicketsToday], [AreaId]) VALUES (4, N'New Join', 17.284559068738243, 78.499708338539662, 1, 5, 2)
  49. GO
  50. SET IDENTITY_INSERT [dbo].[Employee] OFF
  51. GO
  52. SET IDENTITY_INSERT [dbo].[Tickets] ON
  54. GO
  55. INSERT [dbo].[Tickets] ([Id], [TicketDescription], [Latitude], [Longitude], [TypeId], [AreaId]) VALUES (2, N'Emergency Ticket', 17.269480945138515, 78.3290484467444, 1, 1)
  56. GO
  57. INSERT [dbo].[Tickets] ([Id], [TicketDescription], [Latitude], [Longitude], [TypeId], [AreaId]) VALUES (3, N'Normal Ticket', 17.367466654347695, 78.206974995104844, 2, 1)
  58. GO
  59. INSERT [dbo].[Tickets] ([Id], [TicketDescription], [Latitude], [Longitude], [TypeId], [AreaId]) VALUES (4, N'Average Ticket', 17.178406092767386, 78.368524928263341, 1, 2)
  60. GO
  61. SET IDENTITY_INSERT [dbo].[Tickets] OFF
  62. GO

When the user load the tickets I have to filter the employee for each ticket means the system should provide suggestions of the employee who is near to ticket raised area

Conditions:

Location
TypeId
AreaId
TodaysTickets

User can select all or some of the above criteria

1 If Location is selected then
. Sort employees location with tickets location. Display the nearest employee to the ticket location

  1. If TypeId is selected then
    TypeId of Employee should match with ticket typeid
  2. If TodaysTickets Selected
    Get the employee who have less number of TodaysTicket Value
  3. AreaId Selcetd then check the Employee Area Id with Tickets
    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 todaytickets.

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

The final result needed something like this

TicketDescription EmployeeName
Normal Ticket Reader
Emergency Ticket Supervisor

2

There are 2 options I guess, you can simply filter by:

WHERE
(@TypeID=TypeID OR @TypeID IS NULL)

Another option is Dynamic SQL. You are writing the query based on the parameters and execute it when your finished. Be carefull with Dynamic SQL as you will need to validate the input parameters to prevent a seciurity risk. I think I would use Dynamic SQL here because it's much easier to expand and maintain when the requirements change and no need to filter all the parameters but it all depands on your situation. When you have a small database and few changes option 1 will be fine.