Insert pipe file into SQL Server from T-Sql and bulk insert

Hello Readers,

I found a cool way to insert a pip file into a table using all T-Sql.  This will be one of the quickest blogs I have written I think.  First we need to set up a temp table.

Here is the code to do that.

IF EXISTS(
    SELECT *
        FROM tempdb.dbo.sysobjects
        WHERE ID = OBJECT_ID(N'tempdb..#BaseballTeams'))
DROP TABLE #BaseballTeams

CREATE TABLE #BaseballTeams (Team VARCHAR(20),Founded date, City VARCHAR(30), State Char(2))

Now lets create a pipe file with a extension of .txt.  Here is what is in the file.

Milwaukee Brewers|1970|Milwaukee|WI
Chicago Cubs|1870|chicago|IL
Minnesota Twins|1894|Minneapolis|MN
Detroit Tigers|1894|Detroit|MI

Now for the run in SQL Server!  To do this I use some dynamic sql magic.  Here is the simple code to insert the values right into the temp table.

    --variables
    DECLARE @DynamicSQL NVARCHAR(MAX)
            , @PathFileName VARCHAR(256)
    --assigning values to variables
    SET @PathFileName = '\\yourfileserver\baseball.txt'
    -- SQL string concatenation - string concat - string append
    SET @DynamicSQL = 'BULK INSERT #BaeballTeams FROM '''+@PathFileName+
    ''' WITH (FIELDTERMINATOR = ''|'', FIRSTROW=1) '

    -- Dynamic SQL execution
   EXEC sp_executesql @DynamicSQL


Now lets query the table

SELECT *
    FROM #BaseballTeams

Short and quick.

See you later,
Robert

Using Unpivot Statement and Datetime

To My Few Readers Out There,

It's been way to long since my last blog entry!  So here is something cool I learned at work the other day.  I have used pivot statements before but very rarely a unpivot statement.   This example will show how to use it.  So let's keep with the normal baseball theme as the rest of my entries.  We have a table called that has baseball teams and the days they play baseball.  The days saved as a bit field (1 or 0).  We need to find the last day that they played balled on.

Here is what the current table looks like:

This is what we want to show:

Seems simple right?  Well I will explain how we got there.

First lets set up the data in a temp table.


IF EXISTS(
    SELECT *
        FROM tempdb.dbo.sysobjects
        WHERE ID = OBJECT_ID(N'tempdb..#BaseballTeamsSchedule'))
DROP TABLE #BaseballTeamsSchedule

CREATE TABLE #BaseballTeamsSchedule (Team VARCHAR(20),Sunday BIT,Monday BIT,Tuesday BIT,Wednesday BIT,Thursday BIT,Friday BIT,Saturday BIT)


INSERT INTO #BaseballTeamsSchedule ( Team ,Sunday ,Monday ,Tuesday ,Wednesday ,Thursday ,Friday ,Saturday)
    SELECT  'Milwakuee Brewers',1,0,0,1,0,1,1
    UNION ALL 
    SELECT  'Minnesota Twins',0,0,0,1,1,1,1
    UNION ALL 
    SELECT  'Detroit Tigers',0,0,1,1,0,0,0
    UNION ALL 
    SELECT  'Chicago Cubs',0,0,1,0,1,0,0
            

Next let's convert those bit fields to actually dates.  With the use of case statements and datepart function we can easily do this.

Here is the SQL to do that.

SELECT team,Sunday = CASE
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Monday = CASE
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Tuesday = CASE
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Wednesday = CASE
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Thursday = CASE
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Friday = CASE
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Saturday = CASE
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
    FROM #BaseballTeamsSchedule


Now we have to unpivot the data which is where all the magic comes from.

Here is the SQL to unpivot the data.


SELECT team,dates
    FROM  (
    SELECT team,Sunday = CASE
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Monday = CASE
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Tuesday = CASE
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Wednesday = CASE
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Thursday = CASE
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Friday = CASE
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Saturday = CASE
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
    FROM #BaseballTeamsSchedule) p UNPIVOT ( dates FOR pubdates IN ( Sunday,Monday,Tuesday,Wednesday,Thursday, Friday,Saturday ) ) AS unpvt

As you can see this will return multi rows per team but we wanted only the latest date rather than all the days they played in the past week.  If you use the row_number function we can return just one date per team.

Here is the SQL:

SELECT team, dates
    FROM (
    SELECT team ,
                dates,
                ROW_NUMBER() OVER ( PARTITION BY team
            ORDER BY team, dates DESC ) AS rownum
        FROM  (
        SELECT team,Sunday = CASE
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Sunday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Monday = CASE
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Monday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Tuesday = CASE
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Tuesday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Wednesday = CASE
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Wednesday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Thursday = CASE
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Thursday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Friday = CASE
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Friday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
Saturday = CASE
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 1 THEN CAST(DATEADD(dd,-7,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 2 THEN CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 3 THEN CAST(DATEADD(dd,-2,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 4 THEN CAST(DATEADD(dd,-3,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 5 THEN CAST(DATEADD(dd,-4,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 6 THEN CAST(DATEADD(dd,-5,GETDATE()) AS DATE)
                WHEN Saturday = 1
            AND DATEPART(dw,GETDATE()) = 7 THEN CAST(DATEADD(dd,-6,GETDATE()) AS DATE)
    END
    FROM #BaseballTeamsSchedule
        ) p UNPIVOT ( dates FOR pubdates IN ( Sunday,Monday,Tuesday,Wednesday,Thursday, Friday,Saturday ) ) AS unpvt
    ) a
    WHERE     a.rownum = 1
            

Now we get the results we are looking for!

There should do it this time!  Hope you learned some cool SQL stuff.

Till next time,
Robert