quote:

---

Originally posted by jp2code

I'm not the best at SQL. I know the INSERT, UPDATE, and DELETE, but I don't do too much other than that.

I've got a stored procedure I need to modify, though, and I do not fully understand what the procedure is doing now, particularly in the WHERE clause (which uses dateadd).

In the stored procedure:
sp_Shift_Info(@shift int, @startDate DateTime) AS
SELECT
...
WHERE
dateadd(hour, 9 * (@shift - 1), @startDate + '00:00:00')
<= dbo.Date_Time <=
dateadd([hour], 18 + (9 * (@shift - 1)), @startDate + '00:00:00')

1st question: Are hour and [hour] the same? Why were the two fields written different?
They are same. the first argument for dateadd function is date part to be added. here you're telling it to add in hours
2nd question: This WHERE clause is used three (3) times with three (3) tables that are UNIONed together. Would it be more efficient to declare two variables at the start that are used in the query? Then, the WHERE clause would simply be:
WHERE @Date1 <= dbo.Date_Time <= @Date2
My understanding of how SQL Server works is limited. The Server may actually be more efficient using the WHERE clause that was originally constructed.
Yup thats better rather than using the functions everytime
3rd question: I think I understand what the dateadd function is trying to do here, but I'm not sure. If @shift is 1 (First Shift), the middle term for dateadd is 0 and 18, respectively. This is probably done to ensure we catch those who work overtime shifts (longer than 8 hours), but is it really doing what I think it is? The original designer of this query has not been with the company for over 2 years, and I'm the closest thing we have to understanding what it is doing.
whats its doing is to see if Date_Time field is having a value which is between value of @startDate and 18 hours past @startDate (6pm) for the first shift
Thanks for the help in advance!
~Joe

---

quote:

---

Originally posted by jp2code

@startDate + '00:00:00'

---

CREATE  PROCEDURE ACPReporter_Special_BrazerReward_Report(@StartDate varchar(10), @EndDate varchar(10)) AS

SELECT 
	Operator, 
	OperatorID, 
	Shift, 
	TotalJoints, 
	DaysWorked, 
	ChamberPassRatio,
	(CASE 
		WHEN ChamberPassRatio / 10 < 150 THEN ''
		WHEN (TotalJoints / DaysWorked * ChamberPassRatio / 10) < 100000 THEN ''
		WHEN (TotalJoints / DaysWorked * ChamberPassRatio / 10) < 200000 THEN 'D1'
		WHEN (TotalJoints / DaysWorked * ChamberPassRatio / 10) < 300000 THEN 'D2'
		WHEN (TotalJoints / DaysWorked * ChamberPassRatio / 10) < 400000 THEN 'C1'
		WHEN (TotalJoints / DaysWorked * ChamberPassRatio / 10) < 500000 THEN 'C2'
		WHEN (TotalJoints / DaysWorked * ChamberPassRatio / 10) < 600000 THEN 'B1'
		WHEN (TotalJoints / DaysWorked * ChamberPassRatio / 10) < 700000 THEN 'B2'
		WHEN (TotalJoints / DaysWorked * ChamberPassRatio / 10) < 800000 THEN 'A1'
		ELSE 'A2'
		END) AS BrazeLevel		
FROM 
	(
		SELECT DISTINCT
			Results.Operator, 
			Results.OperatorID, 
			Results.SHIFT,
			(
				SELECT
					SUM(CI.BrazeJoint) AS TotalJoints
				FROM
					dbo.vw_CoilInfo CI Inner Join dbo.vw_DecayTests DT on CI.SN = DT.SN
				WHERE
					(DT.OperatorID = Results.OperatorID) AND 
					(DT.FirstTest = 1) AND 
					(DT.[DateTime] >= @StartDate + ' 00:00:00') AND 
					(DT.[DateTime] <=  @EndDate + ' 23:59:59')
			) As TotalJoints,
			(
				SELECT
					COUNT(DISTINCT CAST(MONTH(TR.Date_Time) AS varchar(2)) + '/' + CAST(DAY(TR.Date_Time) AS varchar(2)) + '/' + CAST(YEAR(TR.Date_Time) AS varchar(4))) AS DaysWorked
				FROM
					TestResults TR
		                WHERE
					TR.OP_id = Results.OperatorID AND 
					TR.Date_Time >= @StartDate + ' 00:00:00' AND 
					TR.Date_Time <= @EndDate + ' 23:59:59'
			) AS DaysWorked,
			(
				SELECT
					COUNT(DISTINCT CAST(MONTH(TR.Date_Time) AS varchar(2)) + '/' + CAST(DAY(TR.Date_Time) AS varchar(2)) + '/' + CAST(YEAR(TR.Date_Time) AS varchar(4))  ) AS DaysWorked
				FROM
					TestResults TR
		                WHERE
					TR.OP_id = Results.OperatorID AND 
					TR.Date_Time >= @StartDate + ' 00:00:00' AND 
					TR.Date_Time <= @EndDate + ' 23:59:59'
			) AS HoursWorked,
			(
				SELECT
					Tbl.ChamberPass  * 10000 / Tbl.ChamberTest AS ChamberPassRatio
				FROM
					(
						SELECT
							SUM(CASE WHEN CT.FirstTest = 1 THEN 1 ELSE 0 END) AS ChamberTest,
							COUNT(DISTINCT CASE WHEN CT.TestResult LIKE 'Pass%' THEN SN ELSE NULL END) AS ChamberPass
						FROM
							dbo.vw_ChamberTests CT
						WHERE
							CT.SN IN
							(
								SELECT DISTINCT 
									DT.SN
						                FROM
									dbo.vw_DecayTests DT
						                WHERE
									(DT.OperatorID = Results.OperatorID) AND 
									(DT.FirstTest = 1) AND 
									(DT.[DateTime] >= @StartDate + ' 00:00:00') AND 
									(DT.[DateTime] <= @EndDate + ' 23:59:59')
							)
					) Tbl
			) AS ChamberPassRatio
		FROM
			dbo.vw_DecayTests Results
		WHERE
			(Results.[DateTime] >= @StartDate + ' 00:00:00') AND 
			(Results.[DateTime] <= @EndDate + ' 23:59:59') AND 
			(Results.SN NOT IN (SELECT * FROM SNFilter)) AND 
			(Results.Operator IS NOT NULL) AND 
			(Results.OperatorID IS NOT NULL) AND 
			(Results.JobTitle = 'BRZ OPRTR') AND 
			(Results.FirstTest = 1) 
	) Report
WHERE 
	(ChamberPassRatio >= 150)
ORDER BY 
	(TotalJoints / DaysWorked * ChamberPassRatio / 10)
GO