Am I thinking about this complex (for me) t-sql query?

Copper Contributor

Hi


I'm hoping to get some feedback on my thought process in creating a stored procedure. I'm entirely self-taught (like so many others), so feedback like this is key to my development. Really appreciate any words of wisdom.

 

The goal is to create a stored procedure in SSMS that will take inputs from PowerApps and will use said inputs to create a CallList by (1) filtering tabMembershipList based on those that live in towns that are within X number of miles from StartTown and then (2) dividing the CallList by Y number of Staff Members and assigning a roughly equal number of calls to each Staff Member.

 

The business use starts when one of our associate companies submits a request for workers. Our dispatcher then fills out a form in PowerApps that has the following input (which are inserted into tabCreateCallList):

StartTownID int (FK from tabTown)
RadiusInMiles int
StaffMemberIDs varchar(100) (FK from tabStaffMember) eg: '4, 7, 12'
RequestForWorkersID int (FK from tabRequestForWorkers which has all of the information for the particular request for workers)


Other tables accessed include:

**tabMembershipList**
MemberID int
Name varchar
TownID int

 

**tabTown** (info on towns where our members live)
TownID int
Name varchar
Geo geography (coordinates)

 

The final product will get inserted into **tabCallList** with columns:
MemberID
CreateCallListID
StaffMemberID

 

The code for the stored procedure is below

CREATE PROC uspCreateCallList

--Declare variables
DECLARE @RadiusInMiles int;
DECLARE @StartTownID int;
DECLARE @StartTownGeo geography;
DECLARE @CreateCallListID int;
DECLARE @StaffMemberIDs table ([Value] varchar(max), [ordinal] bigint);
DECLARE @StaffMemberIDsCount int;

--Set variables
SET @CreateCallListID = (input from PowerApps);
SET @StartTownID = (input from PowerApps);
SET @RadiusInMiles = (input from PowerApps) * 1609.34;
-- I access my Person.Town table to get the coordinates that correspond to @TownID
SELECT @StartTownGeo = Geo
	FROM tabTown
	WHERE TownID = @TownID;
-- I create a table variable from the StaffMemberIDs column in tabCreateCallList
INSERT INTO @StaffMemberIDs([value], [ordinal])
	SELECT [value], [ordinal] - 1
	FROM tabCreateCallList
	CROSS APPLY STRING_SPLIT(tabCreateCallList.StaffMemberIDs, ',', 1) 
	WHERE CreateCallListID = @CreateCallListID;

SELECT @StaffMemberIDsCount = COUNT(*) FROM @StaffMemberIDs ;

;WITH cte1(MemberID, CreateCallListID , StaffMemberPlaceholder)
	AS(
	        select 
                MemberID,
		@CreateCallListID CreateCallListID,
		Rank() OVER (PARTITION BY p.TownID ORDER BY @StartTownGeo.STDistance(Geo)) % @StaffMemberIDsCount StaffMemberPlaceholder
		from 
			tabMember m
			JOIN tabTown t ON t.TownID = m.TownID
		WHERE 
			@StartTownGeo.STDistance(Geo) <= @RadiusInMiles
		),
		cte2(MemberID, CreateCallListID, StaffMemberID)
			AS(
				SELECT
					MemberID, CreateCallListID, [Value] StaffMemberID
				FROM
					cte1 a
					JOIN @StaffMemberIDs b ON a.StaffMemberPlaceholder = b.Ordinal
			)
INSERT INTO tabCallList ([MemberID], [CreateCallListID], [StaffMemberID])
SELECT
	MemberID,
	CreateCallListID,
	StaffMemberID
FROM 
	cte2


It works. I just want to know: Is this a sane way to go about this problem? Am I missing something important.

 

Thanks so much for any guidance!!! :lol:

2 Replies

Hi @eli_kent 

 

It works.

 

This might work for your but it cannot work for us since we have no such tables or data :)

 

Please provide:

1) Queries to CREATE your table(s) including indexes
2) Queries to INSERT sample data.
3) The desired result given the sample, as text or image of excel for example.
4) A short description of the business rules, and how you got 1-2 of the results
5) Which version of SQL Server you are using (this will help to fit the query to your version).

Thank you for responding. And what bad manners on my part! I apologize. My attention got diverted form this project, but I should be back to it soon and I'll post the information that is missing.

I appreciate you helping me understand how I can help people help me better!