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



　

Creating Excel File using SSIS

This process creates a excel file with data from the database.

In Execute SQL Task we create the table( excel sheet) . The columns are the column names returned by the query and the names appear as it is in the header of the sheet if we have checked the box where is says first row contains header.

 The following is the data flow task where the excel destination uses a dynamic file connection manager.

 

We are creating the ExcelFilepath property of the connection manager as follows. WE declare a user variable called @varExcelFileName and build the expression for it as follows:

@varExcelFileName  =

@[User::varOutFilePath]+ Replace(@[User::varReconFileName],".xls","") + "_" + (DT_WSTR, 4) YEAR( GETDATE()  ) +(DT_WSTR, 2) MONTH( GETDATE()  )+(DT_WSTR, 2) DAY( GETDATE()  ) + ".xls"

 

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

db_executor role for user permission to execute all procedures

SQL Server has several fixed database roles such as db_datareader and db_datawriter, which grants the user read and write access respectively to all the tables in the database. Curiously there is no role to grant a user permission to execute stored procedures, but fortunately this is easily resolved by creating a new role.

The following SQL creates the new role in a database, and then grants it execute rights :

-- Create a db_executor role
CREATE ROLE db_executor

-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor

 

--then add a member to this role

 

To check the last successful run date and time of the sql job

Declare @jobId as uniqueidentifier

select

j.job_id,j.Name as "Job Name", j.description as "Job Description", CONVERT(DATETIME, RTRIM(h.run_date))

+ ((h.run_time / 10000 * 3600)

+ ((h.run_time % 10000) / 100 * 60)

+ (h.run_time % 10000) % 100) / (86399.9964) AS run_datetime,

case h.run_status

when 0 then 'Failed'

when 1 then 'Successful'

when 3 then 'Cancelled'

when 4 then 'In Progress'

end as JobStatus

from sysJobHistory h, sysJobs j

where j.job_id = h.job_id and h.run_date =

(select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id ) and h.run_status= 1

and cast(cast(h.run_date as varchar(8)) as date) = cast('06/27/2016' as date)

order by 2

Move a user databases

To move a data or log file as part of a planned relocation, follow these steps:

1. Run the following statement.
   
    

   ALTER DATABASE database_name SET OFFLINE;  
   
   

2. Move the file or files to the new location.
3. For each file moved, run the following statement.
   
    

   USE master
   GO
   ALTER DATABASE AdventureWorks2012
   MODIFY FILE 
   ( NAME = AdventureWorks2012_Data, 
   FILENAME = 'C:\Disk2\AdventureWorks2012_Data.mdf'); -- New file path
   
   USE master
   GO
   ALTER DATABASE AdventureWorks2012 
   MODIFY FILE 
   ( NAME = AdventureWorks2012_Log, 
   FILENAME = 'C:\Disk2\AdventureWorks2012_log.ldf'); -- New file path
   

4. Run the following statement.
   
    

   ALTER DATABASE database_name SET ONLINE;  
   
   

5. Verify the file change by running the following query.
   
    

   SELECT name, physical_name AS CurrentLocation, state_desc  
   FROM sys.master_files  
   WHERE database_id = DB_ID(N'<database_name>');  
   
   

Start the SQL Server instance in single user mode.

Click on SQL Configuration Manager
Click on SQL Services
Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box. ( make sure that the semicolon exists after the m.


-m;dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf


Once the desired work is done, make sure to remove the newly added parameter.