Daily Average (Closed Topic) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Daily Average (Closed Topic)

Author

Topic

secuchalan
Starting Member

19 Posts

Posted - 2008-08-13 : 22:55:11

I am trying to get a calculation for monthly average number of tickets but I cannot seem to get the right results.

The start date will always be 8/15/2007 – the end date is a parameter field which can always change, in this example the end date is 8/1/2008.

In order to get the daily average w/in a month – I need to count the number of days within each month and then get the average from the total tickets per month.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-13 : 23:42:20

I think your calculation for DailyAvgTickets should be:
Total Tickets in the Month / Total Days in the Month

In your query, it would look something like this

--Total Tickets in the Month
convert(numeric(10,1),SUM(APP.Tickets)) /
--Total Days in the Month
day(dateadd(month,datediff(month,-1,min(APP.LogDateEnd),-1))

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-14 : 18:02:30

You can use a case statement to compute the number of days in the beginning and ending months of your date range, or return the actual number of days in the month when it isn't.

Since the query you posted is obviously not the one that would produce the expected output, and you didn't provide table structures, or sample data for us to work with, we can't really test it, so you are on you own for that.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 18:42:07

[code]SELECT DATEPART(MONTH, als.LogDateEnd),
DATENAME(YEAR, als.LogDateEnd),
SUM(als.Tickets) AS TicketsPrinted,
1.0 * SUM(als.Tickets) / COUNT(DISTINCT CONVERT(CHAR(8), als.LogDateEnd, 112)) AS DailyAvgTickets
FROM ApplicationLogSummary AS als
INNER JOIN Kiosks AS k ON k.KioskID = als.KioskID
INNER JOIN Airlines AS a ON a.AirlineID = als.AirlineID
WHERE k.KioskName LIKE '%SEASKY%'
AND k.KioskName <> 'SEASKY11'
AND als.LogDateEnd >= '8/15/2007'
AND als.LogDateEnd < DATEADD(DAY, DATEDIFF(DAY, '19000101', @EndDate), '19000102')
GROUP BY DATENAME(YEAR, als.LogDateEnd),
DATEPART(MONTH, als.LogDateEnd)
ORDER BY DATENAME(YEAR, als.LogDateEnd),
DATEPART(MONTH, als.LogDateEnd)[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 03:11:48

Do you mean to say that there are gaps in ApplicationLogSummary for each month data? If yes, you may need a seperate month table with number of days stored which you should use for getting the day count.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 04:49:02

Yes of course!
You are ALWAYS counting the days from 8/15/2007 no matter what month it is.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 04:52:34

[code]SELECT DATEPART(MONTH, als.LogDateEnd),
DATENAME(YEAR, als.LogDateEnd),
SUM(als.Tickets) AS TicketsPrinted,
1.0 * SUM(als.Tickets) / DATEDIFF(DAY, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', als.LogDateEnd), '19000101'), DATEADD(MONTH, DATEDIFF(MONTH, '18991231', als.LogDateEnd), '19000101')) AS DailyAvgTickets
FROM ApplicationLogSummary AS als
INNER JOIN Kiosks AS k ON k.KioskID = als.KioskID
INNER JOIN Airlines AS a ON a.AirlineID = als.AirlineID
WHERE k.KioskName LIKE '%SEASKY%'
AND k.KioskName <> 'SEASKY11'
AND als.LogDateEnd >= '8/15/2007'
AND als.LogDateEnd < DATEADD(DAY, DATEDIFF(DAY, '19000101', @EndDate), '19000102')
GROUP BY DATENAME(YEAR, als.LogDateEnd),
DATEPART(MONTH, als.LogDateEnd)
ORDER BY DATENAME(YEAR, als.LogDateEnd),
DATEPART(MONTH, als.LogDateEnd)[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 14:21:18

for datediff the arguments must be of datetime datatype. why are you converting second date to varchar?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 14:56:55

[code]SELECT DATEPART(MONTH, als.LogDateEnd),
DATENAME(YEAR, als.LogDateEnd),
SUM(als.Tickets) AS TicketsPrinted,
1.0 * SUM(als.Tickets) / MAX(
CASE
WHEN DATEDIFF(MONTH, '8/15/2007', als.LogDateEnd) = 0 THEN 17
WHEN DATEDIFF(MONTH, @EndDate, als.LogDateEnd) = 0 THEN DATEPART(DAY, @EndDate)
ELSE DATEDIFF(DAY, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', als.LogDateEnd), '19000101'), DATEADD(MONTH, DATEDIFF(MONTH, '18991231', als.LogDateEnd), '19000101')) AS DailyAvgTickets
END)
FROM ApplicationLogSummary AS als
INNER JOIN Kiosks AS k ON k.KioskID = als.KioskID
INNER JOIN Airlines AS a ON a.AirlineID = als.AirlineID
WHERE k.KioskName LIKE '%SEASKY%'
AND k.KioskName <> 'SEASKY11'
AND als.LogDateEnd >= '8/15/2007'
AND als.LogDateEnd < DATEADD(DAY, DATEDIFF(DAY, '19000101', @EndDate), '19000102')
GROUP BY DATENAME(YEAR, als.LogDateEnd),
DATEPART(MONTH, als.LogDateEnd)
ORDER BY DATENAME(YEAR, als.LogDateEnd),
DATEPART(MONTH, als.LogDateEnd)[/code]
The days in August 2007 should be 17, not 16.
You have >= Aug 15, which means

15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30 and 31.
17 days.

E 12°55'05.25"
N 56°04'39.16"

secuchalan
Starting Member

19 Posts

Posted - 2011-09-25 : 04:41:57

SwePeso's query worked. Thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-09-25 : 04:52:37

Why reply 3 years later and remove the query from your original post?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources