(
@.varDate as varchar (255),
@.StartDate as datetime,
@.EndDate as datetime
)
AS
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME) ' <--this part gave me an error, because of the comma,
END
ORDER BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END
The month case is giving me an error. I think it has to do with two expressions in one line.
Anyone know how to combine that into 1 expression? or is there away to work around it?
As I would like to display the month as Name, but group and sort by number.
Thx!~Are you saying you don't get the data you want if you remove the datename part from your group by clause? You are getting the month name out by the select part of your procedure and I don't see the need to also group by it if you only want to group by number.|||well, I am trying to get data displayed in the name of the month, but not in ABC order.
i.e.
January
Feb
March
instead of ABC order,
April
December
Febuary
I did the sql before grouping them together.
And this worked,
SELECT
DATENAME(mm, CALLSTARTTIME),
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)
Yet, the GROUP BY clause consist oftwoexpressions for it to function, (from my understanding)
and I don't know how to make that clause work in a CASE expression.
Thx in advance~|||oh yea, this doesn't work from my understanding:
SELECT
DATENAME(mm, CALLSTARTTIME),
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)|||How about this:-
GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END,
CASE @.varDate
WHEN 'Year' Then ??
WHEN 'Quarter' Then ??
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END
I think it would only work if you could put something in for the Year and Quarter too (where the ?? are). Might not work at all.
The only other thing would be to perhaps use a sql if to have 2 different selects, one for month with case no longer needed and one for the other 2 using case:-
if @.varDate='Month'
begin
SELECT DATENAME(mm, CALLSTARTTIME), COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)
end
else
begin
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END
ORDER BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END
end|||Thank you Brian. That will work.
Side Question: is there a way to name the heading by case?
e.g.
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END,|||Yes there is:-
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END as myheadingname|||SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END as myheadingname
Can myheadingname varies by case? like can it be a parameter/variable,and it will display different headings depending on the case selected.
e.g.
When @.varDate = 'Year', the column header will be 'Year'.
When @.varDate = 'Month', the column header will be 'Month'.
When @.varDate = 'Quarter', the column header will be 'Quarter'
the above example, as myheadingname is a generalized header. It will not differ no matter what @.varDate is.|||I don't think you can but no harm in trying something like that:-
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME) As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) as 'Month'
END
You will probably get an error if it doesn't work.|||yup, got an error.|||Well other than that you can expand the if for each possibility and do without the case.
