easier way??
-
I'm running a stored proc to verify components are running. Basically, If certain tables and fields aren't getting updated in a certain amount of time on certain days, it send me an email. My problem is that I have a lot of code written, because depending on the functionality I'm monitoring the values that determin whether or not I should get paged changes. Here's a snippet of one of the pieces I did:
if (@ServerTimeDif > 15 and @Time >= 9 and @Time < 17 and @Day in (2,3,4,5,6)) or --M-F 9a-5p
(@ServerTimeDif > 30 and @Time >= 17 and @Time < 20 and @Day in (2,3,4,5,6)) or --M-F 5p-8p
(@ServerTimeDif > 45 and @Time >= 20 and @Time < 21 and @Day in (2,3,4,5,6)) or --M-F 8p-9p
(@ServerTimeDif > 60 and @Time >= 21 and @Time < 22 and @Day in (2,3,4,5,6)) or --M-F 9p-10p
(@ServerTimeDif > 30 and @Time >= 9 and @Time < 17 and @Day = 7) or --Sat 9a-5p
(@ServerTimeDif > 45 and @Time >= 17 and @Time < 20 and @Day = 7) or --Sat 5p-8p
(@ServerTimeDif > 75 and @Time >= 20 and @Time < 21 and @Day = 7) or --Sat 8p-9p
(@ServerTimeDif > 90 and @Time >= 21 and @Time < 22 and @Day = 7) or --Sat 9p-10p
(@ServerTimeDif > 90 and @Time >= 12 and @Time < 17 and @Day = 1) --Sun 12p-5p
begin
I have similar code for other pieces, but, like I said, the values are different. Is there an easier way to do this?
Thanks in advance.
Justin
-
Don't think so. Might nest the IF's to make it slightly cleaner, or space it out better, but I think this is as good as it gets.
-
I would think it would be possible to create a parm table with the different values.
Select yada
FROM parm_table
WHERE @time between time1 and time2
and @ServerTimeDif between svrTime1 and svrTime2
and '%,'+day+',%' like dayvalues
Or instead of SELECT yada if exists(select something ...)
cheers
-
Personally, I would use something like this structure (Sample code and test below):
Create Table ConditionChecks (
CheckID int identity,
CheckDescription varchar(250)) -- Just descriptions so you know what conditions mean what
GO
Create Table CheckTimeParameters (
CheckID int,
StartDay smallint,
EndDay smallint,
StartTime smallint,
CutoffTime smallint,
TimeDif smallint) -- The actual parameters needed for the checks.
GO
Create Index ix_Test ON CheckTimeParameters (CheckID, StartDay) -- Assumes that there will be many condition checks
GO
/* Now, let's set up a test of this. It will use the values in your original posting, e.g.:
if (@ServerTimeDif > 15 and @Time >= 9 and @Time < 17 and @Day in (2,3,4,5,6)) or --M-F 9a-5p
(@ServerTimeDif > 30 and @Time >= 17 and @Time < 20 and @Day in (2,3,4,5,6)) or --M-F 5p-8p
(@ServerTimeDif > 45 and @Time >= 20 and @Time < 21 and @Day in (2,3,4,5,6)) or --M-F 8p-9p
(@ServerTimeDif > 60 and @Time >= 21 and @Time < 22 and @Day in (2,3,4,5,6)) or --M-F 9p-10p
(@ServerTimeDif > 30 and @Time >= 9 and @Time < 17 and @Day = 7) or --Sat 9a-5p
(@ServerTimeDif > 45 and @Time >= 17 and @Time < 20 and @Day = 7) or --Sat 5p-8p
(@ServerTimeDif > 75 and @Time >= 20 and @Time < 21 and @Day = 7) or --Sat 8p-9p
(@ServerTimeDif > 90 and @Time >= 21 and @Time < 22 and @Day = 7) or --Sat 9p-10p
(@ServerTimeDif > 90 and @Time >= 12 and @Time < 17 and @Day = 1) --Sun 12p-5p
*/
Declare @CheckID int
Insert Into ConditionChecks (CheckDescription)
Values ('Test Conditions for Above Sample. Used in Stored Procedure Whatever')
Select @CheckID = Scope_identity()
Insert Into CheckTimeParameters (CheckID, StartDay, EndDay, StartTime, CutoffTime, TimeDif)
Values (@CheckID, 1, 1, 12, 17, 90)
Insert Into CheckTimeParameters (CheckID, StartDay, EndDay, StartTime, CutoffTime, TimeDif)
Values (@CheckID, 2, 6, 9, 17, 15)
Insert Into CheckTimeParameters (CheckID, StartDay, EndDay, StartTime, CutoffTime, TimeDif)
Values (@CheckID, 2, 6, 17, 20, 30)
Insert Into CheckTimeParameters (CheckID, StartDay, EndDay, StartTime, CutoffTime, TimeDif)
Values (@CheckID, 2, 6, 20, 21, 45)
Insert Into CheckTimeParameters (CheckID, StartDay, EndDay, StartTime, CutoffTime, TimeDif)
Values (@CheckID, 2, 6, 21, 22, 60)
Insert Into CheckTimeParameters (CheckID, StartDay, EndDay, StartTime, CutoffTime, TimeDif)
Values (@CheckID, 7, 7, 9, 17, 30)
Insert Into CheckTimeParameters (CheckID, StartDay, EndDay, StartTime, CutoffTime, TimeDif)
Values (@CheckID, 7, 7, 17, 20, 45)
Insert Into CheckTimeParameters (CheckID, StartDay, EndDay, StartTime, CutoffTime, TimeDif)
Values (@CheckID, 7, 7, 20, 21, 70)
Insert Into CheckTimeParameters (CheckID, StartDay, EndDay, StartTime, CutoffTime, TimeDif)
Values (@CheckID, 7, 7, 21, 22, 90)
Select *
From CheckTimeParameters -- Let's see the table. This is a test, after all
Declare @TimeDif smallint
Declare @Day smallint
Declare @Time smallint
-- Set test values here
Set @TimeDif = 50
Set @Day = 2
Set @Time = 20
-- End of Set Test Values
If Exists (
Select *
From CheckTimeParameters
WHERE CheckID = @CheckID
AND @Day >= StartDay
AND @Day <= EndDay
AND @Time >= StartTime
AND @Time < CutoffTime
AND @TimeDif > TimeDif
)
Begin
Select 'That''s it!!', @CheckID, @Day, @Time, @TimeDif
End
Else
Begin
Select 'Not that!', @CheckID, @Day, @Time, @TimeDif
END
Set @TimeDif = 45 -- Now test with a non-matching condition
If Exists (
Select *
From CheckTimeParameters
WHERE CheckID = @CheckID
AND @Day >= StartDay
AND @Day <= EndDay
AND @Time >= StartTime
AND @Time < CutoffTime
AND @TimeDif > TimeDif
)
Begin
Select 'That''s it!!', @CheckID, @Day, @Time, @TimeDif
End
Else
Begin
Select 'Not that!', @CheckID, @Day, @Time, @TimeDif
END
-- End of code -- Below is the output, without the '(1 row(s) affected)' from the insert statements --
CheckID StartDay EndDay StartTime CutoffTime TimeDif
-------- -------- -------- --------- ---------- -------
1 1 1 12 17 90
1 2 6 9 17 15
1 2 6 17 20 30
1 2 6 20 21 45
1 2 6 21 22 60
1 7 7 9 17 30
1 7 7 17 20 45
1 7 7 20 21 70
1 7 7 21 22 90
(9 row(s) affected)
-------- ----- ----- ------
1 2 20 50
(1 row(s) affected)
--------- -------- ----- ----- ------
That's it!! 1 2 20 50
(1 row(s) affected)
--------- ------- ----- ----- ------
Not that! 1 2 20 45
(1 row(s) affected)
-
Agree with Daryl.
If you code any knowledge you definitely do something wrong.
You need to put all values in database and reference it in your code.
_____________
Code for TallyGenerator -
Thanks, guys! It looks like I'll be creating a parameter/values table. Thanks for the help
Justin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply