How to query last 13 months (completed month) of data

[ker0k3n0]

Hello all,

I have a report that I want to pull last 13 months of data.
The report should look at sys date (i.e. 8-10-2010), calculate the start date as the first day of last month last year (7-1-2009) and the end at the last day of last month this year (7-31-2010). How can I accomplish this in the SQL command line?

I am using Crystal Report 2008. I want to schedule this report monthly from Infoview so I need the report to calculate the date range for me automatically.

here is what I have started -

SELECT
nvl(
(select displayval from listmaster where listgroup = 'demogrph' and listtype = 'marketing' and storedval = d.mktg_code),
'Not Entered') as "Source",
d.customerid,
c.*
from demogrph d
left outer join
view_customer c
on d.customerid = c.customerid
where c.createddt >= (sysdate - 396)
and c.createddt <= (sysdate-1)

 

[lbass]

What type of database are you accessing?

-LB

 

[max10]

Your selection criteria for dates

{c.createddt} in dateadd('m',-13,currentdate) to dateadd('m',-1,currentmonth)

This will give you the last 13 months but not the current month

 

[IanWaterman]

Try

{Createdate} in dateadd("m", -13, maximum(lastfullmonth))+1 to maximum(lastfullmonth)

Ian