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
Solved

SQL procedure explanation -- Need help

Posted on 2014-11-04
2
359 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 48

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert null in sql server 12 46
SQL Error - Query 6 41
PolyServe for SQL server 13 32
Reformat SQL - so SSRS can read the columns 25 12
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
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…

856 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