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.
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 11:00.
Reason: Funny code window thing again....
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;