Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Program and Database logic question

Posted on 2012-09-14
3
Medium Priority
?
712 Views
Last Modified: 2012-09-18
I have an issue I am having trouble wrapping my brain around.  I have not yet developed the database tables or the program.  I am trying to figure out the logic first.  

Here is the situation.

I have a list of Pick- up points consisting of 10 different locations:
            Pick-Up location A
            Pick-Up location B
            etc.

I have a list of Delivery locations consisting of 12 different locations:
            Delivery location A
             Delivery location B
              etc.

pick ups and deliveries can go from any pick up location to any delivery location and will change for each load.  Each configuration has a different shipping rate because of the miles traveled.  Our dispatchers will select the pick up and delivery location for each load as they are dispatched.  

How can I automatically assign the correct rate for any pick up and delivery configuration and apply that rate to the individual load record so that we can bill the customer and pay our drivers the correct amount?

Any help is appreciated.  

thanks.
0
Comment
Question by:rtay
  • 2
3 Comments
 
LVL 26

Accepted Solution

by:
Alan Warren earned 2000 total points
ID: 38400821
Hi rtay,

Could create a link table for looking up the various rates.
Normalise your data, by having a unique ID column for each of the tables, populate the link table (tblRates) with ID's from the delivery and pick up tables along with a specified rate.
Tables/SQL: Relational diagram.
Then use some SQL similar to this (which could be a parameterised stored procedure) to gain the appropriate rate.
DECLARE @PickUpLocationID int = 1
DECLARE @DeliveryLocationID int = 4

SELECT   Delivery_Pick_Up.Pick_Up
		,Delivery_Location.Location
		,Delivery_Rate.Rate
		
FROM	Delivery_Rate INNER JOIN
			Delivery_Pick_Up ON Delivery_Rate.PickUpLocationID = Delivery_Pick_Up.ID INNER JOIN
				Delivery_Location ON Delivery_Rate.DeliveryLocationID = Delivery_Location.ID
				
WHERE	(Delivery_Rate.PickUpLocationID = @PickUpLocationID) AND 
		(Delivery_Rate.DeliveryLocationID = @DeliveryLocationID)
-- Expected Results
-- Pick_Up	Location	Rate
-- Pick Up location A	Delivery Location D	40.00

Open in new window


Create Table [dbo].[Delivery_Pick_Up]
/****** Object:  Table [dbo].[Delivery_Pick_Up]    Script Date: 09/15/2012 07:40:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Delivery_Pick_Up](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Pick_Up] [varchar](50) NULL,
 CONSTRAINT [PK_PickUpPoint] 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 ANSI_PADDING OFF
GO
-- Sample Data  
-- ID	Pick_Up
-- 1	 Pick Up location A
-- 2	 Pick Up location B
-- 3	 Pick Up location C
-- 4	 Pick Up location D

Open in new window

Create Table [dbo].[Delivery_Location]
/****** Object:  Table [dbo].[Delivery_Location]    Script Date: 09/15/2012 07:41:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Delivery_Location](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Location] [varchar](50) NULL,
 CONSTRAINT [PK_locations] 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 ANSI_PADDING OFF
GO
-- Sample Data
-- ID	Location
-- 1	Delivery Location A
-- 2	Delivery Location B
-- 3	Delivery Location C
-- 4	Delivery Location D  

Open in new window

Create Table [dbo].[Delivery_Rate]
/****** Object:  Table [dbo].[Delivery_Rate]    Script Date: 09/15/2012 07:41:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Delivery_Rate](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Rate] [money] NULL,
	[PickUpLocationID] [int] NULL,
	[DeliveryLocationID] [int] NULL,
 CONSTRAINT [PK_Delivery_Rate] 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
-- Sample Data  
-- ID	Rate	PickUpLocationID	DeliveryLocationID
-- 1	10.00	1	1
-- 2	20.00	1	2
-- 3	30.00	1	3
-- 4	40.00	1	4
-- 5	10.00	2	1
-- 6	20.00	2	2
-- 7	30.00	2	3
-- 8	40.00	2	4
-- 9	10.00	3	1
-- 10	20.00	3	2
-- 11	30.00	3	3
-- 12	40.00	3	4
-- 13	10.00	4	1
-- 14	20.00	4	2
-- 15	30.00	4	3
-- 16	40.00	2	4  

Open in new window

Alan
0
 
LVL 5

Author Comment

by:rtay
ID: 38406437
Thanks Alan that info was great.

quick follow up question, I could close this question and create a new one if you prefer.  just let me know.  

Anyway,

in a asp.net page I have a gridview from a table tbl_loadRecord.

I pull the ship_location and delivery location from dropdowns from the various tables.  how do I call that procedure, pass the loadRecord key and the other two parameters and insert the stored procedure rate value into the rate field in loadRecord table?

Thanks.
0
 
LVL 5

Author Closing Comment

by:rtay
ID: 38410401
Thank you.  This solution was great.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question