CODEWITHSUNDEEP
sql-server-2008sql-server-2005
user978324
user978324

Reputation: 59

Number of minutes between the two dates depending on the status

I have a question related to Sql Server 2005 ( Tsql)

I have two columns Date Status

And the Data looks.. like 

Date                    Status

2012-09-01 00:01:00.000 2

2012-09-01 04:17:00.000 4

2012-09-01 04:34:00.000 4

2012-09-01 04:35:00.000 4

2012-09-01 04:35:48.000 4

2012-09-01 04:35:51.000 1

2012-09-01 17:28:25.000 2

2012-09-01 23:58:00.000 4

2012-09-01 23:59:00.000 1

I need to calculate the time difference between the status ... for example. for min time is when status 2 start and max time when status= 1 (stop) between that date i want the number of minutes. I have done through the cursors checking the status and recording the minumum time and maximum time in the variables

Do we have any easy of doing it using CTE.

MY QUERY TAKES LONG TIME TO FINISH ... PLEASE HELP.

   DECLARE @pdunitid INT
        DECLARE @Date DATETIME
        DECLARE @pddatetime DATETIME
        DECLARE @pdstatus INT
        DECLARE @starttime DATETIME
        DECLARE @endTime DATETIME
        DECLARE @calc INT
        DECLARE @Totaltime INT
        DECLARE @START INT


        SET @pdunitid = 33568906
        SET @Date = GETDATE() - 102
        set @Totaltime = 0
        SET @calc = 0
        SET @START = 0


        DECLARE s CURSOR FAST_FORWARD FOR
        SELECT pddatetime,pdstatus FROM s1 WITH (NOLOCK)
        WHERE  pdunitid = @pdUnitid
        AND CONVERT(VARCHAR,pddatetime,112) = CONVERT(VARCHAR,@Date,112)
        ORDER BY pddatetime,pdstatus 

        OPEN s 

        FETCH NEXT FROM s INTO @pddatetime,@pdstatus
        WHILE @@FETCH_STATUS = 0 
            BEGIN
                -- status 2 is for start sometimes you don't get start so you will have to use the first date with status 4

                IF pdstatus IN (2,4)  AND @START = 0 
                    BEGIN 
                        SET @starttime = @pddatetime
                        SET @START = 1
                    END

                -- status 1 is for stop 

                IF (@pdstatus= 1) 
                    BEGIN 
                        SET @endTime = @pddatetime
                        SET @calc = 1   
                    END

                -- if you dont get the status 1 by '23:59:00' take the end time


            IF  convert(varchar,@pdgpsdatetime,108) = '23:59:00'
                    BEGIN 
                        SET @endTime = @pddatetime
                        SET @calc = 1   
            END

                -- Calculate the minutes.

                IF @calc = 1 
                    BEGIN 
                        SET @Totaltime = @Totaltime + DATEDIFF(mi, @starttime,@endTime)
                        SET @calc = 0   
                        SET @START = 0
                    END
        FETCH NEXT FROM s INTO @pddatetime,@pdstatus
            END
        CLOSE s
        DEALLOCATE s

        SELECT  @Totaltime

Upvotes: 1

Views: 1566

Answers (1)

Ivan Golović
Ivan Golović

Reputation: 8832

Try this, instead of #tbl put your table name:

DROP TABLE #tbl
CREATE TABLE #tbl([Date] DATETIME, [Status] INT)
INSERT  #tbl
VALUES  
('2012-09-01 00:01:00.000',  2),
('2012-09-01 04:17:00.000',  4),
('2012-09-01 04:34:00.000',  4),
('2012-09-01 04:35:00.000',  4),
('2012-09-01 04:35:48.000',  4),
('2012-09-01 04:35:51.000',  1),
('2012-09-01 17:28:25.000',  2),
('2012-09-01 23:58:00.000',  4),
('2012-09-01 23:59:00.000',  1),

-- for 2012-09-05 there are no statuses 1 or 2
--('2012-09-05 00:01:00.000',  2),
('2012-09-05 04:17:00.000',  4),
('2012-09-05 04:34:00.000',  4),
('2012-09-05 04:35:00.000',  4),
('2012-09-05 04:35:48.000',  4),
('2012-09-05 04:35:51.000',  4),
('2012-09-05 17:28:25.000',  4),
('2012-09-05 23:58:00.000',  4)
--('2012-09-05 23:59:00.000',  1)

;WITH tbl AS 
(
    SELECT  *,
            ROW_NUMBER() OVER (ORDER BY [Date]) id
    FROM    #tbl
), 
b AS 
(
    SELECT  MIN([Date]) MinDate,
            MAX([Date]) MaxDate,
            CAST([Date] AS DATE) dateWithoutTime
    FROM    tbl
    GROUP   BY CAST([Date] AS DATE)
),
a AS
(
    SELECT  *,
            ROW_NUMBER() OVER (ORDER BY [Date]) num
    FROM    tbl
    LEFT JOIN 
            b ON 
            b.MaxDate = [Date]
    OR      b.MinDate = [Date]
    WHERE   [Status] IN (2, 1)
    OR      [Date] = CASE WHEN NOT EXISTS (SELECT 1 FROM #tbl c WHERE c.Status = 2 AND CAST(c.[Date] AS DATE) = b.dateWithoutTime) THEN b.MinDate END
    OR      [Date] = CASE WHEN NOT EXISTS (SELECT 1 FROM #tbl c WHERE c.Status = 1 AND CAST(c.[Date] AS DATE) = b.dateWithoutTime) THEN b.MaxDate END
)

SELECT  
        tbl.Date,
        tbl.Status,
        CASE WHEN a2.num % 2 = 0 THEN DATEDIFF(MINUTE, a1.Date, a2.Date) END Diff
FROM    tbl
LEFT JOIN   
        a a2 ON tbl.id = a2.id
LEFT JOIN   
        a a1 ON a2.num = a1.num + 1
ORDER BY [Date]

Upvotes: 1

Related Questions