Hour, Minute & Second between two date

To calculate Hour(s), Minute(s) & Second(s) between two dates.

--Creating Function
If OBJECT_ID('UFN_HourMinuteSecond') Is Not Null
Drop Function dbo.UFN_HourMinuteSecond
Go
Exec(
'Create Function dbo.UFN_HourMinuteSecond
(
@StartDateTime DateTime,
@EndDateTime DateTime
) Returns Varchar(10)
As
Begin

Declare @Seconds Int,
@Minute Int,
@Hour Int,
@Elapsed Varchar(10)

Select @Seconds = ABS(DateDiff(SECOND ,@StartDateTime,@EndDateTime))

If @Seconds >= 60
Begin
select @Minute = @Seconds/60
select @Seconds = @Seconds%60

If @Minute >= 60
begin
select @hour = @Minute/60
select @Minute = @Minute%60
end

Else
Goto Final
End

Final:
Select @Hour = Isnull(@Hour,0), @Minute = IsNull(@Minute,0), @Seconds = IsNull(@Seconds,0)
select @Elapsed = Cast(@Hour as Varchar) + '':'' + Cast(@Minute as Varchar) + '':'' + Cast(@Seconds as Varchar)

Return (@Elapsed)
End'
)

--Using the Function (Two Datetime Input parameters)
Select dbo.UFN_HourMinuteSecond ('2011-06-24 00:54:36.503', '2011-06-24 02:11:02.563') [Hour:Minute:Second]
Go

--Result

Various lock(s) held in all the Database

To findout what are all the various Request Modes/Locks held in various level(Key, Page,...Etc.,) in all the Database(s)

Select DB_Name([Database Id]) [Database Name],[Request Mode], [DATABASE], [FILE], [OBJECT], [PAGE], [KEY], [EXTENT], [RID], [APPLICATION], [METADATA], [HOBT], [ALLOCATION_UNIT]
From
(
select Distinct o.resource_database_id [Database Id],
o.request_mode [Request Mode],
o.resource_type [Resource Type],
Count(1) [Counts]
from sys.dm_tran_locks o
Group by o.resource_database_id, o.request_mode, o.resource_type
) as IQ
PIVOT
(
SUM([Counts]) For [Resource Type] In ([DATABASE], [FILE], [OBJECT], [PAGE], [KEY], [EXTENT], [RID], [APPLICATION], [METADATA], [HOBT], [ALLOCATION_UNIT])
)as OQ

The following screen shows as follows... 

Database1 hold 1 IX lock on OBJECT, PAGE level, 4 RangeS-S locks on KEY level, 3 RangeX-X locks on KEY level, 2 SHARED(S) lock on DATABASE lavel.
Database 2 hold 1 SHARED(S) lock on DATABASE level.

--Result

Weekday(s) statistics in a Month and Year

To findout count statistics for each weekdays in a particular Year and Month

--Creating a Stored Procedure
If OBJECT_ID('USP_Weekdays') Is Not Null
Drop Proc USP_Weekdays
Go
Create Proc USP_Weekdays
(
@Year Int,
@Month Varchar(15)
) As
Begin
Set Nocount on
 Declare @Days TinyInt
Select @Days = Dbo.HowManyDays(@Year,@Month)

;With WeekDays
As
(
Select 1 [Days]
Union All
Select [Days] + 1 from WeekDays where [Days] <31
)

Select SUM([Sunday]) [Sunday], SUM([Monday]) [Monday], Sum([Tuesday]) [Tuesday], Sum([Wednesday]) [Wednesday], SUM([Thursday]) [Thursday], Sum([Friday]) [Friday], SUM([Saturday]) [Saturday]
From
(
Select Case [Day] When 'Sunday' Then 1 Else 0 End [Sunday],
Case [Day] When 'Monday' Then 1 Else 0 End [Monday],
Case [Day] When 'Tuesday' Then 1 Else 0 End [Tuesday],
Case [Day] When 'Wednesday' Then 1 Else 0 End [Wednesday],
Case [Day] When 'Thursday' Then 1 Else 0 End [Thursday],
Case [Day] When 'Friday' Then 1 Else 0 End [Friday],
Case [Day] When 'Saturday' Then 1 Else 0 End [Saturday]
from
(
select DATENAME(weekday,Cast(@Year as Varchar) + '-' + @Month + '-' + Cast([Days] as varchar)) [Day] from WeekDays Where [Days] <= @Days
) as X
) as WeekDays
End
Go

--Executing the Stored Procedure
Exec USP_Weekdays 2011,'June'
Go

Note: You can find the dbo.HowManyDays function script from the following post
http://sqlserverbuddy.blogspot.com/2011/06/how-many-days-in-month.html

--Result

How many days in a month ?

To findout how man days in a particular month

If Object_Id('Dbo.HowManyDays') Is Not Null
Drop Function Dbo.HowManyDays
Go
Exec(
'Create Function Dbo.HowManyDays(@Year Int,@Month Varchar(15)) Returns Int
As
Begin
Declare @Date Varchar(50),
@Days TinyInt
 Select @Date = Cast(@Year as Varchar) + ''-'' + @Month + ''-01'',
@Days = DatePart(Day,DateAdd(Month,1,@Date) -1)
 Return @Days
End')

--How to Execute it
Select Dbo.HowManyDays(2011, 'June') [Days]
Go


SQL Job statistics - last 7 days - How to...


To identify the SQL Jobs' statistics for last 7 days...
- How many times the JOB executed
- How many success
- How many failure
- Average Time taken
- Average retried count,.... Etc


SELECT
[Job Name],
[Job ID],
[JOB Owner],
CASE WHEN [Schedule] <=0 THEN 'Not Scheduled' ELSE 'Scheduled' END [Executed Status],
[Steps] [How many Steps],
[Total] [Totally Executed],
[Failure] [How many failure(s)],
[Total]-[Failure] [How many success],
[Average] [Average Time Taken(Secs)],
[Retries] [Average retried(Count)]
FROM
(
SELECT TOP 100
sj.name [Job Name]
, sj.job_id [Job ID]
, sl.name [JOB Owner]
, (SELECT COUNT(1) from msdb.dbo.sysjobschedules js1 WHERE js1.job_id = sj.job_id ) [Schedule]
, (SELECT COUNT(1) from msdb.dbo.sysjobsteps js2 WHERE js2.job_id = sj.job_id ) [Steps]
, (SELECT COUNT(1) from msdb.dbo.sysjobhistory jh1 WHERE jh1.job_id=sj.job_id and jh1.step_id = 0 and jh1.run_status = 0 and DATEDIFF( DAY, CONVERT(DATETIME, CONVERT( VARCHAR, jh1.run_date) ), GETDATE()) < 7 ) [Failure] 
,(SELECT AVG(((run_duration/10000*3600) + ((run_duration%10000)/100*60) + (run_duration%100))+0.0) from msdb.dbo.sysjobhistory jh2 WHERE jh2.job_id=sj.job_id and jh2.step_id = 0 and DATEDIFF( DAY, CONVERT(DATETIME, CONVERT( VARCHAR, jh2.run_date) ), GETDATE()) < 7 ) [Average] 
,(SELECT CAST(AVG(retries_attempted+0.0) AS INT) from msdb.dbo.sysjobhistory jh2 WHERE jh2.job_id=sj.job_id and jh2.step_id = 0 and DATEDIFF( DAY, CONVERT(DATETIME, CONVERT( VARCHAR, jh2.run_date) ), GETDATE()) < 7 ) [Retries] 
,COUNT(1) [Total]
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs sj ON ( jh.job_id = sj.job_id )
INNER JOIN sys.syslogins sl ON ( sl.[sid] = sj.owner_sid )
WHERE jh.step_id = 0 and DATEDIFF( DAY, CONVERT(DATETIME, CONVERT( VARCHAR, jh.run_date) ), GETDATE()) < 7 GROUP BY sj.job_id, sj.name, sl.name
ORDER BY [Average] DESC
) JOBInfo