I am working on a little project to deliver application that pull data from database to show average of shipment made each day of the week. I have made some progress and have now script that count all of shipment but because I count them based on varchar type of column also I need solution to calculate average for each day of the week separately.
So far I end up with something as follows:
SELECT [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]
SELECT DATENAME(dw, Shipment.Date) AS DayWeek, Shipment.ID
WHERE MONTH(Shipment.Date)= MONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0, 'OCTOBER 2016' ), 0)) AND
Shipment.Bur = 'GB'
) AS src
COUNT(ID) FOR DayWeek IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])
) AS pvt
I found the answer for my question by browsing through stackoverflow encountering on similar problem posted by one of the user.
SELECT [Day], SUM(q.Totals) AS "Weekly Shipment Amount", AVG(Totals) AS [Avg] FROM ( SELECT w = DATEDIFF(WEEK, 0, DATE), [Day] = DATENAME(Weekday, DATE), Totals = COUNT(*) FROM dbo.Shipment --INNER JOIN FCLI (NOLOCK) ON FCLI.CCLI = FEXP.CCLI WHERE (BUR = 'GB' AND Shipment.Date >= '20160601' AND Shipment.Date <= '20160630') GROUP BY DATEDIFF(WEEK, 0, Date), DATENAME(WEEKDAY, Date), DATEPART(WEEKDAY, Date) ) AS q GROUP BY [Day] ORDER BY [DaY] ASC;