# Thread: Rolling 12 Month Report

1. Registered User
Join Date
Oct 2004
Posts
15

## Unanswered: Rolling 12 Month Report

Thanks in advance to anyone who can assist!

I need to create a rolling twelve month report that shows a win/loss ratio of quotes to jobs. For example:

March 2004 = Sum(Quotes won between March 2003 and March 2004)
April 2004 = Sum(Quotes won between April 2003 and April 2004)

If you can get me this far, I'll figure out the rest. Is this possible to do in a query, or is code required? I've read similar posts suggesting cross-tab queries with partition functions, but that doesn't quite achieve what I'm going for here.

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Hard coded:
Code:
`SUM(Iif([QuoteSubmittedDate] Between #2004/03/31# AND #2003/03/01# And [QuoteWon] = True, 1, 0))/SUM(Iif([QuoteSubmittedDate] Between #2004/03/31# AND #2003/03/01#, 1, 0)) AS March_2004_QuotesRatio`
You could get more complex with some groupings and use of DATEADD and stuff of course too. Maybe even incorporate into a crosstab.

3. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
AirCode:

Code:
```SELECT
MonthName(Month(QuoteDate)) & "_" & CStr(Year(QuoteDate)) AS QuoteTime,
SUM(Iif([QuoteWon] = True, 1, 0))/Count([QuoteWon]) AS QuoteRatio WHERE QuoteDate >= CDATE("01/" & CSTR(MONTH(QuoteDate)) & "/" & CSTR(Year(QuoteDate) - 1))
GROUP BY MonthName(Month(QuoteDate)) & "_" & CStr(Year(QuoteDate)), Month(QuoteDate), Year(QuoteDate)
ORDER BY Month(QuoteDate), Year(QuoteDate)```
BTW - anyone know why code tags are sometimes ample size and sometimes teeny with scroll bars? Appears to happen when I write directly rathrer than paste....
Last edited by pootle flump; 01-13-06 at 12:00. Reason: Funny code window thing again....

4. Registered User
Join Date
Apr 2003
Location
Alabama, USA
Posts
154
How about something like this
Code:
```SELECT dtYYMM, Sum(lngValue) AS SumOflngValue
FROM tblData, (SELECT DISTINCT Format(dtDate,"yyyy-mm") AS dtYYMM FROM tblData) AS tblYearMonth
WHERE (DateDiff("m",DateSerial(Year([dtYYMM]),Month([dtYYMM]),1),[dtDate]) Between -11 And 0)
GROUP BY dtYYMM;```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•