Database

-- I'm using this variable to indicate when a record was inserted
DECLARE @nextOrdinal INT = (SELECT CASE WHEN MAX(InsertOrdinal) IS NULL THEN 1 
                                        ELSE MAX(InsertOrdinal)+1 
                                        END 
                            FROM [Essentris].[dbo].[VancoMycin]);

-- create a temporary table to hold our grouped/normalized data
IF OBJECT_ID('tempdb..#tempVanco') IS NOT NULL
BEGIN
    DROP TABLE #tempVanco;
END

-- this temp table holds our grouped and normalized data
CREATE TABLE #tempVanco
(
    [ABX_NAME]      [nvarchar](255) NULL,
    [ROUTE]         [nvarchar](255) NULL,
    [DELIVERY_TIME] [datetime]      NULL,
    [HOSPNO]        [int]             NULL,
    [PTNAME]        [nvarchar](255) NULL,
    [UNIT]          [nvarchar](255) NULL,
    [ATTENDING]     [nvarchar](255) NULL,
    [SERVICE]       [nvarchar](255) NULL,
    [ADX]           [nvarchar](255) NULL
);

-- Normalize the data so that there is no unexpected stuff in any of the fields. This 
-- also keeps me from having to do this further down in the code in the cte (this is a 
-- desperation measure after fighting with this for DAYS)

update  [Essentris].[dbo].[IMPORTED_Vanco]
SET     [ABX_NAME] = UPPER(RTRIM(LTRIM([ABX_NAME])))
        ,[ROUTE]    = UPPER(RTRIM(LTRIM([ROUTE])))
        ,[PTNAME]   = UPPER(RTRIM(LTRIM([PTNAME])))
        ,[UNIT]     = UPPER(RTRIM(LTRIM([UNIT])))
        ,[ATTENDING]= UPPER(RTRIM(LTRIM([ATTENDING])))
        ,[SERVICE]  = UPPER(RTRIM(LTRIM([SERVICE])))
        ,[ADX]      = UPPER(RTRIM(LTRIM([ADX])));

-- group the imported table data (the data may have duplicate rows)

;with cte as 
(
    SELECT  [ABX_NAME]
            ,[ROUTE]
            ,[DELIVERY_TIME]
            ,CAST([HOSPNO] AS INT) AS [HOSPNO]
            ,[PTNAME]
            ,[UNIT]
            ,[ATTENDING]
            ,[SERVICE]
            ,[ADX]
    FROM    [Essentris].[dbo].[IMPORTED_Vanco]
    GROUP BY [ABX_NAME]
            ,[ROUTE]
            ,[DELIVERY_TIME]
            ,CAST([HOSPNO] AS INT)
            ,[PTNAME]
            ,[UNIT]
            ,[ATTENDING]
            ,[SERVICE]
            ,[ADX]
)
-- and insert it into the temp table from the cte
insert into #tempvanco 
select * from cte;

-- Up to this point, the contents of #tempvanco is as expected

--================

-- merge #tempvanco into our concrete table. 

MERGE INTO [Essentris].[dbo].[VancoMycin] AS t
USING #tempVanco AS s
ON  
(
    -- this is really weird: if I just use the three fields below, it reinserts fewer 
    -- records than if I use ALL of the fields. This hints at there being a problem 
    -- with one or more of the string fields, but after they've been normalized this 
    -- theoretically should not be the case.
        t.[DELIVERY_TIME] = s.[DELIVERY_TIME]
    AND t.[HOSPNO]        = s.[HOSPNO]
    -- I tried using "like" instead of "=", and it had no effect
    AND t.[PTNAME]        like s.[PTNAME]
    --and t.[ABX_NAME]      = s.[ABX_NAME]
    --and t.[ROUTE]         = s.[ROUTE]
    --and t.[UNIT]          = s.[UNIT]
    --and t.[ATTENDING]     = s.[ATTENDING]
    --and t.[SERVICE]       = s.[SERVICE]
    --and t.[ADX]           = s.[ADX]
)

-- We should never need to update anything, so we only react when a record is new

WHEN NOT MATCHED BY TARGET THEN
    INSERT 
    (
        [ABX_NAME]
        ,[ROUTE]
        ,[DELIVERY_TIME]
        ,[HOSPNO]
        ,[PTNAME]
        ,[UNIT]
        ,[ATTENDING]
        ,[SERVICE]
        ,[ADX]

        -- we need to create some data to fill in these fields
        ,[ABX_NAME_SHORT]
        ,[DELIVERY_DATE]
        ,InsertOrdinal
    )
    VALUES
    (
        s.[ABX_NAME]
        ,s.[ROUTE]
        ,s.[DELIVERY_TIME]
        ,s.[HOSPNO]
        ,s.[PTNAME]
        ,s.[UNIT]
        ,s.[ATTENDING]
        ,s.[SERVICE]
        ,s.[ADX]

        -- created data
        ,'VANCOMYCIN'
        ,CONVERT(DATE, s.[DELIVERY_TIME])
        ,@nextOrdinal
    );

-- drop the temporary table

IF OBJECT_ID('tempdb..#tempVanco') IS NOT NULL 
BEGIN
    DROP TABLE #tempVanco;
END

MCTR_DESCR == "Large" ? "LG" : MCTR_DESCR == "Midsize" ? "LG" : MCTR_DESCR == "Strategic Account" ? "LG" : MCTR_DESCR == "Strategic National" ? "LG" : MCTR_DESCR == "Large National" ? "LG" : MCTR_DESCR == "Small" ? "SG" : ISNULL([Copy of Column 11]) ? "SG" : [Copy of Column 11] == "" ? "SG" : LEFT([Copy of Column 11],1) == "X" ? "IFP" : [Copy of Column 11] == "W0051412" ? "Calpers" : [Copy of Column 11] == "W0051411" ? "Calpers" : "SG"

ID	Device id	TimeZone	Effective Date
1	123	CST	2017-01-07 00:00:00.000
1	123	PST	2017-06-24 00:00:00.000
1	123	MST	2017-08-02 00:00:00.000
2	345	CST	2017-07-01 00:00:00.000
2	345	PST	2017-08-01 00:00:00.000
4	678	CST	2017-08-02 00:00:00.000
			

The above table is Name Timetable .I looking for below result if i pass date parameter  
as ‘2017-08-01 00:00:00.000’ 

ID	Device id TimeZone	Effective Date
1	123	  CST	       2017-01-07 00:00:00.000
2	345	  PST	       2017-08-01 00:00:00.000
4	678	  CST	       2017-08-02 00:00:00.000