Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

azziuhc

Programmer
Apr 29, 2010
26
US
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
 
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



 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top