Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-07-28 : 10:10:45
|
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) ASSELECT...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?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 <= @Date2My understanding of how SQL Server works is limited. The Server may actually be more efficient using the WHERE clause that was originally constructed.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.Thanks for the help in advance!~Joe |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-28 : 10:16:41
|
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) ASSELECT...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 hours2nd 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 <= @Date2My 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 everytime3rd 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 shiftThanks for the help in advance!~Joe
|
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-07-28 : 10:45:13
|
Thanks visakh16! |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-28 : 10:46:13
|
quote: Originally posted by jp2code @startDate + '00:00:00'
WTF???Apparently, Joe, the person who wrote this code knew no more than you, so don't put yourself down.e4 d5 xd5 Nf6 |
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-08-14 : 09:59:34
|
Oh, that is just the most basics of the F-ups in this SQL.For example: There's this view that combine the test results table with the employee name table. That's it! No biggie, right? Well, in another view, there is a line that compares the dates like TestResults.Date < vw_TestResults.Date.What the F is that?Here: Look at this Stored Procedure and tell me if you would be able to understand it:CREATE PROCEDURE ACPReporter_Special_BrazerReward_Report(@StartDate varchar(10), @EndDate varchar(10)) ASSELECT 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) ) ReportWHERE (ChamberPassRatio >= 150)ORDER BY (TotalJoints / DaysWorked * ChamberPassRatio / 10)GO |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-14 : 11:39:32
|
The use of correlated subqueries is king of sloppy, but nothing else jumps out at me as horrible in that code.Boycott Beijing Olympics 2008 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-14 : 11:40:27
|
Well, that and the "SELECT *" too.Boycott Beijing Olympics 2008 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-14 : 11:59:36
|
I cant see a difference between the DaysWorked and HoursWorked subqueries. E 12°55'05.25"N 56°04'39.16" |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-14 : 12:22:30
|
Doesn't look like HoursWorked is referenced anyway. Orphaned code.Boycott Beijing Olympics 2008 |
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-08-20 : 09:05:50
|
"blindman" is a "Flowing Fount of Yak Knowledge" who wants to "Boycott Beijing Olympics 2008".Ok, I'm going to add a signature right now to express some of my personal feelings, too! :) |
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-08-20 : 09:10:10
|
Ok! Profile has been updated to tell everyone to stay away from Sears Home Improvement and the link will tell them why. Avoid Sears Home Improvement |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-20 : 10:13:52
|
Well, I don't shop much at Sears anyway. And its not like Sears is executing political prisoners, repressing religious freedom, supporting despotic regimes, and destroying the environment, so I can't put them in the same league as the Chinese government.But since I'm sure Sears does sell a lot of things made in China, I do appreciate your support. Thanks.Boycott Beijing Olympics 2008 |
|
|
|
|
|
|
|