SQL Server (T-SQL)

SELECT DB_NAME(dbid) as "Database", COUNT(dbid) as "Number Of Open Connections",
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame;
SELECT  creation_time 
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads 
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , (total_elapsed_time / execution_count) avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE execution_count > 10 -- filter out rare requests
ORDER BY total_elapsed_time / execution_count DESC;
/* STORED PROCEDURES AND FUNCTIONS EXECUTION TIME, COUNT AND AVERAGE */

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
      ,sum(qs.total_worker_time) total_cpu_time
      ,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0)  avg_cpu_time
 
FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle
     CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid) 
order by sum(qs.total_worker_time) desc;
/* FIND UNUSED INDEXES - MIGHT AFFECT LOG WRITES */

SELECT o.name Object_Name,
i.name Index_name, 
i.Type_Desc
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.object_id = i.object_id 
LEFT OUTER JOIN 
sys.dm_db_index_usage_stats AS s 
ON i.object_id = s.object_id 
AND i.index_id = s.index_id
WHERE o.type = 'u'
-- Clustered and Non-Clustered indexes
AND i.type IN (1, 2) 
-- Indexes without stats
AND (s.index_id IS NULL) OR
-- Indexes that have been updated but not used
(s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 );
/* DMV to find useful indexes: */

PRINT 'Missing Indexes: '
PRINT 'The "improvement_measure" column is an indicator of the (estimated) improvement that might '
PRINT 'be seen if the index was created. This is a unitless number, and has meaning only relative '
PRINT 'the same number for other indexes. The measure is a combination of the avg_total_user_cost, '
PRINT 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'
PRINT ''
PRINT '-- Missing Indexes --'
SELECT CONVERT (varchar, getdate(), 126) AS runtime, 
  mig.index_group_handle, mid.index_handle, 
  CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure, 
  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) 
  + ' ON ' + mid.statement 
  + ' (' + ISNULL (mid.equality_columns,'') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
  + ')' 
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
PRINT ''
GO
/* Find top 10 queries */

SELECT TOP 10 query_stats.query_hash AS "Query Hash", 
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM 
    (SELECT QS.*, 
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO
/* Monitor query plans */

SELECT
    highest_cpu_queries.plan_handle,  
    highest_cpu_queries.total_worker_time, 
    q.dbid, 
    q.objectid, 
    q.number, 
    q.encrypted, 
    q.[text] 
FROM 
    (SELECT TOP 50  
        qs.plan_handle,  
        qs.total_worker_time 
     FROM 
        sys.dm_exec_query_stats qs 
     ORDER BY qs.total_worker_time desc) AS highest_cpu_queries 
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY highest_cpu_queries.total_worker_time desc;
/* CHECK SIZE OF DB OBJECTS */
SELECT TOP(10)
      o.[object_id]
    , obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.[type]
    , i.total_rows
    , i.total_size
FROM sys.objects o
JOIN (
    SELECT
          i.[object_id]
        , total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
        , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
    FROM sys.indexes i
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    WHERE i.is_disabled = 0
        AND i.is_hypothetical = 0
    GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U', 'S')
ORDER BY i.total_size DESC;
-- to understand who is doing what, alternative view/representation
SELECT
	CAST((SELECT qt.text FROM sys.dm_exec_sql_text(qs.sql_handle) AS qt FOR XML PATH('')) as xml) as query_text,
	qs.blocking_session_id,
	qs.start_time, 
	datediff(ss, qs.start_time, getdate()) as ExecutionTime_Seconds,
	getdate() as  CurrentDate,
	datediff(MINUTE, qs.start_time, getdate()) as ExecutionTime_Minutes,
	qs.session_id,
	qs.command,
	qs.status,
	qs.cpu_time, 
	qs.reads, 
	qs.writes, 
	qs.plan_handle,
	qp.query_plan,
	s.host_name, s.login_name, s.program_name,
	qs.wait_type, qs.open_transaction_count, qs.open_resultset_count, qs.row_count, qs.granted_query_memory, qs.transaction_isolation_level
	--,qs.*
FROM sys.dm_exec_requests AS qs
left join sys.dm_exec_sessions s on s.session_id = qs.session_id ---OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE 	qs.session_id <> @@SPID
	and qs.command not in ('RESOURCE MONITOR', 'XE TIMER', 'XE DISPATCHER', 'LOG WRITER', 'LOCK MONITOR', 'TASK MANAGER', 'TASK MANAGER', 'CHECKPOINT', 'BRKR TASK', 'LAZY WRITER', 'SIGNAL HANDLER', 'TRACE QUEUE TASK', 'BRKR EVENT HNDLR', 'GHOST CLEANUP', 'RECOVERY WRITER', 'SYSTEM_HEALTH_MONITOR', 'RECEIVE', 'UNKNOWN TOKEN', 'FT FULL PASS', 'FT CRAWL MON')
	and isnull(s.program_name, '') <> 'SQL diagnostic manager Collection Service'
ORDER BY ExecutionTime_Minutes DESC;
-- all fragmented indexes on current db, % fragmentation > 30
SELECT a.index_id, OBJECT_NAME(a.object_id), name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(DB_NAME()), 
      NULL, NULL, NULL, NULL) AS a  
     JOIN sys.indexes AS b 
     ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent > 30
order by avg_fragmentation_in_percent desc
GO 
-- rebuild all indexes online
ALTER INDEX ALL ON Table1
REBUILD WITH (ONLINE = ON);   
GO  
-- rebuild single index online
ALTER INDEX IX_IndexName ON Table1
REBUILD WITH (ONLINE = ON);   
GO  
BEGIN TRY
    DROP TABLE [Dim].[Date]
END TRY

BEGIN CATCH
    /*No Action*/
END CATCH

CREATE TABLE [Dim].[Date]
(
    [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
    [DaySuffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc
    [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,
    [IsHoliday] BIT,-- Flag 1=National Holiday, 0-No National Holiday
    [IsWeekday] BIT,-- 0=Week End ,1=Week Day
    [HolidayName] VARCHAR(50),--Name of Holiday in US
)
GO

--=========================================================================================
--Specify Start Date and End date here
--Value of Start Date Must be Less than Your End Date 
--=========================================================================================

DECLARE @StartDate DATETIME = '12/29/2014' --Starting value of Date Range
DECLARE @EndDate DATETIME = '01/01/2100' --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 day of week logic*/
BEGIN
    /*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 [Dim].[Date]
    SELECT
        
        CONVERT (char(8),@CurrentDate,112) as 'DateKey',
        @CurrentDate AS 'Date',
        CONVERT (char(10),@CurrentDate,101) as 'FullDate',
        DATEPART(DD, @CurrentDate) AS 'DayOfMonth',
        --Apply Suffix values like 1st, 2nd 3rd etc..
        CASE 
            WHEN DATEPART(DD,@CurrentDate) IN (11,12,13) THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
            WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 1 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'st'
            WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 2 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'nd'
            WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 3 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'rd'
            ELSE CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th' 
        END AS 'DaySuffix',
        
        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',
        NULL AS 'IsHoliday',
        CASE DATEPART(DW, @CurrentDate)
            WHEN 1 THEN 0
            WHEN 2 THEN 1
            WHEN 3 THEN 1
            WHEN 4 THEN 1
            WHEN 5 THEN 1
            WHEN 6 THEN 1
            WHEN 7 THEN 0
        END AS 'IsWeekday',
        NULL AS 'HolidayName'

    SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END
        
--============================================================================
-- Step 3.
-- Update Values of Holiday as per USA Govt. Declaration for National Holiday.
--============================================================================

/*Update HOLIDAY Field of USA In dimension*/
    /* New Years Day - January 1 */
    UPDATE [Dim].[Date]
        SET HolidayName = 'New Year''s Day'
    WHERE [Month] = 1 AND [DayOfMonth] = 1

    /* Martin Luther King, Jr. Day - Third Monday in January starting in 1983 */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Martin Luther King, Jr. Day'
    WHERE
        [Month] = 1 AND
        [DayOfWeek] = 'Monday' AND
        [Year] >= 1983 AND
        DayOfWeekInMonth = 3

    /* Valentine's Day - February 14 */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Valentine''s Day'
    WHERE
        [Month] = 2 AND
        [DayOfMonth] = 14

    /* President's Day - Third Monday in February */
    UPDATE [Dim].[Date]
        SET HolidayName = 'President''s Day'
    WHERE
        [Month] = 2 AND
        [DayOfWeek] = 'Monday' AND
        [DayOfWeekInMonth] = 3

    /* Saint Patrick's Day */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Saint Patrick''s Day'
    WHERE
        [Month] = 3 AND
        [DayOfMonth] = 17

    /* Memorial Day - Last Monday in May */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Memorial Day'
    FROM [Dim].[Date]
    WHERE DateKey IN 
    (
        SELECT
            MAX(DateKey)
        FROM [Dim].[Date]
        WHERE
            [MonthName] = 'May' AND
            [DayOfWeek] = 'Monday'
        GROUP BY
            [Year],
            [Month]
    )

    /* Mother's Day - Second Sunday of May */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Mother''s Day'
    WHERE
        [Month] = 5 AND
        [DayOfWeek] = 'Sunday' AND
        [DayOfWeekInMonth] = 2

    /* Father's Day - Third Sunday of June */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Father''s Day'
    WHERE
        [Month] = 6 AND
        [DayOfWeek] = 'Sunday' AND
        [DayOfWeekInMonth] = 3

    /* Independence Day */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Independance Day'
    WHERE [Month] = 7 AND [DayOfMonth] = 4

    /* Labor Day - First Monday in September */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Labor Day'
    FROM [Dim].[Date]
    WHERE DateKey IN 
    (
        SELECT
            MIN(DateKey)
        FROM [Dim].[Date]
        WHERE
            [MonthName] = 'September' AND
            [DayOfWeek] = 'Monday'
        GROUP BY
            [Year],
            [Month]
    )

    /* Columbus Day - Second MONDAY in October */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Columbus Day'
    WHERE
        [Month] = 10 AND
        [DayOfWeek] = 'Monday' AND
        [DayOfWeekInMonth] = 2

    /* Halloween - 10/31 */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Halloween'
    WHERE
        [Month] = 10 AND
        [DayOfMonth] = 31

    /* Veterans Day - November 11 */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Veterans Day'
    WHERE
        [Month] = 11 AND
        [DayOfMonth] = 11
    
    /* Thanksgiving - Fourth THURSDAY in November */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Thanksgiving Day'
    WHERE
        [Month] = 11 AND
        [DayOfWeek] = 'Thursday' AND
        [DayOfWeekInMonth] = 4

    /* Christmas */
    UPDATE [Dim].[Date]
        SET HolidayName = 'Christmas Day'
    WHERE [Month] = 12 AND
          [DayOfMonth]  = 25
    
    /* Election Day - The first Tuesday after the first Monday in November */
    BEGIN
    DECLARE @Holidays TABLE
    (
        [ID] INT IDENTITY(1,1),
        [DateID] INT,
        [Week] TINYINT,
        [Year] CHAR(4),
        [Day] CHAR(2)
    )

        INSERT INTO @Holidays([DateID], [Year], [Day])
            SELECT
                [DateKey],
                [Year],
                [DayOfMonth] 
            FROM [Dim].[Date]
            WHERE
                [Month] = 11 AND 
                [DayOfWeek] = 'Monday'
            ORDER BY
                [Year],
                [DayOfMonth]

        DECLARE @CNTR INT,
                @POS INT,
                @STARTYEAR INT,
                @ENDYEAR INT,
                @MINDAY INT

        SELECT @CURRENTYEAR = MIN([Year])
             , @STARTYEAR = MIN([Year])
             , @ENDYEAR = MAX([Year])
        FROM @Holidays

        WHILE @CURRENTYEAR <= @ENDYEAR
        BEGIN
            SELECT @CNTR = COUNT([Year])
            FROM @Holidays
            WHERE [Year] = @CURRENTYEAR

            SET @POS = 1

            WHILE @POS <= @CNTR
            BEGIN
                SELECT @MINDAY = MIN(DAY)
                FROM @Holidays
                WHERE
                    [Year] = @CURRENTYEAR AND
                    [Week] IS NULL

                UPDATE @Holidays
                    SET [Week] = @POS
                WHERE
                    [Year] = @CURRENTYEAR AND
                    [Day] = @MINDAY

                SELECT @POS = @POS + 1
            END

            SELECT @CURRENTYEAR = @CURRENTYEAR + 1
        END

        UPDATE [Dim].[Date]
            SET HolidayName  = 'Election Day'
        FROM [Dim].[Date] DT
            JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey
        WHERE
            [Week] = 1
    END
    --set flag for USA holidays in Dimension
    UPDATE [Dim].[Date]
        SET IsHoliday = CASE WHEN HolidayName IS NULL THEN 0
                                WHEN HolidayName IS NOT NULL THEN 1 END

/*****************************************************************************************/

/* Add Fiscal Calendar columns into table DimDate */

ALTER TABLE [Dim].[Date] ADD
    [FiscalDayOfYear] VARCHAR(3),
    [FiscalWeekOfYear] VARCHAR(3),
    [FiscalMonth] VARCHAR(2), 
    [FiscalQuarter] CHAR(1),
    [FiscalQuarterName] VARCHAR(9),
    [FiscalYear] CHAR(4),
    [FiscalYearName] CHAR(7),
    [FiscalMonthYear] CHAR(10),
    [FiscalMMYYYY] CHAR(6),
    [FiscalFirstDayOfMonth] DATE,
    [FiscalLastDayOfMonth] DATE,
    [FiscalFirstDayOfQuarter] DATE,
    [FiscalLastDayOfQuarter] DATE,
    [FiscalFirstDayOfYear] DATE,
    [FiscalLastDayOfYear] DATE

GO

/***************************************************************************
The following section needs to be populated for defining the fiscal calendar
***************************************************************************/

DECLARE
    @dtFiscalYearStart SMALLDATETIME = 'December 29, 2014',
    @FiscalYear INT = 2015,
    @LastYear INT = 2100,
    @FirstLeapYearInPeriod INT = 2012

/*****************************************************************************************/

DECLARE
    @iTemp INT,
    @LeapWeek INT,
    @CurrentDate DATETIME,
    @FiscalDayOfYear INT,
    @FiscalWeekOfYear INT,
    @FiscalMonth INT,
    @FiscalQuarter INT,
    @FiscalQuarterName VARCHAR(10),
    @FiscalYearName VARCHAR(7),
    @LeapYear INT,
    @FiscalFirstDayOfYear DATE,
    @FiscalFirstDayOfQuarter DATE,
    @FiscalFirstDayOfMonth DATE,
    @FiscalLastDayOfYear DATE,
    @FiscalLastDayOfQuarter DATE,
    @FiscalLastDayOfMonth DATE

/*Holds the years that have 455 in last quarter*/

DECLARE @LeapTable TABLE (leapyear INT)

/*TABLE to contain the fiscal year calendar*/

DECLARE @tb TABLE
(
    [PeriodDate] DATETIME,
    [FiscalDayOfYear] VARCHAR(3),
    [FiscalWeekOfYear] VARCHAR(3),
    [FiscalMonth] VARCHAR(2), 
    [FiscalQuarter] VARCHAR(1),
    [FiscalQuarterName] VARCHAR(9),
    [FiscalYear] VARCHAR(4),
    [FiscalYearName] VARCHAR(7),
    [FiscalMonthYear] VARCHAR(10),
    [FiscalMMYYYY] VARCHAR(6),
    [FiscalFirstDayOfMonth] DATE,
    [FiscalLastDayOfMonth] DATE,
    [FiscalFirstDayOfQuarter] DATE,
    [FiscalLastDayOfQuarter] DATE,
    [FiscalFirstDayOfYear] DATE,
    [FiscalLastDayOfYear] DATE
)

/*Populate the table with all leap years*/

SET @LeapYear = @FirstLeapYearInPeriod
WHILE (@LeapYear < @LastYear)
    BEGIN
        INSERT INTO @leapTable VALUES (@LeapYear)
        SET @LeapYear = @LeapYear + 6
    END

/*Initiate parameters before loop*/

SET @CurrentDate = @dtFiscalYearStart
SET @FiscalDayOfYear = 1
SET @FiscalWeekOfYear = 1
SET @FiscalMonth = 1
SET @FiscalQuarter = 1
SET @FiscalWeekOfYear = 1

IF (EXISTS (SELECT * FROM @LeapTable WHERE @FiscalYear = leapyear))
    BEGIN
        SET @LeapWeek = 1
    END
    ELSE
    BEGIN
        SET @LeapWeek = 0
    END

/*******************************************************************************************/

/* Loop on days in interval*/

WHILE (DATEPART(yy,@CurrentDate) <= @LastYear)
BEGIN
    
/*SET fiscal Month*/
    SELECT @FiscalMonth = CASE
        /* 
        /*Use this section for a 4-5-4 calendar.  
        Every leap year the result will be a 4-5-5*/
        WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 5 AND 9 THEN 2 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 10 AND 13 THEN 3 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 18 AND 22 THEN 5 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 23 AND 26 THEN 6 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 31 AND 35 THEN 8 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 36 AND 39 THEN 9 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 44 AND (48+@LeapWeek) THEN 11 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN (49+@LeapWeek) AND (52+@LeapWeek) THEN 12 /*4 weeks (5 weeks on leap year)*/
        */

        /*Use this section for a 4-4-5 calendar.  
        Every leap year the result will be a 4-5-5*/
        WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 5 AND 8 THEN 2 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 9 AND 13 THEN 3 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 18 AND 21 THEN 5 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 22 AND 26 THEN 6 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 31 AND 34 THEN 8 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 35 AND 39 THEN 9 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 44 AND (47+@leapWeek) THEN 11 /*4 weeks (5 weeks on leap year)*/
        WHEN @FiscalWeekOfYear BETWEEN (48 + @leapWeek) AND (52 + @leapWeek) THEN 12 /*5 weeks*/
        
    END

    /*SET Fiscal Quarter*/
    SELECT @FiscalQuarter = CASE 
        WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 1
        WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 2
        WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 3
        WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 4
    END
    
    SELECT @FiscalQuarterName = CASE 
        WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 'First'
        WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 'Second'
        WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 'Third'
        WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 'Fourth'
    END
    
    /*Set Fiscal Year Name*/
    SELECT @FiscalYearName = 'FY ' + CONVERT(VARCHAR, @FiscalYear)

    INSERT INTO @tb
    (
        PeriodDate,
        FiscalDayOfYear,
        FiscalWeekOfYear,
        FiscalMonth,
        FiscalQuarter,
        FiscalQuarterName,
        FiscalYear,
        FiscalYearName
    ) VALUES (
        @CurrentDate,
        @FiscalDayOfYear,
        @FiscalWeekOfYear,
        @FiscalMonth,
        @FiscalQuarter,
        @FiscalQuarterName,
        @FiscalYear,
        @FiscalYearName
    )

    /*SET next day*/
    SET @CurrentDate = DATEADD(dd, 1, @CurrentDate)
    SET @FiscalDayOfYear = @FiscalDayOfYear + 1
    SET @FiscalWeekOfYear = ((@FiscalDayOfYear-1) / 7) + 1


    IF (@FiscalWeekOfYear > (52+@LeapWeek))
    BEGIN
        /*Reset a new year*/
        SET @FiscalDayOfYear = 1
        SET @FiscalWeekOfYear = 1
        SET @FiscalYear = @FiscalYear + 1
        IF (EXISTS (SELECT * FROM @leapTable WHERE @FiscalYear = leapyear))
        BEGIN
            SET @LeapWeek = 1
        END
        ELSE
        BEGIN
            SET @LeapWeek = 0
        END
    END
END

/********************************************************************************************/

/*Set first and last days of the fiscal months*/
UPDATE @tb
SET
    FiscalFirstDayOfMonth = minmax.StartDate,
    FiscalLastDayOfMonth = minmax.EndDate
FROM
    @tb t,
    (
        SELECT
            FiscalMonth,
            FiscalQuarter,
            FiscalYear,
            MIN(PeriodDate) AS StartDate, 
            MAX(PeriodDate) AS EndDate
        FROM @tb
        GROUP BY
            FiscalMonth,
            FiscalQuarter,
            FiscalYear
    ) minmax

WHERE
    t.FiscalMonth = minmax.FiscalMonth AND
    t.FiscalQuarter = minmax.FiscalQuarter AND
    t.FiscalYear = minmax.FiscalYear 

/*Set first and last days of the fiscal quarters*/

UPDATE @tb
SET FiscalFirstDayOfQuarter = minmax.StartDate,
    FiscalLastDayOfQuarter = minmax.EndDate
FROM
    @tb t,
    (
        SELECT
            FiscalQuarter,
            FiscalYear,
            MIN(PeriodDate) as StartDate,
            MAX(PeriodDate) as EndDate
        FROM
            @tb
        GROUP BY
            FiscalQuarter,
            FiscalYear
    ) minmax
WHERE
    t.FiscalQuarter = minmax.FiscalQuarter AND
    t.FiscalYear = minmax.FiscalYear 

/*Set first and last days of the fiscal years*/

UPDATE @tb
SET
    FiscalFirstDayOfYear = minmax.StartDate,
    FiscalLastDayOfYear = minmax.EndDate
FROM
@tb t,
(
    SELECT FiscalYear, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
    FROM @tb
    GROUP BY FiscalYear
) minmax
WHERE
    t.FiscalYear = minmax.FiscalYear 

/*Set FiscalYearMonth*/
UPDATE @tb
SET
    FiscalMonthYear = 
        CASE FiscalMonth
        WHEN 1 THEN 'Jan'
        WHEN 2 THEN 'Feb'
        WHEN 3 THEN 'Mar'
        WHEN 4 THEN 'Apr'
        WHEN 5 THEN 'May'
        WHEN 6 THEN 'Jun'
        WHEN 7 THEN 'Jul'
        WHEN 8 THEN 'Aug'
        WHEN 9 THEN 'Sep'
        WHEN 10 THEN 'Oct'
        WHEN 11 THEN 'Nov'
        WHEN 12 THEN 'Dec'
        END + '-' + CONVERT(VARCHAR, FiscalYear)

/*Set FiscalMMYYYY*/
UPDATE @tb
SET
    FiscalMMYYYY = RIGHT('0' + CONVERT(VARCHAR, FiscalMonth),2) + CONVERT(VARCHAR, FiscalYear)

/********************************************************************************************/

UPDATE [Dim].[Date]
    SET FiscalDayOfYear = a.FiscalDayOfYear
      , FiscalWeekOfYear = a.FiscalWeekOfYear
      , FiscalMonth = a.FiscalMonth
      , FiscalQuarter = a.FiscalQuarter
      , FiscalQuarterName = a.FiscalQuarterName
      , FiscalYear = a.FiscalYear
      , FiscalYearName = a.FiscalYearName
      , FiscalMonthYear = a.FiscalMonthYear
      , FiscalMMYYYY = a.FiscalMMYYYY
      , FiscalFirstDayOfMonth = a.FiscalFirstDayOfMonth
      , FiscalLastDayOfMonth = a.FiscalLastDayOfMonth
      , FiscalFirstDayOfQuarter = a.FiscalFirstDayOfQuarter
      , FiscalLastDayOfQuarter = a.FiscalLastDayOfQuarter
      , FiscalFirstDayOfYear = a.FiscalFirstDayOfYear
      , FiscalLastDayOfYear = a.FiscalLastDayOfYear
FROM @tb a
    INNER JOIN [Dim].[Date] b ON a.PeriodDate = b.[Date]

/********************************************************************************************/

SELECT * FROM [Dim].[Date]
DECLARE @Object AS INT;
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) = 
'{
    "what": 1,
    "ever": "you",
    "need": "to send as the body"
}'  

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post','http://requestb.in/1h83e3n1', 'false'

EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
EXEC sp_OAMethod @Object, 'send', null, @body

EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
SELECT @ResponseText

EXEC sp_OADestroy @Object
DECLARE @ClarionDate INT = 47563
DECLARE @SqlDateTime DATETIME 

-- Convert the clarion DATE into and SQL DateTime
SET @SqlDateTime = DateAdd(day, @ClarionDate  - 4, '1801-01-01') 

SELECT @SqlDateTime AS 'SQL Date Time'

-- Now convert it back from and SQL DateTime to a Clarion Date
SET @ClarionDate = DateDiff(day, DateAdd(day, -4, '1801-01-01'), @SqlDateTime)
SELECT @ClarionDate AS 'Clarion Date'

Similiar Collections

Python strftime reference pandas.Period.strftime python - Formatting Quarter time in pandas columns - Stack Overflow python - Pandas: Change day - Stack Overflow python - Check if multiple columns exist in a df - Stack Overflow Pandas DataFrame apply() - sending arguments examples python - How to filter a dataframe of dates by a particular month/day? - Stack Overflow python - replace a value in the entire pandas data frame - Stack Overflow python - Replacing blank values (white space) with NaN in pandas - Stack Overflow python - get list from pandas dataframe column - Stack Overflow python - How to drop rows of Pandas DataFrame whose value in a certain column is NaN - Stack Overflow python - How to drop rows of Pandas DataFrame whose value in a certain column is NaN - Stack Overflow python - How to lowercase a pandas dataframe string column if it has missing values? - Stack Overflow How to Convert Integers to Strings in Pandas DataFrame - Data to Fish How to Convert Integers to Strings in Pandas DataFrame - Data to Fish create a dictionary of two pandas Dataframe columns? - Stack Overflow python - ValueError: No axis named node2 for object type <class 'pandas.core.frame.DataFrame'> - Stack Overflow Python Pandas iterate over rows and access column names - Stack Overflow python - Creating dataframe from a dictionary where entries have different lengths - Stack Overflow python - Deleting DataFrame row in Pandas based on column value - Stack Overflow python - How to check if a column exists in Pandas - Stack Overflow python - Import pandas dataframe column as string not int - Stack Overflow python - What is the most efficient way to create a dictionary of two pandas Dataframe columns? - Stack Overflow Python Loop through Excel sheets, place into one df - Stack Overflow python - How do I get the row count of a Pandas DataFrame? - Stack Overflow python - How to save a new sheet in an existing excel file, using Pandas? - Stack Overflow Python Loop through Excel sheets, place into one df - Stack Overflow How do I select a subset of a DataFrame? — pandas 1.2.4 documentation python - Delete column from pandas DataFrame - Stack Overflow python - Convert list of dictionaries to a pandas DataFrame - Stack Overflow How to Add or Insert Row to Pandas DataFrame? - Python Examples python - Check if a value exists in pandas dataframe index - Stack Overflow python - Set value for particular cell in pandas DataFrame using index - Stack Overflow python - Pandas Dataframe How to cut off float decimal points without rounding? - Stack Overflow python - Pandas: Change day - Stack Overflow python - Clean way to convert quarterly periods to datetime in pandas - Stack Overflow Pandas - Number of Months Between Two Dates - Stack Overflow python - MonthEnd object result in <11 * MonthEnds> instead of number - Stack Overflow python - Extracting the first day of month of a datetime type column in pandas - Stack Overflow
MySQL MULTIPLES INNER JOIN How to Use EXISTS, UNIQUE, DISTINCT, and OVERLAPS in SQL Statements - dummies postgresql - SQL OVERLAPS PostgreSQL Joins: Inner, Outer, Left, Right, Natural with Examples PostgreSQL Joins: A Visual Explanation of PostgreSQL Joins PL/pgSQL Variables ( Format Dates ) The Ultimate Guide to PostgreSQL Date By Examples Data Type Formatting Functions PostgreSQL - How to calculate difference between two timestamps? | TablePlus Date/Time Functions and Operators PostgreSQL - DATEDIFF - Datetime Difference in Seconds, Days, Months, Weeks etc - SQLines CASE Statements in PostgreSQL - DataCamp SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN PostgreSQL DESCRIBE TABLE Quick and best way to Compare Two Tables in SQL - DWgeek.com sql - Best way to select random rows PostgreSQL - Stack Overflow PostgreSQL: Documentation: 13: 70.1. Row Estimation Examples Faster PostgreSQL Counting How to Add a Default Value to a Column in PostgreSQL - PopSQL How to Add a Default Value to a Column in PostgreSQL - PopSQL SQL Subquery - Dofactory SQL IN - SQL NOT IN - JournalDev DROP FUNCTION (Transact-SQL) - SQL Server | Microsoft Docs SQL : Multiple Row and Column Subqueries - w3resource PostgreSQL: Documentation: 9.5: CREATE FUNCTION PostgreSQL CREATE FUNCTION By Practical Examples datetime - PHP Sort a multidimensional array by element containing date - Stack Overflow database - Oracle order NULL LAST by default - Stack Overflow PostgreSQL: Documentation: 9.5: Modifying Tables PostgreSQL: Documentation: 14: SELECT postgresql - sql ORDER BY multiple values in specific order? - Stack Overflow How do I get the current unix timestamp from PostgreSQL? - Database Administrators Stack Exchange
Clear config cache Eloquent DB::Table RAW Query / WhereNull Laravel Eloquent "IN" Query get single column value in laravel eloquent php - How to use CASE WHEN in Eloquent ORM? - Stack Overflow AND-OR-AND + brackets with Eloquent - Laravel Daily Database: Query Builder - Laravel - The PHP Framework For Web Artisans ( RAW ) Combine Foreach Loop and Eloquent to perform a search | Laravel.io Access Controller method from another controller in Laravel 5 How to Call a controller function in another Controller in Laravel 5 php - Create a Laravel Request object on the fly php - Laravel 5.6 Upgrade caused Logging to break Artisan Console - Laravel - The PHP Framework For Web Artisans What to include in gitignore for a Laravel and PHPStorm project php - Create a Laravel Request object on the fly Process big DB table with chunk() method - Laravel Daily How to insert big data on the laravel? - Stack Overflow php - How can I build a condition based query in Laravel? - Stack Overflow Laravel Chunk Eloquent Method Example - Tuts Make Database: Migrations - Laravel - The PHP Framework For Web Artisans php - Laravel Model Error Handling when Creating - Exception Laravel - Inner Join with Multiple Conditions Example using Query Builder - ItSolutionStuff.com laravel cache disable phpunit code example | Newbedev In PHP, how to check if a multidimensional array is empty? · Humblix php - Laravel firstOrNew how to check if it's first or new? - Stack Overflow get base url laravel 8 Code Example Using gmail smtp via Laravel: Connection could not be established with host smtp.gmail.com [Connection timed out #110] - Stack Overflow
PostgreSQL POSITION() function PostgresQL ANY / SOME Operator ( IN vs ANY ) PostgreSQL Substring - Extracting a substring from a String How to add an auto-incrementing primary key to an existing table, in PostgreSQL PostgreSQL STRING_TO_ARRAY()function mysql FIND_IN_SET equivalent to postgresql PL/pgSQL Variables ( Format Dates ) The Ultimate Guide to PostgreSQL Date By Examples Data Type Formatting Functions PostgreSQL - How to calculate difference between two timestamps? | TablePlus Date/Time Functions and Operators PostgreSQL - DATEDIFF - Datetime Difference in Seconds, Days, Months, Weeks etc - SQLines CASE Statements in PostgreSQL - DataCamp SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN PL/pgSQL Variables PostgreSQL: Documentation: 11: CREATE PROCEDURE Reading a Postgres EXPLAIN ANALYZE Query Plan Faster PostgreSQL Counting sql - Fast way to discover the row count of a table in PostgreSQL - Stack Overflow PostgreSQL: Documentation: 9.1: tablefunc PostgreSQL DESCRIBE TABLE Quick and best way to Compare Two Tables in SQL - DWgeek.com sql - Best way to select random rows PostgreSQL - Stack Overflow How to Add a Default Value to a Column in PostgreSQL - PopSQL How to Add a Default Value to a Column in PostgreSQL - PopSQL PL/pgSQL IF Statement PostgreSQL: Documentation: 9.1: Declarations SQL Subquery - Dofactory SQL IN - SQL NOT IN - JournalDev PostgreSQL - IF Statement - GeeksforGeeks How to work with control structures in PostgreSQL stored procedures: Using IF, CASE, and LOOP statements | EDB PL/pgSQL IF Statement How to combine multiple selects in one query - Databases - ( loop reference ) DROP FUNCTION (Transact-SQL) - SQL Server | Microsoft Docs SQL : Multiple Row and Column Subqueries - w3resource PostgreSQL: Documentation: 9.5: CREATE FUNCTION PostgreSQL CREATE FUNCTION By Practical Examples datetime - PHP Sort a multidimensional array by element containing date - Stack Overflow database - Oracle order NULL LAST by default - Stack Overflow PostgreSQL: Documentation: 9.5: Modifying Tables PostgreSQL: Documentation: 14: SELECT PostgreSQL Array: The ANY and Contains trick - Postgres OnLine Journal postgresql - sql ORDER BY multiple values in specific order? - Stack Overflow sql - How to aggregate two PostgreSQL columns to an array separated by brackets - Stack Overflow How do I get the current unix timestamp from PostgreSQL? - Database Administrators Stack Exchange
כמה עוד נשאר למשלוח חינם גם לעגלה ולצקאאוט הוספת צ'קבוקס לאישור דיוור בצ'קאאוט הסתרת אפשרויות משלוח אחרות כאשר משלוח חינם זמין דילוג על מילוי כתובת במקרה שנבחרה אפשרות איסוף עצמי הוספת צ'קבוקס לאישור דיוור בצ'קאאוט שינוי האפשרויות בתפריט ה-סידור לפי בווקומרס שינוי הטקסט "אזל מהמלאי" הערה אישית לסוף עמוד העגלה הגבלת רכישה לכל המוצרים למקסימום 1 מכל מוצר קבלת שם המוצר לפי ה-ID בעזרת שורטקוד הוספת כפתור וואטסאפ לקנייה בלופ ארכיון מוצרים הפיכה של מיקוד בצ'קאאוט ללא חובה מעבר ישיר לצ'קאאוט בלחיתה על הוספה לסל (דילוג עגלה) התראה לקבלת משלוח חינם בדף עגלת הקניות גרסה 1 התראה לקבלת משלוח חינם בדף עגלת הקניות גרסה 2 קביעה של מחיר הזמנה מינימלי (מוצג בעגלה ובצ'קאאוט) העברת קוד הקופון ל-ORDER REVIEW העברת קוד הקופון ל-ORDER REVIEW Kadence WooCommerce Email Designer קביעת פונט אסיסנט לכל המייל בתוסף מוצרים שאזלו מהמלאי - יופיעו מסומנים באתר, אבל בתחתית הארכיון הוספת כפתור "קנה עכשיו" למוצרים הסתרת אפשרויות משלוח אחרות כאשר משלוח חינם זמין שיטה 2 שינוי סימן מטבע ש"ח ל-ILS להפוך סטטוס הזמנה מ"השהייה" ל"הושלם" באופן אוטומטי תצוגת הנחה באחוזים שינוי טקסט "בחר אפשרויות" במוצרים עם וריאציות חיפוש מוצר לפי מק"ט שינוי תמונת מוצר לפי וריאציה אחרי בחירה של וריאציה אחת במקרה של וריאציות מרובות הנחה קבועה לפי תפקיד בתעריף קבוע הנחה קבועה לפי תפקיד באחוזים הסרה של שדות משלוח לקבצים וירטואליים הסתרת טאבים מעמוד מוצר הצגת תגית "אזל מהמלאי" בלופ המוצרים להפוך שדות ל-לא חובה בצ'קאאוט שינוי טקסט "אזל מהמלאי" לוריאציות שינוי צבע ההודעות המובנות של ווקומרס הצגת ה-ID של קטגוריות המוצרים בעמוד הקטגוריות אזל מהמלאי- שינוי ההודעה, תגית בלופ, הודעה בדף המוצר והוספת אזל מהמלאי על וריאציה הוספת שדה מחיר ספק לדף העריכה שינוי טקסט אזל מהמלאי תמונות מוצר במאונך לצד תמונת המוצר הראשית באלמנטור הוספת כפתור קנה עכשיו לעמוד המוצר בקניה הזו חסכת XX ש''ח לאפשר למנהל חנות לנקות קאש ברוקט לאפשר רק מוצר אחד בעגלת קניות הוספת סימון אריזת מתנה ואזור להוראות בצ'קאאוט של ווקומרס הצגת הנחה במספר (גודל ההנחה) הוספת "אישור תקנון" לדף התשלום
החלפת טקסט באתר (מתאים גם לתרגום נקודתי) הסרת פונטים של גוגל מתבנית KAVA ביטול התראות במייל על עדכון וורדפרס אוטומטי הוספת תמיכה בקבצי VCF באתר (קבצי איש קשר VCARD) - חלק 1 להחריג קטגוריה מסוימת מתוצאות החיפוש שליפת תוכן של ריפיטר יצירת כפתור שיתוף למובייל זיהוי אלו אלמנטים גורמים לגלילה אופקית התקנת SMTP הגדרת טקסט חלופי לתמונות לפי שם הקובץ הוספת התאמת תוספים לגרסת WP הוספת טור ID למשתמשים הסרת כותרת בתבנית HELLO הסרת תגובות באופן גורף הרשאת SVG חילוץ החלק האחרון של כתובת העמוד הנוכחי חילוץ הסלאג של העמוד חילוץ כתובת העמוד הנוכחי מניעת יצירת תמונות מוקטנות התקנת SMTP הצגת ה-ID של קטגוריות בעמוד הקטגוריות להוריד מתפריט הניהול עמודים הוספת Favicon שונה לכל דף ודף הוספת אפשרות שכפול פוסטים ובכלל (של שמעון סביר) הסרת תגובות באופן גורף 2 בקניה הזו חסכת XX ש''ח חיפוש אלמנטים סוררים, גלישה צדית במובייל שיטה 1 לאפשר רק מוצר אחד בעגלת קניות הצגת הנחה במספר (גודל ההנחה) הוספת "אישור תקנון" לדף התשלום שינוי צבע האדמין לפי סטטוס העמוד/פוסט שינוי צבע אדמין לכולם לפי הסכמות של וורדפרס תצוגת כמות צפיות מתוך הדשבורד של וורדפרס הצגת סוג משתמש בפרונט
הודעת שגיאה מותאמת אישית בטפסים להפוך כל סקשן/עמודה לקליקבילית (לחיצה) - שיטה 1 להפוך כל סקשן/עמודה לקליקבילית (לחיצה) - שיטה 2 שינוי הגבלת הזיכרון בשרת הוספת לינק להורדת מסמך מהאתר במייל הנשלח ללקוח להפוך כל סקשן/עמודה לקליקבילית (לחיצה) - שיטה 3 יצירת כפתור שיתוף למובייל פתיחת דף תודה בטאב חדש בזמן שליחת טופס אלמנטור - טופס בודד בדף פתיחת דף תודה בטאב חדש בזמן שליחת טופס אלמנטור - טפסים מרובים בדף ביי ביי לאריק ג'ונס (חסימת ספאם בטפסים) זיהוי אלו אלמנטים גורמים לגלילה אופקית לייבלים מרחפים בטפסי אלמנטור יצירת אנימציה של "חדשות רצות" בג'ט (marquee) שינוי פונט באופן דינאמי בג'ט פונקציה ששולפת שדות מטא מתוך JET ומאפשרת לשים הכל בתוך שדה SELECT בטופס אלמנטור הוספת קו בין רכיבי התפריט בדסקטופ ולדציה למספרי טלפון בטפסי אלמנטור חיבור שני שדות בטופס לשדה אחד שאיבת נתון מתוך כתובת ה-URL לתוך שדה בטופס וקידוד לעברית מדיה קוורי למובייל לייבלים מרחפים בטפסי אלמנטור תמונות מוצר במאונך לצד תמונת המוצר הראשית באלמנטור הצגת תאריך עברי פורמט תאריך מותאם אישית תיקון שדה תאריך בטופס אלמנטור במובייל שאיבת פרמטר מתוך הכתובת והזנתו לתוך שדה בטופס (PARAMETER, URL, INPUT) עמודות ברוחב מלא באלמנטור עמודה דביקה בתוך אלמנטור יצירת "צל" אומנותי קוד לסוויצ'ר, שני כפתורים ושני אלמנטים סקריפט לסגירת פופאפ של תפריט לאחר לחיצה על אחד העמודים