Coverting SQL from MS Access to SQL

[T.J. Bernard]

This is a tough one, so if there are any SQL guru's out
there, I would welcome any advice/help with my problem.

I have a query that I created in MS Access that selects
all the records that fall with in a date range from the
current day to the previous Friday. If the day happens to
be a Friday that the query is run, then it only selects
that one day of data.

So if the query is run on Tuesday July 29, it pulls in all
records from Friday July 25 - Tuesday July 29.

If the query is run on Friday August 1st, it only pulls in
August 1st.

Here is the WHERE statement in MS Access that accomplishes
this feat:

Between (Select MAX(Date()-[IntValue]) As PreviousFriday
From tbl_Numbers WHERE [intValue] > 0 AND weekday(Date()-
[intValue]) = 6) And IIf(Weekday(Date())=6,Date()-1,Date())

tbl_Numbers - a table with just 1 field stores 0-9 in a
table.

Thank you for your help and advice.

T.J.

 

[Lenny]

I am no guru, but I'll try to help.

IIF function is not supported in SQL use "case when" instead.

 

[Vadim Rapp]

TB> I have a query that I created in MS Access that selects
TB> all the records that fall with in a date range from the
TB> current day to the previous Friday.

between last friday and today:

between
dateadd
(
d,
-cast(
(datepart(dw,getdate())-6) + 7 * (0.5 - sign
(datepart(dw,getdate())-6)/2.0)
- (3.5 * abs(abs(sign(datepart(dw,getdate())-6))-1))
as integer),
getdate()
)

and getdate()


regards,

Vadim