Solved

SQL procedure explanation -- Need help

Posted on 2014-11-04
2
368 Views
Last Modified: 2014-11-04
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

0
Comment
Question by:yguyon28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40421889
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
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40421905
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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 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