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