Sql-server – TSQL – use columns as date range

sql-server-2008-r2t-sql

How can I retrieve the cto column using an @variable?

For example, for @Variable = '2019-03-02' the expected output is'MAR 1 - MAR 7'.

Best Answer

It's kind of a barebone question, but...

@DateToUse is the date you are searching for.

@StringOutput is the cto column.

I'm using the ID field as a tie-breaker in order to protect against duplicate or overlapping ranges in from/to fields.

DECLARE @DateToUse DATE
DECLARE @StringOutput VARCHAR(50)

SET @DateToUse = '3/2/2019'

SET @StringOutput = (SELECT TOP (1) cto FROM dbo.MyTable WHERE @DateToUse BETWEEN from AND to ORDER BY ID)

PRINT @StringOutput

Related Solutions

SQL Server – Transpose Multiple Columns to Single Row

There's a T-SQL construct called UNPIVOT which is designed for this. But in my experience, you'll have more luck with APPLY as per my code below:

SELECT *
FROM YourTable t
CROSS APPLY
    (SELECT 'Col1', Column1
    UNION ALL
    SELECT 'Col2', Column2
    UNION ALL
    SELECT 'Col3', Column3
    UNION ALL
    SELECT 'Col4', Column4
   ) u (columnName, Value)
  ;

SQL Server – How to Use COLUMNS_UPDATED to Check Column Updates

You can use CHECKSUM() as a fairly simple methodology for comparing actual values to see if they were changed. CHECKSUM() will generate a checksum across a list of passed-in values, of which the number and type are indeterminate. Beware, there is a small chance comparing checksums like this will result in false negatives. If you cannot deal with that, you can use HASHBYTES instead¹.

The example below uses an AFTER UPDATE trigger to retain a history of modifications made to the TriggerTest table only if either of the values in the Data1 or Data2 columns change. If Data3 changes, no action is taken.

USE tempdb;
IF COALESCE(OBJECT_ID('dbo.TriggerTest'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerTest;
END
CREATE TABLE dbo.TriggerTest
(
    TriggerTestID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Data1 VARCHAR(10) NULL
    , Data2 VARCHAR(10) NOT NULL
    , Data3 DATETIME NOT NULL
);

IF COALESCE(OBJECT_ID('dbo.TriggerResult'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerResult;
END
CREATE TABLE dbo.TriggerResult
(
    TriggerTestID INT NOT NULL
    , Data1OldVal VARCHAR(10) NULL
    , Data1NewVal VARCHAR(10) NULL
    , Data2OldVal VARCHAR(10) NULL
    , Data2NewVal VARCHAR(10) NULL
);

GO
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0 
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS 
BEGIN
    INSERT INTO TriggerResult
    (
        TriggerTestID
        , Data1OldVal
        , Data1NewVal
        , Data2OldVal
        , Data2NewVal
    )
    SELECT d.TriggerTestID
        , d.Data1
        , i.Data1
        , d.Data2
        , i.Data2
    FROM inserted i 
        LEFT JOIN deleted d ON i.TriggerTestID = d.TriggerTestID
    WHERE CHECKSUM(i.Data1, i.Data2) <> CHECKSUM(d.Data1, d.Data2);
END
GO

INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
VALUES ('blah', 'foo', GETDATE());

UPDATE dbo.TriggerTest 
SET Data1 = 'blah', Data2 = 'fee' 
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult

If you're insistent on using the COLUMNS_UPDATED() function, you should not hard-code the ordinal value of the columns in question, since the table definition may change, which may invalidate hard-coded value(s). You can calculate what the value should be at runtime using the system tables. Be aware that the COLUMNS_UPDATED() function returns true for the given column bit if the column is modified in ANY row affected by the UPDATE TABLE statement.

USE tempdb;
IF COALESCE(OBJECT_ID('dbo.TriggerTest'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerTest;
END
CREATE TABLE dbo.TriggerTest
(
    TriggerTestID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Data1 VARCHAR(10) NULL
    , Data2 VARCHAR(10) NOT NULL
    , Data3 DATETIME NOT NULL
);

IF COALESCE(OBJECT_ID('dbo.TriggerResult'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerResult;
END
CREATE TABLE dbo.TriggerResult
(
    TriggerTestID INT NOT NULL
    , Data1OldVal VARCHAR(10) NULL
    , Data1NewVal VARCHAR(10) NULL
    , Data2OldVal VARCHAR(10) NULL
    , Data2NewVal VARCHAR(10) NULL
);

GO
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0 
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS 
BEGIN
    DECLARE @ColumnOrdinalTotal INT = 0;

    SELECT @ColumnOrdinalTotal = @ColumnOrdinalTotal 
        + POWER (
                2 
                , COLUMNPROPERTY(t.object_id,c.name,'ColumnID') - 1
            )
    FROM sys.schemas s
        INNER JOIN sys.tables t ON s.schema_id = t.schema_id
        INNER JOIN sys.columns c ON t.object_id = c.object_id
    WHERE s.name = 'dbo'
        AND t.name = 'TriggerTest'
        AND c.name IN (
            'Data1'
            , 'Data2'
        );

    IF (COLUMNS_UPDATED() & @ColumnOrdinalTotal) > 0
    BEGIN
        INSERT INTO TriggerResult
        (
            TriggerTestID
            , Data1OldVal
            , Data1NewVal
            , Data2OldVal
            , Data2NewVal
        )
        SELECT d.TriggerTestID
            , d.Data1
            , i.Data1
            , d.Data2
            , i.Data2
        FROM inserted i 
            LEFT JOIN deleted d ON i.TriggerTestID = d.TriggerTestID;
    END
END
GO

--this won't result in rows being inserted into the history table
INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
VALUES ('blah', 'foo', GETDATE());

SELECT *
FROM dbo.TriggerResult;

--this will insert rows into the history table
UPDATE dbo.TriggerTest 
SET Data1 = 'blah', Data2 = 'fee' 
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

--this WON'T insert rows into the history table
UPDATE dbo.TriggerTest 
SET Data3 = GETDATE()
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult

--this will insert rows into the history table, even though only
--one of the columns was updated
UPDATE dbo.TriggerTest 
SET Data1 = 'blum' 
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

This demo inserts rows into the history table that perhaps should not be inserted. The rows have had their Data1 column updated for some rows, and have had the Data3 column updated for some rows. Since this is a single statement, all rows are processed by a single pass through the trigger. Since some rows have Data1 updated, which is part of the COLUMNS_UPDATED() comparison, all rows seen by the trigger are inserted into the TriggerHistory table. If this is "incorrect" for your scenario, you may need to handle each row separately, using a cursor.

INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
SELECT TOP(10) LEFT(o.name, 10)
    , LEFT(o1.name, 10)
    , GETDATE()
FROM sys.objects o
    , sys.objects o1;

UPDATE dbo.TriggerTest 
SET Data1 = CASE WHEN TriggerTestID % 6 = 1 THEN Data2 ELSE Data1 END
    , Data3 = CASE WHEN TriggerTestID % 6 = 2 THEN GETDATE() ELSE Data3 END;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

The TriggerResult table now has some potentially misleading rows that look like they don't belong since they show absolutely no changes (to the two columns in that table). In the 2nd set of rows in the image below, TriggerTestID 7 is the only one that looks like it was modified. The other rows only had the Data3 column updated; however since the one row in the batch had Data1 updated, all rows are inserted in the TriggerResult table.

Alternately, as @AaronBertrand and @srutzky pointed out, you can perform an comparison of the actual data in the inserted and deleted virtual tables. Since the structure of both tables is identical, you can use an EXCEPT clause in the trigger to capture rows where the precise columns you are interested in have changed:

IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0 
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS 
BEGIN
    ;WITH src AS
    (
        SELECT d.TriggerTestID
            , d.Data1
            , d.Data2
        FROM deleted d
        EXCEPT 
        SELECT i.TriggerTestID
            , i.Data1
            , i.Data2
        FROM inserted i
    )
    INSERT INTO dbo.TriggerResult 
    (
        TriggerTestID, 
        Data1OldVal, 
        Data1NewVal, 
        Data2OldVal, 
        Data2NewVal
    )
    SELECT i.TriggerTestID
        , d.Data1
        , i.Data1
        , d.Data2
        , i.Data2
    FROM inserted i 
        INNER JOIN deleted d ON i.TriggerTestID = d.TriggerTestID
END
GO

^{1 - see https://stackoverflow.com/questions/297960/hash-collision-what-are-the-chances for a discsussion of the vanishingly small chance that the HASHBYTES calculation may also result in collisions. Preshing has a decent analysis of this problem as well.}

Best Answer

Related Solutions

SQL Server – Transpose Multiple Columns to Single Row

SQL Server – How to Use COLUMNS_UPDATED to Check Column Updates

Related Question