February 8, 2013 at 8:29 am
Hi, I have the following select query with a CASE statement as part of it but it will not show up as a field in my dataset!
I have tested the CASE statement works in SQL Management Studio and it is fine. Is there some setting or reason why a CASE statement wouldn't show up as a selectable field in the dataset in SSRS?
SELECT rdowner.V_ACTIVITY.Name AS Name, rdowner.V_ACTIVITY.Duration * @PeriodDuration / 60.0 AS Duration,
CASE WHEN (rdowner.V_ACTIVITY_DATETIME.Week <= 11)
THEN 'Term1' WHEN (rdowner.V_ACTIVITY_DATETIME.Week >= 15) AND
(rdowner.V_ACTIVITY_DATETIME.Week <= 27) THEN 'Term2'
WHEN (rdowner.V_ACTIVITY_DATETIME.Week >= 28)
THEN 'Term3' ELSE 'Fail' END AS TermWeek,
rdowner.V_ACTIVITY_DATETIME.Week AS Week,
rdowner.V_ACTIVITY_DATETIME.Day AS Day, rdowner.V_STAFF.Id AS StaffId, rdowner.V_STAFF.Name AS StaffName,
rdowner.V_STAFF.DepartmentId AS DepartmentId, rdowner.V_STAFF.IsPartTime AS IsPartTime,
rdowner.V_STAFF.MaximumHours AS MaximumHours, rdowner.V_DEPARTMENT.Name AS DepartmentName
Thanks in advance...
February 8, 2013 at 9:37 am
Here is my complete SQL statement - is it because of where the CASE statement is in the query?
DECLARE @PeriodDuration INTEGER SELECT @PeriodDuration = rdowner.V_GLOBAL.PeriodLength
FROM rdowner.V_GLOBAL; WITH Wks AS
(SELECT DISTINCT
rdowner.V_Week_Label.WeekNumber AS WkNumber, rdowner.V_Week_Label.WeekLabel AS WkLabel,
rdowner.V_Week_Label.WeekStartDate AS WkStartDate, rdowner.V_Staff.Name AS WName,
rdowner.V_STAFF.ContractHours AS ContractHours, rdowner.V_STAFF.IsPartTime AS IsPartTime,
rdowner.V_STAFF.MaximumHours AS MaximumHours, rdowner.V_Staff.Id AS WStaffId,
rdowner.V_DEPARTMENT.Name AS WDepartmentName
FROM rdowner.V_Week_Label, rdowner.V_STAFF INNER JOIN
rdowner.V_DEPARTMENT ON rdowner.V_DEPARTMENT.Id = rdowner.V_STAFF.DepartmentId
WHERE rdowner.V_STAFF.DepartmentId = @Department), Events AS
(SELECT rdowner.V_ACTIVITY.Name AS Name, rdowner.V_ACTIVITY.Duration * @PeriodDuration / 60.0 AS Duration,
rdowner.V_ACTIVITY_DATETIME.Week AS Week, rdowner.V_ACTIVITY_DATETIME.Day AS Day,
CASE WHEN (rdowner.V_ACTIVITY_DATETIME.Week <= 11)
THEN 'Term1' WHEN (rdowner.V_ACTIVITY_DATETIME.Week >= 15) AND
(rdowner.V_ACTIVITY_DATETIME.Week <= 27) THEN 'Term2' WHEN (rdowner.V_ACTIVITY_DATETIME.Week >= 28)
THEN 'Term3' ELSE 'Fail' END AS TermWeek,
rdowner.V_STAFF.Id AS StaffId, rdowner.V_STAFF.Name AS StaffName,
rdowner.V_STAFF.DepartmentId AS DepartmentId, rdowner.V_STAFF.IsPartTime AS IsPartTime,
rdowner.V_STAFF.MaximumHours AS MaximumHours, rdowner.V_DEPARTMENT.Name AS DepartmentName
FROM rdowner.V_ACTIVITY_DATETIME INNER JOIN
rdowner.V_ACTIVITY ON rdowner.V_ACTIVITY_DATETIME.ActivityId = rdowner.V_ACTIVITY.Id INNER JOIN
rdowner.V_ACTIVITY_STAFF ON rdowner.V_ACTIVITY_STAFF.ActivityId = rdowner.V_ACTIVITY.Id INNER JOIN
rdowner.V_STAFF ON rdowner.V_STAFF.Id = rdowner.V_ACTIVITY_STAFF.StaffId INNER JOIN
rdowner.V_DEPARTMENT ON rdowner.V_DEPARTMENT.Id = rdowner.V_STAFF.DepartmentId
WHERE @Department = rdowner.V_STAFF.DepartmentId)
SELECT w.WkNumber, w.WStaffId, w.WName, e.Name, e.Duration, e.Week, e.Day, e.StaffId, e.StaffName, e.DepartmentId,
e.DepartmentName, w.WDepartmentName, w.ContractHours, w.MaximumHours, w.IsPartTime, w.WkLabel
FROM Wks w LEFT JOIN
Events e ON e.Week = w.WkNumber AND e.StaffId = w.WStaffId
ORDER BY w.WName, w.WkNumber
February 8, 2013 at 9:45 am
you created teh case in the CTE, but you never use it in your final query, i think.
see my reformatted post of your code, with this comment:
e.TermWeek,--<<--don't you want that in your final query since you built a CASE for it?
DECLARE @Department INT
DECLARE @PeriodDuration INTEGER
SELECT
@PeriodDuration = rdowner.V_GLOBAL.PeriodLength
FROM rdowner.V_GLOBAL;
WITH Wks
AS (SELECT
DISTINCT
rdowner.V_Week_Label.WeekNumber AS WkNumber,
rdowner.V_Week_Label.WeekLabel AS WkLabel,
rdowner.V_Week_Label.WeekStartDate AS WkStartDate,
rdowner.V_Staff.Name AS WName,
rdowner.V_STAFF.ContractHours AS ContractHours,
rdowner.V_STAFF.IsPartTime AS IsPartTime,
rdowner.V_STAFF.MaximumHours AS MaximumHours,
rdowner.V_Staff.Id AS WStaffId,
rdowner.V_DEPARTMENT.Name AS WDepartmentName
FROM rdowner.V_Week_Label,rdowner.V_STAFF
INNER JOIN rdowner.V_DEPARTMENT
ON rdowner.V_DEPARTMENT.Id = rdowner.V_STAFF.DepartmentId
WHERE rdowner.V_STAFF.DepartmentId = @Department),
Events
AS (SELECT
rdowner.V_ACTIVITY.Name AS Name,
rdowner.V_ACTIVITY.Duration * @PeriodDuration / 60.0 AS Duration,
rdowner.V_ACTIVITY_DATETIME.Week AS Week,
rdowner.V_ACTIVITY_DATETIME.Day AS Day,
CASE
WHEN ( rdowner.V_ACTIVITY_DATETIME.Week <= 11 )
THEN 'Term1'
WHEN ( rdowner.V_ACTIVITY_DATETIME.Week >= 15 )
AND ( rdowner.V_ACTIVITY_DATETIME.Week <= 27 )
THEN 'Term2'
WHEN ( rdowner.V_ACTIVITY_DATETIME.Week >= 28 )
THEN 'Term3'
ELSE 'Fail'
END AS TermWeek,
rdowner.V_STAFF.Id AS StaffId,
rdowner.V_STAFF.Name AS StaffName,
rdowner.V_STAFF.DepartmentId AS DepartmentId,
rdowner.V_STAFF.IsPartTime AS IsPartTime,
rdowner.V_STAFF.MaximumHours AS MaximumHours,
rdowner.V_DEPARTMENT.Name AS DepartmentName
FROM rdowner.V_ACTIVITY_DATETIME
INNER JOIN rdowner.V_ACTIVITY
ON rdowner.V_ACTIVITY_DATETIME.ActivityId = rdowner.V_ACTIVITY.Id
INNER JOIN rdowner.V_ACTIVITY_STAFF
ON rdowner.V_ACTIVITY_STAFF.ActivityId = rdowner.V_ACTIVITY.Id
INNER JOIN rdowner.V_STAFF
ON rdowner.V_STAFF.Id = rdowner.V_ACTIVITY_STAFF.StaffId
INNER JOIN rdowner.V_DEPARTMENT
ON rdowner.V_DEPARTMENT.Id = rdowner.V_STAFF.DepartmentId
WHERE @Department = rdowner.V_STAFF.DepartmentId)
SELECT
e.TermWeek,--<<--don't you want that in your final query since you built a CASE for it?
w.WkNumber,
w.WStaffId,
w.WName,
e.Name,
e.Duration,
e.Week,
e.Day,
e.StaffId,
e.StaffName,
e.DepartmentId,
e.DepartmentName,
w.WDepartmentName,
w.ContractHours,
w.MaximumHours,
w.IsPartTime,
w.WkLabel
FROM Wks w
LEFT JOIN Events e
ON e.Week = w.WkNumber
AND e.StaffId = w.WStaffId
ORDER BY
w.WName,
w.WkNumber
Lowell
February 8, 2013 at 9:52 am
You Sir are a genius, and I am an idiot - so often the simplest of things....!!!!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply