Room Utilisation Report

These require Visual Studio 2015 unless stated otherwise
VS 2015 should be available as part of your campus agreement
Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
User avatar
robinwilson16
Site Admin
Posts: 185
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile: https://www.linkedin.com/in/robinwilson16
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Room Utilisation Report

Post by robinwilson16 » Mon Oct 17, 2016 9:30 am

Here is a copy of the room utilisation report we have developed in case it is helpful.
Attachments
CPL012 - Room Utilisation.rdl
(220.12 KiB) Downloaded 217 times

User avatar
robinwilson16
Site Admin
Posts: 185
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile: https://www.linkedin.com/in/robinwilson16
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Re: Room Utilisation Report

Post by robinwilson16 » Mon Oct 17, 2016 9:31 am

You will also need this table valued function that works out if a particular date is a teaching day or not:

Code: Select all

USE [GC]
GO
/****** Object:  UserDefinedFunction [dbo].[College_Dates]    Script Date: 17/10/2016 09:30:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[College_Dates] (
	@FirstDateOfYear DATETIME,
	@LastDateOfYear DATETIME,
	@WkPattern VARCHAR(100)
)
RETURNS TABLE
AS
RETURN

(WITH CTE1 AS (
		SELECT
			 1 AS DayID,
			@FirstDateOfYear AS FromDate,
			DATENAME(dw, @FirstDateOfYear) AS Dayname

		UNION ALL

		SELECT 
			CTE1.DayID + 1 AS DayID,
			DATEADD(d, 1 ,CTE1.FromDate),
			DATENAME(dw, DATEADD(d, 1 ,CTE1.FromDate)) AS Dayname
		FROM 
			CTE1
		WHERE 
			DATEADD(d,1,CTE1.FromDate) < @LastDateOfYear
		),

AllDays as(SELECT 
	FromDate AS Date, 
	Dayname,  
	DATEADD(wk, DATEDIFF(wk,0,FromDate), 0) as MondayDate
FROM CTE1

WHERE 
	DayName NOT IN ('Saturday','Sunday')


),
  CTE2 as (
            SELECT			
			  1 as rn,
			  CONVERT(varchar,LEFT(@WkPattern , 1)) as DataItem,
			  STUFF(@WkPattern, 1, 1, '') as Data,
			  @FirstDateOfYear as WkStart			

			UNION ALL
			SELECT 			
				rn + 1 as rn,
				CONVERT(VARCHAR,LEFT(data, 1)) as DataItem,
				STUFF(data,1, 1,'') as Data,
				DATEADD(day,7,WkStart) as WkStart
			 FROM
				CTE2
             WHERE
              LEN(Data) >0
       ),

  YearPattern as (
              SELECT
				rn,
				DataItem,
				Data,				
				WkStart	 
              FROM
				CTE2
              WHERE
				DataItem = 1 )

SELECT
	 AllDays.Date 
FROM YearPattern INNER JOIN AllDays
	 ON AllDays.MondayDate = YearPattern.WkStart
)

Post Reply
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Who is online

Users browsing this forum: No registered users and 1 guest