How many times a day occurs within a date range

Transact-SQL
1

Hi guys.

I already have some SQL which shows me the # of transaction per hour, per day at a store in a given week. I have increased my date range to 2 weeks for this example.

Example:

Week 1 Monday - 12pm-1pm - total # transactions = 10
Week 2 Monday - 12pm-1pm - total # transactions = 12

So now that i have increased my date range the total is showing as 22.

What i would like to do is determine how many times a Monday occurs within the date range so i can then average out the number of transactions. In this example i know it is 2 but it wont always be that simple.

How do i work out how many times a Monday occurs within a certain date range?

Thanks.

Paul

2

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185092

OR

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

Gosh. Be careful. Both of those links use an rCTE (recursive CTE) that counts and that's absolutely one of the worst things that you can do for performance and resource usage in SQL Server. Please see the article at the following link for why and how they're so bad and how at least 3 other methods blow the doors off of rCTEs for this kind of task.
http://www.sqlservercentral.com/articles/T-SQL/74118/

For those folks getting ready to, please don't try to justify the existence of poor performing, resource hungry code based merely on the small number of rows it may handle. Like it states in the article, there are at least 3 other methods to do such a thing and most of them are far easier to write the code for than an rCTE that counts.

5

Thanks for your help guys. My requirements have changed a little bit. if possible can you assist with the new problem i have.

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

Thanks.