I currently have a large amount of SQL queries built for the purpose of Month End reporting. A common factor in these queries is retrieving total counts in tables between the first and last days of the previous month. The field in question is BookDate, which is formatted as YYYYMMDD.
Say today is March 1st, 2011 (20110301) and I want a total count of transactions in the ActivityHist table that are cash from February 1st, 2011 (20110201) through February 28th (20110228)... (See Attached Code)
Currently, I manually change all BookDate info to reflect the previous month... How can I do this automatically?
WHERE CashTran = '1' AND
BookDate >= '20110201' AND BookDate <= '20110228'