sql function to return list of days and occurences

Trying to write a user defined table function that I can pass in a date range and get returned a list of days of the week along with number of occurrences

For example if I pass in 03/01/2015 as start and 04/11/2015 as end

I would like to see

WeekDay                 occurences

Sunday                     6
Monday                  6
Tuesday                     6
Wednesday               6
Thursday                   6
Friday                         6
Saturday                     6
johnnyg123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Eyeballeth thy article called Build your own SQL calendar table to perform complex date expressions.
Once you copy and execute the script at the bottom of the article, it would be as easy as...
Declare @start_dt date = '2015-03-01', @end_dt = '2015-04-11' 
SELECT day_name_long as weekday, COUNT(day_name_long) as occurences
FROM days
WHERE PKDate >= @start_dt AND PKDate < @end_dt
GROUP BY day_name_long

Open in new window

Knock yourself out..
Jim
0
Jeff DarlingDeveloper AnalystCommented:
CREATE FUNCTION [dbo].[fn_ListDatesDay] (
	@cStartDate CHAR(10)
	,@cEndDate CHAR(10)
	)
RETURNS @tableOfDates TABLE (
	strDayName VARCHAR(15)
	,iCnt INT
	)
AS
BEGIN
	DECLARE @DayCounts TABLE (DayName VARCHAR(15))
	DECLARE @StartDate DATETIME

	SET @StartDate = @cStartDate

	DECLARE @EndDate DATETIME

	SET @EndDate = @cEndDate

	DECLARE @CurrentDate DATETIME

	SET @CurrentDate = @startDate

	WHILE @CurrentDate <= @endDate
	BEGIN
		INSERT INTO @DayCounts (DayName)
		VALUES (DATENAME(dw, @CurrentDate))

		SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
	END

	INSERT INTO @tableOfDates
	SELECT DayName
		,count(DayName)
	FROM @DayCounts
	GROUP BY DayName

	RETURN
END

Open in new window

0
jogosCommented:
without loops or filling table

;

WITH calender_CTE (date)
AS
(
Select cast('2015-03-11'as datetime) as date
UNION ALL
Select date + 1 as date from calender_CTE where date +1 < '2015-03-23'
)
select case when dayofweek = 7 then 'sunday'
 when dayofweek = 1 then 'monday'
else 'other' end
,dayofweek
,count(*) as occurences
FROM
(select 
 datepart(weekday,date) AS DAYOFWEEK
  from calender_CTE ) as source_table
 group by dayofweek
;

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

jogosCommented:
And in a function that is
CREATE FUNCTION GetWeekdayCount(@dtFrom date, @dtTo as date )
RETURNS TABLE
AS
RETURN
(WITH calender_CTE (date)
AS
(
Select cast(@dtFrom as datetime) as date
UNION ALL
Select date + 1 as date from calender_CTE where date +1 <@dtTo
)
select case when dayofweek = 7 then 'sunday'
 when dayofweek = 1 then 'monday'
else 'other' end
,dayofweek
,count(*) as occurences
FROM
(select 
 datepart(weekday,date) AS DAYOFWEEK
  from calender_CTE ) as source_table
 group by dayofweek
)

Open in new window

0
Scott PletcherSenior DBACommented:
You don't need any external data or tables to compute those totals.

SELECT * FROM dbo.GetDaysCount ( '03/01/2015', '04/11/2015' )
SELECT * FROM dbo.GetDaysCount ( '02/28/2015', '04/11/2015' )
SELECT * FROM dbo.GetDaysCount ( '03/02/2015', '04/11/2015' )

CREATE FUNCTION dbo.GetDaysCount (
    @start_date date,
    @end_date date
    )
RETURNS TABLE
AS
RETURN (
    SELECT
        WeekDay,
        total_days / 7 + --one day for every full week
            CASE WHEN leftover_days = 0 THEN 0
                      WHEN day_code BETWEEN starting_day AND (starting_day + leftover_days) % 8 THEN 1
                      WHEN day_code <= (starting_day + leftover_days) % 7 THEN 1
                      ELSE 0 END AS Occurences
    FROM (
        SELECT 'Sunday' AS WeekDay, 1 AS day_code UNION ALL
        SELECT 'Monday', 2 UNION ALL
        SELECT 'Tuesday', 3 UNION ALL
        SELECT 'Wednesday', 4 UNION ALL
        SELECT 'Thursday', 5 UNION ALL
        SELECT 'Friday', 6 UNION ALL
        SELECT 'Saturday', 7
    ) AS days
    CROSS APPLY (
        SELECT
            DATEDIFF(DAY, 6, @start_date) % 7 + 1 AS starting_day,
            DATEDIFF(DAY, @start_date, @end_date) + 1 AS total_days,
            (DATEDIFF(DAY, @start_date, @end_date) + 1) % 7 AS leftover_days
    ) AS assign_names_to_calcs
)
GO
0
johnnyg123Author Commented:
hey Scott

It did return the correct days when

Question for you

When I run 03/01/2015 as start and 04/11/2015

But when I run

 SELECT * FROM dbo.GetDaysCount ( '03/22/2015', '03/30/2015' )

I get

Sunday      2
Monday      2
Tuesday      2
Wednesday      1
Thursday      1
Friday      1
Saturday      1

There is only one Tuesday in that period

any ideas?
0
johnnyg123Author Commented:
jogos

I modified your function slightly to the following

ALTER FUNCTION [dbo].[GetWeekdayCount](@dtFrom date, @dtTo as date )
RETURNS TABLE
AS
RETURN
(WITH calender_CTE (date)
AS
(
Select cast(@dtFrom as datetime) as date
UNION ALL
Select date + 1 as date from calender_CTE where date +1 <@dtTo
)
select case when dayofweek = 1 then 'Sunday'
 when dayofweek = 2 then 'Monday'
 when dayofweek = 3 then 'Tuesday'
 when dayofweek = 4 then 'Wednesday'
 when dayofweek = 5 then 'Thursday'
 when dayofweek = 6 then 'Friday'
 when dayofweek = 7 then 'Saturday'
else 'other' end dayofweek
,count(*) as occurences
FROM
(select
 datepart(weekday,date) AS DAYOFWEEK
  from calender_CTE ) as source_table
 group by dayofweek
)

 SELECT * FROM dbo.GetweekdayCount ( '03/22/2015', '03/30/2015' )

Returns

         
Sunday      2
Monday      1
Tuesday      1
Wednesday      1
Thursday      1
Friday      1
Saturday      1
0
Jeff DarlingDeveloper AnalystCommented:
should be 2 Mondays and 2 Sundays in the date range  ( '03/22/2015', '03/30/2015' ) ?
0
Scott PletcherSenior DBACommented:
Sorry, I've corrected the function below.  I'm used to starting with day "0", and I forgot to adjust the code when I started with the first day as "1".

ALTER FUNCTION dbo.GetDaysCount (
     @start_date date,
     @end_date date
     )
 RETURNS TABLE
 AS
 RETURN (
     SELECT
         WeekDay,
         total_days / 7 + --one day for every full week
             CASE WHEN leftover_days = 0 THEN 0
                       WHEN day_code BETWEEN starting_day AND (starting_day + leftover_days - 1) % 8 THEN 1
                       WHEN day_code <= (starting_day + leftover_days - 1) % 7 THEN 1
                       ELSE 0 END AS Occurences
     FROM (
         SELECT 'Sunday' AS WeekDay, 1 AS day_code UNION ALL
         SELECT 'Monday', 2 UNION ALL
         SELECT 'Tuesday', 3 UNION ALL
         SELECT 'Wednesday', 4 UNION ALL
         SELECT 'Thursday', 5 UNION ALL
         SELECT 'Friday', 6 UNION ALL
         SELECT 'Saturday', 7
     ) AS days
     CROSS APPLY (
         SELECT
             DATEDIFF(DAY, 6, @start_date) % 7 + 1 AS starting_day,
             DATEDIFF(DAY, @start_date, @end_date) + 1 AS total_days,
             (DATEDIFF(DAY, @start_date, @end_date) + 1) % 7 AS leftover_days
     ) AS assign_names_to_calcs
 )
 GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnnyg123Author Commented:
Perfect!   Thanks Scott!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.