473,287 Members | 1,659 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Calculate Number of working Days in a Given Period

Hello,

I have some total values, I want to calculate percenatge of these Total Values.
I want to divide the total with No. Of working Days Excluding Saturdays and Sundays in a given period.

How to calculate the Total Number of working Days in a given period . Let us say If i give the period as 08/01/2008 to 08/15/2008, I want total number of working days as 11.

Please help me
Aug 11 '08 #1
4 11183
deepuv04
227 Expert 100+
Hi,
Try the following query:

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(Date_Column)
  2. FROM   Table_Name
  3. WHERE  Date_Column BETWEEN '08/01/2008' AND '08/15/2008'
  4.        AND Datename(weekday, Date_Column) NOT IN ('Sunday','Saturday')
  5.  
  6.  

Thanks
Aug 11 '08 #2
I don't have any Date column.
I just want to calculate the Total No. Of Working Days in the Given Period
Aug 11 '08 #3
deepuv04
227 Expert 100+
I don't have any Date column.
I just want to calculate the Total No. Of Working Days in the Given Period

Hi,
Try the following one

Expand|Select|Wrap|Line Numbers
  1. Declare @startdate datetime 
  2. Declare @enddate datetime 
  3.  
  4. Set @startdate = '08/01/2008'
  5. Set @enddate = '08/15/2008'
  6.  
  7. SELECT Count(Date)
  8. FROM ( Select dateadd(dd,number,@startdate)  as Date
  9.        from master.dbo.spt_values 
  10.        where master.dbo.spt_values.type='p' AND dateadd(dd,number,@startdate)<=(@enddate)
  11.       ) AS T
  12. WHERE Datename(weekday, T.Date) NOT IN ('Sunday','Saturday')
  13.  
  14.  
Thanks
Aug 11 '08 #4
I found this on another site:

select @weekDaysInPeriod = (
DATEDIFF(DAY, @StartDate, @EndDate) + 1
+ (DATEDIFF(DAY, '17530101', @StartDate) + 1) / 7 * 2
- (DATEDIFF(DAY, '17530101', @EndDate) + 1) / 7 * 2
- CASE DATEDIFF(DAY, '17530101', @StartDate) % 7
WHEN 6 THEN 1
ELSE 0
END
- CASE DATEDIFF(DAY, '17530101', @EndDate) % 7
WHEN 5 THEN 1
ELSE 0
END
)
Apr 20 '12 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: JP SIngh | last post by:
Hi All I need to calculate the number of working days between the two dates entered on an ASP page. I am not that great a coder in ASP and was wondering if someone can help. Basically the...
3
by: CrystalDBA | last post by:
I am using SQL Server 2000. I need to query my database for all the contracts that came in during a certain time frame (user is prompted for reportingperiodid). Table - Periods Fields -...
3
by: | last post by:
Hello, I am hoping someone else has thought about a date time calculation i need to perform. I would like to be able to calculate the number of "working minutes" between 2 dates, given my...
4
by: Mayhem05 | last post by:
I'm hoping someone can guide me on solving this vexing problem I have with a database I built to track a projects. I'm using MS Access 2003 and here are the basics: the database is designed to...
2
by: foleyflint | last post by:
Hello, I'm trying to calculate the interest from a bankaccount which is displayed in a text file as followed: 9 2 345 3 3 300 etc. (there are 19 lines in total) the first number...
7
by: Sam | last post by:
Hi, I use C# in my ASP.NET projects. Here's what I need to do: I want to add x business days to a given date i.e. add 12 business days to today's date. What is the best, fastest and most...
4
by: khicon73 | last post by:
Hello All, I would like to calculate weeknumber and period from first day of the week (sunday) and last day of the week (saturday) and week number falls from 1 to 52 only. If week number >= 53 then...
14
by: Tommy Jakobsen | last post by:
Hi. Is there a method in .NET that takes "year" as an argument and returns the total number of weeks in that year? For culture da-DK (Danish). Thanks in advance. Tommy.
5
FishVal
by: FishVal | last post by:
IMHO, the following is not a how-to-do instruction to solve a particular problem but more a concept-proof stuff demonstrating possibilities of SQL. So, let us say the problem is to calculate...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.