SQL procedure explanation -- Need help

Could someone explain me this sql procedure bellow, such as step by step.

- [dbo].[Acct_RateCalculations]





DECLARE
 @Start_Date smalldatetime = '01/01/14'
,@End_Date as smalldatetime = '10/31/14'
,@Business_Unit as varchar(3) ='101' --'101' '103' '104' '106' '107' '110' '111'
,@BeginDate INT
,@EndDate INT
,@EndDateHRS DATETIME
,@Business_Unit2 VARCHAR(12)


SET @BeginDate = (select e1_integrate.dbo.ConvertToPeopleSoftDate(@Start_Date));
SET @EndDate = (select e1_integrate.dbo.ConvertToPeopleSoftDate(@End_Date));
SET @Business_Unit2 = (SELECT '         '+@Business_Unit)

SELECT DISTINCT file#
INTO #TEMP_FILE#
FROM Acct_RR_ADP_WData
WHERE [DATE] BETWEEN @Start_Date AND @End_Date
AND category = 5511 

SELECT DISTINCT O_ID 
INTO #TEMP_O_ID
FROM Acct_RR_ADP_ID_CrossRef
WHERE ADPnum IN (select file# from #TEMP_FILE#)



Select 
IWMCUW
,WTMCU
,WTHRW/100.00 AS 'HOURS'
,WTAN8
,WTTYR
,WTDOCO
,WTDGL
--		INTO #TEMP_WC_Hours_Details1
FROM PS_PROD.PRODDTA.F31122 WITH (NOLOCK)
INNER JOIN PS_PROD.PRODDTA.F30006 WITH (NOLOCK) on WTMCU = IWMCU AND WTMMCU = IWMMCU
WHERE PS_PROD.PRODDTA.F31122.WTDGL between @BeginDate and @EndDate
AND IWMCUW = @Business_Unit2
AND WTMMCU = '         108'
AND WTTYR = '1'
AND NOT WTHRW = 0
AND WTAN8 IN (SELECT O_ID FROM #TEMP_O_ID)

DROP TABLE #TEMP_FILE#;
DROP TABLE #TEMP_O_ID;

/*
-- REMOVE UNWANTED HOUR TYPES
IF @Business_Unit2 IN ('         103','         104')
	BEGIN
		DELETE FROM #TEMP_WC_Hours_Details1
		WHERE NOT WTTYR IN ('1','2')
	END
ELSE 
	BEGIN 
		DELETE FROM #TEMP_WC_Hours_Details1
		WHERE NOT WTTYR = '1'
	END


-- Calculates Detail Hours for the pressroom 106
Select 
 WTMCU
,WTHRW
,WTTYR
INTO #TEMP_WC_Hours_106D
FROM F31122 WITH (NOLOCK)
INNER JOIN F30006 WITH (NOLOCK) ON WTMCU = IWMCU AND WTMMCU = IWMMCU
WHERE F31122.WTDGL Between  @BeginDate and @EndDate
and IWMCUW = '         106' 
and WTTYR in (2,3,4)
AND NOT WTHRW = 0



-- CALCULATES DISTRIBUTION HOURS 107

SELECT 
 WTDOCO
,WTTYR
,WTMCU
,SUM(WTHRW) AS 'WTHRW'
--INTO #TEMP_DIST_HRS_107D		-- 140925 CHANGE
FROM F31122 WITH (NOLOCK)
WHERE WTDGL Between  @BeginDate and @EndDate
AND WTTYR in (2,3)
AND WTMCU IN ('    SHANKLIN','    HANAGATA','    HANDWORK')
GROUP BY WTMCU,WTDOCO,WTTYR

*/

Open in new window

yguyon28Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Declare a bunch of variables in memory
Set two variables to the results of a user-defined function.

Create local in-memory table #TEMP_FILE# based on a select query.
Create local in-memory table #TEMP_O_ID# based on a select query.

Select/return a big select query joining on multiple tables, with multiple filters (aka where)

Then delete the two in-memory # tables.

Everything between the /* */ is commented out and not executed.

If you'd like a more precise answer, then you'll have to ask a more precise question, as 'Here's a pile of T-SQL, tell me what this does' is not the most efficient use of an expert's time.

Hope this helps.
Jim
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
DECLARE section is where the variables are created.
SET section is where the variables are initialized. @BeginDate and @EndDate are initialized by using the function ConvertToPeopleSoftDate with parameters @Start_Date ('01/01/14') and @End_Date ('10/31/14') respectively. @Business_Unit2  has the same value of @Business_Unit ('101') with leading spaces ('         ').

Then information about Files and ID's are stored in temporary tables (#TEMP_FILE# and #TEMP_O_ID respectively) and that information are used with other tables to get some hour types. The script should ends here because after this the code is commented (between /* and */) but you still can read by the commentaries (--) what was doing:
-- REMOVE UNWANTED HOUR TYPES
-- Calculates Detail Hours for the pressroom 106
-- CALCULATES DISTRIBUTION HOURS 107
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.