Case Statement

hi,
my procedure is
create procedure ct_tpin(@.Start_Date datetime,@.End_Date datetime,@.Rpt_Name
varchar(50))
as
begin
case when @.Rpt_Name = 'TpinGenerated' then
select gr_tpin_flag_t.KEY1, gr_cust_m.Name,gr_tpin_flag_t.dateandtime FROM
gr_tpin_flag_t, gr_cust_m
WHERE (gr_tpin_flag_t.tpinflag = 'G') AND (gr_tpin_flag_t.key1 =
gr_cust_m.key1) and
(dateandtime between convert(datetime, @.Start_Date ,3)
and convert(datetime,@.End_Date,3)
end
end
its throwing error near case and end, pls give me a solution.
thanks
vanithaHi Vanitha,
What about
IF @.Rpt_Name = 'TpinGenerated' THEN
BEGIN
<YourCodeor Selectstatement)
END
HTH, Jens SUessmeyer.|||its the alternate method.
but i want to know how i can handle this in case statement
"Jens" wrote:

> Hi Vanitha,
>
> What about
> IF @.Rpt_Name = 'TpinGenerated' THEN
> BEGIN
> <YourCodeor Selectstatement)
> END
> HTH, Jens SUessmeyer.
>|||CASE is an expression, not a statement. You cannot use CASE the way you trie
d. Use IF instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:18033B4C-6327-4E07-BD91-F5DC81DD5C32@.microsoft.com...
> hi,
> my procedure is
> create procedure ct_tpin(@.Start_Date datetime,@.End_Date datetime,@.Rpt_Name
> varchar(50))
> as
> begin
> case when @.Rpt_Name = 'TpinGenerated' then
> select gr_tpin_flag_t.KEY1, gr_cust_m.Name,gr_tpin_flag_t.dateandtime FROM
> gr_tpin_flag_t, gr_cust_m
> WHERE (gr_tpin_flag_t.tpinflag = 'G') AND (gr_tpin_flag_t.key1 =
> gr_cust_m.key1) and
> (dateandtime between convert(datetime, @.Start_Date ,3)
> and convert(datetime,@.End_Date,3)
> end
> end
> its throwing error near case and end, pls give me a solution.
> thanks
> vanitha|||There is no Case "Statement" in TSQL.
Case is an Expression that can be used as part of a query not a control flow
statement like in other languages.
Please read the syntax for it in BOL
"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:449441A9-BDA1-43AD-B49E-866EE67EE89D@.microsoft.com...
> its the alternate method.
> but i want to know how i can handle this in case statement
> "Jens" wrote:
>sql

case expression stor proc, need some help

ALTER PROCEDURE dbo.TEST_TOTALCALLS
(
@.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.

Capture time alone in SQL Server database

Is there any data type in SQL Server 2005 which captures only the TIME in default DATETIME type?

For example, if the datatime field has a value2007-12-11 12:31:00.000, i need a datatype which can capture12:31:00.000 alone. The data type should be in a fashion so that i can find differences in time also...

Any ideas??

Hi,

SQL Server does not have any type which can store only time. One way is to store it as datetime, and when fetching these times, you can convert them to only show time.

|||

venkatesh_ur:

Is there any data type in SQL Server 2005 which captures only the TIME in default DATETIME type?

I'm afraid there is no Datatype to fetch the time only. But of course there are some functions that can be used to get the time part out of any datatime value. Below query will get you the time (converted as varchar):

select getdate() , convert ( varchar , getdate() , 8 )

venkatesh_ur:

he data type should be in a fashion so that i can find differences in time also...

To compare date and time values theDatepartandDateNamefunctions can be useful. They both are quite similar to each other. Read Books Online for more help on these functions.