dear gurus,
I want to get the working days between two days..
in a single query.
i will give the start_date '06-02-2008',end_date '06-13-2008' the result should be as below.
06-02-2008Monday
06-03-2008Tuesday
06-04-2008Wednesday
06-05-2008Thursday
06-06-2008Friday
06-09-2008Monday
06-10-2008Tuesday
06-11-2008Wednesday
06-12-2008Thursday
06-13-2008Friday
Thanks in advance.
cool...,
I Have a date range and i want to calculate the number of days - the weekends(saturdays and sundays) so that only businessdays are obtained.
A simple table's example:
Name, surname, datebeg, dateend
How can can it be done with sql?
BB
Is there any easy way to calulate number of business days between the 2 dates ?
I want to exclude all sat and sun between the 2 dates.
I'm looking for a DATEDIFF function which will give me the
BUSINESS days difference between 2 dates.
e.g: datediff(day,'08/08/2007','08/14/2007) would normally result in 6 (i think), however in business days this would be 4.
is there such a function?
Thx
I need a function to count business days (exclude Sat, Sun) that I can call within a view. I would rather not build a "calendar table" this will be used ongoing for years into the future.
Does anyone have anything like this they could share? If there is another source you could direct me to I would appreciate that as well.
TIA
Phil
I have stumbled upon a sql question I cannot answer. I am looking for the following SELECT sql statement:
Basically I need a way to get "5 days ago from today". BUT, the trick is that there is a table called tblnoworkday with contains weekends and holidays and those dates cannot count. So basically what I am really trying to get is "5 Business days ago from today".
So it would basically do this for an query input date of '4/9/08'. If the table is called TblNoWorkday and contains the following records:
...
2008-04-06 00:00:00.000
2008-04-05 00:00:00.000
...
This is the last 5 business days then: (not in the table)
4/9/08
4/8/08
4/7/08
****weekend****
4/4/08
4/3/08
The query should return just 4/3/08. The problem is 4/3/08 doesn't exist in the database since the database only contains the no work days.
Any ideas on how to do this in a simple query without having to create another table with the valid working dates?
Thanks Before Hand,
Adiel
Does anyone know of a way to calculate the date 'x' number of business days after another date?
View 2 Replies View RelatedHi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas
Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas
I am trying to get a cumulative count of business days. I have a column in my date dimension BusinessDayInd which is 'Y' or 'N'. I have been trying to create a calculated column in the cube but have not been successful yet. I think I'm close with this:
COUNTROWS(FILTER(DATESMTD(PostingDate[Posting Date]), PostingDate[BusinessDayInd] = "Y"))
However, this is giving me a count of calendar days for all business days and null for non-business days. How do I apply the filter to DATESMTD? I've tried many iterations of this using CALCULATETABLE, CALCULATE, and FILTER. All are giving me the same result.
What am I doing wrong?
select DateAdd(dd,-90,getdate())
It gives the 90 days older date but i want to exclude the weekdays and holidays (saturday ,sunday,holiday)
As we dont have permission to call a function or Sp in high level environments looking for a simple query .
I have a date that I need to add 'n' number of business days to. I have a calendar table that has a 'IsBusinessDay' flag, so it would be good if I was able to use this to get what I need. I've tried to use the 'LEAD' function in the following way;
SELECT A. Date, B.DatePlus3BusinessDays
FROM TableA A
LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey
Problem with this is that because I am filtering the Calendar for business days only, when there is a date that is not a business day in TableA, a NULL is being returned.
Is there any way to do a conditional LEAD, so it skips rows that are not business days? Or do I have do go with a completely different approach?
I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot.I do the same in SQl using the following query
DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) -
CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END +
CASE WHEN DATEPART(dw, GETDATE()) = 1 THEN 1 ELSE 0 END END
I am looking for a similar query in Power Pivot.
I have a calendar table against entire year 2015 with each day with 2 flag,
1. WK_DT_IN == except Satarday and Sunday, value is "Y", for Sat/Sun, value is "N"
2. HOL_DT_IN == value will only be "Y" only for holiday, example for '2015-01-01' date, it's value is "Y"
DECLARE @CAL TABLE (CAL_DT DATE, WK_DT_IN CHAR(1), HOL_DT_IN CHAR(1))
INSERT INTO @CAL VALUES ('2015-01-01', 'Y', 'Y'), ('2015-01-02', 'Y', 'N'),('2015-01-03', 'N', 'N'),
('2015-01-04', 'N', 'N'), ('2015-01-05', 'Y', 'N'), ('2015-01-06', 'Y', 'N'), ('2015-01-07', 'Y', 'N')
We have a given date, example '2015-01-01', I need to find out a date after 2 business days? I can think of loop, but will it work.
I need to exclude date which having HOL_DT_IN = "Y" and WK_DT_IN = "N".
I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.
Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.
------ Messages after I click execute on my query window that has my function ------------------------------------------------------
Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19
Select statements included within a function cannot return data to a client.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20
Invalid column name 'WorkDay'.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22
Invalid column name 'Date'.
------ my function code ----------------------------
CREATE FUNCTION [dbo].[FGetWorkDate](
@WorkDays VARCHAR(5),
@DateFromValue AS DateTime )
RETURNS DATETIME
[Code] ....
I want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) col2 (In Hours : Mins)
3days 4:5
In this first have to add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
Does anyone have a successful prescribed sequence for installing VS2005 and Business Intelligence Reports Projects on a Vista Business workstation to be used to create reports for a server?
I've looked through everything I can find here and I don't seem to see a clear solution without a lot of trial and error.
Fact is, I've not been successful getting just the reports to install on a plain XP box. Of course, the report creation looks fine on the server but I don't want to work directly on the server.
Thank you
Can anyone take me through synchronization of contacts within Business Contacts Outlook into Microsoft Small Business Accounts?
I run a stand alone PC with NO network. When SBA came SQL was also installed. Apparently you can synchronise Contacts within Business Contacts with SBA but both SBA & Outlook should work through the same SQL server.
Has anyone tried this?
Can someone walk me through the process?
Thanks
Debbie
I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:
ce.enquiry_time represents when the enquiry was logged
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))
It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.
Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly). -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out? Thanks so much in advance.
I had to do a re-install of SQL 2005 Standand Edition. I
now do not have the BI, I do not know if it came with it or not.
What would I need to install ifit did? I also wanted to
ask: I know that MS Office Excel has the capabilites to sames as
an XML file, but it is not very readable to import into the SQLdatabase. Is there a way to import the XML file into the database via one of the express editions? Thank youDee
I want to understand what is Business intelligence and what is functions of it
View 1 Replies View RelatedHi,
I am wondering what is vb.net method of calculating the prior business day (Monday - Friday).
For example, if today is Monday, Aug. 20, the last business day would have been Friday, Aug. 17.
I have a way to do this in SQL, but not vb.net.
Thanks much
Ok, probibly an easy question but I haven't been able to find what I am looking for.
If I have a simple class (see below), how would I make the People List avalible to build a report off of?
Thanks
asdf46
Code Snippetpublic class Person
{
public string FirstName { get; set;}
public string LastName { get; set;}
}
// and put them into a list
List <Person> People = new List<Person>();
People.Add(new Person () { FirstName = "Joe", LastName = "User"});
People.Add(new Person () { FirstName = "Jane", LastName = "Smith"});
Hi all,
I've created a store dimension. Within the store dimension is a CityID column that is related to a City dimension. Currently this city dimension has the following columns:
CityID (primary key --- foreign key for the StoreDim)
CityName
CountyName
StateName
CountryName
...
My SCD's source is being loaded from a query to a Zip table that contains ZipCodes along with cityID, CountyID, StateID, and CountryID that can be joined out to their respective tables to find the information needed to populate my SCD. However, cities can have several number of zipcodes and several cities in different states can have the same names. This causes a problem with eliminating duplicates for my dimension as well as finding a suitable business key.
For example, let's take a case: Washington city, District of Columbia (county), District of Columbia (state), USA (Country)
Now, this city has SEVERAL zip codes so when I create my joins:
SELECT ...
FROM Zip Z
INNER JOIN City C
ON C.CityID = Z.CityID
INNER JOIN County Cty
ON Cty.CountyID = Z.CountyID
So this returns several CityIDs for the same city... which when I try to use for my SCD causes problems because i have several business keys based off my CityIDs so my dimension loaded would look like
CityID CityDesc CountyDesc StateDesc CountryDesc
1 Washington District of Columbia District of Columbia USA
2 Washington District of Columbia District of Columbia USA
3 Washington District of Columbia District of Columbia USA
4 Washington District of Columbia District of Columbia USA
I want to have just ONE record in my dimension for this city.... How would I do this and what would I use for a business key? Also any recommendations on which of these attributes should be historical attributes? I would appreciate any input you guys have.
The Business Intelligence isn't showing up in the menu with ( Visual Basic, Visual C##, etc). A teckie installed the pro version and said she installed everything, can some one point me in the right direction. We purchase the media and installed from that.
Or maybe it can't find something it's looking for to make my additional symetrical with everything else. This is from one of my businesslogic files. What causes Visual Studio to not allow me to add something here and have it behave like the other parameters/ I'm talking about ShortDesc. Visual studio inserted () and the Get instead of GET like all the others. It would not allow me to capitalize the "get". There has to be a reason for this. Currently my program is adding all of the fields except the ShortDesc to the table in my database. Does anyone have any thoughts as to why this is the case?
#Region "Pvt Members" Private _ArticleID As System.Int32 Private _UserID As System.Int32 Private _WebSiteID As System.Int32 Private _ArticleTitle As System.String Private _Author As System.String Private _ShortDesc As System.String Private _ArticleText As System.String Private _AnchorText As System.String Private _ShowInDirectory As System.Boolean Private _Active As System.Boolean Private _DateAdded As System.DateTime#End Region#Region "Properties" Public Property ArticleID As System.Int32 GET Return _ArticleID End Get Set(ByVal Value As System.Int32) _ArticleID= Value End Set End Property Public Property UserID As System.Int32 GET Return _UserID End Get Set(ByVal Value As System.Int32) _UserID= Value End Set End Property Public Property WebSiteID As System.Int32 GET Return _WebSiteID End Get Set(ByVal Value As System.Int32) _WebSiteID= Value End Set End Property Public Property ArticleTitle As System.String GET Return _ArticleTitle End Get Set(ByVal Value As System.String) _ArticleTitle= Value End Set End Property Public Property Author As System.String GET Return _Author End Get Set(ByVal Value As System.String) _Author= Value End Set End Property Public Property ShortDesc() As System.String Get Return _ShortDesc End Get Set(ByVal value As System.String) End Set End Property Public Property ArticleText As System.String GET Return _ArticleText End Get Set(ByVal Value As System.String) _ArticleText= Value End Set End Property Public Property AnchorText As System.String GET Return _AnchorText End Get Set(ByVal Value As System.String) _AnchorText= Value End Set End Property Public Property ShowInDirectory As System.Boolean GET Return _ShowInDirectory End Get Set(ByVal Value As System.Boolean) _ShowInDirectory= Value End Set End Property Public Property Active As System.Boolean GET Return _Active End Get Set(ByVal Value As System.Boolean) _Active= Value End Set End Property Public Property DateAdded As System.DateTime GET Return _DateAdded End Get Set(ByVal Value As System.DateTime) _DateAdded= Value End Set End Property #End Region
I am trying to calculate business hours that an order that is open. The rules are 8am-5pm. For instance the first row, the clock would stop at 17:00, and pick up again at 8am and add on to total business hours. If the order was created after business hours, and the endstamp was before business hours, this would be 0. If created after 5pm friday, and the endstamp was before 8am monday, this would be 0. In the second set of timestamps I have here the order was recieved at 14:39 but took until the following day at 14:49 to be ordered. I imagine I have to use datepart and datediff for this, but other than that I am not sure on how to do it. Any help would really be appreciated!
StartStampEndstamp
6/27/2005 14:356/27/2005 17:41:11
6/27/2005 14:396/28/2005 14:49
6/27/2005 18:486/27/2005 18:54
6/27/2005 11:416/27/2005 11:45
Interesting news article
http://www.theserverside.com/discussions/thread.tss?thread_id=48599
I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am. Call Center Hours are 8-5.
Essentially I have a number of different alternatives, and have found some potential solutions, including:
www.dbforums.com/arch/7/2003/9/914261
However, my situation has a couple of additional twists to the standard 8hrs of business M-F. The call center is open different hours depending upon the day of the week. For example, 8-5 M, 10-7 T, 8-5 W Th F, 10-2 Sat, 10-12 Sun
Additionally, I would like to remove Holiday's from the calculation for service level as well.
I have explored a number of different table DTD's, but none seem to be a perfect fit for determining the number of "open" hours between when an interaction arrived, and when it was handled.
The DTD I have for the Holiday table is as follows:
CREATE Table Holidays (HolidayDate DateTime)
GO
Insert Into Holidays (HolidayDate) Values ('12-25-2004')
Please let me know what you feel would be the DTD for storing the business hours and also the query for extracting the number of Open hours between two dates
Thank you in advance
Sorry, didn't quite know where this should go.
I'm reading Kimball Group's "Microsoft Data Warehouse Toolkit"
Has anyone got any experience in defining business processes? I'm struggling a little to look at my employer's recruitment busines and work out exactly what the real processes are.
Or maybe one of you has read something somewhere about this specific skill?
------------------------
Me: What do you want to know from your data warehouse?
Client: Err...Emm...Everything
Me: OK, that's great. That's all I need to know. I'll see you when it's done.
SQL BI Dev Studio is installed our SQL 2005 Server, however, it also needs to be on the person who is developing reports. I've already given her access to the sql db she will be working with. Can I install just the BIDS be installed on her desktop from the SQL CD?
View 1 Replies View Relatedhi Everyone,
I have one query, i want to calculate the business working hours (like from monday to friday 9 to 5 pm and saturday 9 to 2 pm) for the given dates. And also i want to eliminate the public holidays with in the given dates.
If any one was faced the same situation plz help me.
Regards
Venki