Transact SQL :: Calc Work Date Using Business Days Field In Calendar Table
May 19, 2014
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] ....
View 10 Replies
ADVERTISEMENT
Sep 10, 2015
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".
View 3 Replies
View Related
May 12, 2015
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?
View 9 Replies
View Related
Nov 29, 2004
Hello!
Your advice might helps me a lot! I'm looking for the answer for days.
I would like to add 35 days to every date field in a table. I The table structure, and content is dynamically changing.
Is it possible?
Thanks,
norbi
View 1 Replies
View Related
Nov 17, 2015
I am wanting to create a custom workday calendar to show Monday - Friday as a workday, then go back and update a few holidays as non-workdays. This is the syntax I have to start with, but it is presenting with an error:
Msg 241, Level 16, State 1, Line 21 Conversion failed when converting date and/or time from character string.
What should I alter?
CREATE TABLE dbo.CreateCustomCalendar
(
wk_Date INT IDENTITY NOT NULL,
FullDate DATETIME NOT NULL,
WeekDayName VARCHAR(9) NOT NULL,
IsWorkday varchar(20) NOT NULL
)
[Code]....
View 7 Replies
View Related
Jul 21, 2001
Does anyone know of a way to calculate the date 'x' number of business days after another date?
View 2 Replies
View Related
Feb 12, 2015
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 .
View 1 Replies
View Related
Aug 20, 2015
Question: How to determine if a date value was between one of the date periods that appear in multiple rows?
Background: We have a table of "license valid" periods, wherein each license can have one or more rows. (As you know, a driver's license can be started, expired, renewed, suspended, reinstated, revoked, etc.) Instead of of having a license activity table--from which valid license periods could be extrapolated--we store just the periods for which a license was valid.
My task is to take a list of licenses and specific dates and determine if each license was valid as of that date, returning either true or false. What is the best way to accomplish this?
DECLARE @ValidityInQuestion TABLE (
LicenseID int
, DateValidityInQuestion date);
DECLARE @LicenseValidPeriods TABLE (
LicenseID int
, BeginDate date
, EndDate date);
[Code] ...
How then do I query both tables in order to get the same result that results from the following query?
SELECT
12345 AS LicenseID
, '2015-01-15' AS DateValidityInQuestion
, 1 AS LicenseActive
UNION
SELECT
67890
, '2015-02-04'
, 0;
I assume I need to join on the LicenseID columns, but my understanding stops there.
View 4 Replies
View Related
May 23, 2001
I've been fighting with this for a few days now, hopefully it's a simple answer.
I need to take a daterange between two dates and output the number of work days in that range.
I wrote this but it doesn't work. I know how to write a query that tells me if a date is a word day or not but not to actually determine a date range.
SELECT in_date,out_date,DATEDIFF((weekday, in_date, out_date) in (1,2,3,4,5)) AS no_of_days
from <table>
where ikey = 'whatever'
I am in desperate need of help as my deadline is coming up soon.
View 7 Replies
View Related
Oct 28, 2015
i have written a sql function which returns only number of working days (excludes holidays and Weekends) between given StartDate and EndDate.
USE [XXX]
GO
/****** Object: UserDefinedFunction [dbo].[CalculateNumberOFWorkDays] Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO
[code]...
I need a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date. Example scenario: If i give date as 12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table tblMasHolidayList where all the 2015 year holidays dates and info are stored.
View 18 Replies
View Related
Apr 22, 2015
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.
View 6 Replies
View Related
Apr 20, 2015
I run into a problem when asking to show a query of employee vacation days.
table 1:
column1 is dates
e.g.
2015-01-01
2015-01-02
2015-01-03
.
.
.
2015-12-31
table2:
employeeID
vacation_date
Tom
2015-01-03
Tom
2015-01-04
David
2015-01-04
John
2015-01-08
Mary
2015-01-012
My query output need to be:
2015-01-01
2015-01-02
2015-1-03
Tom
2015-01-04
Tom
2015-01-04
David
2015-01-05
2015-01-06
2015-01-07
2015-01-08
John
2015-01-09
2015-01-10
2015-01-11
2015-11-12
Mary
... etc... all the way to 2015-12-31
when i use left outer join, i only record one employee per date.
View 4 Replies
View Related
Apr 16, 2015
I'm trying to calculate the time difference between a date field and today's date in days. The date field is not mandatory and can therefore be blank. I'm trying to execute the following query:
SELECT employee_code, Civil_ID, DATEDIFF(Day, Civil_ID, GETDATE())
FROM ODEV_VIEW_Credentials_Expiry_Dates
WHERE Civil_ID IS NOT NULL AND Civil_ID != ''
ORDER BY employee_code
I keep getting the following message:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Warning: Null value is eliminated by an aggregate or other SET operation.
No matter what filter I use to process non-blank dates, it never works.
View 12 Replies
View Related
Sep 23, 2015
I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.
Something like below,
DateTD Days left
2009-01-05 14
2009-01-06 13
2009-01-07 12
2009-01-08 11
2009-01-09 10
2009-01-12 9
2009-01-13 8
2009-01-14 7
2009-01-15 6
2009-01-16 5
2009-01-19 4
2009-01-20 3
2009-01-21 2
2009-01-22 1
2009-01-23 0
2009-02-02 /
2009-02-03 /
View 28 Replies
View Related
May 21, 2015
declare @siva1 datetime;
declare @siva2 datetime;
set @siva1='2014-03-10 05:02:11'
set @siva2='2014-03-12 23:52:11'
i want output like this 2.18 means 2day 18hours difference how using query
View 6 Replies
View Related
Sep 8, 2015
I need to run a select on Mondays to pull data for 7 days prior to the Thursday of last week; i.e. Friday - Thursday inclusive. I'm sure this is simple, but I work with dates so infrequently that I need a refressher.
View 7 Replies
View Related
Feb 4, 2015
We are running sql 2008 R2. We have the JE_DATE field set up as an int. We are trying to subtract 30 days from this date field.
select * from GENERAL_LEDGER
where JE_DATE >= DATEADD(DAY,-30,GETDATE())
In addition to the where clause above we have also tried
where JE_DATE >= DATEADD(dd,-30,GETDATE())
For both we get the following error "Arithmetic overflow error converting expression to data type datetime."
View 7 Replies
View Related
Oct 14, 2015
Here I have 2 Dates. CreatedDttm & ModifiedDttm.
I want - DATEDIFF(Day,CreatedDttm,ModifiedDttm) and I have to exclude the Weekend days from that query result.
View 10 Replies
View Related
Sep 18, 2015
I need to create a calculated field that will give me 'yes' or 'no' if a date field is within +/- 28 days of another date field.
So for example if a.date = 1/1/15 and b.date is 30/3/15 the calculated field will say 'no'; if the b.date was 10/1/15 it would say yes.
Similarly, if the b.date was 1/11/14 it would say 'no'; if it was 10/12/14 it would say 'yes'.
View 2 Replies
View Related
Sep 21, 2015
I have a dynamic sql query where in I am comparing two tables and loading data for last 15 days. e.g today 2050921 then I am going to load till 20150906.
I pass on 2 variables @currentdate and @currentdate-1 to the query which are in date format 'yyyymmdd'
I need to do this for last 15 days how do I do this using while loop.
Note my date format is YYYYMMDD.
DECLARE @SQL VARCHAR(MAX)
@sql = ' insert into target
select from table_1_currentdate a
LEFT JOIN Table_2_currentdate-1 b
on a.col1=b.col1 where b.col1 is null '
exec(@sql)
I have to use while loop and decrement it every time and load data for last 15 days comparing two tables. I tried so many times I am not getting it right .
View 3 Replies
View Related
Apr 23, 2015
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID StartDate
1 2014-01-01
1 2014-02-01
1 2014-05-01-------After Gap Restarted
1 2014-06-01
1 2014-09-01---------After last gap restarted
1 2014-10-01
1 2014-11-01
2 2014-01-01
2 2014-02-01
2 2014-03-01
2 2014-04-01
2 2014-05-01
2 2014-06-01
2 2014-07-01
For Id 1 the start date after the latest gap is 2014-10-01 and for id=2 there is no gap so i need the minimum date 2014-01-01
My Expected Output
id Startdate
1 2014-10-01
2 2014-01-01
View 4 Replies
View Related
Jun 20, 2008
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...,
View 5 Replies
View Related
Sep 24, 2007
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
View 3 Replies
View Related
Mar 22, 2001
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.
View 1 Replies
View Related
Aug 16, 2007
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
View 4 Replies
View Related
Apr 21, 2000
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
View 1 Replies
View Related
Apr 7, 2008
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
View 10 Replies
View Related
Dec 10, 2007
May I know how to use a "date" to select out previous 14 days record from the table? and find the duplicated records?
-- sort out duplicate order from tblOrder
Select * FROM tblOrder
WHERE DDay > @prmDDay("day", -14, getDate())
Group by DDay
Many thanks~~~~~
Fr New Learner
View 3 Replies
View Related
Nov 27, 1999
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
View 1 Replies
View Related
Nov 27, 1999
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
View 1 Replies
View Related
Jul 9, 2015
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?
View 7 Replies
View Related
Oct 5, 2015
I am having an XML field in a table "RMRECC" (field name: "Amount") and the value of the field is given below.
<X C="0" I="1">
<E D="1Y0M0W0D" P="0" A="0.05" />
<E D="1Y0M0W0D" P="0" A="0" />
<E D="1Y0M0W0D" P="0" A="0.03" />
</X>
I need to extract the date value "1Y0M0W0D" in MM/DD/YYYY format.how this can be handled in SQL query?
View 2 Replies
View Related
Sep 30, 2014
What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?
as in
SELECT EffectiveDate
from FL.CEFHistory
I need to return the effective date - 90 days and 1 year from that.
[URL]
View 6 Replies
View Related