SELECTCOUNT(*)AScalls,DATENAME(@.varDate, CALLSTARTTIME)AS 'Total Calls'
FROMCALL_LOG_MASTER
WHERE(COMMERCIALS='1')AND(CALLSTARTTIME >= @.StartDate)AND(CALLENDTIME <=@.EndDate
sql doesn't like: DATENAME( @.varDate, CallStartTime)
sql works fine if I change @.varDate into 'yy', 'mm', 'dd', or 'wk'
Since I do not want to make 5 unique Stored Proc just because of @.varDate....
Is there any way to work around this problem?
DECLARE @.varDate VARCHAR(10)
SELECT @.varDate = 'mm'
SELECT
OrderDate,
CASE @.varDate
WHEN 'mm' THEN DATENAME(mm,OrderDate)
WHEN 'yy' THEN DATENAME(yy,OrderDate)
WHEN 'dw' THEN DATENAME(dw,OrderDate)
END -- added this as an EDIT to my post
FROM Orders
|||I tried,
ALTER PROCEDURE dbo.GET_TOTALCALLS
(
@.VarDate as char,
@.StartDate as char,
@.EndDate as char
)
AS
SELECT
COUNT(*) AS 'Calls',
CASE @.VarDate
When 'yy' Then DATEPART(yy, CALLSTARTTIME) AS 'Year'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME IS NOT NULL) AND (CALLENDTIME IS NOT NULL)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)
SQL wouldn't allow me to save. What did I do wrong?
Try it like this:
SELECTCOUNT(*) AS 'Calls',CASE @.VarDateWhen 'yy' Then DATEPART(yy, CALLSTARTTIME)ENDAS 'Year'FROM CALL_LOG_MASTERWHERE (COMMERCIALS = '1') AND (CALLSTARTTIME IS NOT NULL) AND (CALLENDTIME IS NOT NULL)GROUP BY DATEPART(yy, CALLSTARTTIME)ORDER BY DATEPART(yy, CALLSTARTTIME)
Also, in the future, you need to be more specific than "SQL wouldn'tallow me to save". Exact error messages go a long way to gettingexact help.
|||thanks for the advice.
got another error:
ADO error: Column 'Call_LOG_MASTER.CALLSTARTTIME' is invalid in theselect list because it is not contained in either an aggregate functionor the GROUP BY clause.
SELECT
CASE @.varDate
when 'yy' Then DATEPART(yy, CALLSTARTTIME) END as 'Year',
COUNT(DATEPART(yy, CALLSTARTTIME)) AS 'Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)|||I think I found the problem,
ALTER PROCEDUREdbo.GET_TOTALCALLS
(
@.varDateas varchar(255),
@.StartDateas datetime,
@.EndDateas datetime
)
AS
SELECT
CASE@.varDate
When'Year'Then DATEPART(yy, CALLSTARTTIME)
When'Month'Then DATEPART(mm, CALLSTARTTIME)
When'Week'Then DATEPART(wk, CALLSTARTTIME)
When'Day'Then DATEPART(dd, CALLSTARTTIME)
when'Quarter'Then Datepart(qq, CALLSTARTTIME)
END,
Count(*)as'Total Calls'
FROMCALL_LOG_MASTER
WHERE(COMMERCIALS = '1')AND(CALLSTARTTIME >= @.StartDate)AND(CALLENDTIME <= @.EndDate)
GROUP BY
CASE@.varDate
When'Year'Then DATEPART(yy, CALLSTARTTIME)
When'Month'Then DATEPART(mm, CALLSTARTTIME)
When'Week'Then DATEPART(wk, CALLSTARTTIME)
When'Day'Then DATEPART(dd, CALLSTARTTIME)
when'Quarter'Then Datepart(qq, CALLSTARTTIME)
End
ORDER BY
CASE@.varDate
When'Year'Then DATEPART(yy, CALLSTARTTIME)
When'Month'Then DATEPART(mm, CALLSTARTTIME)
When'Week'Then DATEPART(wk, CALLSTARTTIME)
When'Day'Then DATEPART(dd, CALLSTARTTIME)
when'Quarter'Then Datepart(qq, CALLSTARTTIME)
End
RETURN
However, the column can't be named this way. My new question is how do I renamed the datepart column by case?
Namely,
when @.varDate = 'Year', the column name should be 'Year',
when @.varDate = 'Month', the column name should be as 'Month', etc..
ALTER PROCEDURE dbo.GET_TOTALCALLS
(
@.varDate as varchar (255),
@.StartDate as datetime,
@.EndDate as datetime
)
AS
IF @.varDate = 'Year'
BEGIN
SELECT
DATEPART(yy, CALLSTARTTIME) AS Year,
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY 1
END
IF @.varDate = 'Month'
BEGIN
SELECT
DATEPART(mm, CALLSTARTTIME) AS Month,
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(mm, CALLSTARTTIME)
ORDER BY 1
END
-- etc
RETURN
|||yes, thank you. That will work fine
