Help reqd. in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help reqd. in SQL

Author

Topic

koushikchandra
Starting Member

24 Posts

Posted - 2011-02-18 : 07:54:50

Hi,

I have written a sql like below, but it is failing on the then condition "then 'Wednesday, Saturday'". I have tried many ways like
"then '''Wednesday'',''Saturday'''". But it didn't work.

Can some one please help me here.

select case when datename (dw, getdate()-2)='Wednesday'
then (select COUNT(1)
from A041_Time
where TimeId > CAST(CONVERT(CHAR,getdate()-2,112) as int)
and TimeId <= CAST(CONVERT(CHAR,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),112) AS INT)
and TimeSalesDayFlag='Y'
and TimeDayShortDescription in ((case when (datename (dw, getdate()-2)='Wednesday') OR (datename (dw, getdate()-2)='Saturday')
then 'Wednesday, Saturday'
else datename (dw, getdate()-2)
end))
)
end
Regards,
Koushik

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-18 : 12:01:12


select 

case when datename (dw, getdate()-2)='Wednesday'
then (  select COUNT(1)
	    from A041_Time
		where TimeId > CAST(CONVERT(CHAR,getdate()-2,112) as int)
         and TimeId <= CAST(CONVERT(CHAR,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),112) AS INT)
         and TimeSalesDayFlag='Y'
      )
end

What does the above produce?

What is in the TimeDayShortDescription field?

YOur count is only going to occur if the condition for the subquery = 'Wednesday', so I am not sure what you are trying to do with

 ((case when (datename (dw, getdate()-2)='Wednesday') OR (datename (dw, getdate()-2)='Saturday')
then 'Wednesday, Saturday'
else datename (dw, getdate()-2)
end))

But you can't conditionally set the in statement, you would have to something else..but whatever you are actually trying to do isn't clear.

It looks like you want it TimeDayShortDescription to be either Wednesday,Saturday or whatever other datename(dw,getdate()-2) produces, however, the subquery will only fire if the datename(dw,getdate()-2) = 'Wednesday' to begin with...

Provide sample structures, data, and expected result if you want help with this...it isn't clear what you are even trying to do.

Poor planning on your part does not constitute an emergency on my part.

koushikchandra
Starting Member

24 Posts

Posted - 2011-02-20 : 05:10:44

Hi -

Please don't get into the other part of the query. What I want is TimeDayShortDescription in ('Wednesday, Saturday') when ((case when (datename (dw, getdate()-2)='Wednesday') OR (datename (dw, getdate()-2)='Saturday')

But the string 'Wednesday, Saturday' is not working for me in the case statement. Means the query is not executing .. I need help to write this part only properly.

select case when datename (dw, getdate()-2)='Wednesday'
then (select COUNT(1)
from A041_Time
where TimeId > CAST(CONVERT(CHAR,getdate()-2,112) as int)
and TimeId <= CAST(CONVERT(CHAR,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),112) AS INT)
and TimeSalesDayFlag='Y'
and TimeDayShortDescription in ((case when (datename (dw, getdate()-2)='Wednesday') OR (datename (dw, getdate()-2)='Saturday')
then 'Wednesday, Saturday'
else datename (dw, getdate()-2)
end))
)

select case when datename (dw, getdate()-2)='Wednesday'
then (select COUNT(1)
from A041_Time
where TimeId > CAST(CONVERT(CHAR,getdate()-2,112) as int)
and TimeId <= CAST(CONVERT(CHAR,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),112) AS INT)
and TimeSalesDayFlag='Y'
and TimeDayShortDescription in ('Wednesday, Saturday')
)

koushikchandra
Starting Member

24 Posts

Posted - 2011-02-20 : 05:39:50

OK.. I got a alternative way to write this ..

select case when datename (dw, getdate()-4)='Wednesday'
then (select COUNT(1)
from A041_Time
where TimeId > CAST(CONVERT(CHAR,getdate()-4,112) as int)
and TimeId <= CAST(CONVERT(CHAR,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()-4)+1,0)),112) AS INT)
and TimeSalesDayFlag='Y'
and TimeDayShortDescription in (datename (dw, getdate()-4),
(case when (datename (dw, getdate()-4)='Wednesday')
then 'Saturday'
when (datename (dw, getdate()-4)='Saturday')
then 'Wednesday'
end))
)
end

Anyway thanks foryour help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources