SQL Date Range Calculation (SQL 2000)

[marydn]

Hello, I am trying to determine the peak minutes and non-peak minutes between dates. My table is structured as follows:

Date Down Time Down Date Up Time Up
4-14-02 17:00:00 4-15-02 13:00:00

Now my peak minutes are 8:00:00 to 18:59:59 and non-peak are
19:00:00 to 7:59:59.

Is there any simple way for me to take the above dates and divide up the time into it's correct time category?

Also, the above data type is VarChar.

Any help would be greatly appreciated!

M.

 

[rac2]

Hunh?

Do you mean that you want to get the total minutes something was down during peak hours and non-peak hours?

What if the downage started during peak hours and continued into non-peak hours? Do you want to divide it into the two categories?


To convert those VARCHAR values to DATETIME values

CONVERT(DATETIME, DateDown + ' ' + TimeDown)

To get the number of minutes between two DATETIME values

DATEDIFF(minutes, @dtStartTime, @dtEndTime)

Here you would substitute the expression with the CONVERT function in place of the variables @dtStartTime and @dtEndTime.

To split up a particular downage you will need to write a CASE expression taking into account four conditions

CASE
     WHEN @dtStartTime BETWEEN @dailyPeak8am AND @dailyPeak7pm 
          AND @dtEndTime BETWEEN @dailyPeak8am AND @dailyPeak7pm
         THEN DATEDIFF(minutes, @dtStartTime, @dtEndTime)
     WHEN @dtStartTime < @dailyPeak8am 
          AND @dtEndTime BETWEEN @dailyPeak8am AND @dailyPeak7pm
         THEN DATEDIFF(minutes, @dailyPeak8am, @dtEndTime)
     WHEN @dtStartTime BETWEEN @dailyPeak8am AND @dailyPeak7pm 
          AND @dtEndTime > @dailyPeak7pm
         THEN DATEDIFF(minutes, @dtStartTime, @dailyPeak7pm)
     WHEN @dtStartTime < @dailyPeak8am 
          AND @dtEndTime > @dailyPeak7pm
         THEN DATEDIFF(minutes, @dailyPeak8am, @dailyPeak7pm)
     ELSE 0
END AS &quot;PeakMinutesDown&quot;

You will need to use the CONVERT function to calculate values for @dailyPeak8am and @dailyPeak7pm using the DateDown + '08:00:00' and DateUp + '18:59:59'.

Then you would write a similar CASE statement to calculate the minutes during the non-peak hours.

Then you can put these pieces together into a GROUP BY query. You might want to define a view using the CASE statement then write the GROUP BY query to use the view instead on putting all this mess into one query.

Presto! Anyway that is what occurs to me off the top of my head. Maybe there is an easier way. Let me know if you find it.

 

[marydn]

rac2 - thanks for the response. However, it did not work. I guess the problem is that there may be more than 4 conditions.

I think it gets complicated depending on whether or not the down time and up time takes place in the AM or PM.

For example:

Date Down Time Down Date Up Time Up
4-14-02 17:00:00 4-15-02 13:00:00

4-14-02 17:00:00 to 19:00:00 (120 peak Mins)
4-14-02 19:00:00 to 24:00:00 (300 non-peak mins)
4-15-02 24:00:00 to 08:00:00 (480 non-peak mins)
4-15-02 08:00:00 to 13:00:00 (300 peak mins)

420 Peak, 780 non-peak

Now what if it is up at 1AM on 4-15-02?

4-14-02 17:00:00 to 19:00:00 (120 peak Mins)
4-14-02 19:00:00 to 24:00:00 (300 non-peak mins)
4-15-02 24:00:00 to 01:00:00 (60 non-peak mins)

120 peak, 360 Non-peak

Maybe I am complicating things - I tend to do that sometimes - okay - OFTEN.......

Anyway, any ideas would be appreciated.

 

[rac2]

It is a lot more complicated than that when the downage spans more than 24 hours.

Given that I think you need to write a procedure that can identify the number of categories that are spanned by the outage. Four conditions would handle 24 hours; eight conditions would handle 48 hours; etc. But that becomes messy and if a downage lasts more days than you have in the query then it fails.

Note that the fourth condition uses a calculation but to obtain the number of peak minutes but it could use a constant because DATEDIFF(minutes, @dtStartTime, @dailyPeak7pm) is always the same value. It is the same value when the downage is less than 24 hours, that is. For longer outages we need to calculate how many full periods are included, say we are down for two to three days starting in the peak period and ending in a non-peak period. Then we know that we were down for two full non-peak periods. Plus part of the final non-peak period. And we were down two full peak periods plus part of the intial period. That could be represented by additional conditions, but it will more general to write a procedure that works through the downage interval identifying the pieces and the whole periods and adds them up.

Use that procedure on each row of data; save the numbers for each row in a temporary table with columns for peak and non-peak minutes then get the SUM.

One final note regarding the example data in your last post. There is no need for two lines for the non-peak minutes

4-14-02 19:00:00 to 24:00:00 (300 non-peak mins)
4-15-02 24:00:00 to 08:00:00 (480 non-peak mins)

The DATEDIFF() function works from day to day, in effect you would have a single row like this.

4-14-02 19:00:00 to 4-15-02 24:00:00 (780 non-peak mins)


It doesn't seem like this should be so complicated, but I think it really is. Another approach would be to classify and calculate the minutes at the point where the row is added or updated with the ending time. Add two columns to the basic table to hold these numbers and fill-in the numbers when you fill-in the ending time. Then when it comes to doing the report, all of the messy caculation is behind you, and you can just SUM those two columns.

 

[rac2]

As I think about it the four conditions each for peak and non-peak will only give the first pieces of the two categories of time.

A procedure might look like this.

/*The downage starts in some category.*/
IF @dtStartTime BETWEEN @dailyPeak8am AND @dailyPeak7pm
   BEGIN
      SET @categoryStart = 'peak'
      SET @totalPeak = CASE expression for peak period
      /*If the downage ends within the same peak period we are finished.*/
      IF @dtEndTime < @dailyPeak7pm SET @finished = 1
   END
ELSE 
   BEGIN
      SET @categoryStart = 'nonpeak'
      SET @totalNonPeak = CASE expression for non-peak period
      /*If the downage ends within the same non-peak period we are finished.*/
      IF @dtEndTime < @dailyNonPeak8am SET @finished = 1
   END

/*If we are not finished move the starting time to the boundary of the next period.*/
IF @finished = 0 AND @categoryStart = 'peak'
   SET @dtStartTime = @dailyPeak7pm

IF @finished = 0 AND @categoryStart = 'nonpeak'
   SET @dtStartTime = @dailyPeak8am

/*Here we begin to loop.   */
/*Loop until @finished = 1.*/
/*The identification of the category will be slightly different because the remainder of the downage now begins on the initial boundary of the peak and non-peak periods.*/
/*And we know the categories alternate.*/
/*If the ending time exceeds the ending boundary of the current category we can add the number of minutes in the category.*/
/*If the ending time is within the current category then we get the remaining minutes and we are finished.  Whew!*/

I am not feeling good about this whole thing. And I apologize for the first mistaken post. Maybe you will want to start over with a new post and get some fresh thinking.

 

[marydn]

Okay, I asked the SQL guru at work to help me out and this is what he created. It seems to work perfect!

FUNCTION udfPeakMins (@Date1 datetime,@Date2 datetime,@peek int)
RETURNS int
AS
begin
-- Peek of 0 =Peek hours 8am-7pm
-- Pekk of 1 =Non-Peer hours 7pm-8am

declare @tot int
declare @ttot int
declare @testdate datetime
declare @enddate datetime
declare @tdate1 datetime
declare @tdate2 datetime

select @ttot=0,@tot=0
select @testdate=@Date1
select @enddate=@Date2

while @testdate<@enddate
begin
if datediff(day,@testdate,@enddate) > 0
select @enddate=convert(datetime,convert(varchar(10),@testdate,101)+' 23:59')

select @tdate1=null,@tdate2=null


if datepart(hour,@testdate) < 8
select @tdate1=convert(datetime,convert(varchar(10),@testdate,101)+' 8:00')
if datepart(hour,@testdate) >= 8 and datepart(hour,@testdate) <= 19
select @tdate1=convert(datetime,@testdate,120)
if datepart(hour,@testdate) > 19
select @tdate1=convert(datetime,convert(varchar(10),@testdate,101)+' 19:00')

if datepart(hour,@enddate) > 19
select @tdate2=convert(datetime,convert(varchar(10),@enddate,101)+' 19:00')
if datepart(hour,@enddate) >= 8 and datepart(hour,@enddate) <= 19
select @tdate2=convert(datetime,@enddate,120)
if datepart(hour,@enddate)< 8
select @tdate2=convert(datetime,convert(varchar(10),@enddate,101)+' 8:00')

select @ttot=datediff(minute,@tdate1,@tdate2)


select @tot=@tot+@ttot

select @testdate=convert(datetime,convert(varchar(10),@testdate,101))
select @testdate=dateadd(day,1,@testdate)
select @enddate=@Date2
end

if @peek=1
select @tot=datediff(minute,@date1,@date2) - @tot

return @tot
end

Thanks for the help!

M.

 

[rac2]

Hey thank you for posting the solution. I have filed it away for future use. Kudos for your guru as well.