Script: Delete back up file

--script to delete file from sql server instance1

Declare @DeleteDate varchar(50)
Declare @DeleteExecuteSQL varchar(1000)
Set @DeleteDate = cast(DATEADD(day,-3,GetDate()) as varchar(50))
Set   @DeleteExecuteSQL =
'EXECUTE master.sys.xp_delete_file 0,N''V:\MSSQL$instance1\BackUp\Daily\Client000\' + @@servername + '\User'',N''bak'',N' + quotename(@DeleteDate,'''') +  ',1'

print @DeleteExecuteSQL
--Execute (@DeleteExecuteSQL)

Tally table in SQL

I had a situation where I had to write out to a file where the  column3 ( string)  should be split and write to the next line if the length was greater than 50 . I used Tally table to do that.

WITH cteTally AS
(
SELECT *
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(v)
)

,cte AS
(
SELECT
  v = (ROW_NUMBER() OVER(ORDER BY c1.v) * 50  + 1) - 50
FROM cteTally c1
CROSS JOIN cteTally c2
)

SELECT
  tbl1.col1   'Column1'
  ,tbl1.col2  'Column2'
  ,SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50)  'Column3'
FROM tbl1
CROSS JOIN cte c
WHERE
SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) <> ''

Explanation ---

select * from cteTally
V
-----
1
1
1
1
1
1
1
1
1
1
Select * from cte
v
----
1
51
101
151
201
.
.
.
.
4951
(100 rows because of cross join)
finally use the place holder row value in the sub string

SELECT
  tbl1.col1   'Column1'
  ,tbl1.col2  'Column2'
  ,SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50)  'Column3'
FROM tbl1
CROSS JOIN cte c
WHERE
SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) <> ''

Another easy to understand example that I found on http://blogs.inkeysolutions.com/2011/05/creating-tally-tables-using-cte-in-sql.html
Generating a sequence of numbers from 1 to 20
DECLARE @Max AS INT = 20


;WITH CTE AS (
  SELECT 1 Num
  UNION ALL
  SELECT Num + 1 FROM CTE WHERE Num < @Max
)


SELECT * FROM CTE
 



----Generating a sequence of Dates starting with the current date & going till next 20 days


DECLARE @MaxDate AS DATETIME = GETDATE() + 20


;WITH CTE AS (
  SELECT GETDATE() Dates
  UNION ALL
  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate
)


SELECT * FROM CTE

---Generating a sequence of Dates starting with the current date & going till next 20 days


DECLARE @MaxDate AS DATETIME = GETDATE() + 20


;WITH CTE AS (
  SELECT GETDATE() Dates
  UNION ALL
  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate
)


SELECT * FROM CTE

Using Lag to get the difference between two rows

I used the following query to find how name rows were there in a table in a given date where the time captured in a column were more than one minute between two consecutive rows

--using lag
  declare @startdate date ,@enddate date

set @startdate = '01/09/2017'

set @enddate = '01/09/2017'




 SELECT *

FROM (

SELECT LogId, lag(PackageStep) over (order by packagename) Over1minPackagestep,

LAG(starttime) OVER (ORDER BY starttime)

StepStartTime ,starttime as StepEndTime, HospitalCode

FROM Package_RunHistory where cast(starttime as date) between @startdate and @enddate

) q

WHERE StepStartTime IS NOT NULL

and DATEDIFF(minute, StepStartTime, StepEndTime) > 1

and clientcode is not null



　

Find age based on date - TSQL

--this query finds the current ( as of the date the query is run) age of the person.

declare @dob datetime

set @dob = '07/12/1966'

select CASE

WHEN dateadd(year, datediff (year, @dob, GETDATE()),@dob) > GETDATE()

THEN datediff (year, @dob, GETDATE()) - 1

ELSE datediff (year, @dob, GETDATE())

END as Age

--select dateadd(year, datediff (year, @dob, GETDATE()),@dob)

--select datediff (year, @dob, GETDATE())

script: Change the owner of the sql jobs

DECLARE @name_holder VARCHAR(1000)

DECLARE My_Cursor CURSOR

FOR

SELECT [name]  FROM msdb..sysjobs 

OPEN My_Cursor

FETCH NEXT FROM My_Cursor INTO @name_holder

WHILE (@@FETCH_STATUS <> -1)

BEGIN

exec msdb..sp_update_job

        @job_name = @name_holder,

        @owner_login_name = 'sa'

FETCH NEXT FROM My_Cursor INTO @name_holder

END 

CLOSE My_Cursor

DEALLOCATE My_Cursor

Batch Update : using TOP n

---do not use set rowcount.
--Use top n
WHILE (1=1)
  BEGIN
     BEGIN TRANSACTION
    UPDATE r
    SET col1= col2
    from Table1 r
    where r.col3 in (select top 10000 col3 from Table1 where col1 is null)

   -- Update 10000 nonupdated rows

   IF @@ROWCOUNT = 0
     BEGIN
       COMMIT TRANSACTION
       BREAK
    END

    COMMIT TRANSACTION
END

Deleting duplicate rows

A very cool technique to find and delete duplicate rows and just keep one unique row uinsg CTE ( common table expression) in SQL 2008

Initially the table had data

updateId  ptno
1                95689
2                95689
3                95689
4               91458
5               91000

Result desired
1               95689
4               91458
5               91000

(3 unique rows)

SELECT UpdateID,Ptno INTO #TempPtno From  dataTable1
WHERE uploaded = 0
AND deleted=0
AND flag=0 ;

/* Delete Duplicate records */
WITH CTE ( UpdateId,Ptno, DuplicateCount)
AS
(
SELECT UpdateId,Ptno,
ROW_NUMBER() OVER(PARTITION BY Ptno ORDER BY ptno) AS DuplicateCount
FROM #TempPtno
)
DELETE
FROM CTE
WHERE DuplicateCount > 1



**If there is a semicolon missing at the end of the statement before WITH CTE the following ing error will be thrown
Msg 319, Level 15, State 1, Procedure  procName , Line number
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.