SQl Select table where clause 'WHERE is a conditional' ???

[azziuhc]

I am using SQl 2005.
I am trying to accomplish a simple view sql select using WHERE Clause conditional based on End of Month date
This is my View sql

Alter view view_name
as
select * from table
Where
Active_client = 'Y'
And sales = 1
AND (if day(getdate()) = 1 -- Beginning of new month
'01/ month(getdate()-1 / year(getdate()'
< client_date = <
'31/month(getdate()-1/Year(getdate()'
Else
do nothing
Can you please advice if this is possible ?
Please advice
Thank you
dre

 

[leiela]

Hi im not really sure at what exactly you want your query to do, if you can explain it i can help.

Looks to me like you need a case statement in your where clause something like.

Where Active_client = 'Y'
And sales = 1
AND 1 = Case
When day(getdate()) = 1
AND Client_Date Between
Cast('01/' + Cast(month(getdate())-1 as Varchar) + '/'+ Cast(year(getdate())as Varchar)as Date) AND Cast('31/' + Cast(month(getdate())-1 as Varchar) + '/'+ Cast(year(getdate())as Varchar)as Date)

Then 1
Else 0
End

 

[azziuhc]

Thank you for your quick answer.
Actually the purpose is to get all client for the previous month.
Therefore when it is the beginning of the month get all client that where entered from the previosu month.
I was able to resolve it by entering the following command.
Alter view view_name
as
select * from table
Where
Active_client = 'Y'
And sales = 1
And
-- this is beginning of previous month
Entered_Date >= DateAdd(mm, 0,DATEADD(dd,-(DAY(getdate())-1),getdate()))
AND
-- this would be the begining of the current month / note the use of '<'
Entered_date < dateadd(mm,1+datediff(mm,0,getdate()),0)

Thank you