Exclude partial month data from Script Load?

[#1]

I have data I'm importing from a QVD file that has multiple rows of data for each day, for example:

LOAD

Date,

Month,

Year,

Amount

FROM

data.qvd

(qvd);

This data has activity every day, so every date has data in it.

I'm trying to put in a WHERE clause to exclude the most recent month's worth of data but only if it's a partial month.  That way I can load the data from my QVD with only full months' worth of data.

I was trying to use MonthEnd function, but cannot seem to get it right.

Thanks all.

[#2]

where ( monthend(date_field)=today() and date_field <=today() ) or ( date_field <=date(floor(monthend(addmonths(Today(),-1))), 'YYYYMMDD'))

not tested sorry.

[#3]

This did not work, but perhaps that is because you are using Today function and there is a lag in when I run this... in other word.... today might be 12/15/14, but the most recent activity date might be 12/6/2014... and I'm trying to exclude the partial month of December in this case..... so would I not use the Today function and use max(date) instead?

[#4 · Accepted solution]

I think I got it.  I replaced Today() in your code with a constant MaxDate value I created in a previous table, and it worked!    Thanks!

where ( monthend(Date)=floor(peek('MaxDate',0,'MaxDateTable'))

and

Date <=floor(peek('MaxDate',0,'MaxDateTable')))

or

( Date <=date(floor(monthend(addmonths(floor(peek('MaxDate',0,'MaxDateTable')),-1))), 'YYYYMMDD'))

;