Dutch Date Time Formant

select 
        convert(varchar(2),datepart(d,getdate()))                    -- Dag
+ ' ' + convert(varchar(20),datename (MONTH,getdate()))            -- Maandnaam
+ ' ' + convert(varchar(4),datepart(yyyy,getdate()))                -- Jaar
+ ' ' + convert(varchar(2),datepart(HH,getdate()))                    -- Uur
+ ':' + convert(varchar(2),datepart(mi,getdate()))                    -- Minuut
Till Next Time

Parent Child with Hierarchy Path

;WITH cte AS (
    SELECT
      EmployeeKey
    , FirstName + ' ' + LastName EmployeeName
    , ParentEmployeeKey
    , 1 EmployeeLevel
    , CAST(EmployeeKey as varchar(max)) as  HierarchyPath
    FROM dbo.DimEmployee
    WHERE ParentEmployeeKey IS NULL
    and EndDate IS NULL
    UNION ALL
    SELECT
      c.EmployeeKey
    , c.FirstName + ' ' + c.LastName
    , c.ParentEmployeeKey
    , p.EmployeeLevel + 1
    , p.HierarchyPath + '\' + cast(c.EmployeeKey as varchar(max)) as  HierarchyPath
    FROM dbo.DimEmployee c
    JOIN cte p ON p.EmployeeKey = c.ParentEmployeeKey
    where EndDate IS NULL
)
SELECT
      cte.EmployeeName
    , cte.EmployeeLevel
    , HierarchyPath
    , cte.EmployeeKey
    , cte.ParentEmployeeKey
    , case when sum(isnull(f.SalesAmount,0)) <> 0 then cast(1 as bit) else cast(0 as bit) end as HasFactRecords
 FROM cte
LEFT JOIN dbo.FactResellerSales f
  ON f.EmployeeKey = cte.EmployeeKey
  group by   cte.EmployeeName, cte.EmployeeLevel , HierarchyPath     , cte.EmployeeKey ,cte.ParentEmployeeKey
ORDER BY HierarchyPath, EmployeeName






Till Next Time

TSQL Parent Child

;WITH cte AS (
    SELECT
      EmployeeKey
    , FirstName + ' ' + LastName EmployeeName
    , ParentEmployeeKey
    , 1 EmployeeLevel
    FROM dbo.DimEmployee
    WHERE ParentEmployeeKey IS NULL
    UNION ALL
    SELECT
      c.EmployeeKey
    , c.FirstName + ' ' + c.LastName
    , c.ParentEmployeeKey
    , p.EmployeeLevel + 1
    FROM dbo.DimEmployee c
    JOIN cte p ON p.EmployeeKey = c.ParentEmployeeKey
)
SELECT
      REPLICATE('- ',cte.EmployeeLevel) +  cte.EmployeeName
    , cte.EmployeeLevel
    , sum(ISNULL(f.SalesAmount, 0)) SalesAmount
FROM cte
LEFT JOIN dbo.FactResellerSales f
  ON f.EmployeeKey = cte.EmployeeKey
  group by cte.EmployeeName,cte.ParentEmployeeKey,cte.EmployeeLevel
ORDER BY EmployeeLevel, EmployeeName

Till Next Time

TSQL Linear Regression

USE DWH;
GO

CREATE PROCEDURE LinearRegression (
  @TableName nvarchar(250)  = '[dbo].[linear]'
, @XColumnName nvarchar(250) = '[Xvalue]'
, @YColumnName nvarchar(250) = '[Yvalue]'
, @Where    nvarchar(250) = '[RecordId] <=11'
)
-----------------------------------------------------------------------
-- Liear regression using T-Sql
-- John Minkjan 2013
-----------------------------------------------------------------------
AS
BEGIN
declare @sqlstatement nvarchar(4000)
declare @MeanX decimal(18,6)
declare @MeanY decimal(18,6)
declare @VarX decimal(18,6)
declare @VarY decimal(18,6)
declare @StdX decimal(18,6)
declare @StdY decimal(18,6)
declare @sumX decimal(18,6)
declare @sumY decimal(18,6)
declare @sumXY decimal(18,6)
declare @X2 decimal(18,6)
declare @Y2 decimal(18,6)
declare @XY decimal(18,6)
declare @X2t decimal(18,6)
declare @Y2t decimal(18,6)
declare @XYt decimal(18,6)
declare @CorXY decimal(18,6)
declare @Slope decimal(18,6)
declare @Intercept decimal(18,6)
declare @CountXY bigint

-- get the basic stats
set @sqlstatement = 'Declare  users_cursor CURSOR FOR SELECT AVG('
    +@XColumnName +'), AVG(' + @YColumnName
    +') , COUNT(*), VAR('
    +@XColumnName +'), VAR(' + @YColumnName
    +') , STDEV('
    +@XColumnName +'), STDEV(' + @YColumnName
    +') , SUM('
    +@XColumnName +'), SUM(' + @YColumnName
    +') , SUM(' + @YColumnName
    +' * ' +@XColumnName +') FROM ' + @TableName + case when @Where <> '' then ' WHERE ' + @Where end

exec sp_executesql @sqlstatement

OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @MeanX ,@MeanY,@CountXY,@VarX ,@VarY,@StdX,@StdY,@sumX,@sumY,@sumXY

CLOSE users_cursor
DEALLOCATE users_cursor

-- Get the Linear regression info

set @X2 = 0
set @y2 = 0
set @xy = 0

set @sqlstatement = 'Declare  users_cursor CURSOR FOR SELECT ('
    + @XColumnName +' - ' +cast( @MeanX as varchar(250)) + ') * ('
    + @YColumnName +' - ' + cast( @MeanY as varchar(250)) + ') as XY, (('
    + @XColumnName +' - ' +cast( @MeanX as varchar(250))
    +') * (' + @XColumnName +' - ' +cast( @MeanX as varchar(250))+')) as X2 , (('
    + @YColumnName +' - ' + cast( @MeanY as varchar(250)) +') * ('
    + @YColumnName +' - ' + cast( @MeanY as varchar(250))+')) as Y2 '
    + ' FROM ' + @TableName + case when @Where <> '' then ' WHERE ' + @Where end

exec sp_executesql @sqlstatement

OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @xyt, @x2t, @y2t

WHILE @@FETCH_STATUS = 0
BEGIN

set @X2 = @x2+  @x2t
set @y2 = @y2+  @y2t
set @xy = @xy+  @xyt

FETCH NEXT FROM users_cursor
INTO @xyt, @x2t, @y2t

END

CLOSE users_cursor
DEALLOCATE users_cursor

-- Calculate the correlation
set @CorXY = (@xy / SQRT(@x2 * @y2))

-- Get the slope and intercept
set @Slope = (@xy/ @x2)
set @Intercept =  @MeanY-((@xy/ @x2) * @MeanX)

-- Output the result
set @sqlstatement = 'SELECT '
    +@XColumnName +' as X '
    +' ,'+@YColumnName +' as Y '
    +' ,'+cast(@MeanX as varchar(250)) + ' as MeanX  '
    +' ,'+cast(@MeanY  as varchar(250)) + ' as MeanY  '
    +' ,'+cast(@VarX as varchar(250))  +' as VarianceX  '
    +' ,'+cast(@VarY  as varchar(250)) + ' as VarianceY '
    +' ,'+cast(@CorXY  as varchar(250)) + ' as CorrelationXY'
    +' ,' +cast(@Slope  as varchar(250)) + ' as Slope '
    +' ,' +cast(@Intercept  as varchar(250)) + ' as Intercept '
        +' , (' + @XColumnName +' * ' + cast( @slope  as varchar(250)) +' ) +' +cast( @Intercept as varchar(250)) + ' as PredictedY'
    +' ,' +    cast(@StdX as varchar(250)) +' as  StandardDeviationX'
    +' ,' + cast(@StdY as varchar(250)) +' as  StandardDeviationY'
    +' ,' + cast(@sumX as varchar(250)) +' as  SumX'
    +' ,' +    cast(@sumY as varchar(250)) +' as  SumY'
    +' ,' +    cast(@sumXY as varchar(250)) +' as  SumXY'

    +' FROM ' + @TableName + case when @Where <> '' then ' WHERE ' + @Where end

exec sp_executesql @sqlstatement;
end

Till Next Time

UPDATE in Batches

WHILE (2 > 1)
BEGIN
  BEGIN TRANSACTION
  UPDATE TOP(100000)
  <<TABLE>>  
  SET <<COLUMN>> = <<VALUE>>
  WHERE <<WHERE_CLAUSE>>

   IF @@ROWCOUNT = 0
     BEGIN
       COMMIT TRANSACTION
       BREAK
     END
COMMIT TRANSACTION
-- 1 second delay
  WAITFOR DELAY '00:00:01'
END -- WHILE



Till Next Time

 

CHECKSUM_AGG

Playing around with CHECKSUM_AGG:

select CHECKSUM_AGG ( QTY ) AS CheckSumAgg_QTY, CHECKSUM_AGG ( Distinct QTY ) AS CheckSumAgg_DistinctQTY, sum(QTY) as Sum_QTY


from (

select 4 as qty

union all

select 2 as qty

union all

select 2 as qty

union all

select 2 as qty) T   Till Next Time

Playing around with default Contraints

select
schema_name(t.schema_id) as Schema_Name,
t.name as Table_Name,
d.name as Default_Constraint_Name,
c.name as Column_Name,
d.definition as Constraint_Definition
, 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(T.SCHEMA_ID)) +'.'+ QUOTENAME(T.NAME) + ' DROP CONSTRAINT ' + D.NAME + ';' as Drop_Statement
, 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(T.SCHEMA_ID)) +'.'+ QUOTENAME(T.NAME) + ' ADD CONSTRAINT DF_'+schema_name(t.schema_id)+'_'+t.name+'_'+ c.name + ' DEFAULT '+d.definition + ' FOR '+QUOTENAME( C.NAME) +';' as Create_Statement
, 'exec sp_rename ''' +schema_name(t.schema_id)+'.'+d.name +''', ''DF_'+schema_name(t.schema_id)+'_'+ t.name+'_'+ c.name +''';' as Rename_Statement
from sys.tables t
join sys.default_constraints d
on d.parent_object_id = t.object_id
join sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id

Till Next Time

TSQL Difference between 2 rows

use AdventureWorks2012;

select

CustomerID,
OrderDate,
TotalDue,
sum(Totaldue) over(partition by CustomerId order by OrderDate Desc ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ) as LastOrderAmount,
TotalDue - sum(Totaldue) over(partition by CustomerId order by OrderDate Desc ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ) as ChangeFromLastOrder from sales.SalesOrderHeader

order by CustomerID, OrderDate Desc

Till Next Time

TSQL Last Backup Taken

select

@@SERVERNAME as ServerName,
Database_name,
COALESCE(Convert(varchar(20), MAX(backup_finish_date), 112),'Backup Not Taken') as
LastBackUpTakenDate,
COALESCE(Convert(varchar(20), MAX(user_name), 101),'NA') as BackupTakenUser
from
msdb.dbo.backupset
GROUP BY
Database_name
order by
LastBackUpTakenDate desc

Till Next Time

TSQL Please avoid Float / Real datatypes

The Float / Real (=> Float(24)) datatype are Approximate-number data types. This means you can run into trouble converting them to other type or doing math with them:

From the documentation:

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

(see: http://msdn.microsoft.com/en-us/library/ms173773(v=sql.110).aspx)

Converting example:

DECLARE @f as Float = '29545428.022495';
select cast(@f as numeric(28,14));

result:

29545428.02249500200000

Math Example:

DECLARE @f as Float = '29545428.022495';

select cast(((@f * 4))as numeric(28,14)) ;

118181712.08998001000000

result:
select cast(@f as numeric(28,14)) * 4 ;

result:

118181712.08998000800000

Till Next Time

TSQL The danger of using SCOPE_IDENTITY() and @@IDENTITY

If you are using

select SCOPE_IDENTITY();

or

select @@IDENTITY;

to determine the last sequence number added to an identity column you must keep in mind that these are session and scope depended functions.

• SCOPE_IDENTITY() returns the last inserted identity value within the scope of the current session.
• @@IDENTITY returns the last inserted identity value within the current session.
  

Since both function are table independent you run the risks of getting the identity value for the wrong table. It’s better to use IDENT_CURRENT(‘TableName’);

Till Next Time

TSQL: Finding last key identity value

Often when people need the last inserted ID you see query’s like:

USE AdventureWorks2012;

select
    MAX([SalesOrderDetailID])
FROM
[Sales].[SalesOrderDetail];

On large table’s this can be very expensive especially if the identity column if part of a combined column primary key.

Luckily TSQL has a build in function called “IDENT_CURRENT”. This function gives back the last used identity value for a given table.

select IDENT_CURRENT( '[Sales].[SalesOrderDetail]' );

Till Next Time

TSQL EOMONTH Function

Most people use something like this:

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,SYSDATETIME())+1,0)) as LastDayOfCurrentMonth;

to determine the last day of the current month. The problem with this function is that it actually return the last second of the current month.

Few people now the build in TSQL function EOMONTH. Here are some examples:

SELECT EOMONTH(SYSDATETIME()) as LastDayOfCurrentMonth;
SELECT EOMONTH('20130115') as LastDayOfSpecificMonth;
SELECT DATEADD(DAY,1,EOMONTH(SYSDATETIME(),-1)) as FirstDayOfCurrentMonth;
SELECT DATEADD(DAY,1,EOMONTH('20130115',-1)) as FistDayOfSpecificMonth;

Till Next Time

TSQL COALESCE v ISNULL

The COALESCE function accepts a list of expressions as input and returns the first that is not NULL. COALESCE(<expr1>, <expr2>…<exprn>). The ISNULL function replaces  expression1 with expression2 if expression1 is NULL. The difference is that ISNULL uses the dataformat of expression1 for the replacement whereas COALESCE uses the dataformat of the first not NULL expression:

DECLARE
    @x as varchar(5) = NULL,
    @y as varchar(10) = '0123456789',
    @z as varchar(15) = 'ABCDEGHIJKLMNOP';

select
    COALESCE    (@X,@Y) as Cxy,
    ISNULL        (@x,@y)    as Ixy,
    COALESCE    (@X,@z) as Cxz,
    ISNULL        (@x,@z)    as Ixz
   
--Cxy                Ixy        Cxz                           Ixz
--0123456789    01234    ABCDEGHIJKLMNOP    ABCDE

set @y = NULL;

select
    COALESCE    (@X,@Y,@z) as Cxyz,
    ISNULL(ISNULL(@x,@y),@z)    as Ixyz
--Cxyz                          Ixyz
--ABCDEGHIJKLMNOP    ABCDE

Till Next Time

TSQL newsequentialid()

If you have a table where you use a GUID as a tablekey please consider using newsequentialid() instead of newid().

Why? newsequentialid() uses fewer cached data pages.  Normally a data page or an index page reserves space for in between values, with newsequentialid()  you will have a better filled page, making you DBA happy

Here is an example:

CREATE TABLE [dbo].[tNewSequentialID](
    [RecGuid] [uniqueidentifier] NOT NULL,
    [Text] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_tNewSequentialID] PRIMARY KEY CLUSTERED
(
    [RecGuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tNEWSEQUENTIALID] ADD  CONSTRAINT [DF_tNEWSEQUENTIALID_RecGuid]  DEFAULT (newsequentialid()) FOR [RecGuid]
GO

INSERT into  [dbo].[tNewSequentialID] ([Text]) Values (N'A');
INSERT into  [dbo].[tNewSequentialID] ([Text]) Values (N'B');
INSERT into  [dbo].[tNewSequentialID] ([Text]) Values (N'C');

Select * from  [dbo].[tNEWSEQUENTIALID];

Till Next Time

TSQL TRY_CAST, TRY_PARSE & TRY_CONVERT

Normally if you use a CAST, PARSE or CONVERT the whole statement fails if a value can’t be cast, parsed or converted. If you use the TRY_ version you get a NULL result if the conversion fails without an error.

SELECT CAST(N'ABC' as INT) as CastResult;
--Conversion failed when converting the nvarchar value 'ABC' to data type int.

SELECT TRY_CAST(N'ABC' as INT) as CastResult;
-- Result NULL

SELECT PARSE('$345,98' AS money USING 'nl-NL') as ParseResult;
-- Error converting string value '$345,98' into data type money using culture 'nl-NL'.
-- (They use Euro's in NL)

SELECT TRY_PARSE('$345,98' AS money USING 'de-DE') AS ParseResult;
-- Result Null

SET DATEFORMAT dmy;
SELECT CONVERT(datetime2, '12/31/2010') AS ConvertResult;
-- Conversion failed when converting date and/or time from character string.

SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS ConvertResult;
-- Result Null

Till Next Time

TSQL Playing with aggregates part 2

In part 1 we went back to the basics in this part I want to show more advanced possibility's.

Consider the following question: How much do my customers contribute to my sales on a yearly basis?

This means we have to have at least two numbers in the same query:

• Total Sales per year.
• Total Sales per year per customer.

To achieve this we can use the “PARTITION BY” clause in the “OVER ()” statement:

 

Don’t panic if you see the same number reappearing in SalesPerYearPerCustomer column. This is caused by the fact that the people who made the AdventureWorks base set weren’t very creative with the orders…….

To work around this I added an extra column named SalesAmountReal and filled this with a randomized amount:

This would give us:

Even in my randomized set there isn’t a huge spread in sales amount.

Till Next Time

TSQL Playing with aggregates part 1

One of the least understood functionalities in TSQL is the usage of aggregates. In this article I want to back to the basics. For my examples I’m using the AdventureWorks 2012 DW:

The most basic aggregates are count and sum. How many or how much do I have:

For an information point of view these number don’t tell much. Most of the time one is interested in amount per category. A category can be something like period, customer, product, salesrep etc. Most querys have a period based axis.

This introduces the “GROUP BY” part. As you can see the results aren’t ordered. For that we have to a an “ORDER BY”.

Both the “GROUP BY” and the “ORDER BY” work for the whole set. In part 2 of this series I will show you how to make them work for subsets.

Till Next Time