https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context-to-master/

Some important things to check after server migration

1. make sure tempdb files are configured per cpu, Initial and autoghrowth should have same size for all files mdf, ndf, log foles 2. Cost of threshold can be 50 3. max dop based on cpu

Audit login trigger

USE [AuditDB] GO /****** Object: DdlTrigger [AuditObjects] Script Date: 2/15/2023 12:24:21 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [AuditObjects] ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_VIEW,ALTER_VIEW,DROP_VIEW,DROP_TRIGGER, CREATE_TRIGGER,ALTER_TRIGGER AS BEGIN SET NOCOUNT ON DECLARE @data XML DECLARE @HostName VARCHAR(500) SET @data = EVENTDATA() SET @HostName = HOST_NAME() INSERT INTO AuditDB.dbo.ObjectTracking(DatabaseName,EventType,ObjectName,ObjectType,LoginName,HostName,SqlCommand,date) VALUES( @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(500)'), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(500)'), @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(500)'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(500)'), @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(500)'), @HostName, @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), GETDATE() ) END EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default', @recipients = 'naresh.koudagani@frontpointsecurity.com', @subject = 'Object has been Altered/Created/Dropped! please look at Audtdb.dbo.ObjectTracking' , @body_format = 'HTML', @importance = 'High' GO ENABLE TRIGGER [AuditObjects] ON DATABASE GO

Get schema name , table name, row count and total size

SELECT s.Name AS SchemaName, t.Name AS TableName, p.rows AS RowCounts, CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id GROUP BY t.Name, s.Name, p.Rows ORDER BY 4 desc GO

How to delete Temp dB files when you get error The file cannot be removed because it is not empty.

USE [tempdb] GO ALTER DATABASE [tempdb] REMOVE FILE [temp6] GO Msg 5042, Level 16, State 1, Line 13 The file 'temp6' cannot be removed because it is not empty. USE [tempdb] GO ALTER DATABASE [tempdb] REMOVE FILE [temp6] GO that should help if not --DBCC FREEPROCCACHE('ALL') but test in test environments before doing in production

SQL SERVER did not detect newly added CPUs

hi , we had a situation where Windows team added 2cpu and it did not detect in SQL SERVER before:

EXECUTE RECONFIGURE now the change showuld show and no need of reboot After:

Audit login time, login name, hostname ,program name, client Etc Connection tracking and other info

1.USE [master] GO /****** Object: Table [dbo].[DBALogonAudit] Script Date: 1/12/2023 4:39:31 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DBALogonAudit]( [SessionId] [int] NULL, [LogonTime] [datetime] NULL, [HostName] [varchar](50) NULL, [ProgramName] [varchar](500) NULL, [LoginName] [varchar](50) NULL, [ClientHost] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO 2.---create trigger USE [master] GO /****** Object: DdlTrigger [LogonAuditTrigger] Script Date: 1/12/2023 4:26:12 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [LogonAuditTrigger] /* Creates trigger for logons */ ON ALL SERVER FOR LOGON AS BEGIN DECLARE @LogonTriggerData xml, @EventTime datetime, @LoginName varchar(50), @ClientHost varchar(50), @LoginType varchar(50), @HostName varchar(50), @AppName varchar(500) SET @LogonTriggerData = eventdata() SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)') SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)') SET @HostName = HOST_NAME() SET @AppName = APP_NAME() INSERT INTO DBALogonAudit SELECT @@spid, @EventTime, @HostName, @AppName, @LoginName, @ClientHost END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER [LogonAuditTrigger] ON ALL SERVER GO 3. put below inside a sql job --table should exist in a database Use master go DECLARE @xml NVARCHAR(MAX) DECLARE @body NVARCHAR(MAX) SET @xml = CAST(( SELECT @@SERVERNAME AS 'td','', s.name + '.' + t.Name AS 'td','', part.rows AS 'td','', CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) AS 'td' FROM SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id AND idx.Index_id = part.Index_id INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id where s.name + '.' + t.Name Like '%DBALogonAudit%' GROUP BY t.Name, s.name, part.rows ORDER BY 2 DESC FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @body ='

Schemas Info, Please vist master.dbo.DBALogonAudit table to get more detailed Login Report

' SET @body = @body + @xml +'

Servername	Tablename	RowsModified	TotalSpaceinGB

' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLMAil', -- replace with your SQL Database Mail Profile @body = @body, @body_format ='HTML', @recipients = 'abc@abc.com', -- replace with your email address @subject = 'Audit report' ;

Serious performance issues after migrating SQL SERVER 2016 on a windows platform from windows Server 2012 to 2019

We migrated sql server 2016 from windows 2012 to windows 2019 and we had very bad perforamnce on queries.. we updated stats with full scan, rebuildd indexes after migration.. looked at Mx dop cost of threshold etc all loked good from our side. but no improvement and asked windows and network team to look what are rubnning new in 2019 vs 2012 and they found out that They have identified one potential issue that may be causing the performance drop between Server 2012 and Server 2019. We've noticed that Server 2019 does not properly disable Windows Defender when FireEye is installed, which may be causing additional resource contention from having two malware scanning platforms installed and running concurrently. Uninstalled windows defernder reboot and BOOM all queries are running as before in old production system

Backup Percentage completed, to check status and also to heck history of backups

SELECT command, s.text, percent_complete, CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), ' + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, ' + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time, CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), ' + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, ' + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go, start_time, dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG','ALTER DATABASE','DROP DATABASE','CREATE DATABASE') go SELECT top 150 bs.database_name, bs.backup_start_date, bs.backup_finish_date, bs.server_name, bs.user_name, bs.type, bm.physical_device_name FROM msdb.dbo.backupset AS bs INNER JOIN msdb.dbo.backupmediafamily AS bm on bs.media_set_id = bm.media_set_id where backup_start_date>'2023-01-06 09:00:00.000' order by backup_finish_date desc

Using Variables To Update and Increment the Value by 1

UPDATE accounts2 SET id = NULL GO DECLARE @id INT SET @id = 0 UPDATE accounts2 SET @id = id = @id + 10 GO SELECT * FROM accounts2 GO if you do not have any option to use Idenity column , the above will be helpful

Load Dim_date ( i have loaded 2023)

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'DimDate')) BEGIN DROP TABLE DimDate END go GO CREATE TABLE [dbo].[DimDate] ( [DateKey] INT primary key, [Date] DATETIME, [FullDate] CHAR(10),-- Date in MM-dd-yyyy format [DayOfMonth] VARCHAR(2), -- Field will hold day number of Month [DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday [DayOfWeek] CHAR(1),-- First Day Sunday=1 and Saturday=7 [DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month [DayOfWeekInYear] VARCHAR(2), [DayOfQuarter] VARCHAR(3), [DayOfYear] VARCHAR(3), [WeekOfMonth] VARCHAR(1),-- Week Number of Month [WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter [WeekOfYear] VARCHAR(2),--Week Number of the Year [Month] VARCHAR(2), --Number of the Month 1 to 12 [MonthName] VARCHAR(9),--January, February etc [MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter [Quarter] CHAR(1), [QuarterName] VARCHAR(9),--First,Second.. [Year] CHAR(4),-- Year value of Date stored in Row [YearName] CHAR(7), --CY 2012,CY 2013 [MonthYear] CHAR(10), --Jan-2013,Feb-2013 [MMYYYY] CHAR(6), [FirstDayOfMonth] DATE, [LastDayOfMonth] DATE, [FirstDayOfQuarter] DATE, [LastDayOfQuarter] DATE, [FirstDayOfYear] DATE, [LastDayOfYear] DATE ) GO /********************************************************************************************/ --Specify Start Date and End date here --Value of Start Date Must be Less than Your End Date DECLARE @StartDate DATETIME = '01/01/2023' --Starting value of Date Range DECLARE @EndDate DATETIME = '12/31/2023' --End Value of Date Range --Temporary Variables To Hold the Values During Processing of Each Date of Year DECLARE @DayOfWeekInMonth INT, @DayOfWeekInYear INT, @DayOfQuarter INT, @WeekOfMonth INT, @CurrentYear INT, @CurrentMonth INT, @CurrentQuarter INT /*Table Data type to store the day of week count for the month and year*/ DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT) INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0) --Extract and assign various parts of Values from Current Date to Variable DECLARE @CurrentDate AS DATETIME = @StartDate SET @CurrentMonth = DATEPART(MM, @CurrentDate) SET @CurrentYear = DATEPART(YY, @CurrentDate) SET @CurrentQuarter = DATEPART(QQ, @CurrentDate) /********************************************************************************************/ --Proceed only if Start Date(Current date ) is less than End date you specified above WHILE @CurrentDate < @EndDate BEGIN /*Begin day of week logic*/ /*Check for Change in Month of the Current date if Month changed then Change variable value*/ IF @CurrentMonth != DATEPART(MM, @CurrentDate) BEGIN UPDATE @DayOfWeek SET MonthCount = 0 SET @CurrentMonth = DATEPART(MM, @CurrentDate) END /* Check for Change in Quarter of the Current date if Quarter changed then change Variable value*/ IF @CurrentQuarter != DATEPART(QQ, @CurrentDate) BEGIN UPDATE @DayOfWeek SET QuarterCount = 0 SET @CurrentQuarter = DATEPART(QQ, @CurrentDate) END /* Check for Change in Year of the Current date if Year changed then change Variable value*/ IF @CurrentYear != DATEPART(YY, @CurrentDate) BEGIN UPDATE @DayOfWeek SET YearCount = 0 SET @CurrentYear = DATEPART(YY, @CurrentDate) END -- Set values in table data type created above from variables UPDATE @DayOfWeek SET MonthCount = MonthCount + 1, QuarterCount = QuarterCount + 1, YearCount = YearCount + 1 WHERE DOW = DATEPART(DW, @CurrentDate) SELECT @DayOfWeekInMonth = MonthCount, @DayOfQuarter = QuarterCount, @DayOfWeekInYear = YearCount FROM @DayOfWeek WHERE DOW = DATEPART(DW, @CurrentDate) /*End day of week logic*/ /* Populate Your Dimension Table with values*/ INSERT INTO [dbo].[DimDate] SELECT CONVERT (char(8),@CurrentDate,112) as DateKey, @CurrentDate AS Date, CONVERT (char(10),@CurrentDate,101) as FullDate, DATEPART(DD, @CurrentDate) AS DayOfMonth, DATENAME(DW, @CurrentDate) AS DayName, DATEPART(DW, @CurrentDate) AS DayOfWeek, @DayOfWeekInMonth AS DayOfWeekInMonth, @DayOfWeekInYear AS DayOfWeekInYear, @DayOfQuarter AS DayOfQuarter, DATEPART(DY, @CurrentDate) AS DayOfYear, DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS WeekOfMonth, (DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) / 7) + 1 AS WeekOfQuarter, DATEPART(WW, @CurrentDate) AS WeekOfYear, DATEPART(MM, @CurrentDate) AS Month, DATENAME(MM, @CurrentDate) AS MonthName, CASE WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1 WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2 WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3 END AS MonthOfQuarter, DATEPART(QQ, @CurrentDate) AS Quarter, CASE DATEPART(QQ, @CurrentDate) WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END AS QuarterName, DATEPART(YEAR, @CurrentDate) AS Year, 'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName, LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MonthYear, RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY, CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, @CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth, CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1, @CurrentDate)))) AS LastDayOfMonth, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter, DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter, CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS FirstDayOfYear, CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS LastDayOfYear SET @CurrentDate = DATEADD(DD, 1, @CurrentDate) END /********************************************************************************************/ go SELECT * FROM [dbo].[DimDate] INSERT INTO dbo.DimDate ( DateKey, Date, FullDate, DayOfMonth, DayName, DayOfWeek, DayOfWeekInMonth, DayOfWeekInYear, DayOfQuarter, DayOfYear, WeekOfMonth, WeekOfQuarter, WeekOfYear, Month, MonthName, MonthOfQuarter, Quarter, QuarterName, Year, YearName, MonthYear, MMYYYY, FirstDayOfMonth, LastDayOfMonth, FirstDayOfQuarter, LastDayOfQuarter, FirstDayOfYear, LastDayOfYear ) VALUES ( 20231231, -- DateKey - int '2023-12-31 00:00:00.000', -- Date - datetime '12/31/2023', -- FullDate - char(10) '31', -- DayOfMonth - varchar(2) 'Sunday', -- DayName - varchar(9) '1', -- DayOfWeek - char(1) '6', -- DayOfWeekInMonth - varchar(2) '53', -- DayOfWeekInYear - varchar(2) '14', -- DayOfQuarter - varchar(3) '365', -- DayOfYear - varchar(3) '6', -- WeekOfMonth - varchar(1) '14', -- WeekOfQuarter - varchar(2) '53', -- WeekOfYear - varchar(2) '12', -- Month - varchar(2) 'December', -- MonthName - varchar(9) '3', -- MonthOfQuarter - varchar(2) '4', -- Quarter - char(1) 'Fourth', -- QuarterName - varchar(9) '2023', -- Year - char(4) 'CY 2023', -- YearName - char(7) 'Dec-2023 ', -- MonthYear - char(10) '122023', -- MMYYYY - char(6) '2023-12-01', -- FirstDayOfMonth - date '2023-12-31', -- LastDayOfMonth - date '2023-10-01', -- FirstDayOfQuarter - date '2023-12-31', -- LastDayOfQuarter - date '2023-01-01', -- FirstDayOfYear - date '2023-12-31' -- LastDayOfYear - date ) --another example --INSERT INTO [dbo].[Dim_Date] -- ([Date_Type] -- ,[Full_Date] -- ,[Full_Date_BI] -- ,[Year_Month_Number] -- ,[Year_Number] -- ,[Day_Of_Week_Number] -- ,[Day_Of_Month_Number] -- ,[Day_Of_Quarter_Number] -- ,[Day_Of_Year_Number] -- ,[Day_Name] -- ,[Week_Of_Month_Number] -- ,[Week_Of_Quarter_Number] -- ,[Week_Of_Year_Number] -- ,[Month_Of_Quarter_Number] -- ,[Month_Of_Year_Number] -- ,[Month_Name] -- ,[Abbr_Month_Name] -- ,[Quarter_Of_Year_Number] -- ,[Quarter_Name] -- ,[is_Today] -- ,[is_Current_Week] -- ,[is_Last_Week] -- ,[is_Current_Month] -- ,[is_Last_Month] -- ,[is_Last_Day_Of_Month] -- ,[is_Last_30_Days] -- ,[is_Last_28_Days] -- ,[is_Last_12_Weeks] -- ,[is_Last_12_Months] -- ,[is_Holiday] -- ,[Sales_Start_Time] -- ,[Sales_End_Time] -- ,[Support_Start_Time] -- ,[Support_End_Time] -- ,[Dialer_Start_Time] -- ,[Dialer_End_Time] -- ,[Lead_Business_Day] -- ,[Sale_Business_Day] -- ,[Shipping_Day_Num] -- ,[Insert_Audit_Key] -- ,[Update_Audit_Key]) SELECT 'Date' AS Date_type , [Date] AS FULL_date ,[Date] AS FULL_date_BI ,CASE WHEN [MonthName] = 'January' THEN '202301' WHEN [MonthName] = 'February' THEN '202302' WHEN [MonthName] = 'March' THEN '202303' WHEN [MonthName] = 'April' THEN '202304' WHEN [MonthName] = 'May' THEN '202305' WHEN [MonthName] = 'June' THEN '202306' WHEN [MonthName] = 'July' THEN '202307' WHEN [MonthName] = 'August' THEN '202308' WHEN [MonthName] = 'September' THEN '202309' WHEN [MonthName] = 'October' THEN '202310' WHEN [MonthName] = 'November' THEN '202311' WHEN [MonthName] = 'December' THEN '202312' ELSE NULL END AS Year_month_Number, [Year] AS Year_Number, [Dayofweek] AS Day_Of_Week_Number ,[dayofmonth] AS Day_of_Month_number ,dayofQuarter AS Day_of_Quarter_number---? Have to fix this ,[DayOFyear] AS day_of_year_number ,[dayname] AS Day_name ,[Weekofmonth] AS week_of_month_number ,[WeekofQuarter] AS week_of_Quarter_number ,WeekOfyear AS Week_of_year_number ,MonthofQuarter AS Month_Of_Quarter_number ,[Month] AS Month_of_year_number ,[MonthName] AS Month_name , CASE WHEN [MonthName] = 'January' THEN 'Jan' WHEN [MonthName] = 'February' THEN 'Feb' WHEN [MonthName] = 'March' THEN 'Mar' WHEN [MonthName] = 'April' THEN 'Apr' WHEN [MonthName] = 'May' THEN 'May' WHEN [MonthName] = 'June' THEN 'Jun' WHEN [MonthName] = 'July' THEN 'Jul' WHEN [MonthName] = 'August' THEN 'Aug' WHEN [MonthName] = 'September' THEN 'Sep' WHEN [MonthName] = 'October' THEN 'Oct' WHEN [MonthName] = 'November' THEN 'Nov' WHEN [MonthName] = 'December' THEN 'Dec' ELSE NULL END AS Abbr_month_name ,[Quarter] AS Quarter_of_Year_name ,Quartername AS Quarter_name ,0 AS is_today ,0 AS is_current_week ,0 AS is_last_week ,0 AS is_current_Month ,0 AS is_last_month ,0 AS is_last_day_of_month --case add here ---ned to fix case when month =02 and date 28 then 1 ---like that addd case ,0 AS is_last_30_Days ,0 AS is_last_28_days ,0 AS is_last_12_weeks ,0 AS is_last_12_months ,0 as is_holiday , CASE WHEN [DayName]= 'Sunday' THEN '10:00:00' WHEN [DayName]= 'Saturday' THEN '10:00:00' WHEN [DayName]= 'Friday' THEN '08:00:00' WHEN [DayName]= 'Thursday' THEN '08:00:00' WHEN [DayName]= 'Wednesday' THEN '08:00:00' WHEN [DayName]= 'Tuesday' THEN '08:00:00' WHEN [DayName]= 'Monday' THEN '08:00:00' ELSE NULL END AS sales_start_Time , CASE WHEN [DayName]='Sunday' THEN '19:00:00' WHEN [DayName]='Saturday' THEN '19:00:00' WHEN [DayName]='Friday' THEN '23:00:00' WHEN [DayName]='Thursday' THEN '23:00:00' WHEN [DayName]='Wednesday' THEN '23:00:00' WHEN [DayName]='Tuesday' THEN '23:00:00' WHEN [DayName]='Monday' THEN '23:00:00' ELSE NULL END AS sales_End_Time , CASE WHEN [DayName]='Sunday' THEN '19:00:00' WHEN [DayName]='Saturday' THEN '19:00:00' WHEN [DayName]='Friday' THEN '23:00:00' WHEN [DayName]='Thursday' THEN '23:00:00' WHEN [DayName]='Wednesday' THEN '23:00:00' WHEN [DayName]='Tuesday' THEN '23:00:00' WHEN [DayName]='Monday' THEN '23:00:00' ELSE NULL END AS Support_Start_Time , CASE WHEN [DayName]= 'Sunday' THEN '19:00:00' WHEN [DayName]= 'Saturday' THEN '19:00:00' WHEN [DayName]= 'Friday' THEN '23:00:00' WHEN [DayName]= 'Thursday' THEN '23:00:00' WHEN [DayName]= 'Wednesday' THEN '23:00:00' WHEN [DayName]= 'Tuesday' THEN '23:00:00' WHEN [DayName]= 'Monday' THEN '23:00:00' ELSE NULL END AS Support_End_Time , CASE WHEN [DayName]= 'Sunday' THEN '19:00:00' WHEN [DayName]= 'Saturday' THEN '19:00:00' WHEN [DayName]= 'Friday' THEN '23:00:00' WHEN [DayName]= 'Thursday' THEN '23:00:00' WHEN [DayName]= 'Wednesday' THEN '23:00:00' WHEN [DayName]= 'Tuesday' THEN '23:00:00' WHEN [DayName]= 'Monday' THEN '23:00:00' ELSE NULL END AS Dialer_Start_Time , CASE WHEN [DayName]= 'Sunday' THEN '19:00:00' WHEN [DayName]= 'Saturday' THEN '19:00:00' WHEN [DayName]= 'Friday' THEN '23:00:00' WHEN [DayName]= 'Thursday' THEN '23:00:00' WHEN [DayName]= 'Wednesday' THEN '23:00:00' WHEN [DayName]= 'Tuesday' THEN '23:00:00' WHEN [DayName]= 'Monday' THEN '23:00:00' ELSE NULL END AS Dialer_End_Time , CASE WHEN [DayName]= 'Sunday' THEN '0.39' WHEN [DayName]= 'Saturday' THEN '0.46' WHEN [DayName]= 'Friday' THEN '0.69' WHEN [DayName]= 'Thursday' THEN '0.75' WHEN [DayName]= 'Wednesday' THEN '0.83' WHEN [DayName]= 'Tuesday' THEN '0.84' WHEN [DayName]= 'Monday' THEN '1.00' ELSE NULL END AS Lead_Business_day , CASE WHEN [DayName]= 'Sunday' THEN '0.30' WHEN [DayName]= 'Saturday' THEN '0.70' WHEN [DayName]= 'Friday' THEN '0.71' WHEN [DayName]= 'Thursday' THEN '0.84' WHEN [DayName]= 'Wednesday' THEN '0.83' WHEN [DayName]= 'Tuesday' THEN '1,00' WHEN [DayName]= 'Monday' THEN '0.06' ELSE NULL END AS Lead_Business_day --,Shipping_day_num --have to fix this ---increment need to fix this , 681161 AS Insert_audit_key ,-1 AS Update_Audit_key FROM AuditDB.[dbo].[DimDate]