Script Repository

SQL Select multiple rows without table

SELECT 1 as Column1, 2 as Column2, 3 as Column3
UNION ALL SELECT 4, 5, 6
UNION ALL SELECT 7, 8, 9

SQL Auto Increment

CREATE TABLE [TABLE_NAME](
[ID] INT IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NULL,
) ON [PRIMARY]
GO

Split FullName

SQL:
SELECT 
  [fullname],
  LTRIM(RTRIM(SUBSTRING(fullname, 1, CHARINDEX(' ', fullname)))) AS FirstName,
  LTRIM(RTRIM(SUBSTRING(fullname, CHARINDEX(' ', fullname) + 1, 8000))) AS LastName
FROM [STAGE_TABLES]

Excel:
Firstname =LEFT(A2,FIND(" ",a2,1)-1) 
Lastname =RIGHT(A2,LEN(A2)-FIND(" ",A2,1))

SSIS Derived Column
LEFT([FIELD],FINDSTRING([FIELD]," ",1) - 1) 
RIGHT([FIELD],LEN([FIELD]) - FINDSTRING([FIELD]," ",1))

SQL Explode rows

IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
  /*Then it exists*/
  DROP TABLE #TableA
SELECT * INTO #TableA
FROM
 (
    SELECT ID, ExcelName, CrmShortName FROM CustomerTable
    
) T

;WITH Nbrs ( Number ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + Number FROM Nbrs WHERE Number < 100
)
SELECT
   A.ID, A.ExcelName, A.CrmShortName
FROM
   #TableA A
   JOIN
   Nbrs N ON N.Number <= A.ID

   order by ID asc

SQL Round date

SQL: 
CAST(ROUND(CAST(datetime_field field as float),0) as datetime) as Datetime_field

SSIS derived column:
(DT_DBTIMESTAMP)ROUND((DT_R4)(DT_DATE) Datetime_field , 0 )

SQL Week generator

DECLARE @FromDate DATETIME, @ToDate DATETIME;
SET @FromDate = DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0);
SET @ToDate = DATEADD(YEAR,10,GETDATE());
SET DATEFIRST 7;

SELECT TOP (DATEDIFF(WEEK, @FromDate, @ToDate)+1) 
  TheStartDate  = DATEADD(WEEK, number, @FromDate),
  TheEndDate = DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK,0,DATEADD(WEEK, number, @FromDate))+1,0)),
  TheWeek = DATEPART(WEEK,(DATEADD(WEEK, number, @FromDate))),
  TheMonth = MONTH(DATEADD(WEEK, number, @FromDate)),
  TheQuarter = DATEPART(qq,DATEADD(WEEK, number, @FromDate)),
  TheYear  = YEAR(DATEADD(WEEK, number, @FromDate))
  FROM [master].dbo.spt_values 
  WHERE [type] = N'P' ORDER BY number;

SQL Month generator

DECLARE @FromDate DATETIME, @ToDate DATETIME;
SET @FromDate = DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0);
SET @ToDate = DATEADD(yyyy,50,GETDATE());

SELECT TOP (DATEDIFF(MONTH, @FromDate, @ToDate)+1) 
  TheStartDate  = DATEADD(MONTH, number, @FromDate),
  TheEndDate = DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH, number, @FromDate))+1,0)),
  TheMonth = MONTH(DATEADD(MONTH, number, @FromDate)),
  TheQuarter = DATEPART(qq,DATEADD(MONTH, number, @FromDate)),
  TheYear  = YEAR(DATEADD(MONTH, number, @FromDate))
  FROM [master].dbo.spt_values 
  WHERE [type] = N'P' ORDER BY number;

SQL Year generator

DECLARE @FromDate DATETIME, @ToDate DATETIME;
SET @FromDate = DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0);
SET @ToDate = DATEADD(yyyy,20,GETDATE());

SELECT TOP (DATEDIFF(YEAR, @FromDate, @ToDate)+1) 
  TheStartDate  = DATEADD(yy, DATEDIFF(yy, 0, DATEADD(YEAR, number, @FromDate)),0),
  TheEndDate = DATEADD(d,-1,DATEADD(yyyy, DATEDIFF(yyyy,0,DATEADD(YEAR, number, @FromDate))+1,0)),
  TheYear  = YEAR(DATEADD(YEAR, number, @FromDate))
  FROM [master].dbo.spt_values 
  WHERE [type] = N'P' ORDER BY number;

SQL Season generator

DECLARE @FromDate DATETIME, @ToDate DATETIME;
SET @FromDate = '01-01-2000' /*DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)*/;
SET @ToDate = DATEADD(yyyy,30,GETDATE());

SELECT TOP (DATEDIFF(YEAR, @FromDate, @ToDate)+1) 
  TheStartDate  = DATEADD(mm,6,DATEADD(yy, DATEDIFF(yy, 0, DATEADD(YEAR, number, @FromDate)),0)),
	
  TheEndDate =  DATEADD(dd,-1,DATEADD(yy,1,DATEADD(mm,6,DATEADD(yy, DATEDIFF(yy, 0, DATEADD(YEAR, number, @FromDate)),0)))),
  FirstYear  = YEAR(DATEADD(YEAR, number, @FromDate)),
  SecondYear  = YEAR(DATEADD(YEAR, number, @FromDate))+1,
  Season = CAST(YEAR(DATEADD(YEAR, number, @FromDate)) as nvarchar (4)) + '/' + CAST(YEAR(DATEADD(YEAR, number, @FromDate))+1 as nvarchar(4))
  FROM [master].dbo.spt_values 
  WHERE [type] = N'P' ORDER BY number;