September 11, 2014 at 5:22 am
Hi,
I need to customize an existing function to also include time values. The current function only calculates the time difference between dates and does not include the time. Also, it only returns integer values but it should return decimal values rounded to 2 decimal places.
As per the sample data provided below if the @start_dt is '2014-07-05 23:30:00.000' and the @end_dt is '2014-07-09 12:29:26 ', the existing function returns a value of 2 which is wrong and the correct value should be 1.
Please find the existing function below:
ALTER FUNCTION [dbo].[nr_holidays_for_country_between_dates] (@country as varchar(50), @start_dt datetime, @end_dt datetime)
RETURNS int
AS
BEGIN
declare @lag as int
set @lag=0
set @start_dt=cast(cast(month(@start_dt) as varchar(4))+'/'+ cast(day(@start_dt) as varchar(4))+'/'+ cast(year(@start_dt) as varchar(4)) as datetime)
set @end_dt=cast(cast(month(@end_dt) as varchar(4))+'/'+ cast(day(@end_dt) as varchar(4))+'/'+ cast(year(@end_dt) as varchar(4)) as datetime)
if @start_dt<@end_dt
select @lag=-count(bc.country_cd) from mg_bank_calendar bc inner join mg_country c on bc.country_cd=c.country_cd
where bc.deleted_flg='N' and
c.country_dsc=@country and
bc.business_DT between @start_dt and @end_dt and
bc.business_type_flg='C'
if @start_dt>@end_dt
select @lag=count(bc.country_cd) from mg_bank_calendar bc inner join mg_country c on bc.country_cd=c.country_cd
where bc.deleted_flg='N' and
c.country_dsc=@country and
bc.business_DT between @end_dt and @start_dt and
bc.business_type_flg='C'
return @lag
END
Please also find the DDL and sample data for the 2 tables used in the function:
CREATE TABLE [dbo].[MG_COUNTRY](
[COUNTRY_CD] [varchar](2) NOT NULL,
[COUNTRY_DSC] [varchar](35) NOT NULL
)
insert into MG_COUNTRY
values('GB','United Kingdom')
CREATE TABLE [dbo].[MG_BANK_CALENDAR](
[COUNTRY_CD] [varchar](2) NOT NULL,
[BUSINESS_DT] [datetime] NOT NULL,
[BUSINESS_TYPE_FLG] [char](1) NOT NULL,
[DELETED_FLG] [char](1) NULL,
CONSTRAINT [PK_MG_BANK_CALENDAR] PRIMARY KEY CLUSTERED
(
[COUNTRY_CD] ASC,
[BUSINESS_DT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-05 00:00:00.000','C','N')
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-06 00:00:00.000','C','N')
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-07 00:00:00.000','O','N')
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-08 00:00:00.000','O','N')
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-09 00:00:00.000','O','N')
Could somebody please advice on this ?
Thanks.
September 11, 2014 at 7:13 am
Hi Paul
"Number of holidays for country between dates", the name of the function, gives the game away. Are there any holidays which don't last a full day? If there are, then DATETIME is appropriate. I don't think we have any part-day business holidays in the UK, in which case the parameters passed into the function should be DATE datatype (they're clumsily converted to date in the function anyway) and the dates in table MG_BANK_CALENDAR should be DATE too, not DATETIME. In which case the return value of 2 days is correct, because 5th July and 6th July are both holidays in the bank calendar table. The function might not be particularly well written but it does appear to generate the correct results according to its name.
My guess is you want to write something quite different, which when given two DATETIMES, will return the difference between them in DATETIME, for those values where a match is found in a table - including the fractional part of the start and end date. It's a little more tricky to do, which is why I'm asking. Incidentally, IIRC someone posted a function to do most of this last week under the Scripts section.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 11, 2014 at 7:55 am
ChrisM@Work (9/11/2014)
Hi Paul"Number of holidays for country between dates", the name of the function, gives the game away. Are there any holidays which don't last a full day? If there are, then DATETIME is appropriate. I don't think we have any part-day business holidays in the UK, in which case the parameters passed into the function should be DATE datatype (they're clumsily converted to date in the function anyway) and the dates in table MG_BANK_CALENDAR should be DATE too, not DATETIME. In which case the return value of 2 days is correct, because 5th July and 6th July are both holidays in the bank calendar table. The function might not be particularly well written but it does appear to generate the correct results according to its name.
My guess is you want to write something quite different, which when given two DATETIMES, will return the difference between them in DATETIME, for those values where a match is found in a table - including the fractional part of the start and end date. It's a little more tricky to do, which is why I'm asking. Incidentally, IIRC someone posted a function to do most of this last week under the Scripts section.
Hi Chris,
Thanks for your suggestion. The function works okay for week days dates but not for the dates which fall on a weekend or bank holidays. The function still needs changes in terms of calculating the lag correctly and that too in decimal format.
I work in the shipping industy and sometimes the depart date for the ship can be on the weekend. In that case it needs to take into consideration the exact time it departed which it is not doing currently. Its just taking the date part and not the time which in business terms is wrong.
For e.g. with regards to the sample data, if the depart date is '2014-07-05 23:30:00.000' which is a saturday, it just takes the date part into consideration and not the time and does that with sunday as well and calculates the lag as 2. However, in fact as per the depart time, the lag should be 1 (rounded off) starting from '2014-07-05 23:30:00.000' till '2014-08-05 23:30:00.000' (ignoring the half an hour afterwards).
I hope I been able to explain the problem clearly.
Thanks.
September 11, 2014 at 8:34 am
pwalter83 (9/11/2014)
ChrisM@Work (9/11/2014)
Hi Paul"Number of holidays for country between dates", the name of the function, gives the game away. Are there any holidays which don't last a full day? If there are, then DATETIME is appropriate. I don't think we have any part-day business holidays in the UK, in which case the parameters passed into the function should be DATE datatype (they're clumsily converted to date in the function anyway) and the dates in table MG_BANK_CALENDAR should be DATE too, not DATETIME. In which case the return value of 2 days is correct, because 5th July and 6th July are both holidays in the bank calendar table. The function might not be particularly well written but it does appear to generate the correct results according to its name.
My guess is you want to write something quite different, which when given two DATETIMES, will return the difference between them in DATETIME, for those values where a match is found in a table - including the fractional part of the start and end date. It's a little more tricky to do, which is why I'm asking. Incidentally, IIRC someone posted a function to do most of this last week under the Scripts section.
Hi Chris,
Thanks for your suggestion. The function works okay for week days dates but not for the dates which fall on a weekend or bank holidays. The function still needs changes in terms of calculating the lag correctly and that too in decimal format.
I work in the shipping industy and sometimes the depart date for the ship can be on the weekend. In that case it needs to take into consideration the exact time it departed which it is not doing currently. Its just taking the date part and not the time which in business terms is wrong.
For e.g. with regards to the sample data, if the depart date is '2014-07-05 23:30:00.000' which is a saturday, it just takes the date part into consideration and not the time and does that with sunday as well and calculates the lag as 2. However, in fact as per the depart time, the lag should be 1 (rounded off) starting from '2014-07-05 23:30:00.000' till '2014-08-05 23:30:00.000' (ignoring the half an hour afterwards).
I hope I been able to explain the problem clearly.
Thanks.
the lag should be 1 (rounded off)
and that too in decimal format
ignoring the half an hour afterwards
it just takes the date part into consideration and not the time
I hope I been able to explain the problem clearly
Not really, there are quite a few contradictions here. Sort those out first, decide what you really want and post up some sample data which more than covers the date range you want to select from it - with edge cases. The function should be easy but you're confusing folks with these ambiguities and putting them off from answering your question.
I've worked in shipping too - 9 months at VShips, about 2002. Glasgow was a fantastic place to work.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 11, 2014 at 9:14 am
the lag should be 1 (rounded off)
and that too in decimal format
ignoring the half an hour afterwards
it just takes the date part into consideration and not the time
I hope I been able to explain the problem clearly
Not really, there are quite a few contradictions here. Sort those out first, decide what you really want and post up some sample data which more than covers the date range you want to select from it - with edge cases. The function should be easy but you're confusing folks with these ambiguities and putting them off from answering your question.
I've worked in shipping too - 9 months at VShips, about 2002. Glasgow was a fantastic place to work.
[/quote]
Hi Chris,
Please find my answers below:
1. The lag should be counted as 1
What I meant was if the lag is say 1.0005, then it would be approx. 1. It should be displayed as 1.00 (2 decimal places).
2. and that too in decimal format
I meant the lag to have at least 2 decimal places. for e.g. 2.50
3. ignoring the half an hour afterwards
Even if you include the half an hour the lag would still comes out to be approx. 1 and not 2 as I mentioned in the example above.
The sample data I posted covers the period for the related issue. Below I have added some more sample data.
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-04 00:00:00.000','O','N')
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-10 00:00:00.000','O','N')
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-11 00:00:00.000','O','N')
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-12 00:00:00.000','C','N')
I hope we dont veer off the track by getting hooked on trivial issues.
Thanks.
September 11, 2014 at 9:41 am
pwalter83 (9/11/2014)
the lag should be 1 (rounded off)
and that too in decimal format
ignoring the half an hour afterwards
it just takes the date part into consideration and not the time
I hope I been able to explain the problem clearly
Not really, there are quite a few contradictions here. Sort those out first, decide what you really want and post up some sample data which more than covers the date range you want to select from it - with edge cases. The function should be easy but you're confusing folks with these ambiguities and putting them off from answering your question.
I've worked in shipping too - 9 months at VShips, about 2002. Glasgow was a fantastic place to work.
Hi Chris,
Please find my answers below:
1. The lag should be counted as 1
What I meant was if the lag is say 1.0005, then it would be approx. 1. It should be displayed as 1.00 (2 decimal places).
2. and that too in decimal format
I meant the lag to have at least 2 decimal places. for e.g. 2.50
3. ignoring the half an hour afterwards
Even if you include the half an hour the lag would still comes out to be approx. 1 and not 2 as I mentioned in the example above.
The sample data I posted covers the period for the related issue. Below I have added some more sample data.
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-04 00:00:00.000','O','N')
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-10 00:00:00.000','O','N')
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-11 00:00:00.000','O','N')
INSERT INTO [MG_BANK_CALENDAR]
VALUES('GB','2014-07-12 00:00:00.000','C','N')
I hope we dont veer off the track by getting hooked on trivial issues.
Thanks.[/quote]
Paul, you find this type of work very difficult or you wouldn’t be asking for help on a forum. I find it extremely easy – so much so that I use your problem as my watercooler when I need a break from the really difficult stuff I’m doing here. A major contributing factor to this difference is that I understand the importance of fine detail in defining the exact nature of a problem - and you feel that it’s trivial. I hope that you can learn from this and not take it as a personal attack.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 11, 2014 at 10:11 am
Paul, you find this type of work very difficult or you wouldn’t be asking for help on a forum. I find it extremely easy – so much so that I use your problem as my watercooler when I need a break from the really difficult stuff I’m doing here. A major contributing factor to this difference is that I understand the importance of fine detail in defining the exact nature of a problem - and you feel that it’s trivial. I hope that you can learn from this and not take it as a personal attack.
I know that already Chris. We all have our own circumstances, I am still a struggling employee while you might be a highly paid contractor.
I dont see this as a personal attack at all. You have helped me a lot of times before and so are justified in asking questions. I only need to get this issue resolved and would be very grateful if you could mention what further info I need to provide with regards to my query.
Thanks.
September 12, 2014 at 2:28 am
USE TEMPDB
/*
CREATE TABLE MG_COUNTRY([COUNTRY_CD] [varchar](2) NOT NULL,[COUNTRY_DSC] [varchar](35) NOT NULL)
INSERT INTO MG_COUNTRY VALUES ('GB','United Kingdom')
CREATE TABLE [dbo].[MG_BANK_CALENDAR](
[COUNTRY_CD] [varchar](2) NOT NULL,
[BUSINESS_DT] [datetime] NOT NULL,
[BUSINESS_TYPE_FLG] [char](1) NOT NULL,
[DELETED_FLG] [char](1) NULL,
CONSTRAINT [PK_MG_BANK_CALENDAR] PRIMARY KEY CLUSTERED ([COUNTRY_CD] ASC,[BUSINESS_DT] ASC))
INSERT INTO [MG_BANK_CALENDAR] VALUES
('GB','2014-07-04 00:00:00.000','O','N'),
('GB','2014-07-05 00:00:00.000','C','N'),
('GB','2014-07-06 00:00:00.000','C','N'),
('GB','2014-07-07 00:00:00.000','O','N'),
('GB','2014-07-08 00:00:00.000','O','N'),
('GB','2014-07-09 00:00:00.000','O','N'),
('GB','2014-07-10 00:00:00.000','O','N'),
('GB','2014-07-11 00:00:00.000','O','N'),
('GB','2014-07-12 00:00:00.000','C','N')
*/
DECLARE @country VARCHAR(50) = 'United Kingdom', @start_dt DATETIME = '2014-07-09 12:29:26', @end_dt DATETIME = '2014-07-05 23:30:00.000'
-- Query 1: Somewhere to start. Switch startdate and enddate if necessary,
-- and check we're returning the correct date range from the calendar table
-- Leave out the other filters for now.
SELECT d.*, bc.*
FROM (
SELECT RangeStart = CASE WHEN @start_dt < @end_dt THEN @start_dt ELSE @end_dt END,
RangeEnd = CASE WHEN @end_dt > @start_dt THEN @end_dt ELSE @start_dt END
) d
INNER JOIN MG_BANK_CALENDAR bc ON bc.BUSINESS_DT BETWEEN CAST(RangeStart AS DATE) AND CAST(RangeEnd AS DATE)
INNER JOIN MG_COUNTRY c ON c.COUNTRY_CD = bc.COUNTRY_CD
WHERE c.COUNTRY_DSC = @country
-- Query 2: If the beginning of the qualifying date range is the same as the startdate
-- then assign a flag value to that date. Similarly with enddate.
SELECT
d.*, bc.*,
DaysToCount = CASE
WHEN bc.BUSINESS_DT = CAST(RangeStart AS DATE) THEN 0.25
WHEN bc.BUSINESS_DT = CAST(RangeEnd AS DATE) THEN 0.75
ELSE 1 END
FROM (
SELECT RangeStart = CASE WHEN @start_dt < @end_dt THEN @start_dt ELSE @end_dt END,
RangeEnd = CASE WHEN @end_dt > @start_dt THEN @end_dt ELSE @start_dt END
) d
INNER JOIN MG_BANK_CALENDAR bc ON bc.BUSINESS_DT BETWEEN CAST(RangeStart AS DATE) AND CAST(RangeEnd AS DATE)
INNER JOIN MG_COUNTRY c ON c.COUNTRY_CD = bc.COUNTRY_CD
WHERE c.COUNTRY_DSC = @country
-- Ok so far? Next, evaluate the elapsed part of a day as a decimal fraction.
SELECT CAST(GETDATE() AS NUMERIC(18,12))%1
-- Here's proof that it works
SELECT 60*60*24*1000 -- 86400000 milliseconds in a day
SELECT CAST(GETDATE() AS NUMERIC(18,12))%1*86400000 -- ms elapsed so far today
-- Add ms elapsed so far today to GETDATE() which has been cast as DATE then recast as DATETIME to drop the time component,
-- and check the result is the same as GETDATE()
SELECT GETDATE(), DATEADD(
millisecond,
CAST(GETDATE() AS NUMERIC(18,12))%1*86400000,
CAST(CAST(GETDATE() AS DATE) AS DATETIME)
)
-- Query 3: Incorporate the elapsed part of the day into Query 2
-- For stardate, you want the remaining part of the day
-- For enddate, you want the elapsed part of the day
-- Use NUMERIC(9,3) to drop some of the digits from the decimal fraction
SELECT
d.*, bc.*,
DaysToCount = CASE
WHEN bc.BUSINESS_DT = CAST(RangeStart AS DATE) THEN 1-CAST(RangeStart AS NUMERIC(9,3))%1
WHEN bc.BUSINESS_DT = CAST(RangeEnd AS DATE) THEN CAST(RangeEnd AS NUMERIC(9,3))%1
ELSE 1 END
FROM (
SELECT RangeStart = CASE WHEN @start_dt < @end_dt THEN @start_dt ELSE @end_dt END,
RangeEnd = CASE WHEN @end_dt > @start_dt THEN @end_dt ELSE @start_dt END
) d
INNER JOIN MG_BANK_CALENDAR bc ON bc.BUSINESS_DT BETWEEN CAST(RangeStart AS DATE) AND CAST(RangeEnd AS DATE)
INNER JOIN MG_COUNTRY c ON c.COUNTRY_CD = bc.COUNTRY_CD
WHERE c.COUNTRY_DSC = @country
-- Before going any further than this, you must decide exactly how to handle [DaysToCount]
-- in row 1 and row 5 of the output.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 12, 2014 at 2:58 am
ChrisM@Work (9/12/2014)
USE TEMPDB/*
CREATE TABLE MG_COUNTRY([COUNTRY_CD] [varchar](2) NOT NULL,[COUNTRY_DSC] [varchar](35) NOT NULL)
INSERT INTO MG_COUNTRY VALUES ('GB','United Kingdom')
CREATE TABLE [dbo].[MG_BANK_CALENDAR](
[COUNTRY_CD] [varchar](2) NOT NULL,
[BUSINESS_DT] [datetime] NOT NULL,
[BUSINESS_TYPE_FLG] [char](1) NOT NULL,
[DELETED_FLG] [char](1) NULL,
CONSTRAINT [PK_MG_BANK_CALENDAR] PRIMARY KEY CLUSTERED ([COUNTRY_CD] ASC,[BUSINESS_DT] ASC))
INSERT INTO [MG_BANK_CALENDAR] VALUES
('GB','2014-07-04 00:00:00.000','O','N'),
('GB','2014-07-05 00:00:00.000','C','N'),
('GB','2014-07-06 00:00:00.000','C','N'),
('GB','2014-07-07 00:00:00.000','O','N'),
('GB','2014-07-08 00:00:00.000','O','N'),
('GB','2014-07-09 00:00:00.000','O','N'),
('GB','2014-07-10 00:00:00.000','O','N'),
('GB','2014-07-11 00:00:00.000','O','N'),
('GB','2014-07-12 00:00:00.000','C','N')
*/
DECLARE @country VARCHAR(50) = 'United Kingdom', @start_dt DATETIME = '2014-07-09 12:29:26', @end_dt DATETIME = '2014-07-05 23:30:00.000'
-- Query 1: Somewhere to start. Switch startdate and enddate if necessary,
-- and check we're returning the correct date range from the calendar table
-- Leave out the other filters for now.
SELECT d.*, bc.*
FROM (
SELECT RangeStart = CASE WHEN @start_dt < @end_dt THEN @start_dt ELSE @end_dt END,
RangeEnd = CASE WHEN @end_dt > @start_dt THEN @end_dt ELSE @start_dt END
) d
INNER JOIN MG_BANK_CALENDAR bc ON bc.BUSINESS_DT BETWEEN CAST(RangeStart AS DATE) AND CAST(RangeEnd AS DATE)
INNER JOIN MG_COUNTRY c ON c.COUNTRY_CD = bc.COUNTRY_CD
WHERE c.COUNTRY_DSC = @country
-- Query 2: If the beginning of the qualifying date range is the same as the startdate
-- then assign a flag value to that date. Similarly with enddate.
SELECT
d.*, bc.*,
DaysToCount = CASE
WHEN bc.BUSINESS_DT = CAST(RangeStart AS DATE) THEN 0.25
WHEN bc.BUSINESS_DT = CAST(RangeEnd AS DATE) THEN 0.75
ELSE 1 END
FROM (
SELECT RangeStart = CASE WHEN @start_dt < @end_dt THEN @start_dt ELSE @end_dt END,
RangeEnd = CASE WHEN @end_dt > @start_dt THEN @end_dt ELSE @start_dt END
) d
INNER JOIN MG_BANK_CALENDAR bc ON bc.BUSINESS_DT BETWEEN CAST(RangeStart AS DATE) AND CAST(RangeEnd AS DATE)
INNER JOIN MG_COUNTRY c ON c.COUNTRY_CD = bc.COUNTRY_CD
WHERE c.COUNTRY_DSC = @country
-- Ok so far? Next, evaluate the elapsed part of a day as a decimal fraction.
SELECT CAST(GETDATE() AS NUMERIC(18,12))%1
-- Here's proof that it works
SELECT 60*60*24*1000 -- 86400000 milliseconds in a day
SELECT CAST(GETDATE() AS NUMERIC(18,12))%1*86400000 -- ms elapsed so far today
-- Add ms elapsed so far today to GETDATE() which has been cast as DATE then recast as DATETIME to drop the time component,
-- and check the result is the same as GETDATE()
SELECT GETDATE(), DATEADD(
millisecond,
CAST(GETDATE() AS NUMERIC(18,12))%1*86400000,
CAST(CAST(GETDATE() AS DATE) AS DATETIME)
)
-- Query 3: Incorporate the elapsed part of the day into Query 2
-- For stardate, you want the remaining part of the day
-- For enddate, you want the elapsed part of the day
-- Use NUMERIC(9,3) to drop some of the digits from the decimal fraction
SELECT
d.*, bc.*,
DaysToCount = CASE
WHEN bc.BUSINESS_DT = CAST(RangeStart AS DATE) THEN 1-CAST(RangeStart AS NUMERIC(9,3))%1
WHEN bc.BUSINESS_DT = CAST(RangeEnd AS DATE) THEN CAST(RangeEnd AS NUMERIC(9,3))%1
ELSE 1 END
FROM (
SELECT RangeStart = CASE WHEN @start_dt < @end_dt THEN @start_dt ELSE @end_dt END,
RangeEnd = CASE WHEN @end_dt > @start_dt THEN @end_dt ELSE @start_dt END
) d
INNER JOIN MG_BANK_CALENDAR bc ON bc.BUSINESS_DT BETWEEN CAST(RangeStart AS DATE) AND CAST(RangeEnd AS DATE)
INNER JOIN MG_COUNTRY c ON c.COUNTRY_CD = bc.COUNTRY_CD
WHERE c.COUNTRY_DSC = @country
-- Before going any further than this, you must decide exactly how to handle [DaysToCount]
-- in row 1 and row 5 of the output.
Thanks a lot for the code, Chris. However, I am wondering should I use this to modify the function I posted earlier because my main concern was to amend the function in the first place ?
Thanks.
September 12, 2014 at 3:40 am
If the existing function has been in use for some time then you should assume that it is tried and tested and wherever it’s in use, any changes to it will yield unexpected results. So write a new function.
What I’ve posted so far is unfinished. If you can describe exactly what you want to do for the first and last day then I’ll finish it off for you. For instance, if the time component of the startdate (on a valid day) is 01:00 (23 hours of the day remaining), 06:00, 11:00, 12:00, 13:00, 18:00, 23:00 (1 hour of the day remaining), what do you want returned as the decimal fraction of a day, in each case? Same with the enddate please, except it will be time elapsed.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 12, 2014 at 4:24 am
ChrisM@Work (9/12/2014)
If the existing function has been in use for some time then you should assume that it is tried and tested and wherever it’s in use, any changes to it will yield unexpected results. So write a new function.What I’ve posted so far is unfinished. If you can describe exactly what you want to do for the first and last day then I’ll finish it off for you. For instance, if the time component of the startdate (on a valid day) is 01:00 (23 hours of the day remaining), 06:00, 11:00, 12:00, 13:00, 18:00, 23:00 (1 hour of the day remaining), what do you want returned as the decimal fraction of a day, in each case? Same with the enddate please, except it will be time elapsed.
Hi Chris,
Thanks again.
The function is to calculate the lag for the holiday days like weekends and bank holidays. So for example if the time component of the start date (lets say on a saturday) is 11:30pm and for the end date (lets say wednesday) is 12:30am, then it should calculate the lag which is for saturday and sunday) based on the following division:
CAST(24 as decimal(10,2)) which is approx 1.00 (calculating from 11:30pm saturday till 12:00 am sunday because after that its officially Monday and we dont need to calculate from monday till wednesday). In other words, 24 hours = 1 lag.
This is what is precisely missing in the existing function because it just calculates the 2 weekend days as 48 hours and calculates the lag as 2 and does not take the time part into consideration.
Did it help to answer your query ?
Thanks.
September 12, 2014 at 5:03 am
pwalter83 (9/12/2014)
ChrisM@Work (9/12/2014)
If the existing function has been in use for some time then you should assume that it is tried and tested and wherever it’s in use, any changes to it will yield unexpected results. So write a new function.What I’ve posted so far is unfinished. If you can describe exactly what you want to do for the first and last day then I’ll finish it off for you. For instance, if the time component of the startdate (on a valid day) is 01:00 (23 hours of the day remaining), 06:00, 11:00, 12:00, 13:00, 18:00, 23:00 (1 hour of the day remaining), what do you want returned as the decimal fraction of a day, in each case? Same with the enddate please, except it will be time elapsed.
Hi Chris,
Thanks again.
The function is to calculate the lag for the holiday days like weekends and bank holidays. So for example if the time component of the start date (lets say on a saturday) is 11:30pm and for the end date (lets say wednesday) is 12:30am, then it should calculate the lag which is for saturday and sunday) based on the following division:
CAST(24 as decimal(10,2)) which is approx 1.00 (calculating from 11:30pm saturday till 12:00 am sunday because after that its officially Monday and we dont need to calculate from monday till wednesday). In other words, 24 hours = 1 lag.
This is what is precisely missing in the existing function because it just calculates the 2 weekend days as 48 hours and calculates the lag as 2 and does not take the time part into consideration.
Did it help to answer your query ?
Thanks.
Not really.
If Saturday doesn’t count if it’s after 11.30pm, does it count if it’s 1am? At what point exactly does it change from counting (because it’s 1am and all or most of saturday is included) to not counting (because it’s 11.30pm and there’s only half an hour of Saturday left)?
Or does a startdate with a time component not count at all?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 12, 2014 at 5:41 am
Not really.
If Saturday doesn’t count if it’s after 11.30pm, does it count if it’s 1am? At what point exactly does it change from counting (because it’s 1am and all or most of saturday is included) to not counting (because it’s 11.30pm and there’s only half an hour of Saturday left)?
Or does a startdate with a time component not count at all?
The first thing I need to mention is - We do not need to calculate the lags for the weekdays because it is out of the range of the function.
Now I will explain this with 3 different examples -
1. The first scenario is very straight forward. If the start date is on a thursday (Sept 4th) at 1300 hrs and end date is the next tuesday (Sept 9th) at 1200 hrs, then in this case, both the weekend days will be calculated as 48 hours in which case the lag would be 2. This is because the start date or end date did not fall on the weekend and the ship was travelling for the whole 2 weekend days.
2. The 2nd scenario is the issue I am having which is not covered by the function.
If the start date is on a saturday (Sept 6th) at 1200 hrs and end date is the next wednesday (Sept 10th) at 1300 hrs, then in this case the time part till 1200 hrs on saturday will not be included for lag calculation. We need to include the time part from saturday 1200 hrs onwards till 00:00 on sunday because after that its monday and the function is not meant to calculate anything for the weekdays as I mentioned before.
So in this case the lag calculation will be as follows:
- Saturday 1200 hrs till Sunday 1200 hrs - 1 lag (24 hours = 1 lag)
- Sunday 1200 hrs till Sunday 00:00 hrs - 0.5 lag (12 hours = 0.5 lag)
So the lag in this case is 1.5.
3. This is when the start date and end date both fall on weekends.
If the start date is on a saturday (Sept 6th) at 1200 hrs and end date is sunday (Sept 7th) at 1300 hrs, then in this case the time part till 1200 hrs on saturday will not be included for lag calculation. We need to include the time part from saturday 1200 hrs onwards till 1300 hrs on sunday.
In this case the lag would be 1.04 (from 1200 hrs sat 6th till 1300 hrs sunday 7th) based on 24 hrs = 1 lag
Does this explanation help ?
Thanks.
September 12, 2014 at 5:45 am
Can you amend that post to use a 24-hour clock please?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 12, 2014 at 5:52 am
ChrisM@Work (9/12/2014)
Can you amend that post to use a 24-hour clock please?
I have amended the post to use 24 hr clock. Hope it seems okay now.
Thanks.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply