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.
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
AS Number_Of_Mondays
I'm looking for the data in the temp table to look something like this:
Day # Occurances
..... .....................
Monday 2
Tuesday 2
Wed 2
Thursday 3
Friday 3
Sat 3
Sunday 3
I get slightly different count to you, but this should do what you're after (I only see Monday not being seen 3 times as the range starts on Tuesday and ends on a Sunday):
DECLARE @StartDate DATETIME = '20150602',
@EndDate DATETIME = '20150621',
@DTDiff INT;
SELECT @DTDiff = DATEDIFF(DAY, @StartDate, @EndDate) + 1;
WITH Nums (N)
AS
(
SELECT TOP(@DTDiff) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))- 1
FROM sys.all_columns ac
)
SELECT DofWeek = DATENAME(dw,DATEADD(DAY,N.N,@StartDate)),
DayCount = COUNT(*)
FROM Nums AS N
GROUP BY DATENAME(dw,DATEADD(DAY,N.N,@StartDate));
Whilst your script gives me the exact result i was after I am unable to insert the results into a Temp table. I assumed that this would be the easy part.
I receive the error message "An INSERT statement cannot contain a SELECT statement that assigns values to a variable."
Do you have any ideas as to how i can get around this?
How/Where are you putting the INSERT? This should work.
DECLARE @StartDate DATETIME = '20150602',
@EndDate DATETIME = '20150621',
@DTDiff INT;
CREATE TABLE #Test
(
DofWeek VARCHAR(25) NOT NULL,
DayCount INT NOT NULL
);
SELECT @DTDiff = DATEDIFF(DAY, @StartDate, @EndDate) + 1;
WITH Nums (N)
AS
(
SELECT TOP(@DTDiff) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))- 1
FROM sys.all_columns ac
)
INSERT INTO #Test (DofWeek,DayCount)
SELECT DofWeek = DATENAME(dw,DATEADD(DAY,N.N,@StartDate)),
DayCount = COUNT(*)
FROM Nums AS N
GROUP BY DATENAME(dw,DATEADD(DAY,N.N,@StartDate));
SELECT * FROM #Test;
DROP TABLE #Test;