HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Execute every Monday that is not the Monday following last Saturday in month

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
lasttheeveryfollowingthatmonthmondaysaturdaynotexecute

Problem

I think the following is good but wondering if it can be simplified or improved.

My condition for code execution is the following:

It is a Monday
AND
It is not the Monday following the last Saturday in the month.

This is the current sql:

DECLARE @now DATETIME = DATEADD(dd,DATEDIFF(dd,'19000101',SYSDATETIME()),'19000101');

IF (
        (--is it a monday?
            SELECT DATEPART(dw,@now)
        ) = 2  

        AND

        (--is it not the monday following the last Saturday of the month?
            (
            SELECT DATEDIFF(
                            DAY,
                            @now-2, 
                            DATEADD(MONTH,DATEDIFF(MONTH,0,@now-2)+1,0)-1 
                        )
            ) > 6
        )
    )
BEGIN;

  ...
  ...

END;

Solution

Sometimes, comments aren't the clearest way to make a script readable:

IF (
        (--is it a monday?
            SELECT DATEPART(dw,@now)
        ) = 2


Consider:

declare @MONDAY int;
set @MONDAY = 2;

if (datepart(weekday,@now) = @MONDAY)


Does one need a comment to figure out what's going on? I hope not!

This part however:

DECLARE @now DATETIME = DATEADD(dd,DATEDIFF(dd,'19000101',SYSDATETIME()),'19000101');


If it wasn't of the @now identifier, I'd wonder what this convoluted assignment intends to do.

Why not just do this instead?

declare @now date = cast(getdate() as date);


I mean, why work with a datetime when clearly you're only interested in the date part from that point on?

I find there's way too much whitespace in your code, you need to strike a balance between wall-of-code and air code.

Now, changing @now from a datetime to a date will break your code. The @now-2 here:

DATEDIFF(DAY, @now-2, DATEADD(MONTH,DATEDIFF(MONTH,0,@now-2)+1,0)-1


...is illegal because...

I would extract that bit of logic into its own variable, for readability's sake, and using a dateadd function there would be no issue with subtracting 2 days from that date.

Code Snippets

IF (
        (--is it a monday?
            SELECT DATEPART(dw,@now)
        ) = 2
declare @MONDAY int;
set @MONDAY = 2;

if (datepart(weekday,@now) = @MONDAY)
DECLARE @now DATETIME = DATEADD(dd,DATEDIFF(dd,'19000101',SYSDATETIME()),'19000101');
declare @now date = cast(getdate() as date);
DATEDIFF(DAY, @now-2, DATEADD(MONTH,DATEDIFF(MONTH,0,@now-2)+1,0)-1

Context

StackExchange Code Review Q#114661, answer score: 7

Revisions (0)

No revisions yet.