backup and remove old files

-- i8 Solutions
-- backup database
-- retention 2 weeks


DECLARE @strDirectory NVARCHAR(50)= 'D:\i8Solutions\bu\';
DECLARE @dtDeleteBefore datetime;
DECLARE @MyFileName varchar(50);
DECLARE @strDatabaseName NVARCHAR (50)=N'i8_Portal';

SELECT @MyFileName = (SELECT  @strDirectory + @strDatabaseName + '_' + convert(varchar(50),GetDate(),112) + '.bak')

-- Declare and initialize variables
DECLARE @dt DATETIME =
ISNULL(@dtDeleteBefore, DATEADD(WEEK, -2, GETDATE()));


-- Delete the backup history older than the specified date
EXECUTE msdb.dbo.sp_delete_backuphistory
@oldest_date = @dt;

-- Delete the backup files older than the specified date
EXECUTE master.dbo.xp_delete_file
0 -- file type; 0 = backup, 1 = report
, @strDirectory -- directory in which to look for files
,  'bak'-- extension of files to look for (no period!)
, @dt -- delete files older than this date
, 1 -- traverse sub-folders; 0 = FALSE, 1 = TRUE




BACKUP DATABASE @strDatabaseName
TO  DISK = @MyFileName WITH NOFORMAT,
INIT,  NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

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