WTD, MTD, and YTD Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a query that can total week-to-date, month-to-date, and
year-to-date stats. I think a separate query for each would be easist?
Also, prompting for each query would be preferred.

An example of each record would be:
Business Date 11/01/07
Calls Received 10
Calls Answered 8

Business Date 11/02/07
Calls Received 20
Calls Answered 17

When I use the Between () And () function, it doesn't sum the Calls Received
and Calls Answered together (MTD), but sums them for each day.

Thanks in advance!
 
Try this ---
SELECT [Enter start] AS [Begining Period], [Enter end] AS [End of Period],
Sum(IIf([Business Date] Between Date() And
DateAdd("d",-DatePart("y",Date()),Date()),[Calls Received],0)) AS [YTD Calls
Received], Sum(IIf([Business Date] Between Date() And
DateAdd("d",-DatePart("y",Date()),Date()),[Calls Answered],0)) AS [YTD Calls
Answered], Sum(IIf([Business Date] Between Date() And
DateAdd("d",-DatePart("d",Date()),Date())+1,[Calls Received],0)) AS [MTD
Calls Received], Sum(IIf([Business Date] Between Date() And
DateAdd("d",-DatePart("d",Date())+1,Date()),[Calls Answered],0)) AS [MTD
Calls Answered], Sum(IIf([Business Date] Between Date() And
DateAdd("d",-DatePart("w",Date()),Date())+1,[Calls Received],0)) AS [WTD
Calls Received], Sum(IIf([Business Date] Between Date() And
DateAdd("d",-DatePart("w",Date())+1,Date()),[Calls Answered],0)) AS [WTD
Calls Answered]
FROM Ben
WHERE (((Ben.[Business Date]) Between [Enter start] And [Enter end]))
GROUP BY [Enter start], [Enter end];

It might need some tweaking to make sure that the XTD is not one day off.
 
You cannot group by the dates. If you want to get a specific range of dates
change GROUP BY to WHERE in your query.

SELECT Sum([Calls Received]) as RecievedCount
, Sum ([Calls Answered]) as AnswerCount
FROM YourTable
WHERE [Business Date] Between () and ()



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
SELECT a.BusinessDate, LAST(a.received), LAST(a.answered),
SUM(b.received) AS wtd, SUM(c.received) AS mtd, SUM(d.received) AS ytd

FROM ((myTable As a INNER JOIN myTable AS b
ON a.businessDate >= b.businessDate
AND 0= DateDiff("ww", a.businessDate, b.businessDate)
)
INNER JOIN myTable As c
ON a.businessDate >= c.businessDate
AND 0 = DateDiff("m", a.businessDate, c.businessDate)
)
INNER JOIN myTable AS d
ON a.businessDate >= d.businessDate
AND 0 = DateDiff("yyyy", a.businessDate, d.businessDate)

GROUP BY a.businessDate




That can be very slow, though. You can add the SUM(b.answered),
SUM(c.answered) and SUM(d.answered) too. That won't add much to the running
time. I didn't include them to not obscure the query. The main point is the
FROM clause which clearly deals with date before the actual grouped date, on
the same week (aliased as b), month (aliased as c) or year (aliased as d)




Hoping it may help
Vanderghast, Access MVP
 
Forget that ugly join. Does not seem to work, and is definitively too slow.
No, instead, try:

========================
SELECT a.stamp, LAST(a.amount) As acutal, SUM(b.amount) as running, p.period
FROM (History AS a INNER JOIN History AS b ON a.Stamp>=b.Stamp) , Periods
AS p
WHERE 0=DateDiff(p.period, a.stamp, b.stamp)
GROUP BY a.stamp, p.period
ORDER BY p.period, a.stamp
========================

where History is your actual table;
stamp is your businessDate field
amount is either your number of answered calls or of your received
call
Periods is a new table, one field, period, 3 records, with
values "ww", "m" and "yyyy".



As example, with data:

History Stamp Amount
2007.01.01 1
2007.01.02 2
2007.01.03 3
2007.02.01 4
2007.02.08 5
2007.02.09 6
2007.03.03 7
2007.04.04 8
2007.04.06 9
2007.04.11 10


and

Periods period
m
ww
yyyy



I got, as result:


Query3 stamp acutal running period
2007.01.01 1 1 m
2007.01.02 2 3 m
2007.01.03 3 6 m
2007.02.01 4 4 m
2007.02.08 5 9 m
2007.02.09 6 15 m
2007.03.03 7 7 m
2007.04.04 8 8 m
2007.04.06 9 17 m
2007.04.11 10 27 m
2007.01.01 1 1 ww
2007.01.02 2 3 ww
2007.01.03 3 6 ww
2007.02.01 4 4 ww
2007.02.08 5 5 ww
2007.02.09 6 11 ww
2007.03.03 7 7 ww
2007.04.04 8 8 ww
2007.04.06 9 17 ww
2007.04.11 10 10 ww
2007.01.01 1 1 yyyy
2007.01.02 2 3 yyyy
2007.01.03 3 6 yyyy
2007.02.01 4 10 yyyy
2007.02.08 5 15 yyyy
2007.02.09 6 21 yyyy
2007.03.03 7 28 yyyy
2007.04.04 8 36 yyyy
2007.04.06 9 45 yyyy
2007.04.11 10 55 yyyy



where the last column supplies the period for which the running sum is
computed.


Vanderghast, Access MVP
 
Back
Top