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 Search database for tables with specific Column Name

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%ColumnName%'
ORDER BY    TableName
            ,ColumnName;

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 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;

JavaScript: Conditionally Hide/Show Tabs and Sections

function showHideSection()
{
    var type = Xrm.Page.getAttribute("FIELD").getValue();
	
    switch(type) {
        case 1: // If FIELD value is 1, then hide the section
            // Hides section
            Xrm.Page.ui.tabs.get("TAB NAME").sections.get("SECTION NAME").setVisible(false);
            break;
            // Shows section
            Xrm.Page.ui.tabs.get("TAB NAME").sections.get("SECTION NAME").setVisible(true);

    }
}

function showHideTab()
{
    var type = Xrm.Page.getAttribute("FIELD").getValue();
	
    switch(type) {
        case 100000000: // If FIELD value is 100000000, then hide the tab
            // Hide tab
            Xrm.Page.ui.tabs.get("TAB NAME").setVisible(false);
            break;
            // Show tab
            Xrm.Page.ui.tabs.get("TAB NAME").setVisible(true);

    }
}