SQL - SELECT previous date

[Kamil]

Hi.
I'm using a query:
SELECT BOM, EMAILS, Count(IIf([date]=Date(),[adalnm],Null)) AS Today,
Count(IIf([date]=Date()-1,[adalnm],Null)) AS Yesterday
FROM T_BOM
GROUP BY BOM, EMAILS;

For counting all rows for today and yesterday. Is it possible to
replace the yesterday formula by PreviousDay formula?
Purpose: In my table there's data for all workdays, w/o weekends. So
on Monday the yesterday shows me 0. Instead it I'd like to see last
date which is filled with some data (picking Friday in case of Monday
is not the solution because of holidays)

Please advice,
Best regards,
Kamil

 

[Allen Browne]

DMax() could give you the max date value prior to today:

SELECT BOM, EMAILS, Count(IIf([date]=Date(),[adalnm],Null)) AS Today,
Count(IIf([date] = DMax("[date]", "T_BOM", "[date] < Date()"),
[adalnm], Null)) AS Yesterday
FROM T_BOM
GROUP BY BOM, EMAILS;

A subquery might be more efficient:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[Kamil]

DMax() could give you the max date value prior to today:

SELECT BOM, EMAILS, Count(IIf([date]=Date(),[adalnm],Null)) AS Today,
Count(IIf([date] = DMax("[date]", "T_BOM", "[date] < Date()"),
    [adalnm], Null)) AS Yesterday
FROM T_BOM
GROUP BY BOM, EMAILS;

A subquery might be more efficient:
   http://allenbrowne.com/subquery-01.html#AnotherRecord

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi.
I'm using a query:
SELECT BOM, EMAILS, Count(IIf([date]=Date(),[adalnm],Null)) AS Today,
Count(IIf([date]=Date()-1,[adalnm],Null)) AS Yesterday
FROM T_BOM
GROUP BY BOM, EMAILS;

For counting all rows for today and yesterday. Is it possible to
replace the yesterday formula by PreviousDay formula?
Purpose: In my table there's data for all workdays, w/o weekends. So
on Monday the yesterday shows me 0. Instead it I'd like to see last
date which is filled with some data (picking Friday in case of Monday
is not the solution because of holidays)

Please advice,
Best regards,
Kamil

Thanks!
You're right, subquery works a lot faster:

SELECT BOM, EMAILS, Count(IIf([date]=Date(),[adalnm],Null)) AS Today,
Count(IIf([date]= (SELECT Max(Date) FROM T_BOM WHERE Date<Date()) ,[adalnm],Null)) AS Yesterday
FROM T_BOM
GROUP BY BOM, EMAILS;

 

[John Spencer]

If you want faster, you might try moving the subquery into the where clause
and grouping on the date. This may make no difference as the query optimizer
may build the same query plan in both instances.

SELECT BOM, Emails, [Date], Count(adalnm) as TheCount
FROM T_BOM
WHERE T_BOM.[Date] = Date()
OR T_BOM.[Date] =
(SELECT Max(T.[Date])
FROM T_BOM as T
WHERE T.[Date] < Date())
GROUP BY BOM, Emails, [Date]


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

DMax() could give you the max date value prior to today:

SELECT BOM, EMAILS, Count(IIf([date]=Date(),[adalnm],Null)) AS Today,
Count(IIf([date] = DMax("[date]", "T_BOM", "[date] < Date()"),
[adalnm], Null)) AS Yesterday
FROM T_BOM
GROUP BY BOM, EMAILS;

A subquery might be more efficient:
http://allenbrowne.com/subquery-01.html#AnotherRecord

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi.
I'm using a query:
SELECT BOM, EMAILS, Count(IIf([date]=Date(),[adalnm],Null)) AS Today,
Count(IIf([date]=Date()-1,[adalnm],Null)) AS Yesterday
FROM T_BOM
GROUP BY BOM, EMAILS;
For counting all rows for today and yesterday. Is it possible to
replace the yesterday formula by PreviousDay formula?
Purpose: In my table there's data for all workdays, w/o weekends. So
on Monday the yesterday shows me 0. Instead it I'd like to see last
date which is filled with some data (picking Friday in case of Monday
is not the solution because of holidays)
Please advice,
Best regards,
Kamil

Thanks!
You're right, subquery works a lot faster:

SELECT BOM, EMAILS, Count(IIf([date]=Date(),[adalnm],Null)) AS Today,
Count(IIf([date]= (SELECT Max(Date) FROM T_BOM WHERE Date<Date()) ,[adalnm],Null)) AS Yesterday
FROM T_BOM
GROUP BY BOM, EMAILS;