SQLTeam.com | Weblogs | Forums

Temp table to capture how many times a day occurs


#1

Hi guys.

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
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

Any help is greatly appreciated.

Thanks.


#2

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));

#3

Thanks! That worked perfectly.


#4

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?

Thanks.


#5

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;