Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Help with Query - How to limit/create result group
Message
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Versions des environnements
SQL Server:
SQL Server 2000
Divers
Thread ID:
01201189
Message ID:
01201579
Vues:
21
Here is my working solution so far. There may be a better way to do it, but this is what I've worked out. Sorry for the length of the code here, but I thought it might be helpful to others.
declare @tiSlots int
declare @tiCtr int
set @tiCtr=0

declare @nStartDate int
set @nStartDate=(datediff(dd,'01/01/1900',getdate()) + 1)



declare @tiProcID int
set @tiProcID=38286

declare @tiDuration int
set @tiDuration=(Select defaultTime from coProc where procID=@tiProcID)


declare @tiDurationIncrement int
set @tiDurationIncrement = @tiDuration/15


-- Create table to hold our result set we want to return
create table #SearchList (DateValue int,RoomID int,SchedItemID int,TimeValue int,NormalDateTime datetime)
-- Get a list of rooms the selected procedure can be done in
declare avail_Slots cursor STATIC for
select det.DateValue,
	det.RoomID,
	det.SchedItemID,
	det.TimeValue,
	dateadd(mi,det.TimeValue,NormalizedDate) as NormalDateTime
from
(
SELECT  dbo.DateRoom.DateValue, 
	dbo.DateRoom.RoomID, 
	dbo.SchedItem.SchedItemID, 
	dbo.SchedItem.TimeValue, 
	dbo.SchedItem.Meetingnumber, 
        dbo.SchedItem.SlotOpen, 
	dbo.SchedItem.ResourceType, dbo.SchedItem.ResourceID,
	dateadd(dd,DateValue,'01/01/1900') as NormalizedDate
FROM    dbo.DateRoom INNER JOIN
        dbo.SchedItem ON dbo.DateRoom.dateRoomID = dbo.SchedItem.DateRoomID

-- Limit to the specified Date
where  DateValue = @nStartDate
-- Limit the result to valid rooms for this procedure
and RoomID IN (
	SELECT distinct dbo.corooms.roomid
	FROM   dbo.coproc INNER JOIN
	       dbo.coProcRoomGrp ON dbo.coproc.procid = dbo.coProcRoomGrp.procID INNER JOIN
	       dbo.coRoomToGroup ON dbo.coProcRoomGrp.coRoomGrpID = dbo.coRoomToGroup.coRoomGrpID INNER JOIN
	       dbo.corooms ON dbo.coRoomToGroup.RoomID = dbo.corooms.roomid inner Join
	       dbo.coProcStartTimes on coProc.ProcID=coProcStartTimes.ProcID
	WHERE  dbo.coproc.procid = @tiProcID and dbo.coRooms.Isactive=1)) det
--where TimeValue >= 360 and TimeValue < 1000



Open avail_Slots
set @tiSlots=@@cursor_rows

declare @tiSchedIDEnd int
declare @tiAvailableSlots int

-- Setup holding varaiables for fetch
declare @TDateValue int
declare	@TRoomID int
declare	@TSchedItemID int
declare	@TTimeValue int
declare	@TNormalDateTime datetime

-- Loop Through the Cursor

while (@tiCtr < @tiSlots)
Begin
   set @tiCtr=@tiCtr+1
   fetch  avail_Slots into @TDateValue,@TRoomID,@TSchedItemID,@TTimeValue,@TNormalDateTime


   set @tiSchedIDEnd=@TSchedItemID+(@tiDurationIncrement - 1)

   set @tiAvailableSlots = 
	(Select count(SchedItemID) 
               from SchedItem inner join 
		DateRoom on SchedItem.DateRoomID=DateRoom.DateRoomID
               where DateValue=@TDateValue 
               and RoomID=@TRoomID 
	       and SlotOpen=1
               and SchedItemID between @TSchedItemID and @tiSchedIDEnd)

   if (@tiAvailableSlots >= @tiDurationIncrement)
    begin
         insert into #SearchList 
	(DateValue,RoomID,SchedItemID,TimeValue,NormalDateTime) values (@TDateValue,@TRoomID,@TSchedItemID,@TTimeValue,@TNormalDateTime)
    end 

End
-- Get our results now
select *,@tiDuration from #SearchList

-- Close the Cursor
Close Avail_Slots
DeAllocate Avail_Slots
-- Close the tempory table
drop table #SearchList
Kirk
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform