CODEWITHSUNDEEP
sqldatetime
Karen Gordon
Karen Gordon

Reputation: 9

SQL Difference betwen two dates (Segmented by hours)

I have a shift database which tells me the start and end time of a shift for an individual, i.e. 20/8/2014 07:00:00 to 20/08/2014 15:00:00

However, I need to work out how many people were on shift during a particular hour, i.e. 07:00:00 - 10, 08:00:00 12 etc, as we need to reflect on why performance may have dipped etc, is it because we were short staffed etc?

I can do datediff, to see the total number of hours they were on shift, but I would need to put together some sort of tally chart I guess to do the breakdown, i.e. 

Shift 07:00-15:00 - Joe Bloggs
06:00 - 0
07:00 - 1
08:00 - 1
09:00 - 1

14:00 - 1
15:00 - 0

I hope this make sense.

Karen

Upvotes: 0

Views: 75

Answers (2)

Hiral Nayak
Hiral Nayak

Reputation: 1072

this code set in your SP and pass two parameter as startdate and enddate

CREATE Table #Temp(Datedif varchar(100)) 

DECLARE @counter int;
select @counter =  datediff(hour,'8/20/2013 07:00:00','8/20/2013 15:00:00') 


DECLARE @StartHr varchar(50);
SET @StartHr = Substring(Convert(varchar(50),'8/20/2013 07:00:00'),charindex(' ','8/20/2013 07:00:00'),6)

INSERT INTO #Temp(Datedif)VAlues( @StartHr+' - 1')

WHILE @counter > 1
BEGIN
        DECLARE @NextHr varchar(50);
        SET @NextHr = Substring(@StartHr,1,charindex(':',@StartHr)-1) + 1
        IF(@NextHr LIKE '%' + ':' + '%')
        BEGIN
            INSERT INTO #Temp(Datedif)VAlues( @NextHr+' - 1')
        END
        ELSE
        BEGIN
            IF(len(@NextHr) > 1)
            BEGIN
                INSERT INTO #Temp(Datedif)VAlues( @NextHr+':00 - 1')
            END
            ELSE
            BEGIN
                INSERT INTO #Temp(Datedif)VAlues(' 0'+@NextHr+':00 - 1')
            END
        END
        SET @StartHr = @NextHr+':00-1'
        SET @counter = @counter - 1;
END


select * from #Temp

DROP TABLE #Temp

Upvotes: 1

Maas
Maas

Reputation: 1357

If you are using SQL Server, you can use the script something like below, but the similar logic can be applied for other DBMS

DECLARE
   @currentDate datetime;
SET @currentDate = GETDATE( );

-- Hoursly  count for last X hours
DECLARE
   @counter int;
SET @counter = <Hr Diff>;

 WHILE @counter > 0
            BEGIN

                DECLARE
                   @startTime datetime;
                   SET  @startTime = DATEADD( HOUR , -@counter , @currentDate );
                DECLARE
                   @endTime datetime ;
                   SET @endTime = DATEADD( HOUR , -(@counter - 1), @currentDate );
       -- Fetch count from your table <Business Logic>
        select @startTime, @endTime COUNT( <Entity> ) from tableName BETWEEN @startTime AND @endTime;

         SET @counter = @counter - 1;
       END;

Upvotes: 0

Related Questions