Friday, May 29, 2009

SUM of TIME over 24 HRS

 
DECLARE @TimeCalculation AS TABLE (ID INT IDENTITY, [TIME] TIME)

INSERT INTO @TimeCalculation

SELECT '01:00:00' UNION ALL
SELECT '02:15:00' UNION ALL
SELECT '03:20:00' UNION ALL
SELECT '01:25:00' UNION ALL
SELECT '18:00:00' UNION ALL
SELECT '00:15:00' UNION ALL
SELECT '02:00:00' UNION ALL
SELECT '00:20:00' UNION ALL
SELECT '22:00:00' UNION ALL
SELECT '02:00:00' UNION ALL
SELECT '23:00:00'

SELECT ID, [TIME] FROM @TimeCalculation


SELECT
--Default format that we got to calculate the minutes
CONVERT(CHAR(10),DATEADD(SECOND,ABS(SUM(DATEDIFF(SECOND, '00:00', [TIME]))), '19000101'), 8) ,

LEFT(

RIGHT(
CONVERT(CHAR(10),DATEADD(SECOND,ABS(SUM(DATEDIFF(SECOND, '00:00', [TIME]))), '19000101'), 8)
,7)

,2)

AS MIN,
CAST(ABS(SUM(DATEDIFF(SECOND, '00:00:00', [TIME]))) / 3600 AS VARCHAR(12)) AS Hrs
FROM @TimeCalculation

--Now we are using FUNCTION STUFF to get in full format
SELECT

STUFF(CONVERT(CHAR(10), DATEADD(SECOND, ABS(SUM(DATEDIFF(SECOND, '00:00', [TIME]))), '19000101'), 8), 1, 2, CAST(ABS(SUM(DATEDIFF(SECOND, '00:00', [TIME]))) / 3600 AS VARCHAR(12)))
FROM @TimeCalculation

Thursday, May 21, 2009

Time Conversion

 
--Convert Seconds to Hours
SELECT CONVERT(VARCHAR(10), DATEADD(second, 3614, 0), 108) AS HrsFormat

--Convert Seconds to Hrs format
SELECT CONVERT(VARCHAR(10), DATEADD(second, 4320, 0) , 8) AS HrsFormat

--Convert hours to MilliSeconds
SELECT SUM(DATEDIFF(millisecond,0,CAST('01:12:00' AS DATETIME))) AS MilliSeconds

--Convert MilliSeconds to Seconds
SELECT CONVERT(DECIMAL(20,0), DATEDIFF(millisecond,0,CAST('01:12:00' AS DATETIME)) / 1000.00) AS Seconds

--Convert MilliSeconds to Minutes
SELECT (CONVERT(DECIMAL(20,0), DATEDIFF(millisecond,0,CAST('01:12:00' AS DATETIME)) / 1000.00))/60 AS Minutes

--Convert Seconds to Minutes
SELECT (4320 / 60) AS TotalDurationInMinutes

--Convert hours to minutes
SELECT (DATEDIFF(SECOND, '00:00:00', '01:12:00') / 60.0E0) AS [Minutes]

--Convert Minutes to Hours
SELECT CONVERT(varchar, FLOOR(72 / 60.0)) + ':' + RIGHT('0' + CONVERT(varchar, 72 % 60), 2) AS HrMin

--1 Minute = 0.0166666666666667 Hours

Get Modified Date of Table

Original Link 



 
DECLARE @n_days INT
DECLARE @n_days INT

SET @n_days = 10 --Give the number of days

SELECT
[name]
,create_date
,modify_date
FROM
sys.tables
WHERE modify_date > GETDATE() - @n_days
AND schema_id = 5
ORDER BY modify_date DESC

Get Modified Date Of Stored Procedure

Original Link 

 
DECLARE @n_days INT
DECLARE @SCHEMA_NAME VARCHAR(25)
DECLARE @TableOrOrProcedre CHAR(1)
DECLARE @schema_id INT
SET @n_days = 10 --Give the number of days
SET @SCHEMA_NAME = 'Vehicle' --Name of schema
SET @schema_id = 5 -- Schema ID
SET @TableOrOrProcedre = 'p' -- T --> Table, P--> Procedure
--Get All the schema_id and its corresponding SCHEMA_NAME
SELECT DISTINCT SCHEMA_NAME(schema_id) AS SCHEMA_NAME ,
schema_id
FROM sys.objects
--IF we know the schema_id
SELECT name ,
create_date,
modify_date,
SCHEMA_NAME(schema_id) AS schema_name
FROM sys.objects AS SysObj
WHERE type = @TableOrOrProcedre
AND schema_id = @schema_id
AND modify_date > GETDATE() - @n_days
ORDER BY modify_date DESC
--IF we don't know the schema_id
SELECT name ,
create_date,
modify_date,
SCHEMA_NAME(SysObj.schema_id) AS schema_name
FROM sys.objects AS SysObj
INNER JOIN
(SELECT DISTINCT SCHEMA_NAME(schema_id) AS SCHEMA_NAME ,
schema_id
FROM sys.objects
) AS GetSchemaName
ON GetSchemaName.schema_id = SysObj.schema_id
WHERE type = @TableOrOrProcedre
AND GetSchemaName.SCHEMA_NAME = @SCHEMA_NAME
AND modify_date > GETDATE() - @n_days
ORDER BY modify_date DESC

Wednesday, May 20, 2009

Auto Increment Of Variables In SQL SERVER

 
DECLARE @TestingSourceTable TABLE (Value INT NOT NULL)
DECLARE @TestingTable TABLE (ID INT NOT NULL)

INSERT
INTO @TestingSourceTable

SELECT 100 UNION ALL
SELECT 200 UNION ALL
SELECT 300 UNION ALL
SELECT 400 UNION ALL
SELECT 500 UNION ALL
SELECT 600

SELECT (
--This gives number 1,2.. for the result set
ROW_NUMBER() OVER(ORDER BY(SELECT 1)) +
--If our @TestingTable is null
--So we use ISNULL(MAX(ID),0)
(SELECT ISNULL(MAX(ID),0)FROM @TestingTable )
AS ID,
[Value]
FROM @TestingSourceTable