Solved

SQL procedure explanation -- Need help

Posted on 2014-11-04
2
352 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 46

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

896 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now