Sub-routines in Stored Procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sub-routines in Stored Procedures

Author

Topic

TexDec
Starting Member

22 Posts

Posted - 2002-09-11 : 14:15:08

What is the best way to emulate a gosub & return call in a SP?
I'm using the following, which I think is kinda cumbersome but its the only way I've managed to get it to work. If anyone has any better suggestion they would be appreciated.

SECTION_1_A:
Set @return = 1
GOTO SECTION_3 (the sub routine)
SECTION_1_B:
Section 1 code
GOTO SECTION_2_A

SECTION_2_A:
Set @return = 2
GOTO SECTION_3 (the sub routine)
SECTION_2_B:
Section 2 code
GOTO SECTION_1_A (restart)

SECTION_3:
sub routine code

IF certain criteria THEN
GOTO SECTION_4
ELSE IF @return = 1 THEN
GOTO SECTION_1_B
ELSE
GOTO SECTION_2_B
END IF
END IF

SECTION_4:
wrap up code

Declan K

The only Keane we need is Robbie

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-11 : 14:59:45

You cannot gosub/return in a T-SQL procedure, and although GOTO is a valid statement you should avoid it at all costs. Nothing personal, but GOTO programming is poor programming in any language, and especially so in T-SQL programming.

Blocks of code can be delineated using BEGIN...END constructs, mimicing code sections, and IF statements allow for blocks to be executed conditionally. If there is code that is common to multiple blocks, it can be placed in its own procedure and called explicitly (like the SECTION_3 code in your example) without using GOTO.

As far as "restarting", a stored procedure can call itself recursively (up to a limit of 32 levels), but without more detail about your procedure it will be very hard to tell if recursion is necessary.

If you can post the code you have now, and as much detail about what you're trying to accomplish, we can definitely help you get the procedure to function the way you want.

TexDec
Starting Member

22 Posts

Posted - 2002-09-11 : 15:56:48

Thanks for look at this for me.

The procedure is designed to group (bundle) zip codes within a geography. The model being built has a number of boundry conditions for bundle growth (size, density).

The procedure is trying to do the following.
1/ check to see if there are any zip codes left in the geography.
2/ If so, create a new bundle and add the initial zip code, otherwise goto step 6/.
3/ check to see if there are any zip codes left in the geography.
4/ If so, keep adding zips to the bundle, nearest first, until a boundry condition hass been met, otherwise goto step 6.
5/ Go back to 1/
6/Group all bundles with less than 4 zips into a single bundle.

Here is the actual code

CREATE PROCEDURE ORGANIC_BUILD
AS
SET NOCOUNT ON

DECLARE @MODEL_ID INT
DECLARE @MSA_ID INT
DECLARE @MAX_DIST FLOAT
DECLARE @MAX_AREA FLOAT
DECLARE @MIN_DEN FLOAT
DECLARE @MIN_DISP FLOAT
DECLARE @BUN_CNT INT
DECLARE @ZIP_COUNT INT
DECLARE @RETURN_LABEL INT
DECLARE @ACTIVE_BUN INT
DECLARE @ACTIVE_ZIP CHAR(5)
DECLARE @TOT_DISP FLOAT
DECLARE @TOT_AREA FLOAT
DECLARE @CUR_AREA FLOAT
DECLARE @CUR_DISP FLOAT
DECLARE @OLD_BUN INT

--determine the active model--
SET @MODEL_ID = (SELECT MODEL_ID FROM ACTIVE_MODEL)

--determine the active msa--
SET @MSA_ID =(SELECT MSA_ID
FROM MODEL
WHERE MODEL_ID = @MODEL_ID)

--select dataset--
IF @MSA_ID <> (SELECT DISTINCT MSA_ID FROM ACTIVE_MODEL_DISPATCH_BY_ZIP)
BEGIN
EXEC UPDATE_ACTIVE_MODEL_DISPATCH_BY_ZIP
END

--delete old bundles & reset used flag--
EXEC DROP_MODEL_BUNDLES @MODEL_ID
EXEC RESET_ZIP_CODE_USED_FLAG

--establish control parameters --
SET @MAX_DIST = (SELECT MAX_DISTANCE
FROM MODEL
WHERE MODEL_ID = @MODEL_ID)

SET @MAX_AREA = (SELECT MAX_AREA
FROM MODEL
WHERE MODEL_ID = @MODEL_ID)

SET @MIN_DEN = (SELECT MIN_DENSITY
FROM MODEL
WHERE MODEL_ID = @MODEL_ID)

SET @MIN_DISP = (SELECT MIN_DAILY_DISPATCH
FROM MODEL
WHERE MODEL_ID = @MODEL_ID)

SET @BUN_CNT = 0

BUILD_BUNDLE_A:
SET @RETURN_LABEL = 1
GOTO CHECK_FOR_MORE_ZIP

BUILD_BUNDLE_B:
SET @BUN_CNT = @BUN_CNT + 1
--create bundle header and add anchor zip--
EXEC ADD_BUNDLE_HEADER @MODEL_ID

SET @ACTIVE_BUN = (SELECT MAX(BUNDLE_ID) FROM BUNDLE_HEADER)
SET @ACTIVE_ZIP = (SELECT TOP 1
ZIP_CODE
FROM ACTIVE_MODEL_DISPATCH_BY_ZIP
WHERE USED = 'N'
ORDER BY DENSITY DESC)

EXEC ADD_BUNDLE_DETAIL @ACTIVE_BUN,@ACTIVE_ZIP
EXEC UPDATE_ZIP_CODE_USED_FLAG @ACTIVE_ZIP

SET @TOT_DISP = (SELECT DISPATCHES
FROM ACTIVE_MODEL_DISPATCH_BY_ZIP
WHERE ZIP_CODE = @ACTIVE_ZIP)

SET @TOT_AREA = (SELECT AREA
FROM ACTIVE_MODEL_DISPATCH_BY_ZIP
WHERE ZIP_CODE = @ACTIVE_ZIP)

ADD_ZIPS_A:
SET @RETURN_LABEL = 2
GOTO CHECK_FOR_MORE_ZIP
ADD_ZIPS_B:
DECLARE ZIP_CURSOR CURSOR FOR
SELECT D.TO_ZIP
,DBZ.AREA
,DBZ.DISPATCHES
FROM DISTANCE D
,ACTIVE_MODEL_DISPATCH_BY_ZIP DBZ
,BUNDLE_DETAIL BD
WHERE D.FROM_ZIP = BD.ZIP_CODE
AND D.TO_ZIP = DBZ.ZIP_CODE
AND BD.BUNDLE_ID = @ACTIVE_BUN
AND DBZ.USED = 'N'
AND D.DISTANCE <= @MAX_DIST
ORDER BY D.DISTANCE,D.TO_ZIP

OPEN ZIP_CURSOR

FETCH NEXT FROM ZIP_CURSOR
INTO @ACTIVE_ZIP,@CUR_AREA,@CUR_DISP

WHILE @@FETCH_STATUS = 0
BEGIN
IF @TOT_AREA + @CUR_AREA <= @MAX_AREA
BEGIN
IF (@TOT_DISP + @CUR_DISP)/(@TOT_AREA + @CUR_AREA) >= @MIN_DEN
BEGIN
EXEC ADD_BUNDLE_DETAIL @ACTIVE_BUN,@ACTIVE_ZIP
EXEC UPDATE_ZIP_CODE_USED_FLAG @ACTIVE_ZIP
SET @TOT_AREA = @TOT_AREA + @CUR_AREA
SET @TOT_DISP = @TOT_DISP + @CUR_DISP
END
END

FETCH NEXT FROM ZIP_CURSOR
INTO @ACTIVE_ZIP,@CUR_AREA,@CUR_DISP
END
CLOSE ZIP_CURSOR
DEALLOCATE ZIP_CURSOR

GOTO BUILD_BUNDLE_A

CHECK_FOR_MORE_ZIP:
SET @ZIP_COUNT=(SELECT COUNT(*)
FROM ACTIVE_MODEL_DISPATCH_BY_ZIP
WHERE USED = 'N')

IF @ZIP_COUNT <> 0
BEGIN
IF @RETURN_LABEL = 1
BEGIN
GOTO BUILD_BUNDLE_B
END
ELSE
BEGIN
GOTO ADD_ZIPS_B
END
END
ELSE
BEGIN
GOTO TIDY_UP_BUNDLE
END

TIDY_UP_BUNDLE:
EXEC ADD_BUNDLE_HEADER @MODEL_ID
SET @ACTIVE_BUN = (SELECT MAX(BUNDLE_ID) FROM BUNDLE_HEADER)

DECLARE BUN_CURSOR CURSOR FOR
SELECT BD.BUNDLE_ID
FROM BUNDLE_DETAIL BD
,BUNDLE_HEADER BH
WHERE BD.BUNDLE_ID = BH.BUNDLE_ID
AND BH.MODEL_ID = @MODEL_ID
GROUP BY BD.BUNDLE_ID
HAVING COUNT(BD.ZIP_CODE) <= 3

OPEN BUN_CURSOR

FETCH NEXT FROM BUN_CURSOR
INTO @OLD_BUN

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE BUNDLE_DETAIL
SET BUNDLE_ID = @ACTIVE_BUN
WHERE BUNDLE_ID = @OLD_BUN

DELETE FROM BUNDLE_HEADER
WHERE BUNDLE_ID = @OLD_BUN

FETCH NEXT FROM BUN_CURSOR
INTO @OLD_BUN
END
CLOSE BUN_CURSOR
DEALLOCATE BUN_CURSOR

SET NOCOUNT OFF

Declan K

The only Keane we need is Robbie

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-11 : 16:17:50

I must say this is a perfect example of what companies get when they don't hire real SQL people and try to make procedural coders do database work

don't mind me I am just trying to get my post count higher

TexDec
Starting Member

22 Posts

Posted - 2002-09-11 : 16:19:50

I'm not really a programmer at all, I'm an accountant. But its landed on my plate and I'm trying to do the best I can.

:-(

Declan K

The only Keane we need is Robbie

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-11 : 17:22:58

I hate to be a pest, but could you also include the code for all of the stored procedures used here, as well as the table definitions for the tables involved? You can right-click the database and choose "Generate SQL Scripts" and then select only the objects needed for this procedure, then copy the SQL code from the script file.

PLEASE PLEASE PLEASE if you do this include ALL of the columns, not just the columns used by the procedures. I can't count the number of posts where people provided only half of the columns, and someone posted a perfectly good solution that would've worked except for the "other columns I didn't include originally".

It would also help if the terms "bundle" and "geography" could be defined in more detail. Is a bundle analogous to a county, for example? Would a state be equivalent to a geography? If those terms can be somehow demonstrated in more concrete ways that would be immensely helpful. If you could also provide some sample data that would be good too.

Thanks.

TexDec
Starting Member

22 Posts

Posted - 2002-09-11 : 17:39:34

OK, here goes...
Here are the scripts, I will follow up with another post outlining my
(flawed?) logic.

CREATE TABLE [dbo].[ACTIVE_MODEL] (
[MODEL_ID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ACTIVE_MODEL_DISPATCH_BY_ZIP] (
[ZIP_CODE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MSA_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USED] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DISPATCHES] [float] NULL ,
[AREA] [float] NULL ,
[DENSITY] [float] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BUNDLE_DETAIL] (
[BUNDLE_ID] [int] NOT NULL ,
[ZIP_CODE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BUNDLE_HEADER] (
[BUNDLE_ID] [int] IDENTITY (1, 1) NOT NULL ,
[MODEL_ID] [int] NOT NULL ,
[MODEL_BUNDLE_NUM] [int] NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[COUNTY] (
[COUNTY_ID] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COUNTY_NAME] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE_ALPHA_CODE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MSA_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CUSTOMER_CHOICE] (
[CUSTOMER_NUM] [int] NOT NULL ,
[VENDOR_ID] [int] NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DISPATCH_FACT] (
[ZIP_CODE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMER_NUM] [int] NULL ,
[VENDOR_ID] [int] NULL ,
[DISPATCHES] [float] NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DMA] (
[DMA_ID] [int] NOT NULL ,
[DMA_DESC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MODEL] (
[MODEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
[MODEL_TYPE_ID] [int] NOT NULL ,
[MSA_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MODEL_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INITIAL_ANCHOR_COUNT] [int] NULL ,
[CC_CALLS_PER_DAY] [float] NULL ,
[MAX_DISTANCE] [float] NULL ,
[MAX_AREA] [float] NULL ,
[MIN_DENSITY] [float] NULL ,
[MIN_DAILY_DISPATCH] [int] NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MODEL_TYPE] (
[MODEL_TYPE_ID] [int] IDENTITY (1, 1) NOT NULL ,
[MODEL_TYPE_DESC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MSA] (
[MSA_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MSA_NAME] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MSA_TYPE_ID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MSA_LEVEL_ID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CMSA_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MSA_LEVEL] (
[MSA_LEVEL_ID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POPULATION_BAND] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MSA_TYPE] (
[MSA_TYPE_ID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MSA_TYPE_DESC] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[STATE] (
[STATE_ALPHA_CODE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STATE_NAME] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE_NUMERIC_CODE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MAINLAND_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ZIP_CODE] (
[ZIP_CODE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COUNTY_ID] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TIME_ZONE] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LATITUDE] [real] NULL ,
[LONGITUDE] [real] NULL ,
[DMA_ID] [int] NULL ,
[ZIP_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AREA] [float] NULL ,
[PARENT_ZIP_CODE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ZIP_TYPE] (
[ZIP_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ZIP_TYPE_DESC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATE_DATE] [datetime] NULL ,
[UPDATE_USER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACTIVE_MODEL_DISPATCH_BY_ZIP] WITH NOCHECK ADD
CONSTRAINT [PK_ACTIVE_MODEL_DISPATCH_BY_ZIP] PRIMARY KEY CLUSTERED
(
[ZIP_CODE]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BUNDLE_DETAIL] WITH NOCHECK ADD
CONSTRAINT [PK_BUNDLE_DETAIL] PRIMARY KEY CLUSTERED
(
[BUNDLE_ID],
[ZIP_CODE]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BUNDLE_HEADER] WITH NOCHECK ADD
CONSTRAINT [PK_BUNDLE_HEADER] PRIMARY KEY CLUSTERED
(
[BUNDLE_ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[COUNTY] WITH NOCHECK ADD
CONSTRAINT [PK_COUNTY] PRIMARY KEY CLUSTERED
(
[COUNTY_ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CUSTOMER_CHOICE] WITH NOCHECK ADD
CONSTRAINT [PK_CUSTOMER_CHOICE] PRIMARY KEY CLUSTERED
(
[CUSTOMER_NUM]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DMA] WITH NOCHECK ADD
CONSTRAINT [PK_DMA] PRIMARY KEY CLUSTERED
(
[DMA_ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MODEL] WITH NOCHECK ADD
CONSTRAINT [PK_MODEL] PRIMARY KEY CLUSTERED
(
[MODEL_ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MODEL_TYPE] WITH NOCHECK ADD
CONSTRAINT [PK_MODEL_TYPE] PRIMARY KEY CLUSTERED
(
[MODEL_TYPE_ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MSA] WITH NOCHECK ADD
CONSTRAINT [PK_MSA] PRIMARY KEY CLUSTERED
(
[MSA_ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MSA_LEVEL] WITH NOCHECK ADD
CONSTRAINT [PK_MSA_LEVEL] PRIMARY KEY CLUSTERED
(
[MSA_LEVEL_ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MSA_TYPE] WITH NOCHECK ADD
CONSTRAINT [PK_MSA_TYPE] PRIMARY KEY CLUSTERED
(
[MSA_TYPE_ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[STATE] WITH NOCHECK ADD
CONSTRAINT [PK_STATE] PRIMARY KEY CLUSTERED
(
[STATE_ALPHA_CODE]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ZIP_CODE] WITH NOCHECK ADD
CONSTRAINT [PK_ZIP_CODE] PRIMARY KEY CLUSTERED
(
[ZIP_CODE]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ZIP_TYPE] WITH NOCHECK ADD
CONSTRAINT [PK_ZIP_TYPE] PRIMARY KEY CLUSTERED
(
[ZIP_TYPE]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BUNDLE_DETAIL] ADD
CONSTRAINT [FK_BUNDLE_DETAIL_BUNDLE_HEADER] FOREIGN KEY
(
[BUNDLE_ID]
) REFERENCES [dbo].[BUNDLE_HEADER] (
[BUNDLE_ID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[BUNDLE_HEADER] ADD
CONSTRAINT [FK_BUNDLE_HEADER_MODEL] FOREIGN KEY
(
[MODEL_ID]
) REFERENCES [dbo].[MODEL] (
[MODEL_ID]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[COUNTY] ADD
CONSTRAINT [FK_COUNTY_MSA] FOREIGN KEY
(
[MSA_ID]
) REFERENCES [dbo].[MSA] (
[MSA_ID]
),
CONSTRAINT [FK_COUNTY_STATE] FOREIGN KEY
(
[STATE_ALPHA_CODE]
) REFERENCES [dbo].[STATE] (
[STATE_ALPHA_CODE]
)
GO

alter table [dbo].[COUNTY] nocheck constraint [FK_COUNTY_MSA]
GO

ALTER TABLE [dbo].[DISPATCH_FACT] ADD
CONSTRAINT [FK_DISPATCH_FACT_CUSTOMER_CHOICE] FOREIGN KEY
(
[CUSTOMER_NUM]
) REFERENCES [dbo].[CUSTOMER_CHOICE] (
[CUSTOMER_NUM]
) NOT FOR REPLICATION
GO

alter table [dbo].[DISPATCH_FACT] nocheck constraint [FK_DISPATCH_FACT_CUSTOMER_CHOICE]
GO

ALTER TABLE [dbo].[MODEL] ADD
CONSTRAINT [FK_MODEL_MODEL_TYPE] FOREIGN KEY
(
[MODEL_TYPE_ID]
) REFERENCES [dbo].[MODEL_TYPE] (
[MODEL_TYPE_ID]
)
GO

ALTER TABLE [dbo].[MSA] ADD
CONSTRAINT [FK_MSA_MSA] FOREIGN KEY
(
[CMSA_ID]
) REFERENCES [dbo].[MSA] (
[MSA_ID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_MSA_MSA_LEVEL] FOREIGN KEY
(
[MSA_LEVEL_ID]
) REFERENCES [dbo].[MSA_LEVEL] (
[MSA_LEVEL_ID]
),
CONSTRAINT [FK_MSA_MSA_TYPE] FOREIGN KEY
(
[MSA_TYPE_ID]
) REFERENCES [dbo].[MSA_TYPE] (
[MSA_TYPE_ID]
)
GO

alter table [dbo].[MSA] nocheck constraint [FK_MSA_MSA]
GO

ALTER TABLE [dbo].[ZIP_CODE] ADD
CONSTRAINT [FK_ZIP_CODE_COUNTY] FOREIGN KEY
(
[COUNTY_ID]
) REFERENCES [dbo].[COUNTY] (
[COUNTY_ID]
),
CONSTRAINT [FK_ZIP_CODE_DMA] FOREIGN KEY
(
[DMA_ID]
) REFERENCES [dbo].[DMA] (
[DMA_ID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_ZIP_CODE_ZIP_CODE] FOREIGN KEY
(
[PARENT_ZIP_CODE]
) REFERENCES [dbo].[ZIP_CODE] (
[ZIP_CODE]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_ZIP_CODE_ZIP_TYPE] FOREIGN KEY
(
[ZIP_TYPE]
) REFERENCES [dbo].[ZIP_TYPE] (
[ZIP_TYPE]
) NOT FOR REPLICATION
GO

alter table [dbo].[ZIP_CODE] nocheck constraint [FK_ZIP_CODE_DMA]
GO

alter table [dbo].[ZIP_CODE] nocheck constraint [FK_ZIP_CODE_ZIP_CODE]
GO

alter table [dbo].[ZIP_CODE] nocheck constraint [FK_ZIP_CODE_ZIP_TYPE]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW BUNDLE_STATS
AS
SELECT BH.MODEL_ID
,BH.MODEL_BUNDLE_NUM AS BUNDLE
,COUNT(BD.ZIP_CODE) AS ZIPS
,SUM(DBZ.DISPATCHES) AS DISPATCHES
,SUM(ZC.AREA) AS AREA
,SUM(DBZ.DISPATCHES)/SUM(ZC.AREA) AS DENSITY
FROM BUNDLE_HEADER BH
,BUNDLE_DETAIL BD
,DISPATCH_BY_ZIP DBZ
,ZIP_CODE ZC
WHERE BH.BUNDLE_ID = BD.BUNDLE_ID
AND BD.ZIP_CODE = ZC.ZIP_CODE
AND BD.ZIP_CODE = DBZ.ZIP_CODE
GROUP BY
BH.MODEL_ID
,BH.MODEL_BUNDLE_NUM

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW DISPATCH_BY_ZIP
AS
SELECT ISNULL(ZC.PARENT_ZIP_CODE,ZC.ZIP_CODE) AS ZIP_CODE
,SUM(DF.DISPATCHES) AS DISPATCHES
FROM ZIP_CODE ZC
,DISPATCH_FACT DF
WHERE DF.ZIP_CODE = ZC.ZIP_CODE
GROUP BY ISNULL(ZC.PARENT_ZIP_CODE,ZC.ZIP_CODE)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.DISTANCE
AS
SELECT A.ZIP_CODE AS FROM_ZIP
,B.ZIP_CODE AS TO_ZIP
,SQRT (
SQUARE(69.1 * (B.LATITUDE-A.LATITUDE))
+ SQUARE(69.1 * (B.LONGITUDE-A.LONGITUDE)* COS(A.LATITUDE / 57.3))
) AS DISTANCE
FROM dbo.ZIP_CODE A
,dbo.ZIP_CODE B

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE ADD_BUNDLE_DETAIL
@BUNDLE_ID INT
,@ZIP_CODE CHAR(5)

AS
INSERT INTO BUNDLE_DETAIL
VALUES (@BUNDLE_ID
,@ZIP_CODE
,CURRENT_TIMESTAMP
,SYSTEM_USER)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE ADD_BUNDLE_HEADER
@MODEL_ID INT

AS
DECLARE @MODEL_BUNDLE_NUM INT

SET @MODEL_BUNDLE_NUM =
(SELECT ISNULL(MAX(MODEL_BUNDLE_NUM)+1,1)
FROM BUNDLE_HEADER
WHERE MODEL_ID = @MODEL_ID)

INSERT INTO BUNDLE_HEADER
(MODEL_ID
,MODEL_BUNDLE_NUM
,UPDATE_DATE
,UPDATE_USER)
VALUES (@MODEL_ID
,@MODEL_BUNDLE_NUM
,CURRENT_TIMESTAMP
,SYSTEM_USER)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE ADD_MODEL
@MODEL_NAME VARCHAR(50)
,@MODEL_TYPE_ID INT
,@MSA_ID CHAR(4)
,@INITIAL_ANCHOR INT = 0
,@CC_CALLS_PER_DAY FLOAT = 0
,@MAX_DISTANCE FLOAT = 0
,@MAX_AREA FLOAT = 0
,@MIN_DENSITY FLOAT = 0
,@MIN_DAILY_DISPATCH INT = 0
AS
INSERT INTO
MODEL (MODEL_NAME
,MODEL_TYPE_ID
,MSA_ID
,INITIAL_ANCHOR_COUNT
,CC_CALLS_PER_DAY
,MAX_DISTANCE
,MAX_AREA
,MIN_DENSITY
,MIN_DAILY_DISPATCH
,UPDATE_DATE
,UPDATE_USER)
VALUES (@MODEL_NAME
,@MODEL_TYPE_ID
,@MSA_ID
,@INITIAL_ANCHOR
,@CC_CALLS_PER_DAY
,@MAX_DISTANCE
,@MAX_AREA
,@MIN_DENSITY
,@MIN_DAILY_DISPATCH
,CURRENT_TIMESTAMP
,SYSTEM_USER)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE ADD_MODEL_TYPE
@MODEL_TYPE_DESC VARCHAR(50)
AS
INSERT INTO MODEL_TYPE (MODEL_TYPE_DESC,UPDATE_DATE,UPDATE_USER)
VALUES (@MODEL_TYPE_DESC,CURRENT_TIMESTAMP,SYSTEM_USER)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE BALANCED_BUILD
AS

SET NOCOUNT ON
DECLARE @MODEL_ID INT
DECLARE @MSA_ID INT
DECLARE @BUN_CNT INT
DECLARE @X INT
DECLARE @ACTIVE_BUN INT
DECLARE @ACTIVE_ZIP CHAR(5)
DECLARE @ZIP_COUNT INT
DECLARE @Z INT

--determine the active model-
SET @MODEL_ID = (SELECT MODEL_ID FROM ACTIVE_MODEL)

--determine the active msa--
SET @MSA_ID =(SELECT MSA_ID
FROM MODEL
WHERE MODEL_ID = @MODEL_ID)

--select dataset--
IF @MSA_ID <> (SELECT DISTINCT MSA_ID FROM ACTIVE_MODEL_DISPATCH_BY_ZIP)
BEGIN
EXEC UPDATE_ACTIVE_MODEL_DISPATCH_BY_ZIP
END

--delete old bundles & reset used flag--
EXEC DROP_MODEL_BUNDLES @MODEL_ID
EXEC RESET_ZIP_CODE_USED_FLAG

--determine the number of bundles--
SET @BUN_CNT =(SELECT INITIAL_ANCHOR_COUNT
FROM MODEL
WHERE MODEL_ID = @MODEL_ID)

--create bundle headers and anchor zip codes-
SET @X = 1
WHILE @X <= @BUN_CNT
BEGIN
EXEC ADD_BUNDLE_HEADER @MODEL_ID
SET @ACTIVE_BUN = (SELECT MAX(BUNDLE_ID) FROM BUNDLE_HEADER)
SET @ACTIVE_ZIP = (SELECT TOP 1
ZIP_CODE
FROM ACTIVE_MODEL_DISPATCH_BY_ZIP
WHERE USED = 'N'
ORDER BY DENSITY DESC)
EXEC ADD_BUNDLE_DETAIL @ACTIVE_BUN,@ACTIVE_ZIP
EXEC UPDATE_ZIP_CODE_USED_FLAG @ACTIVE_ZIP
SET @X = @X + 1
END

--incrementally build bundles--
SET @ZIP_COUNT = (SELECT COUNT(*)
FROM ACTIVE_MODEL_DISPATCH_BY_ZIP
WHERE USED = 'N')

DECLARE BUNDLE_CURSOR SCROLL CURSOR FOR
SELECT BUNDLE_ID
FROM BUNDLE_HEADER
WHERE MODEL_ID = @MODEL_ID

OPEN BUNDLE_CURSOR

SET @Z=1
SET @X=1
WHILE @X<=@ZIP_COUNT
BEGIN
FETCH ABSOLUTE @Z FROM BUNDLE_CURSOR INTO @ACTIVE_BUN
SET @ACTIVE_ZIP = (SELECT TOP 1
D.TO_ZIP
FROM DISTANCE D
,BUNDLE_DETAIL BD
,ACTIVE_MODEL_DISPATCH_BY_ZIP DBZ
WHERE D.FROM_ZIP = BD.ZIP_CODE
AND D.TO_ZIP = DBZ.ZIP_CODE
AND BD.BUNDLE_ID = @ACTIVE_BUN
AND DBZ.USED = 'N'
ORDER BY D.DISTANCE
,D.TO_ZIP)

EXEC ADD_BUNDLE_DETAIL @ACTIVE_BUN,@ACTIVE_ZIP
EXEC UPDATE_ZIP_CODE_USED_FLAG @ACTIVE_ZIP
SET @X = @X + 1

IF @Z = @BUN_CNT
BEGIN
SET @Z=1
END
ELSE
BEGIN
SET @Z=@Z+1
END
END

CLOSE BUNDLE_CURSOR
DEALLOCATE BUNDLE_CURSOR

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE DROP_MODEL_BUNDLES
@MODEL_ID INT
AS
DELETE
FROM BUNDLE_HEADER
WHERE MODEL_ID = @MODEL_ID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE ORGANIC_BUILD
AS
SET NOCOUNT ON

DECLARE @MODEL_ID INT
DECLARE @MSA_ID INT
DECLARE @MAX_DIST FLOAT
DECLARE @MAX_AREA FLOAT
DECLARE @MIN_DEN FLOAT
DECLARE @MIN_DISP FLOAT
DECLARE @BUN_CNT INT
DECLARE @ZIP_COUNT INT
DECLARE @RETURN_LABEL INT
DECLARE @ACTIVE_BUN INT
DECLARE @ACTIVE_ZIP CHAR(5)
DECLARE @TOT_DISP FLOAT
DECLARE @TOT_AREA FLOAT
DECLARE @CUR_AREA FLOAT
DECLARE @CUR_DISP FLOAT
DECLARE @OLD_BUN INT

--determine the active model--
SET @MODEL_ID = (SELECT MODEL_ID FROM ACTIVE_MODEL)

--determine the active msa--
SET @MSA_ID =(SELECT MSA_ID
FROM MODEL
WHERE MODEL_ID = @MODEL_ID)

--select dataset--
IF @MSA_ID <> (SELECT DISTINCT MSA_ID FROM ACTIVE_MODEL_DISPATCH_BY_ZIP)
BEGIN
EXEC UPDATE_ACTIVE_MODEL_DISPATCH_BY_ZIP
END

--delete old bundles & reset used flag--
EXEC DROP_MODEL_BUNDLES @MODEL_ID
EXEC RESET_ZIP_CODE_USED_FLAG

--establish control parameters --
SET @MAX_DIST = (SELECT MAX_DISTANCE
FROM MODEL
WHERE MODEL_ID = @MODEL_ID)

SET @MAX_AREA = (SELECT MAX_AREA
FROM MODEL
WHERE MODEL_ID = @MODEL_ID)

SET @MIN_DEN = (SELECT MIN_DENSITY
FROM MODEL
WHERE MODEL_ID = @MODEL_ID)

SET @MIN_DISP = (SELECT MIN_DAILY_DISPATCH
FROM MODEL
WHERE MODEL_ID = @MODEL_ID)

SET @BUN_CNT = 0

BUILD_BUNDLE_A:
SET @RETURN_LABEL = 1
GOTO CHECK_FOR_MORE_ZIP

BUILD_BUNDLE_B:
SET @BUN_CNT = @BUN_CNT + 1
--create bundle header and add anchor zip--
EXEC ADD_BUNDLE_HEADER @MODEL_ID

SET @ACTIVE_BUN = (SELECT MAX(BUNDLE_ID) FROM BUNDLE_HEADER)
SET @ACTIVE_ZIP = (SELECT TOP 1
ZIP_CODE
FROM ACTIVE_MODEL_DISPATCH_BY_ZIP
WHERE USED = 'N'
ORDER BY DENSITY DESC)

EXEC ADD_BUNDLE_DETAIL @ACTIVE_BUN,@ACTIVE_ZIP
EXEC UPDATE_ZIP_CODE_USED_FLAG @ACTIVE_ZIP

SET @TOT_DISP = (SELECT DISPATCHES
FROM ACTIVE_MODEL_DISPATCH_BY_ZIP
WHERE ZIP_CODE = @ACTIVE_ZIP)

SET @TOT_AREA = (SELECT AREA
FROM ACTIVE_MODEL_DISPATCH_BY_ZIP
WHERE ZIP_CODE = @ACTIVE_ZIP)

ADD_ZIPS_A:
SET @RETURN_LABEL = 2
GOTO CHECK_FOR_MORE_ZIP
ADD_ZIPS_B:
DECLARE ZIP_CURSOR CURSOR FOR
SELECT D.TO_ZIP
,DBZ.AREA
,DBZ.DISPATCHES
FROM DISTANCE D
,ACTIVE_MODEL_DISPATCH_BY_ZIP DBZ
,BUNDLE_DETAIL BD
WHERE D.FROM_ZIP = BD.ZIP_CODE
AND D.TO_ZIP = DBZ.ZIP_CODE
AND BD.BUNDLE_ID = @ACTIVE_BUN
AND DBZ.USED = 'N'
AND D.DISTANCE <= @MAX_DIST
ORDER BY D.DISTANCE,D.TO_ZIP

OPEN ZIP_CURSOR

FETCH NEXT FROM ZIP_CURSOR
INTO @ACTIVE_ZIP,@CUR_AREA,@CUR_DISP

WHILE @@FETCH_STATUS = 0
BEGIN
IF @TOT_AREA + @CUR_AREA <= @MAX_AREA
BEGIN
IF (@TOT_DISP + @CUR_DISP)/(@TOT_AREA + @CUR_AREA) >= @MIN_DEN
BEGIN
EXEC ADD_BUNDLE_DETAIL @ACTIVE_BUN,@ACTIVE_ZIP
EXEC UPDATE_ZIP_CODE_USED_FLAG @ACTIVE_ZIP
SET @TOT_AREA = @TOT_AREA + @CUR_AREA
SET @TOT_DISP = @TOT_DISP + @CUR_DISP
END
END

FETCH NEXT FROM ZIP_CURSOR
INTO @ACTIVE_ZIP,@CUR_AREA,@CUR_DISP
END
CLOSE ZIP_CURSOR
DEALLOCATE ZIP_CURSOR

GOTO BUILD_BUNDLE_A

CHECK_FOR_MORE_ZIP:
SET @ZIP_COUNT=(SELECT COUNT(*)
FROM ACTIVE_MODEL_DISPATCH_BY_ZIP
WHERE USED = 'N')

IF @ZIP_COUNT <> 0
BEGIN
IF @RETURN_LABEL = 1
BEGIN
GOTO BUILD_BUNDLE_B
END
ELSE
BEGIN
GOTO ADD_ZIPS_B
END
END
ELSE
BEGIN
GOTO TIDY_UP_BUNDLE
END

TIDY_UP_BUNDLE:
EXEC ADD_BUNDLE_HEADER @MODEL_ID
SET @ACTIVE_BUN = (SELECT MAX(BUNDLE_ID) FROM BUNDLE_HEADER)

DECLARE BUN_CURSOR CURSOR FOR
SELECT BD.BUNDLE_ID
FROM BUNDLE_DETAIL BD
,BUNDLE_HEADER BH
WHERE BD.BUNDLE_ID = BH.BUNDLE_ID
AND BH.MODEL_ID = @MODEL_ID
GROUP BY BD.BUNDLE_ID
HAVING COUNT(BD.ZIP_CODE) <= 3

OPEN BUN_CURSOR

FETCH NEXT FROM BUN_CURSOR
INTO @OLD_BUN

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE BUNDLE_DETAIL
SET BUNDLE_ID = @ACTIVE_BUN
WHERE BUNDLE_ID = @OLD_BUN

DELETE FROM BUNDLE_HEADER
WHERE BUNDLE_ID = @OLD_BUN

FETCH NEXT FROM BUN_CURSOR
INTO @OLD_BUN
END
CLOSE BUN_CURSOR
DEALLOCATE BUN_CURSOR

SET NOCOUNT OFF

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE RESET_ZIP_CODE_USED_FLAG
AS
UPDATE ACTIVE_MODEL_DISPATCH_BY_ZIP
SET USED = 'N'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE UPDATE_ACTIVE_MODEL_DISPATCH_BY_ZIP
AS

DELETE FROM ACTIVE_MODEL_DISPATCH_BY_ZIP

INSERT INTO ACTIVE_MODEL_DISPATCH_BY_ZIP
SELECT ISNULL(ZC.PARENT_ZIP_CODE,ZC.ZIP_CODE)
,C.MSA_ID
,'N'
,0
,SUM(ZC.AREA)
,0
FROM dbo.ZIP_CODE ZC
,dbo.COUNTY C
,dbo.MODEL M
,dbo.ACTIVE_MODEL AM
WHERE ZC.COUNTY_ID = C.COUNTY_ID
AND C.MSA_ID = M.MSA_ID
AND M.MODEL_ID = AM.MODEL_ID
GROUP BY
ISNULL(ZC.PARENT_ZIP_CODE,ZC.ZIP_CODE)
,C.MSA_ID

UPDATE ACTIVE_MODEL_DISPATCH_BY_ZIP
SET DISPATCHES = A.DISPATCHES
FROM ACTIVE_MODEL_DISPATCH_BY_ZIP DBZ
,( SELECT ISNULL(ZC.PARENT_ZIP_CODE,ZC.ZIP_CODE) AS ZIP_CODE
,SUM(DF.DISPATCHES) AS DISPATCHES
FROM DISPATCH_FACT DF
,ZIP_CODE ZC
,COUNTY C
,MODEL M
,ACTIVE_MODEL AM
WHERE DF.ZIP_CODE = ZC.ZIP_CODE
AND ZC.COUNTY_ID = C.COUNTY_ID
AND C.MSA_ID = M.MSA_ID
AND M.MODEL_ID = AM.MODEL_ID
GROUP BY ISNULL(ZC.PARENT_ZIP_CODE,ZC.ZIP_CODE)) A
WHERE DBZ.ZIP_CODE = A.ZIP_CODE

UPDATE ACTIVE_MODEL_DISPATCH_BY_ZIP
SET DENSITY = DISPATCHES/AREA
WHERE AREA<>0

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE UPDATE_ZIP_CODE_USED_FLAG
@ZIP_CODE CHAR(5)
AS
UPDATE ACTIVE_MODEL_DISPATCH_BY_ZIP
SET USED = 'Y'
WHERE ZIP_CODE = @ZIP_CODE

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Declan K

The only Keane we need is Robbie

TexDec
Starting Member

22 Posts

Posted - 2002-09-11 : 17:52:01

Continuing the last post

A "bundle" is a grouping of zip_codes, it may not be consistent with county lines. The result of this SP is a collection of bundles, (called a model), which encompass all the zip codes in an area.
I am using MSA's (Metropolitan Statistical Areas) as my geographic areas. These are collections of counties.

Zip Hierarchy = zip_code-->parent_zip_code-->county_id-->msa_id
Parent zip codes are used for unique zip codes which are entirely contained within another zip, mainly used by big Co's)

A bundle contains zip codes, so the BUNDLE_DETAIL table contains bundle_id and zip_code. Its parent table is BUNDLE_HEADER (PK bundle_id) which has a record for each bundle and the associated model_id. Its parent table is MODEL which contains a record for each model. Data in this record includes the type of model and the MSA upon which the model is based. It also contains the parameters used to build the bundles.

The ORGANIC_BUILD SP is supposed to build bundles in series. Starting with the most dense zip_code and adding additional zips, closest first, until its growth is stoped by meeting one of the parameters set in the MODEL record. It then starts to build another bundle, repeating the process until all zip codes have been allocated.

When working on a particular model I update the value in the ACTIVE_MODEL table with the MODEL_ID of the model I'm working on, this table is really used as a WHERE clause to restrict the record selected from the MODEL table.

The ACTIVE_MODEL_DISPATCH_BY_ZIP table is used to gather the fact data for the ACTIVE_MODEL so that I don't have to keep going to my large DISPATCH_FACT table during processing.

Hope this helps....

Declan K

The only Keane we need is Robbie

Edited by - texdec on 09/11/2002 17:52:38

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-11 : 22:22:09

let me re-iterate ... once again another company has shown it lack of fore sight in wanting something but not providing the proper resources to attain what it wants ... I'm not bashing you its not your fault this got dumped on you unless you said you could do it and it should really be done by a qualified DBA or developer... its just that most companies will do this and in sometime down the road look back and wonder why the application runs so slow and where'd their $1k of money go... the uppers don't understand scalability and availability ... they just want it to work and work well ... as long as they don't have to pay too much...

rant rant rant ... up my posting level ... i'll catch up with you 3000 post guys someday!

TexDec
Starting Member

22 Posts

Posted - 2002-09-12 : 01:00:30

Onamuji
Welcome to my pain.

Not to go off on a rant here, but...
I'm sure you've all heard this before...
I only got tasked with developing this app because the operations manager on the project spent all the budget on hiring a couple of his consultant mates to do a "business case analysis", which ended up telling us that it was a great idea to do this project, which we knew already. I was seen as the 'SQL guy', i.e. I can write a SELECT statement, so I've ended up having to do the work of the developer that I pushed for hiring in the first place.

OK, so I'm a tad bitter about all this. I know my code is crap, I've never written a SP before and have no programming training, but I'm trying to do the best I can (soft violins play in the backround).

I really do appreciate all your help with this and I'm always willing to learn better techniques. That's why I love your forum and that's why I came to you guys for advice. The SQLteam forum is the one place where I've learned anything of real value about SQLserver specifically and relational databases in general. Keep it up guys.

Declan K

The only Keane we need is Robbie

Tim
Starting Member

392 Posts

Posted - 2002-09-17 : 00:49:29

Firstly, Onamuji posting stuff like that doesn't help anyone get their problem solved. This guy is in the real world and you have to be practical.

Anyway, here are my sketchy ideas (as oppossed to a well thought out solution) I assumed I can put the density on the zip table for simplicity and also didn't deal with all the model constraints. I assume the constraints can be applied to individiual zips to know when a bundle is finished.

First I would make a view

create view AvailableZips as
select * from Zips where ZipID not in
(select ZipID from BundleDetails)

Now make a procedure soemthing like this:

select @StartZipID =
(select top 1 ZipID from AvailableZips where density =
(select max(density) from AvailableZips)
)

while @StartZipID is not null
begin

-- insert a new bundle and a new bundledetail with @StartZipID
INSERT into Bundles blah blah blah
INSERT into BundleDetails blah blah blah

-- save the ID of the new bundle

-- get available zips in order of proximity to the starting zip
(distance would be a udf based on calc in the sp provided)

insert into bundledetails
select @NewBundleID, ZipID from AvailableZips
where distance(ZipID, @StartZipID) < @modelmaxdistance
and density > @modelmindensity

-- get start zip of next bundle
select @StartZipID =
(select top 1 ZipID from AvailableZips where density =
(select max(density) from Available Zips)
)
end

Finally I would insert a new bundle record to accumulate the bundles with 4 or less zips. Then update the bundleID on the bundledetails table with this new BundleID for all such bundles. Then delete the bundles with no details. (Starting to sound like a tongue twister)

Is that totally shonky or what?

----
Nancy Davolio: Best looking chick at Northwind 1992-2000

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-09-17 : 02:58:44

quote:

Firstly, Onamuji posting stuff like that doesn't help anyone get their problem solved.

Couldn't have said it better myself. Sounds like Onamuji ate some bad pizza for dinner last night.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources