CRM, SSIS & Integrations ...configuring CRM as a non-coder
  • Home
  • Categories
    • Visual Studio
    • KingswaySoft
    • SSIS
    • JavaScript
    • CRM
    • SQL
    • ERP
    • Azure
    • How-to
    • Clubtimiser A/S
    • Office
    • Personal
  • About me
  • Privacy Policy
CRM, SSIS & Integrations
  • Home
  • Categories
    • Visual Studio
    • KingswaySoft
    • SSIS
    • JavaScript
    • CRM
    • SQL
    • ERP
    • Azure
    • How-to
    • Clubtimiser A/S
    • Office
    • Personal
  • About me
  • Privacy Policy
  • Home
  • Script Repository

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

    }
}

The author

KingswaySoft MVP & Microsoft certified professional

My name is Thomas and I am a Senior Consultant at a company called netcompany A/S in Denmark. My primary focus is working with SSIS and KingswaySoft, where I perform integrations and datatranformations.

Click for more info

Recent Posts

  • Homemade SSIS load balancer for large data loads
  • Difference between two dates (SQL, SSIS and JavaScript)
  • Easy way to fetch row count for all tables in a SQL database
  • Create Application user in Azure for use in Dynamics 365
  • SSIS Basics: Merge join

Useful information

  • Installation guide to KingswaySoft
  • Software download list
  • CRM Object Type Codes
  • CRM State & Status codes
  • Script Repository

Archives

  • February 2021 (1)
  • May 2020 (1)
  • February 2020 (1)
  • January 2020 (2)
  • August 2019 (1)
  • May 2019 (1)
  • February 2019 (2)
  • January 2019 (2)
  • November 2018 (1)
  • September 2018 (1)
  • August 2018 (3)
  • May 2018 (4)
  • December 2017 (2)
  • August 2017 (1)
  • February 2017 (1)
  • November 2016 (4)
  • October 2016 (1)
  • September 2016 (2)
  • August 2016 (5)
  • June 2016 (4)
  • May 2016 (3)

Useful blogs/links

Henrik Jensen, my good friend and colleague, has a blog regarding EVERYTHING Dynamics crm. He's got 50+ certifications, and he is a force to be reckoned with. Link to his blog (danish): crmblog.dk

KingswaySoft have developed an outstanding piece of software for datamanagement to and from CRM, and I can recommend this toolkit for nearly every situation. Link to their website: kingswaysoft.com

Tag Cloud

Azure Bug CRM 2015 CRM 2016 CRM Online Data Dynamics365 ERP Excel How To JavaScript KingswaySoft Log News Office Outlook Personal Solution SQL SSIS Visual Studio

Categories

  • Azure (2)
  • Clubtimiser A/S (1)
  • CRM (31)
  • ERP (2)
  • How-to (15)
  • JavaScript (3)
  • Office (3)
  • Personal (4)
  • SQL (3)
  • Visual Studio (24)
    • SSIS (24)
      • KingswaySoft (15)

Disclaimer

The information given on the website is tested on a mixture of live production environments and test environments, and therefore I cannot guarantee my information will work for you, if you attempt to follow my steps.
If you experience problems, please feel free to contact me.

@June 7th 2016