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

Transact SQL :: Calendar Table - Get The Date Post 3 Business Days

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

SQL Server 2012 :: Add Business Days To A Date Using Calendar Table

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

How To Add 35 Days To Every Date Field In A Table?

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

Transact SQL :: Create Custom Work Day Calendar

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

Calculating Date Based On Business Days

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

T-SQL (SS2K8) :: Query To Find Older Date That Is Less Than 90 Business Days

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

Transact SQL :: Date Value Between One Of The Calendar Periods In Multiple Rows?

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

Date Range Only Including Work Days -HELP!!

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

Transact SQL :: Return Date Which Is 15 Working Days Prior To Given Future Date

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

Transact SQL :: To Display Days Hours Mins Format Based On Business Hours

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

Transact SQL :: Joining A Calendar Table And Employee Table?

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

Transact SQL :: Calculating Date Difference In Days

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

Transact SQL :: Number Of Days Between A Date And Its Month End

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

Transact SQL :: Date Difference In The Form Of Days And Hours?

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

Transact SQL :: Date Calculation - 7 Days Prior To Specified Day Of Last Week

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

T-SQL (SS2K8) :: Today - Subtract 30 Days From Date Field

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

Transact SQL :: How To Get Date Difference Between 2 Dates In DAYS Excluding Weekends

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

Reporting Services :: Calculated Field To Capture +/- 28 Days From Date

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

Transact SQL :: Load Data For Last 15 Days By Decrementing Current Date And While Loop

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

Transact SQL :: Find Latest Start Date After A Gap In Date Field For Each ID

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

Business Days

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

Business Days

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

Number Of Business Days

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

DATEDIFF For Business Days Only

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

Need Function To Count Business Days

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

SELECT To Get The Last 5 Business Days- Tricky

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

Use A Date To Select Out Previous 14 Days Record From The Table

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

Finding Number Of Business Days Between Two Dates

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

Finding Number Of Business Days Between Two Dates

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

Analysis :: How To Get Cumulative Count Of Business Days

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

Transact SQL :: How To Extract Date Value From XML Field Value

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

T-SQL (SS2K8) :: Calculate 90 Days And 3 Years Ago From Effective Date In A Table?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved