I would like to build a temp table that has each day and how many times it occurs. The reason for this is that im going to group all transactions that fall on a Monday and want to get an average.
I can group the days and the transactions but need the # of times a day occurs to work out the average.
I have been provided with the below SQL to determine an individual day but can work out how to build the table to include all days in the week.
DECLARE @start_date datetime
DECLARE @end_date datetime
SET @start_date = '20150602'
SET @end_date = DATEADD(DAY, 19, @start_date)
SELECT @start_date AS start_date, @end_date AS end_date, DATEDIFF(DAY, @start_date, @end_date) + 1 AS Total_Days,
(DATEDIFF(DAY, @start_date, @end_date) + 1) / 7 /* one Monday for every full week */ +
CASE WHEN DATEDIFF(DAY, 1, @start_date) % 7 + (DATEDIFF(DAY, @start_date, @end_date) + 1) % 7 >= 7 THEN 1 ELSE 0 END
I'm looking for the data in the temp table to look something like this:
Day # Occurances
Any help is greatly appreciated.