Creating a Date Range from Multiple Rows Based on a Single Date

Solution (SQL 2005): Using a Self-JOIN

There are probably other ways to establish an exact match for the JOIN criteria of a self-JOIN that would be compatible back to SQL 2000, but we’ll simplify the task somewhat by using ROW_NUMBER(), which was introduced in SQL 2005.


-- Solution (SQL 2005): Using a self-JOIN (ROW_NUMBER establishes exact match criteria)

WITH ProductPrices AS
(
    SELECT ProductID, EffectiveStartDT
        ,rn=ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY EffectiveStartDT)
    FROM dbo.ProductPrices
-- add filtering Condition
)
SELECT a.ProductID, a.EffectiveStartDT, EffectiveEndDT=b.EffectiveStartDT
FROM ProductPrices a
LEFT JOIN ProductPrices b ON a.ProductID = b.ProductID AND a.rn =b.rn-1
-- add filtering Condition
ORDER BY a.ProductID, a.EffectiveStartDT;


Result :
ProductID EffectiveDate        EffectiveEndDT
7103         2016-12-20           2017-01-01
7103        2017-01-01           2017-01-03
7103        2017-01-03           2017-03-28
7103        2017-03-28           2017-04-04
7103        2017-04-04           NULL

Disable Enable Trigger SQL server

Use the following Commands :-

ALTER TABLE your_table_name DISABLE TRIGGER your_trigger_name


ALTER TABLE your_table_name ENABLE TRIGGER your_trigger_name

SELECT from 3rd comma in string(Get the 4th occurrence of a value in a delimited string)

Syntax
declare @v varchar(max) = 'Text1,Text2,Text3,Text4'

select substring(@v, CHARINDEX(',', @v, CHARINDEX(',', @v, CHARINDEX(',', @v)+1)+1)+1, len(@v))


Result :
Text4

How to split a comma-separated value to columns in sql server (How to split a single column values to multiple column values? )

Syntax:
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mytable]') AND type in (N'U'))
DROP TABLE [dbo].[mytable]
GO
CREATE TABLE [dbo].[mytable](
[id] [int] NULL,
[FullName] [varchar](5000) NULL
) ON [PRIMARY]

GO

Insert into mytable values (1,'Test,King,Demo')


mytable:

Id     FullName
---  ----------------
1     Test,King,Demo


ResultTable:

To separate the comma delimited string into 3 columns

Id    FullName                  Name      Surname         Last
---   ------------                -------        -----------          ------
1     Test,King,Demo           Test        King            Demo



Method 1 :-

Use the CommonTableExpression(CTE)

-- Define the CTE expression name and column list.
;WITH Split_Names (ID,Name, xmlname)
AS
-- Define the CTE query.
(
    SELECT ID,
    Name,
    CONVERT(XML,'<Names><name>'
    + REPLACE(YourCommaSeparatedValueColumn,',', '</name><name>') + '</name></Names>') AS xmlname
      FROM YourTableName
)
-- Define the outer query referencing the CTE name.
 SELECT ID,   
 xmlname.value('/Names[1]/name[1]','varchar(100)') AS Name, 
 xmlname.value('/Names[1]/name[2]','varchar(100)') AS Surname,
 xmlname.value('/Names[1]/name[3]','varchar(100)') AS Last
 FROM Split_Names

Method 2:-

 SELECT DISTINCT id
, S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name
, S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS SurName
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Last
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(fullname, ',', '</r><r>')  + '</r></H>' AS XML) AS [vals]
FROM mytable) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)