Problems:
- Given a date, the date format was reverting to US format as opposed to European
- Given a Campus as a text value, the SQL query was ignoring this completely
- Hardcoding the scalar local variables worked
I have two parameters that will be submitted (GivenDate and GivenCampus). Consider the following script:
-- =============================================================================== -- Description: Gets the room bookings for a week based on a given date and campus -- =============================================================================== DECLARE @GivenDate datetime, @GivenCampus varchar(100), @setId varchar(10), @weekNumber int, @siteId nvarchar(5), @baseDate datetime, @specifiedDate datetime, @specifiedCampus varchar(25) SET DATEFORMAT dmy --SET @GivenDate = '1/7/2011' --SET @GivenCampus = 'Lansdowne Campus' SET @specifiedDate = (SELECT @GivenDate) SET @specifiedCampus = (SELECT @GivenCampus) -- Get columns SetId and WeekNumber from the weekstructure table based on the given date SET @setId = (SELECT TOP 1 SetId FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 6, @specifiedDate)) SET @weekNumber = (SELECT TOP 1 WeekNumber FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 1, @specifiedDate)) -- Get the SiteID based on the given campus and the setId (based on the given date) SET @siteId = (SELECT TOP 1 SiteId FROM SITES WHERE SiteName=@specifiedCampus AND SetId=@setId) -- Get the start date for the week in question SET @baseDate = (SELECT TOP 1 StartDate FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 1, @specifiedDate)) SELECT @specifiedDate AS 'GivenDate', dateadd(d, tt.[WeekDay] - 1, @baseDate) AS 'Date', datename(weekday, dateadd(d, tt.[WeekDay] - 1, @baseDate)) AS 'Day', tt.[SiteId] AS 'Site', tt.[StartTime] AS 'Start', tt.[FinishTime] AS 'Finish', tt.[RoomId] AS 'Room', sd.[Descrip] AS 'Event Type', sd.[Notes] AS 'Booking Notes', c.[Name] AS 'Contact', c.[Company] AS 'Dept', c.[Phone] AS 'Phone' FROM [CMISdata].[cmisuser].[SLOTDETAILS] sd INNER JOIN [CMISdata].[cmisuser].[TIMETABLE] tt ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId] INNER JOIN [CMISdata].[cmisuser].[CONTACT] c ON c.[ContactId] = sd.[Contact] and c.[SetId] = sd.[SetId] INNER JOIN [CMISdata].[cmisuser].[WEEKMAPNUMERIC] wm ON (wm.[SetId] = tt.[SetId] and wm.[WeekId] = tt.[WeekId]) WHERE sd.[SetId] = @setId --AND dateadd(d, tt.[WeekDay] - 1, @baseDate) = @SelectedDate AND wm.[WeekNumber] = @weekNumber AND tt.[SiteId] = @siteId AND tt.[Status] 3 -- Remove cancelled bookings AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%' ORDER BY Date ASC, Room ASC, tt.[StartTime] ASC, tt.[FinishTime] ASC
- -- ===============================================================================
- -- Description: Gets the room bookings for a week based on a given date and campus
- -- ===============================================================================
- DECLARE
- @GivenDate datetime,
- @GivenCampus varchar(100),
- @setId varchar(10),
- @weekNumber int,
- @siteId nvarchar(5),
- @baseDate datetime,
- @specifiedDate datetime,
- @specifiedCampus varchar(25)
- SET DATEFORMAT dmy
- --SET @GivenDate = '1/7/2011'
- --SET @GivenCampus = 'Lansdowne Campus'
- SET @specifiedDate = (SELECT @GivenDate)
- SET @specifiedCampus = (SELECT @GivenCampus)
- -- Get columns SetId and WeekNumber from the weekstructure table based on the given date
- SET @setId = (SELECT TOP 1 SetId FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 6, @specifiedDate))
- SET @weekNumber = (SELECT TOP 1 WeekNumber FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 1, @specifiedDate))
- -- Get the SiteID based on the given campus and the setId (based on the given date)
- SET @siteId = (SELECT TOP 1 SiteId FROM SITES WHERE SiteName=@specifiedCampus AND SetId=@setId)
- -- Get the start date for the week in question
- SET @baseDate = (SELECT TOP 1 StartDate FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 1, @specifiedDate))
- SELECT
- @specifiedDate AS 'GivenDate',
- dateadd(d, tt.[WeekDay] - 1, @baseDate) AS 'Date',
- datename(weekday, dateadd(d, tt.[WeekDay] - 1, @baseDate)) AS 'Day',
- tt.[SiteId] AS 'Site',
- tt.[StartTime] AS 'Start',
- tt.[FinishTime] AS 'Finish',
- tt.[RoomId] AS 'Room',
- sd.[Descrip] AS 'Event Type',
- sd.[Notes] AS 'Booking Notes',
- c.[Name] AS 'Contact',
- c.[Company] AS 'Dept',
- c.[Phone] AS 'Phone'
- FROM [CMISdata].[cmisuser].[SLOTDETAILS] sd
- INNER JOIN [CMISdata].[cmisuser].[TIMETABLE] tt
- ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId]
- INNER JOIN [CMISdata].[cmisuser].[CONTACT] c
- ON c.[ContactId] = sd.[Contact] and c.[SetId] = sd.[SetId]
- INNER JOIN [CMISdata].[cmisuser].[WEEKMAPNUMERIC] wm
- ON (wm.[SetId] = tt.[SetId] and wm.[WeekId] = tt.[WeekId])
- WHERE sd.[SetId] = @setId
- --AND dateadd(d, tt.[WeekDay] - 1, @baseDate) = @SelectedDate
- AND wm.[WeekNumber] = @weekNumber
- AND tt.[SiteId] = @siteId
- AND tt.[Status] 3 -- Remove cancelled bookings
- AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%'
- ORDER BY Date ASC, Room ASC,
- tt.[StartTime] ASC,
- tt.[FinishTime] ASC
In case you've been stuck for ages (as I have), I found out you cannot declare the parameters used by the software within the SQL script... So the above should look more like this:
-- =============================================================================== -- Description: Gets the room bookings for a week based on a given date and campus -- =============================================================================== DECLARE @setId varchar(10), @weekNumber int, @siteId nvarchar(5), @baseDate datetime, @specifiedDate datetime SET DATEFORMAT dmy -- Get columns SetId and WeekNumber from the weekstructure table based on the given date SET @setId = (SELECT TOP 1 SetId FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @GivenDate) AND DATEADD(day, 6, @GivenDate)) SET @weekNumber = (SELECT TOP 1 WeekNumber FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @GivenDate) AND DATEADD(day, 1, @GivenDate)) -- Get the SiteID based on the given campus and the setId (based on the given date) SET @siteId = (SELECT TOP 1 SiteId FROM SITES WHERE SiteName=@GivenCampus AND SetId=@setId) -- Get the start date for the week in question SET @baseDate = (SELECT TOP 1 StartDate FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @GivenDate) AND DATEADD(day, 1, @GivenDate)) SELECT @GivenDate AS 'GivenDate', dateadd(d, tt.[WeekDay] - 1, @baseDate) AS 'Date', datename(weekday, dateadd(d, tt.[WeekDay] - 1, @baseDate)) AS 'Day', tt.[SiteId] AS 'Site', tt.[StartTime] AS 'Start', tt.[FinishTime] AS 'Finish', tt.[RoomId] AS 'Room', sd.[Descrip] AS 'Event Type', sd.[Notes] AS 'Booking Notes', c.[Name] AS 'Contact', c.[Company] AS 'Dept', c.[Phone] AS 'Phone' FROM [CMISdata].[cmisuser].[SLOTDETAILS] sd INNER JOIN [CMISdata].[cmisuser].[TIMETABLE] tt ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId] INNER JOIN [CMISdata].[cmisuser].[CONTACT] c ON c.[ContactId] = sd.[Contact] and c.[SetId] = sd.[SetId] INNER JOIN [CMISdata].[cmisuser].[WEEKMAPNUMERIC] wm ON (wm.[SetId] = tt.[SetId] and wm.[WeekId] = tt.[WeekId]) WHERE sd.[SetId] = @setId --AND dateadd(d, tt.[WeekDay] - 1, @baseDate) = @SelectedDate AND wm.[WeekNumber] = @weekNumber AND tt.[SiteId] = @siteId AND tt.[Status] 3 -- Remove cancelled bookings AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%' ORDER BY Date ASC, Room ASC, tt.[StartTime] ASC, tt.[FinishTime] ASC
- -- ===============================================================================
- -- Description: Gets the room bookings for a week based on a given date and campus
- -- ===============================================================================
- DECLARE
- @setId varchar(10),
- @weekNumber int,
- @siteId nvarchar(5),
- @baseDate datetime,
- @specifiedDate datetime
- SET DATEFORMAT dmy
- -- Get columns SetId and WeekNumber from the weekstructure table based on the given date
- SET @setId = (SELECT TOP 1 SetId FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @GivenDate) AND DATEADD(day, 6, @GivenDate))
- SET @weekNumber = (SELECT TOP 1 WeekNumber FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @GivenDate) AND DATEADD(day, 1, @GivenDate))
- -- Get the SiteID based on the given campus and the setId (based on the given date)
- SET @siteId = (SELECT TOP 1 SiteId FROM SITES WHERE SiteName=@GivenCampus AND SetId=@setId)
- -- Get the start date for the week in question
- SET @baseDate = (SELECT TOP 1 StartDate FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @GivenDate) AND DATEADD(day, 1, @GivenDate))
- SELECT
- @GivenDate AS 'GivenDate',
- dateadd(d, tt.[WeekDay] - 1, @baseDate) AS 'Date',
- datename(weekday, dateadd(d, tt.[WeekDay] - 1, @baseDate)) AS 'Day',
- tt.[SiteId] AS 'Site',
- tt.[StartTime] AS 'Start',
- tt.[FinishTime] AS 'Finish',
- tt.[RoomId] AS 'Room',
- sd.[Descrip] AS 'Event Type',
- sd.[Notes] AS 'Booking Notes',
- c.[Name] AS 'Contact',
- c.[Company] AS 'Dept',
- c.[Phone] AS 'Phone'
- FROM [CMISdata].[cmisuser].[SLOTDETAILS] sd
- INNER JOIN [CMISdata].[cmisuser].[TIMETABLE] tt
- ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId]
- INNER JOIN [CMISdata].[cmisuser].[CONTACT] c
- ON c.[ContactId] = sd.[Contact] and c.[SetId] = sd.[SetId]
- INNER JOIN [CMISdata].[cmisuser].[WEEKMAPNUMERIC] wm
- ON (wm.[SetId] = tt.[SetId] and wm.[WeekId] = tt.[WeekId])
- WHERE sd.[SetId] = @setId
- --AND dateadd(d, tt.[WeekDay] - 1, @baseDate) = @SelectedDate
- AND wm.[WeekNumber] = @weekNumber
- AND tt.[SiteId] = @siteId
- AND tt.[Status] 3 -- Remove cancelled bookings
- AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%'
- ORDER BY Date ASC, Room ASC,
- tt.[StartTime] ASC,
- tt.[FinishTime] ASC
- SET DATEFORMAT dmy returns the date in European format irrespective of the regional settings of either the client or the reporting server.
- Removed @GivenDate datetime, from the SQL script
- Removed @GivenCampus varchar(100) from the SQL script