Get Number of Weekdays (Sundays, Mondays, Tuesdays) Between Two Dates SQL

您所在的位置：网站首页 › weekdays › Get Number of Weekdays (Sundays, Mondays, Tuesdays) Between Two Dates SQL

Get Number of Weekdays (Sundays, Mondays, Tuesdays) Between Two Dates SQL

#Get Number of Weekdays (Sundays, Mondays, Tuesdays) Between Two Dates SQL| 来源: 网络整理| 查看: 265

Get number of weekdays (Sundays, Mondays, Tuesdays) between two dates SQL

Given what I think you're trying to get, this should do it:

SET DATEFIRST 1

DECLARE @start_date DATETIME, @end_date DATETIME

SET @start_date = '2011-07-11'SET @end_date = '2011-07-22'

;WITH Days_Of_The_Week AS ( SELECT 1 AS day_number, 'Monday' AS day_name UNION ALL SELECT 2 AS day_number, 'Tuesday' AS day_name UNION ALL SELECT 3 AS day_number, 'Wednesday' AS day_name UNION ALL SELECT 4 AS day_number, 'Thursday' AS day_name UNION ALL SELECT 5 AS day_number, 'Friday' AS day_name UNION ALL SELECT 6 AS day_number, 'Saturday' AS day_name UNION ALL SELECT 7 AS day_number, 'Sunday' AS day_name)SELECT day_name, 1 + DATEDIFF(wk, @start_date, @end_date) - CASE WHEN DATEPART(weekday, @start_date) > day_number THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, @end_date) < day_number THEN 1 ELSE 0 ENDFROM Days_Of_The_Week

Calculate all Sundays,Mondays...Saturdays between two days in SQL Server

I think your query delivers the correct result but could be simplified a bit.

It is however dependent on SET DATEFIRST setting.

datepart(dw,[Date]) = 1 will count the number of Mondays if SET DATEFIRST is 1.

Try this:

set datefirst 7 -- Sundayselect datepart(dw, '20111227')set datefirst 1 -- Mondayselect datepart(dw, '20111227')

Result:

-----------3

-----------2

Update:Another query that does the same.

select count(*) as Daycountfrom master..spt_values as Numberwhere Number.type = 'P' and dateadd(day, Number.number, @StartDate) = 1 AND weekday( S+0 )=5, 1, 0 ) +IF( @days >= 2 AND weekday( S+1 )=5, 1, 0 ) +IF( @days >= 3 AND weekday( S+2 )=5, 1, 0 ) +IF( @days >= 4 AND weekday( S+3 )=5, 1, 0 ) +IF( @days >= 5 AND weekday( S+4 )=5, 1, 0 ) +IF( @days >= 6 AND weekday( S+5 )=5, 1, 0 )

Done.

Working SQL Fiddle

T-SQL - Determine number of specific days between 2 dates based on sales

If I understand correctly, you can use a calendar table to count the number of days where the day of week is n and between the start and end and is a date that has ticket sales, which I guess is when the date exists in tickets and has the sum(c_items_total) > 0

WITH cal AS ( SELECT cast('2012-01-01' AS DATE) dt, datepart(weekday, '2012-01-01') dow UNION ALL SELECT dateadd(day, 1, dt), datepart(weekday, dateadd(day, 1, dt)) FROM cal WHERE dt < getdate())

SELECT COUNT(1)FROM calWHERE dow = 5 AND dt BETWEEN '2012-04-01' AND '2012-12-31' AND EXISTS ( SELECT 1 FROM tickets WHERE cast(dt_create_time AS DATE) = dt GROUP BY cast(dt_create_time AS DATE) HAVING sum(c_items_total) > 0 )OPTION (MAXRECURSION 0)

SQLFiddle

MySQL function to find the number of working days between two dates

This expression -

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

calculates the number of business days between the start date @S and the end date @E.

Assumes end date (@E) is not before start date (@S).Compatible with DATEDIFF in that the same start date and end dategives zero business days.Ignores holidays.

The string of digits is constructed as follows. Create a table ofstart days and end days, the rows must start with monday (WEEKDAY0) and the columns must start with Monday as well. Fill in thediagonal from top left to bottom right with all 0 (i.e. there are 0working days between Monday and Monday, Tuesday and Tuesday, etc.).For each day start at the diagonal (must always be 0) and fill inthe columns to the right, one day at a time. If you land on aweekend day (non business day) column, the number of business daysdoesn't change, it is carried from the left. Otherwise, the numberof business days increases by one. When you reach the end of therow loop back to the start of the same row and continue until youreach the diagonal again. Then go on to the next row.

E.g. Assuming Saturday and Sunday are not business days -

| M T W T F S S-|--------------M| 0 1 2 3 4 4 4T| 4 0 1 2 3 3 3W| 3 4 0 1 2 2 2T| 2 3 4 0 1 1 1F| 1 2 3 4 0 0 0S| 1 2 3 4 5 0 0S| 1 2 3 4 5 5 0

Then concatenate the 49 values in the table into the string.

Please let me know if you find any bugs.

-Edit improved table:

| M T W T F S S-|--------------M| 0 1 2 3 4 4 4T| 4 0 1 2 3 3 3W| 3 4 0 1 2 2 2T| 2 3 4 0 1 1 1F| 1 2 3 4 0 0 0S| 0 1 2 3 4 0 0S| 0 1 2 3 4 4 0

improved string: '0123444401233334012222340111123400001234000123440'

improved expression:

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1) Related Topics

SQL Query with Union in Doctrine Symfony

How to Store SQL Server Sort Order in a Variable

Superkey VS. Candidate Key

How to Get a Wpf Datagrid to Save Changes Back to the Database

Postgres: What Is the Query 'Select * from User' Actually Doing

How to Get Time Part from SQL Server 2005 Datetime in 'Hh:Mm Tt' Format

SQL Do Inner Join If Condition Met

SQL Server: Select Top 0

Xml Query() Works, Value() Requires Singleton Found Xdt:Untypedatomic

How to Group by and Return Sum Row in Postgres

Row with Minimum Value of a Column

Select Average from MySQL Table with Limit

SQL - Pivot Table and Group by Not Working

Retrieve Rank from SQLite Table

How to Find Values in All Caps in SQL Server

T-SQL Column Alias on Computed Column - Invalid Column Name

SQL Group by Day, Show Orders for Each Day

"Invalid Column Name" Error on SQL Statement from Openquery Results

【本文地址】

公司简介

联系我们