November 9, 2011 at 6:12 am
Hi
I was wondering if someone could help understand what exactly this code is doing(i hope it doesn't seem like a silly question) A few years back a developer designed this code without any comments which has now been given to me to find out what exactly it is doing and purpose.
In an attempt to understand it i have made as much detailed comments to the code as possible. What i really want to know is its purpose??.
Unfortunately, I cant get a copy of the DB which i feel would of made things a lot easier for me personally.
USE [DSAPS_CM01]
GO
/****** Object: StoredProcedure [dbo].[DS_DemandParse] Script Date: 11/07/2011 10:37:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DS_DemandParse]
AS
-- Deleting a table called DSDemand
DELETE DSDemand
-- Declare a variable called N and give it a datatype integrator
-- Declare a variable called ReqD and give it a datatype Datetime
DECLARE @N [integer],
@ReqD [DateTime]
-- Set a value of the declare variable N to zero
SET @N = 0
WHILE @N <= 12 -- A while loop that looks at the variable N and if it is less or equal to 12.
BEGIN
SET @ReqD = DATEADD(day,7 *(@N), -- Then begin by setting the variable ReqD to add a specified time interval to a date( in this case its 7 which represents June)
(SELECT CAST(SUBSTRING(DemCOString,53,8) AS DateTime)-- A cast function is used to convert and substring that grab a portion of the stored data then rename the field 'Datetime'
FROM DSCOString -- What table to extract the information from.
WHERE LEFT(DemCOString,5)=' ' )) -- A where clause to that uses a return left part which specifies the number of characters to return(the othe section dont know)
-- insert data into the fields Item, ShipTo, CustOrds and ReqDate fields
INSERT INTO DSDemand
([Item]
,[ShipTo]
,[CustOrds]
,[ReqDate])
SELECT Left(DemCOString,32)-- a left function returns specified number of characters which in this case is 32
-- The substring is used to grap portion of the of the field DemCoString
,SubString(DemCOString,33,12) -- grap the portion of the field decostring from 33 to 12(seems wrong way round to me)
,SUBSTRING(DemCOString,53 + (8*(@N)),8)+ --The substring is used to grap portion of the of the field DemCoString
/*a case statement is basically saying that when the variable N is equal to one then
grap a portion of the democoString field from 45 to 8 or if it is not 1 then set to zero
*/
CASE WHEN @N = 1
THEN SUBSTRING(DemCOString,45,8)
ELSE 0
END
/*
The value from the variable RegD
From the table DSCoString grab a portion of the field DemCoString from 2 - 5( dont know what the other part those)
*/
,@ReqD
FROM DSCOString WHERE SUBSTRING(DemCOString,2,5)<>' '
/*Perform an update of DSDemand
set the field(column name) DepDemand to 53 characters long by include the data from the variable N
when variable N is equal to 1
This comes from the DSDString table
a where clause that only displays 32 characters from the data in the DemDDstring field starting from the left
the and clause to grap a portion of the DemDDString field from position 33 to 12
the and which is saying ReqData is equal to whatever is declared previously
the add to grab a portion of the DemDDstring from 2 to 5*/
UPDATE DSDemand
SET DepDemand=SUBSTRING(DemDDString,53 + (8*(@N)),8)+
CASE WHEN @N = 1
THEN SUBSTRING(DemDDString,45,8)
ELSE 0
END
FROM DSDString
WHERE Item = Left(DemDDString,32)
AND ShipTo = SubString(DemDDString,33,12)
AND ReqDate = @ReqD
AND SUBSTRING(DemDDString,2,5)<>' '
/*update the DSDemand table
set the field GrossReq String to a portion of the DemGRString field and the variable N
when N is equal to one grap a portion of the field DemGRString or else set it to zero
from the */
UPDATE DSDemand
SET GrossReq=SUBSTRING(DemGRString,53 + (8*(@N)),8)+
CASE WHEN @N = 1
THEN SUBSTRING(DemGRString,45,8)
ELSE 0
END
FROM DSGRString
WHERE Item = Left(DemGRString,32)
AND ShipTo = SubString(DemGRString,33,12)
AND ReqDate = @ReqD
AND SUBSTRING(DemGRString,2,5)<>' '
-- increment variable n by 1
SET @N = @N + 1
END
/*Update DSDemand table
set the field forecast to Gross - DepDemand - CustOrds
where GrossReg is greater or equal to DepDemand plus Custords*/
UPDATE DSDemand
SET Forecast = GrossReq - DepDemand - CustOrds
WHERE GrossReq >= DepDemand + Custords
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
November 9, 2011 at 6:18 am
It's iterating through weeks. 7 doesn't indicate June (not sure why it would, but it's commented that way), it's a multiple of a number of days based on the loop value. So, 7*0 days, then 7*1 days, then 7*2 days, etc., up through 7 * 12 days.
It then uses that to query and parse out a value from a table. I'd have to see what the values are in the table to tell you more about that, but what you could do is look at the table itself and probably figure out the business-data it is storing.
But the key thing is it loops through weeks.
Almost certainly, it could be rewritten to get all the data in one go, with either a calendar table or a numbers table. I'd need to see more of the database to be completely sure of that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 10, 2011 at 3:25 am
Hi GSquared
Many thanks for your reply. I'm in the process of trying to get a copy of the database(very frustrating)once i have it i'll upload it and maybe that will give you a better idea of what the script is doing.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
November 10, 2011 at 6:08 am
I wouldn't upload the database if I were you.
The usual thing is to post create scripts for the tables in the query, and a few rows worth of insert statements for each.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2011 at 8:42 am
What GSquared said.
FYI: Depending on the business, you could get fired for uploading a database onto the internet. Engage in CYA. Post sample tables and sample data insert scripts only for the things you need assistance with, and make sure to scrub your data before posting it.
November 11, 2011 at 4:15 pm
Post the Table DDL and sample data for "DSCOString" and "DSDemand" table DDL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply