Solved

SQL procedure explanation -- Need help

Posted on 2014-11-04
2
358 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
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 47

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

778 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