-2

Can someone please help me rewrite this using a CASE (using easy to read syntax):

SELECT *
FROM TBL_A
WHERE COLUMN_A BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, +1, GETDATE()), 0) 
                   AND DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ishy
  • 9
  • 4
  • Do you have a column called `TABL_A` in `TABL_A`? And what is this query supposed to do – Code Different Sep 20 '18 at 17:37
  • You don't have a valid SQL query at all. If you don't have a query, you can't rewrite it to use a CASE. – Ken White Sep 20 '18 at 17:38
  • Just corrected the query (added the column name). sorry guys. – Ishy Sep 20 '18 at 17:40
  • 1
    When `Column_A` is between those two dates, what would want returned? A Case expression returns a value. Perhaps sample data and your desired results would help us understand better. – JNevill Sep 20 '18 at 17:43
  • Also "Easy to read syntax" is meaningless here. The only way you are going to get the results you are currently getting is with that messy `DateAdd(DateDiff(GetDate()))` logic. It's already pretty simple. – JNevill Sep 20 '18 at 17:45
  • @JNevill Thanks for the help! I don't really understand what the query is actually doing. Could you please explain? And is it possible to do this using a CASE? – Ishy Sep 20 '18 at 17:50
  • `CASE WHEN Column_A BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, +1, GETDATE()), 0) AND DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1) THEN 'SOMETHING' ELSE 'SOMETHINGELSE' END` would be a case statement that does this, but again it's not at all clear what you are wanting out of the case statement or why you think you should need one here. – JNevill Sep 20 '18 at 17:51
  • I'm also clueless what this is doing as this would just throw an error. What do you WANT it to do? like.. return all records between previous month and next month?? – JNevill Sep 20 '18 at 17:53
  • @JNevill Makes sense. Thank you :) Could you please explain what the query is actually doing? In very simple term please :) – Ishy Sep 20 '18 at 17:54
  • `DATEDIFF()` takes three arguments 1) a `datepart` like `month` which is correct here 2) a begin date, yet here you have `+1` which is definitely not a date and 3) an end date, you have a date here so it's good. It then returns the number of `dateparts` between those two dates. Yours though will error because the second parameter is wrong. – JNevill Sep 20 '18 at 17:55
  • So.. the only I thing I can say about what this query is actually doing is "Throws an error". (unless I'm misreading it although I'm certain someone else on here will be happy to tell me how wrong I am ;) – JNevill Sep 20 '18 at 18:01
  • @JNevill It doesn't give me an error. you've been helpful. Thank you! :) – Ishy Sep 20 '18 at 18:05
  • I'll be darned. It doesn't error. I've never seen a constant used as the second parameter for `datediff()` and I don't see where in the documentation this is legal. [see here for this in action](http://sqlfiddle.com/#!18/ba96d/3) proving me wrong. This query returns records where the `Column_A` is between the first of this month and the last of this month (essentially records for this current month). – JNevill Sep 20 '18 at 18:26
  • @JNevill Thanks for all of your help! – Ishy Sep 20 '18 at 20:11

2 Answers2

0

Creating a date dimension can really simplify stuff like this; especially if you want to add columns for fiscal month or whatever. Pulling the date labels or values from the table, assigning them to variables, and using those in your query. Easier and cleaner. Less room for error as well. Pretty sure this query is solid, but you should audit a few records if you decide to use it.

Here's the table:

CREATE TABLE dbo.Dates (
    DateId DATE NOT NULL CONSTRAINT PK_Dates PRIMARY KEY,
    StartDatetime DATETIME2(7) NOT NULL,
    EndDatetime DATETIME2(7) NOT NULL,
    DateFullName NVARCHAR(30) NOT NULL,
    DayOfWeekNumber TINYINT NOT NULL,
    DayOfWeekdayNumber TINYINT NOT NULL,
    DayCode INT NOT NULL,
    DayOFWeekNameShort NVARCHAR(4) NOT NULL,
    DayOFWeekNameLong NVARCHAR(9) NOT NULL,
    DayOfMonthNumber TINYINT NOT NULL,
    DayOfQuarterNumber TINYINT NOT NULL,
    DayOfYearNumber SMALLINT NOT NULL,
    WeekOfMonthNumber TINYINT NOT NULL,
    WeekOfQuarterNumber TINYINT NOT NULL,
    WeekOfYearNumber TINYINT NOT NULL,
    WeekStart DATE NOT NULL,
    WeekEnd DATE NOT NULL,
    WeekStartDatetime DATETIME2(7) NOT NULL,
    WeekEndDatetime DATETIME2(7) NOT NULL,
    WeekPercentComplete FLOAT NOT NULL,
    WeekPercentRemaining FLOAT NOT NULL,
    MonthNumber TINYINT NOT NULL,
    MonthCode INT NOT NULL,
    MonthNameLong NVARCHAR(9) NOT NULL,
    MonthNameShort NVARCHAR(9) NOT NULL,
    MonthYearNameLong NVARCHAR(15) NOT NULL,
    MonthYearNameShort NVARCHAR(15) NOT NULL,
    MonthStart DATE NOT NULL,
    MonthEnd DATE NOT NULL,
    MonthStartDatetime DATETIME2(7) NOT NULL,
    MonthEndDatetime DATETIME2(7) NOT NULL,
    MonthPercentComplete FLOAT NOT NULL,
    MonthPercentRemaining FLOAT NOT NULL,
    QuarterNumber TINYINT NOT NULL,
    QuarterStart DATE NOT NULL,
    QuarterEnd DATE NOT NULL,
    QuarterStartDatetime DATETIME2(7) NOT NULL,
    QuarterEndDatetime DATETIME2(7) NOT NULL,
    QuarterShort NVARCHAR(2) NOT NULL,
    QuarterLong NVARCHAR(9) NOT NULL,
    QuarterPercentComplete FLOAT NOT NULL,
    QuarterPercentRemaining FLOAT NOT NULL,
    YearNumber SMALLINT NOT NULL,
    YearStart DATE NOT NULL,
    YearEnd DATE NOT NULL,
    YearStartDatetime DATETIME2(7) NOT NULL,
    YearEndDatetime DATETIME2(7) NOT NULL,
    YearPercentComplete FLOAT NOT NULL,
    YearPercentRemaining FLOAT NOT NULL,
    WeekOfMonthOccurance TINYINT NOT NULL,
    WeekOfQuarterOccurance TINYINT NOT NULL,
    WeekOfYearOccurance TINYINT NOT NULL,
    IsWeekday TINYINT NOT NULL,
    IsWeekend TINYINT NOT NULL,
    IsHoliday TINYINT NOT NULL,
    HolidayName NVARCHAR(50) NULL,
    ModifiedDateTime DATETIME2(7) CONSTRAINT DF_Dates_ModifiedDateTime DEFAULT(SYSUTCDATETIME()) NOT NULL,
    [Version] ROWVERSION NOT NULL
    )
GO

CREATE UNIQUE NONCLUSTERED INDEX IX_Dates_StartDatetime_EndDatetime ON dbo.Dates (
    StartDatetime ASC,
    EndDatetime ASC
    )
GO

CREATE NONCLUSTERED INDEX IX_Dates_WeekStartDatetime_WeekEndDatetime ON dbo.Dates (
    WeekStartDatetime ASC,
    WeekEndDatetime ASC
    )
GO

CREATE NONCLUSTERED INDEX IX_Dates_MonthStartDatetime_MonthEndDatetime ON dbo.Dates (
    MonthStartDatetime ASC,
    MonthEndDatetime ASC
    )
GO

CREATE NONCLUSTERED INDEX IX_Dates_QuarterStartDatetime_QuarterEndDatetime ON dbo.Dates (
    QuarterStartDatetime ASC,
    QuarterEndDatetime ASC
    )
GO

CREATE NONCLUSTERED INDEX IX_Dates_YearStartDatetime_YearEndDatetime ON dbo.Dates (
    YearStartDatetime ASC,
    YearEndDatetime ASC
    )
GO

Here's the seed script:

SET DATEFIRST 7;

DECLARE @FDate DATE,
    @TDate DATE,
    @Now DATETIME2(7) = SYSUTCDATETIME();

/******************************************************************************
DATE RANGE TO BUILD TABLE DATA
*******************************************************************************/

SET @FDate = '1900-01-01';
SET @TDate = '2099-12-31';

/******************************************************************************
DECLARE TABLE VARIABLES FOR WEEK / MONTH / QUARTER DATA POINTS
*******************************************************************************/

DECLARE @OccuranceMonth TABLE (
    FDate INT NOT NULL,
    TDate INT NOT NULL,
    Occurance INT NOT NULL,
    UNIQUE CLUSTERED (FDate,TDate)
);

INSERT INTO @OccuranceMonth
VALUES
(1,7,1),
(8,14,2),
(15,21,3),
(22,28,4),
(29,31,5);

DECLARE @OccuranceQuarter TABLE (
    FDate INT NOT NULL,
    TDate INT NOT NULL,
    Occurance INT NOT NULL,
    UNIQUE CLUSTERED (FDate,TDate)
);

INSERT INTO @OccuranceQuarter
VALUES
(1,7,1),
(8,14,2),
(15,21,3),
(22,28,4),
(29,35,5),
(36,42,6),
(43,49,7),
(50,56,8),
(57,63,9),
(64,70,10),
(71,77,11),
(78,84,12),
(85,91,13),
(92,98,14);

DECLARE @OccuranceYear TABLE (
    FDate INT NOT NULL,
    TDate INT NOT NULL,
    Occurance INT NOT NULL,
    UNIQUE CLUSTERED (FDate,TDate)
);

INSERT INTO @OccuranceYear
VALUES
(1,7,1),
(8,14,2),
(15,21,3),
(22,28,4),
(29,35,5),
(36,42,6),
(43,49,7),
(50,56,8),
(57,63,9),
(64,70,10),
(71,77,11),
(78,84,12),
(85,91,13),
(92,98,14),
(99,105,15),
(106,112,16),
(113,119,17),
(120,126,18),
(127,133,19),
(134,140,20),
(141,147,21),
(148,154,22),
(155,161,23),
(162,168,24),
(169,175,25),
(176,182,26),
(183,189,27),
(190,196,28),
(197,203,29),
(204,210,30),
(211,217,31),
(218,224,32),
(225,231,33),
(232,238,34),
(239,245,35),
(246,252,36),
(253,259,37),
(260,266,38),
(267,273,39),
(274,280,40),
(281,287,41),
(288,294,42),
(295,301,43),
(302,308,44),
(309,315,45),
(316,322,46),
(323,329,47),
(330,336,48),
(337,343,49),
(344,350,50),
(351,357,51),
(358,364,52),
(365,371,53);

/******************************************************************************
CLEAR TABLE - YOU COULD CHANGE THE WHOLE THING INTO A MERGE
*******************************************************************************/

TRUNCATE TABLE dbo.Dates;

/******************************************************************************
USE RECURSION INSTEAD OF LOOP FOR DATE RANGE BASE
*******************************************************************************/

WITH DATES AS (
    SELECT
        @FDate AS DateId
    UNION ALL
    SELECT
        DATEADD(DD,1,DateId) AS DateId
    FROM DATES
    WHERE DATEADD(DD,1,DateId) <= @TDate
)

/******************************************************************************
INSERT FINAL RESULTS
*******************************************************************************/

INSERT INTO dbo.Dates (
    DateId,
    StartDatetime,
    EndDatetime,
    DateFullName,
    DayOfWeekNumber,
    DayOfWeekdayNumber,
    DayCode,
    DayOFWeekNameShort,
    DayOFWeekNameLong,
    DayOfMonthNumber,
    DayOfQuarterNumber,
    DayOfYearNumber,
    WeekOfMonthNumber,
    WeekOfQuarterNumber,
    WeekOfYearNumber,
    WeekStart,
    WeekEnd,
    WeekStartDatetime,
    WeekEndDatetime,
    WeekPercentComplete,
    WeekPercentRemaining,
    MonthNumber,
    MonthCode,
    MonthNameLong,
    MonthNameShort,
    MonthYearNameLong,
    MonthYearNameShort,
    MonthStart,
    MonthEnd,
    MonthStartDatetime,
    MonthEndDatetime,
    MonthPercentComplete,
    MonthPercentRemaining,
    QuarterNumber,
    QuarterStart,
    QuarterEnd,
    QuarterStartDatetime,
    QuarterEndDatetime,
    QuarterShort,
    QuarterLong,
    QuarterPercentComplete,
    QuarterPercentRemaining,
    YearNumber,
    YearStart,
    YearEnd,
    YearStartDatetime,
    YearEndDatetime,
    YearPercentComplete,
    YearPercentRemaining,
    WeekOfMonthOccurance,
    WeekOfQuarterOccurance,
    WeekOfYearOccurance,
    IsWeekday,
    IsWeekend,
    IsHoliday,
    HolidayName
    )
SELECT
    dte.DateId,
    CAST(dte.DateId AS DATETIME2(7)) AS StartDatetime,
    CAST(CAST(dte.DateId AS NVARCHAR(10)) + ' 23:59:59.9999999' AS DATETIME2(7)) AS EndDatetime,
    DATENAME(MONTH,dte.DateId) + ' ' + CAST(DAY(dte.DateId) AS NVARCHAR(2)) + ', ' + CAST(YEAR(dte.DateId) AS NVARCHAR(4)) AS DateFullName,
    DATEPART(DW,dte.DateId) AS DayOfWeekNumber,
    REPLACE(DATEPART(DW,dte.DateId) - 1, 6, 0) AS DayOfWeekdayNumber,
    CAST(CAST(YEAR(dte.DateId) AS NVARCHAR(4)) + 
        CASE WHEN LEN(MONTH(dte.DateId)) = 1 THEN '0' + CAST(MONTH(dte.DateId) AS NVARCHAR(2)) ELSE CAST(MONTH(dte.DateId) AS NVARCHAR(2)) END + 
        CASE WHEN LEN(DAY(dte.DateId)) = 1 THEN '0' + CAST(DAY(dte.DateId) AS NVARCHAR(2)) ELSE CAST(DAY(dte.DateId) AS NVARCHAR(2)) END AS INT) AS DayCode,
    CASE DATENAME(WEEKDAY,dte.DateId)
        WHEN 'Thursday' THEN 'Thur'
        ELSE LEFT(DATENAME(WEEKDAY,dte.DateId),3)
    END AS DayOFWeekNameShort,
    DATENAME(WEEKDAY,dte.DateId) AS DayOFWeekNameLong,
    DAY(dte.DateId) AS DayOfMonthNumber,
    DATEDIFF(d,DATEADD(qq,DATEDIFF(qq,0,dte.DateId),0),dte.DateId) + 1 AS DayOfQuarterNumber,
    DATEPART(dy,dte.DateId) AS DayOfYearNumber,
    DATEPART(WEEK,dte.DateId) - DATEPART(WEEK,DATEADD(MM,DATEDIFF(MM,0,dte.DateId),0)) + 1 AS WeekOfMonthNumber,
    DATEDIFF(WEEK,DATEADD(QUARTER,DATEDIFF(QUARTER,0,dte.DateId),0),dte.DateId) + 1 AS WeekOfQuarterNumber,
    DATEPART(WEEK,dte.DateId) AS WeekOfYearNumber,
    DATEADD(DD,-DATEPART(DW,dte.DateId) + 1,dte.DateId) AS WeekStart,
    DATEADD(DD,-DATEPART(DW,dte.DateId) + 7,dte.DateId) AS WeekEnd,
    CAST(DATEADD(DD,-DATEPART(DW,dte.DateId) + 1,dte.DateId) AS DATETIME) AS WeekStartDatetime,
    CAST(CAST(DATEADD(DD,-DATEPART(DW,dte.DateId) + 7,dte.DateId) AS NVARCHAR(10)) + ' 23:59:59.9999999' AS DATETIME2(7)) AS WeekEndDatetime,
    CAST(DATEPART(DW,dte.DateId) AS FLOAT) / 7 AS WeekPercentComplete,
    1 - CAST(DATEPART(DW,dte.DateId) AS FLOAT) / 7 AS WeekPercentRemaining,
    MONTH(dte.DateId) AS MonthNumber,
    CAST(CAST(YEAR(dte.DateId) AS NVARCHAR(4)) + CASE WHEN LEN(MONTH(dte.DateId)) = 1 THEN '0' + CAST(MONTH(dte.DateId) AS NVARCHAR(2)) ELSE CAST(MONTH(dte.DateId) AS NVARCHAR(2)) END AS INT) AS MonthCode,
    DATENAME(MONTH,dte.DateId) AS MonthNameLong,
    CASE 
        WHEN LEN(DATENAME(MONTH,dte.DateId)) = 4 THEN DATENAME(MONTH,dte.DateId)
        WHEN DATENAME(MONTH,dte.DateId) = 'September' THEN 'Sept'
        ELSE LEFT(DATENAME(MONTH,dte.DateId),3)
    END AS MonthNameShort,
    DATENAME(MONTH,dte.DateId) + ' ' + CAST(YEAR(dte.DateId) AS NVARCHAR(4)) AS MonthYearNameLong,
    CASE 
        WHEN LEN(DATENAME(MONTH,dte.DateId)) = 4 THEN DATENAME(MONTH,dte.DateId)
        WHEN DATENAME(MONTH,dte.DateId) = 'September' THEN 'Sept'
        ELSE LEFT(DATENAME(MONTH,dte.DateId),3)
    END + ' ' + CAST(YEAR(dte.DateId) AS NVARCHAR(4)) AS MonthYearNameShort,
    CAST(CAST(MONTH(dte.DateId) AS NVARCHAR(2)) + '-01-' + CAST(YEAR(dte.DateId) AS NVARCHAR(4)) AS DATE) AS MonthStart,
    DATEADD(DD,-1,DATEADD(MONTH,1,CAST(CAST(MONTH(dte.DateId) AS NVARCHAR(2)) + '-01-' + CAST(YEAR(dte.DateId) AS NVARCHAR(4)) AS DATE))) AS MonthEnd,
    CAST(CAST(CAST(MONTH(dte.DateId) AS NVARCHAR(2)) + '-01-' + CAST(YEAR(dte.DateId) AS NVARCHAR(4)) AS DATE) AS DATETIME) AS MonthStartDatetime,
    CAST(CAST(DATEADD(DD,-1,DATEADD(MONTH,1,CAST(CAST(MONTH(dte.DateId) AS VARCHAR(2)) + '-01-' + CAST(YEAR(dte.DateId) AS VARCHAR(4)) AS DATE))) AS NVARCHAR(10)) + ' 23:59:59.9999999' AS DATETIME2(7)) AS MonthEndDatetime,
    CAST(DAY(dte.DateId) AS FLOAT) / CAST(DAY(DATEADD(DD,-1,DATEADD(MONTH,1,CAST(CAST(MONTH(dte.DateId) AS NVARCHAR(2)) + '-01-' + CAST(YEAR(dte.DateId) AS NVARCHAR(4)) AS DATE)))) AS FLOAT) AS MonthPercentComplete,
    1 - (CAST(DAY(dte.DateId) AS FLOAT) / CAST(DAY(DATEADD(DD,-1,DATEADD(MONTH,1,CAST(CAST(MONTH(dte.DateId) AS NVARCHAR(2)) + '-01-' + CAST(YEAR(dte.DateId) AS NVARCHAR(4)) AS DATE)))) AS FLOAT)) AS MonthPercentRemaining,
    DATENAME(QQ,dte.DateId) AS QuarterNumber,
    CAST(DATEADD(q,DATEDIFF(q,0,dte.DateId),0) AS DATE) AS QuarterStart,
    CAST(DATEADD(d,-1,DATEADD(q,DATEDIFF(q,0,dte.DateId)+1,0)) AS DATE) AS QuarterEnd,
    CAST(CAST(DATEADD(q,DATEDIFF(q,0,dte.DateId),0) AS DATE) AS DATETIME) AS QuarterStartDatetime,
    CAST(CAST(CAST(DATEADD(d,-1,DATEADD(q,DATEDIFF(q,0,dte.DateId)+1,0)) AS DATE) AS NVARCHAR(10)) + ' 23:59:59.9999999' AS DATETIME2(7)) AS QuarterEndDatetime,
    'Q' + DATENAME(QQ,dte.DateId) AS QuarterShort,
    'Quarter ' + DATENAME(QQ,dte.DateId) AS QuarterLong,
    CAST(DATEDIFF(d,DATEADD(qq,DATEDIFF(qq,0,dte.DateId),0),dte.DateId) + 1 AS FLOAT) / CAST(DATEDIFF(DD,CAST(DATEADD(q,DATEDIFF(q,0,dte.DateId),0) AS DATE),CAST(DATEADD(d,-1,DATEADD(q,DATEDIFF(q,0,dte.DateId)+1,0)) AS DATE)) + 1 AS FLOAT) AS QuarterPercentComplete,
    1 - (CAST(DATEDIFF(d,DATEADD(qq,DATEDIFF(qq,0,dte.DateId),0),dte.DateId) + 1 AS FLOAT) / CAST(DATEDIFF(DD,CAST(DATEADD(q,DATEDIFF(q,0,dte.DateId),0) AS DATE),CAST(DATEADD(d,-1,DATEADD(q,DATEDIFF(q,0,dte.DateId)+1,0)) AS DATE)) + 1 AS FLOAT)) AS QuarterPercentRemaining,
    YEAR(dte.DateId) AS YearNumber,
    CAST(CAST(YEAR(dte.DateId) AS NVARCHAR(4)) + '-01-01' AS DATE) AS YearStart,
    CAST(CAST(YEAR(dte.DateId) AS NVARCHAR(4)) + '-12-31' AS DATE) AS YearEnd,
    CAST(CAST(CAST(YEAR(dte.DateId) AS NVARCHAR(4)) + '-01-01' AS DATE) AS DATETIME) AS YearStartDatetime,
    CAST(CAST(CAST(CAST(YEAR(dte.DateId) AS NVARCHAR(4)) + '-12-31' AS DATE) AS NVARCHAR(10)) + ' 23:59:59.9999999' AS DATETIME2(7)) AS YearEndDatetime,
    CAST(DATEPART(dy,dte.DateId) AS FLOAT) / CAST(DATEDIFF(DD,CAST(CAST(YEAR(dte.DateId) AS NVARCHAR(4)) + '-01-01' AS DATE),CAST(CAST(YEAR(dte.DateId) AS NVARCHAR(4)) + '-12-31' AS DATE)) + 1 AS FLOAT) AS YearPercentComplete,
    1 - (CAST(DATEPART(dy,dte.DateId) AS FLOAT) / CAST(DATEDIFF(DD,CAST(CAST(YEAR(dte.DateId) AS NVARCHAR(4)) + '-01-01' AS DATE),CAST(CAST(YEAR(dte.DateId) AS NVARCHAR(4)) + '-12-31' AS DATE)) + 1 AS FLOAT)) AS YearPercentRemaining,
    a.Occurance AS WeekOfMonthOccurance,
    b.Occurance AS WeekOfQuarterOccurance,
    c.Occurance AS WeekOfYearOccurance,
    CASE
        WHEN DATEPART(DW,dte.DateId) BETWEEN 2 AND 6 THEN 1
        ELSE 0
    END AS IsWeekday,
    CASE
        WHEN DATEPART(DW,dte.DateId) IN (1,7) THEN 1
        ELSE 0
    END AS IsWeekend,
    0 AS IsHoliday,
    NULL AS HolidayName
FROM DATES AS dte
LEFT JOIN @OccuranceMonth AS a
    ON 1 = 1
    AND DAY(dte.DateId) BETWEEN a.FDate AND a.TDate
LEFT JOIN @OccuranceQuarter AS b
    ON 1 = 1
    AND DATEDIFF(d,DATEADD(qq,DATEDIFF(qq,0,dte.DateId),0),dte.DateId) + 1 BETWEEN b.FDate AND b.TDate
LEFT JOIN @OccuranceYear AS c
    ON 1 = 1
    AND DATEPART(dy,dte.DateId) BETWEEN c.FDate AND c.TDate
OPTION (MAXRECURSION 0);

/******************************************************************************
HOLIDAY FLAGGING
*******************************************************************************/

-- New Year's Day

UPDATE dte
SET dte.IsHoliday = 1,
    dte.HolidayName = 'New Year''s Day',
    dte.ModifiedDateTime = @Now
FROM dbo.Dates AS dte
WHERE MONTH(dte.DateId) = 1
    AND DAY(dte.DateId) = 1
    AND dte.IsHoliday = 0;

-- President's Day

UPDATE dte
SET dte.IsHoliday = 1,
    dte.HolidayName = 'President''s Day',
    dte.ModifiedDateTime = @Now
FROM dbo.Dates AS dte
WHERE MONTH(dte.DateId) = 2
    AND dte.WeekOfMonthOccurance = 3
    AND dte.DayOfWeekNumber = 2
    AND dte.IsHoliday = 0;

-- Memorial Day

UPDATE dte
SET dte.IsHoliday = 1,
    dte.HolidayName = 'Memorial Day',
    dte.ModifiedDateTime = @Now
FROM dbo.Dates AS dte
WHERE MONTH(dte.DateId) = 5
    AND DAY(dte.DateId) BETWEEN 25 AND 31
    AND dte.DayOfWeekNumber = 2
    AND dte.IsHoliday = 0;

-- Independence Day

UPDATE dte
SET dte.IsHoliday = 1,
    dte.HolidayName = 'Independence Day',
    dte.ModifiedDateTime = @Now
FROM dbo.Dates AS dte
WHERE MONTH(dte.DateId) = 7
    AND DAY(dte.DateId) = 4
    AND dte.IsHoliday = 0;

-- Labor Day

UPDATE dte
SET dte.IsHoliday = 1,
    dte.HolidayName = 'Labor Day',
    dte.ModifiedDateTime = @Now
FROM dbo.Dates AS dte
WHERE MONTH(dte.DateId) = 9
    AND dte.DayOfWeekNumber = 2
    AND dte.WeekOfMonthOccurance = 1
    AND dte.IsHoliday = 0;

--Veteran's Day

UPDATE dte
SET dte.IsHoliday = 1,
    dte.HolidayName = 'Veteran''s Day',
    dte.ModifiedDateTime = @Now
FROM dbo.Dates AS dte
WHERE MONTH(dte.DateId) = 11
    AND DAY(dte.DateId) = 11
    AND dte.IsHoliday = 0;

-- Thanksgiving Day

UPDATE dte
SET dte.IsHoliday = 1,
    dte.HolidayName = 'Thanksgiving Day',
    dte.ModifiedDateTime = @Now
FROM dbo.Dates AS dte
WHERE MONTH(dte.DateId) = 11
    AND dte.WeekOfMonthOccurance = 4
    AND dte.DayOfWeekNumber = 5
    AND dte.IsHoliday = 0;

-- Black Friday

UPDATE dte
SET dte.IsHoliday = 1,
    dte.HolidayName = 'Black Friday',
    dte.ModifiedDateTime = @Now
FROM dbo.Dates AS dte
WHERE MONTH(dte.DateId) = 11
    AND dte.WeekOfMonthOccurance = 4
    AND dte.DayOfWeekNumber = 6
    AND dte.IsHoliday = 0;

-- Christmas Day'

UPDATE dte
SET dte.IsHoliday = 1,
    dte.HolidayName = 'Christmas Day',
    dte.ModifiedDateTime = @Now
FROM dbo.Dates AS dte
WHERE MONTH(dte.DateId) = 12
    AND DAY(dte.DateId) = 25
    AND dte.IsHoliday = 0;

Here are some sample scripts (there are also columns with start and end datetime for each day / week / month / year):

DECLARE @BaseDate DATE,
    @FromDate DATE,
    @ToDate DATE;

SET @BaseDate = SYSUTCDATETIME();

/******************************************************************************
THIS IS WHERE YOU WOULD FETCH A SPECIFIC DATE TO GET ALL KINDS OF INFO ON IT.
A DATE DIMENSION IS ESPECIALLY USEFUL WHEN CREATING REPORTS.
*******************************************************************************/

SELECT
    @FromDate = dte.MonthStart,
    @ToDate = dte.MonthEnd
FROM dbo.Dates AS dte
WHERE dte.DateId = @BaseDate;

SELECT *
FROM TBL_A
WHERE COLUMN_A BETWEEN @FromDate AND @ToDate;
Utrolig
  • 251
  • 2
  • 13
0

Using a case expression in that where clause is just not going to make it any easier to read. You will need to become familiar with date handling in T-SQL which includes the dateadd() and datediff() functions. Also, getdate() which is a system function that returns the current date and time from the server.

Try running this first:

select dateadd(day,0,0) [Zero Point of Zero Date in TSQL]

= 1900-01-01 00:00:00+00000

In T-SQL all dates/times are measured from 1st of January 1900, so that select returns: because we have added zero days to the zero date with this: dateadd(day,0,0)

So now you know what the zero date is, comparing that to the current date & time can be used to calculate other useful dates, such as the first day of the current month. Try this:

SELECT DATEDIFF(MONTH, 0, GETDATE()) [Number of Months From Date Zero] 

= 1424

This returns an integer being the number of Months since zero. In your initial query it uses +1 instead of zero, but it still returns the same number of months.

SELECT DATEDIFF(MONTH, +1, GETDATE()) [Number of Months From Date One] 

= 1424

Now, if we add 1424 Months to 1900-01-01 (which is the "zero date") we get an interesting result, it is the first day of the current month

select DATEADD(MONTH, DATEDIFF(MONTH, +1, GETDATE()), 0)  [1st Day of Current Month]

= 2018-09-01 00:00:00+00000

Now, running the following is another interesting date:

select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1) [Last Day of Current Month]

= 2018-09-30 00:00:00+00000

So, your query equates to this:

SELECT *
FROM TBL_A
WHERE COLUMN_A BETWEEN "1st Day of Current Month"
                   AND "Last Day of Current Month"

I can't leave this unsaid, I dislike the way your current query works because "Last Day of Current Month" is NOT the actual end of the month which is 24 hours after 2018-09-30 00:00:00+00000

There is a far better way to accurately define the wanted date range (in my opinion):

select
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)   [Current Month Starts At]
, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0) [Current Month Finishes At]

= 2018-09-01 00:00:00+00000 2018-10-01 00:00:00+00000

and I would rewrite your query as:

SELECT *
FROM TBL_A
WHERE COLUMN_A >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)   -- "Current Month Starts At"
  AND COLUMN_A  < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0) -- "Current Month Finishes At"

This approach will select all data for the current month regardless of time precision in COLUMN_A (i.e. data types: date, smalldatetime, datetime or datetime2)

Most queries listed above can be run here: http://rextester.com/CTQ4148

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51