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
4 11195 deepuv04 227
Recognized Expert New Member
Hi,
Try the following query: -
SELECT Count(Date_Column)
-
FROM Table_Name
-
WHERE Date_Column BETWEEN '08/01/2008' AND '08/15/2008'
-
AND Datename(weekday, Date_Column) NOT IN ('Sunday','Saturday')
-
-
Thanks
I don't have any Date column.
I just want to calculate the Total No. Of Working Days in the Given Period
deepuv04 227
Recognized Expert New Member
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 -
Declare @startdate datetime
-
Declare @enddate datetime
-
-
Set @startdate = '08/01/2008'
-
Set @enddate = '08/15/2008'
-
-
SELECT Count(Date)
-
FROM ( Select dateadd(dd,number,@startdate) as Date
-
from master.dbo.spt_values
-
where master.dbo.spt_values.type='p' AND dateadd(dd,number,@startdate)<=(@enddate)
-
) AS T
-
WHERE Datename(weekday, T.Date) NOT IN ('Sunday','Saturday')
-
-
Thanks
I found this on another site:
select @weekDaysInPeri od = (
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
)
Sign in to post your reply or Sign up for a free account.
Similar topics |
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 form has Two textboxes to enter dates
From Date - Fdate
To Date - TDate
|
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 - Reporting Period id int
Reporting Period desc varchar(30)
Reporting Period Begin Date datetime
Reporting Period End Date datetime
|
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 working week definition.
Lets say I have a working week definition of Monday through Friday, 9 am to
5 pm.
Date1 = January 1st, 2005 at 8 am
|
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 track companies
that are supporting projects and the number of personnel in each
company that supports each project. I'm stuck when I want to sum the...
|
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 of each line means a day, the second stands for the month and the last for the amount of money on the account.
so the first line is Februari 9th with...
| |
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 efficient way for me to do this?
--
Thanks,
Sam
|
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 week number will equal to 52. And Last Day of First week is the first Saturday of January, as long as it falls at least four days into the month...
|
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.
|
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 business days count which is defined as count of days (optionally inclusive in the current implementation) excluding weekend days and holidays.
Let us...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |