Considering time by excluding overlapping and gaps

by Amol 26. February 2011 19:06

If we need to deal with schedules, we often require calculating total period by excluding overlap and schedule gaps.  Here is the scenario. Below table contains one schedule. According the schedule total hours should be 6 hours by excluding gaps and overlaps.

9:00 AM

10:00 AM

11:00 AM

12:00 PM

1:00 PM

2:00 PM

3:00 PM

4:00 PM

5:00 PM

 

 

 

 

 

 

 

 

 

1hr

 

 

 

 

 

 

 

 

1hr

1hr

1hr

 

 

 

 

 

 

 

 

 

 

1hr

 

 

 

 

 

 

 

 

 

 

1hr

1hr

 

 

 

1hr

 

 

 

 

 

 

Below script is used to populate some sample data according the above schedule and script to get the actual hours (you can say working hours). 

To get the total working hours, first need to calculate total hours between very first time and last time.  Then subtract the gaps between schedules if there are any.

IF OBJECT_ID('XYZ') IS NOT NULL

      DROP TABLE XYZ

GO

 

CREATE TABLE XYZ

(

id int identity(1,1),

ST smalldatetime NOT NULL,

ET smalldatetime NOT NULL

)

GO

 

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 9:00AM', '2010-01-01 10:00AM')

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 9:00AM', '2010-01-01 12:00PM')

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 1:00PM', '2010-01-01 2:00PM')

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 3:00PM', '2010-01-01 5:00PM')

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 11:00AM', '2010-01-01 12:00PM')

 

GO

 

WITH Gaps(Gap) AS 

(

    SELECT COALESCE(SUM(DATEDIFF(MINUTE,ST,ET)), 0) 

    FROM (

            SELECT MAX(P1.ET) AS ST, P2.ST AS ET

            FROM XYZ AS P1

            INNER JOIN XYZ AS P2 ON (P1.ST < P2.ST)

            GROUP BY P2.ST

            HAVING MAX(P1.ET) < P2.ST    

         ) gaps

)

SELECT ( 

            COALESCE(DATEDIFF(MINUTE, MIN(ST), MAX(ET)), 0) 

            - (SELECT Gap FROM Gaps)

       ) / 60.0 TotalHrs

FROM XYZ

Tags:

SQL Server Scripts

T-SQL Get Partition Details

by Amol 22. February 2011 19:16

SELECT 

      i.object_id,

      i.name AS IndexName,

      p.partition_number,

      fg.name AS FileGroupName,

      value,

      ps.name AS SchemaName,

      f.name FunctionName

FROM sys.partitions p

JOIN sys.indexes i

      ON (p.object_id = i.object_id

          AND p.index_id = i.index_id)

JOIN sys.partition_schemes ps

      ON (ps.data_space_id = i.data_space_id)

JOIN sys.partition_functions f

      ON (f.function_id = ps.function_id)

LEFT JOIN sys.partition_range_values rv   

      ON (f.function_id = rv.function_id

          AND p.partition_number = rv.boundary_id)

JOIN sys.destination_data_spaces dds

      ON (dds.partition_scheme_id = ps.data_space_id

          AND dds.destination_id = p.partition_number)

JOIN sys.filegroups fg

      ON (dds.data_space_id = fg.data_space_id)

WHERE i.index_id < 2

      AND i.object_id = Object_Id('<ObjectName>')

Tags:

SQL Server Partitioning | SQL Server Scripts

Convert decimal to binary

by Amol 21. February 2011 19:20

CREATE FUNCTION [dbo].[GetBinary] (@DecimalNumber BIGINT)

RETURNS VARCHAR(8000)

AS

BEGIN

   

    DECLARE @BinaryNumber VARCHAR(MAX);

   

    WITH cte (Id, Reminder) 

    AS

    (

    SELECT

         @DecimalNumber / 2 AS Id

        ,@DecimalNumber % 2 AS Reminder

    UNION ALL

    SELECT

         cte.id / 2

        ,cte.id % 2 AS Reminder

    FROM cte

    WHERE cte.Id > 0

    )

    SELECT @BinaryNumber = COALESCE(@BinaryNumber, '') + CAST(Reminder AS CHAR(1))

    FROM cte

    ORDER BY id

   

    RETURN (@BinaryNumber)

 

END

Tags:

SQL Server Scripts

Tag cloud