March 27, 2013 at 12:04 pm
Tried this below with no luck
I Wanted to use today's date if the parameter @enddate was greater than today
WHERE (KindOfDay = 'Weekday') AND (Date BETWEEN @startdate AND CASE When @enddate > getdate() THEN Getdate() ELSE (@enddate + 1))
Thanks in Advance.. Again 🙂
Joe
March 27, 2013 at 12:07 pm
Have you tried? If so, did you get an error?
March 27, 2013 at 12:23 pm
I get Incorrect syntax near ')'.
But can't find it?
here is everything
O
/****** Object: StoredProcedure [dbo].[jb_test_deleteme_sp] Script Date: 03/27/2013 14:18:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[jb_test_deleteme_sp]
(
@StartDate datetime,@EndDate datetime
)
as
SELECT Year, SUM(426) AS Total_Hours, Date,
CASE Month WHEN '1' THEN '7January' WHEN '2' THEN '8February' WHEN '3' THEN '9March' WHEN '4' THEN 'xApril' WHEN '5' THEN 'yMay' WHEN '6' THEN 'zJune'
WHEN '7' THEN '1July' WHEN '8' THEN '2August' WHEN '9' THEN '3September' WHEN '10' THEN '4October' WHEN '11' THEN '5November' WHEN '12' THEN '6December'
END AS month, CASE kindofday WHEN 'Weekday' THEN 'Max Hour Capacity' END AS kindofday
FROM Auxiliary.Calendar
WHERE (KindOfDay = 'Weekday') AND (Date BETWEEN @startdate AND CASE When @enddate > getdate() THEN Getdate() ELSE @enddate + 1)
GROUP BY Year, KindOfDay, Month, Date
March 27, 2013 at 12:28 pm
jbalbo (3/27/2013)
I get Incorrect syntax near ')'.But can't find it?
here is everything
O
/****** Object: StoredProcedure [dbo].[jb_test_deleteme_sp] Script Date: 03/27/2013 14:18:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[jb_test_deleteme_sp]
(
@StartDate datetime,@EndDate datetime
)
as
SELECT Year, SUM(426) AS Total_Hours, Date,
CASE Month WHEN '1' THEN '7January' WHEN '2' THEN '8February' WHEN '3' THEN '9March' WHEN '4' THEN 'xApril' WHEN '5' THEN 'yMay' WHEN '6' THEN 'zJune'
WHEN '7' THEN '1July' WHEN '8' THEN '2August' WHEN '9' THEN '3September' WHEN '10' THEN '4October' WHEN '11' THEN '5November' WHEN '12' THEN '6December'
END AS month, CASE kindofday WHEN 'Weekday' THEN 'Max Hour Capacity' END AS kindofday
FROM Auxiliary.Calendar
WHERE (KindOfDay = 'Weekday') AND (Date BETWEEN @startdate AND CASE When @enddate > getdate() THEN Getdate() ELSE @enddate + 1)
GROUP BY Year, KindOfDay, Month, Date
After formating the code, it was apparent. You are missing the END for your CASE in the where clause.
SELECT
Year,
SUM(426) AS Total_Hours,
Date,
CASE Month WHEN '1' THEN '7January'
WHEN '2' THEN '8February'
WHEN '3' THEN '9March'
WHEN '4' THEN 'xApril'
WHEN '5' THEN 'yMay'
WHEN '6' THEN 'zJune'
WHEN '7' THEN '1July'
WHEN '8' THEN '2August'
WHEN '9' THEN '3September'
WHEN '10' THEN '4October'
WHEN '11' THEN '5November'
WHEN '12' THEN '6December'
END AS month,
CASE kindofday WHEN 'Weekday' THEN 'Max Hour Capacity' END AS kindofday
FROM
Auxiliary.Calendar
WHERE
(KindOfDay = 'Weekday') AND
(Date BETWEEN @startdate AND CASE When @enddate > getdate() THEN Getdate() ELSE @enddate + 1 end)
GROUP BY
Year,
KindOfDay,
Month,
Date;
March 27, 2013 at 12:42 pm
jbalbo (3/27/2013)
Tried this below with no luckI Wanted to use today's date if the parameter @enddate was greater than today
WHERE (KindOfDay = 'Weekday') AND (Date BETWEEN @startdate AND CASE When @enddate > getdate() THEN Getdate() ELSE (@enddate + 1))
Thanks in Advance.. Again 🙂
Joe
You're missing an END statement inside the last Bracket.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 27, 2013 at 12:50 pm
Thanks Lynn..
Just another reason to format !!! 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply