Bob
As life would have it, what I thought was simple changed to more challenging. I probably should have included this in the previous question, but the table of course covers multiple days. I created a sql table called onehour with the following fields:
onehourid int
respk int
resgrp int
restime datetime
minutes int
resused bit
jdate int
Then I wrote a routine in VFP to load a large dataset (648,000 records) to test the speed of the query. Once I indexed it ran very fast. I modified the query you sent to handle (or so I thought) a query for records over a multiple time period. The jdate field is a julian date representation of the restime field.
Below is the current query with my modifications (I used the query analyzer):
declare @startdate datetime
declare @enddate datetime
declare @nResGrp int
set @startdate=GETDATE()
set @enddate=dateadd(day,1,@startdate)
set @nResGrp=3
SELECT onehour.*
from onehour,
(select *
from
(select *, cnt = (select count(*) from onehour b
where b.restime between @startdate and @enddate
and b.resgrp=@nResGrp
and b.resused = 0
and b.minutes >= a.minutes
and b.minutes < a.minutes + 60
and a.respk = b.respk
and a.resgrp = b.resgrp
group by resgrp)
from onehour a
where a.resused=0 and a.restime between @startdate and @enddate and a.resgrp=@nResGrp ) as a
where a.cnt = 4) as b
where onehour.minutes between b.minutes
and b.minutes + 59
and onehour.resgrp = b.resgrp
and onehour.respk = b.respk
and onehour.restime between @startdate and @enddate
and onehour.resused = 0
and onehour.resgrp=@nResGrp
Below is a subset of what was returned:
onehourid respk resgrp restime minutes resused jdate
----------- ----------- ----------- --------------------------- ----------- ------- -----------
7924 21 3 2001-03-01 07:45:00.000 465 0 2451970
7925 21 3 2001-03-01 08:00:00.000 480 0 2451970
7926 21 3 2001-03-01 08:15:00.000 495 0 2451970
7927 21 3 2001-03-01 08:30:00.000 510 0 2451970
7925 21 3 2001-03-01 08:00:00.000 480 0 2451970
7926 21 3 2001-03-01 08:15:00.000 495 0 2451970
7927 21 3 2001-03-01 08:30:00.000 510 0 2451970
7928 21 3 2001-03-01 08:45:00.000 525 0 2451970
7926 21 3 2001-03-01 08:15:00.000 495 0 2451970
7927 21 3 2001-03-01 08:30:00.000 510 0 2451970
7928 21 3 2001-03-01 08:45:00.000 525 0 2451970
7929 21 3 2001-03-01 09:00:00.000 540 0 2451970
7927 21 3 2001-03-01 08:30:00.000 510 0 2451970
7928 21 3 2001-03-01 08:45:00.000 525 0 2451970
7929 21 3 2001-03-01 09:00:00.000 540 0 2451970
7930 21 3 2001-03-01 09:15:00.000 555 0 2451970
7928 21 3 2001-03-01 08:45:00.000 525 0 2451970
7929 21 3 2001-03-01 09:00:00.000 540 0 2451970
7930 21 3 2001-03-01 09:15:00.000 555 0 2451970
7931 21 3 2001-03-01 09:30:00.000 570 0 2451970
But, if I set the @enddate=dateadd(day,2,@startdate) the result set comes back all messed up:
onehourid respk resgrp restime minutes resused jdate
----------- ----------- ----------- --------------------------- ----------- ------- -----------
7933 21 3 2001-03-01 10:00:00.000 600 0 2451970
11534 21 3 2001-03-02 10:15:00.000 615 0 2451971
7934 21 3 2001-03-01 10:15:00.000 615 0 2451970
11536 21 3 2001-03-02 10:45:00.000 645 0 2451971
11534 21 3 2001-03-02 10:15:00.000 615 0 2451971
7934 21 3 2001-03-01 10:15:00.000 615 0 2451970
11536 21 3 2001-03-02 10:45:00.000 645 0 2451971
7937 21 3 2001-03-01 11:00:00.000 660 0 2451970
11534 21 3 2001-03-02 10:15:00.000 615 0 2451971
7934 21 3 2001-03-01 10:15:00.000 615 0 2451970
11536 21 3 2001-03-02 10:45:00.000 645 0 2451971
7937 21 3 2001-03-01 11:00:00.000 660 0 2451970
7940 21 3 2001-03-01 11:45:00.000 705 0 2451970
4342 21 3 2001-02-28 12:15:00.000 735 0 2451969
7942 21 3 2001-03-01 12:15:00.000 735 0 2451970
7943 21 3 2001-03-01 12:30:00.000 750 0 2451970
7943 21 3 2001-03-01 12:30:00.000 750 0 2451970
7944 21 3 2001-03-01 12:45:00.000 765 0 2451970
7945 21 3 2001-03-01 13:00:00.000 780 0 2451970
4346 21 3 2001-02-28 13:15:00.000 795 0 2451969
4352 21 3 2001-02-28 14:45:00.000 885 0 2451969
7952 21 3 2001-03-01 14:45:00.000 885 0 2451970
7953 21 3 2001-03-01 15:00:00.000 900 0 2451970
4354 21 3 2001-02-28 15:15:00.000 915 0 2451969
4352 21 3 2001-02-28 14:45:00.000 885 0 2451969
7952 21 3 2001-03-01 14:45:00.000 885 0 2451970
7953 21 3 2001-03-01 15:00:00.000 900 0 2451970
4354 21 3 2001-02-28 15:15:00.000 915 0 2451969
7953 21 3 2001-03-01 15:00:00.000 900 0 2451970
4354 21 3 2001-02-28 15:15:00.000 915 0 2451969
7956 21 3 2001-03-01 15:45:00.000 945 0 2451970
4356 21 3 2001-02-28 15:45:00.000 945 0 2451969
11558 22 3 2001-03-02 07:15:00.000 435 0 2451971
The more days I add to the enddate, the worse it gets.
I'm hoping that you not tired of this challange. With the proper indexes, this thing is returning a result set in less than 2 seconds. I have a prg to load the data and one to mark a random set of the resused field to 1.
Thanks again for all the help
