Date Query Help

[michaela18]

I want to be able to run a query for an entire month but the date is really confusing as to how I would write this query.

Originally I wanted to write it as:

select maildate,
       COUNT (CASE WHEN CodeList LIKE '%23412%'
		    OR	 CodeList LIKE '%12341%'
		    THEN 1
                    END) AS 'A',
       COUNT (CASE WHEN CodeList LIKE '%1234112%'
		    OR	 CodeList LIKE '%46563%'
		    THEN 1
                    END) AS 'b'
from history 
where maildate between '20100201' and '20100228'

This code only works for days from tuesday - friday because the data is 1 day ahead, so tuesday is actually monday's data and friday is thursday's data. So is the maildate is on a monday, it would include the data for the friday and saturday before.

So in order to put in the figures for friday and saturday, I need to add in an extra line like this;

from history
where maildate = '20100222' --a monday
and reDate <= '20100219 - friday

from history
where maildate = '20100222' --a monday
and reDate >= '20100220 - Saturday


So for a full week, I would need to run 3 different queries. Sunday is nothing.

Any Ideas

 

[SQLBill]

How about:

WHERE maildate >= '20100202'
AND maildate <= '20100301'

Feb02 was a Tuesday, so that is really Feb 01's data and Mar 01 was a Monday, so that is really Feb 26/27's data.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875