September 12, 2014 at 6:17 am
Much better, thank you.
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.
An easier way of looking at this - well I think so anyway - is as follows:
- Saturday 1200 hrs till Saturday 2400 hrs (12 hours = 0.5 lag)
- Sunday 0000 hrs till Sunday 2400 hrs (24 hours = 1.0 lag)
So the lag in this case is 1.5.
Any comment?
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 7:08 am
-- Following on from the last post, here's the same query as above with a couple of amendments.
-- Firstly, I've put the remaining filters into place, the two flag columns.
-- Secondly, I've added a new column, the weekday of the BUSINESS_DT - so you don't have
-- to keep looking at your calendar. The date range is saturday to wednesday so it's
-- your scenario 2.
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'
SELECT
d.RangeStart,
d.RangeEnd,
bc.BUSINESS_DT,
BUSINESS_DT_day = DATENAME(WEEKDAY,BUSINESS_DT),
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
AND bc.deleted_flg = 'N'
AND bc.business_type_flg = 'C'
RangeStart RangeEnd BUSINESS_DT BUSINESS_DT_day DaysToCount
2014-07-05 23:30:00.000 2014-07-09 12:29:26.000 2014-07-05 00:00:00.000 Saturday 0.021
2014-07-05 23:30:00.000 2014-07-09 12:29:26.000 2014-07-06 00:00:00.000 Sunday 1.000
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 7:18 am
ChrisM@Work (9/12/2014)
Much better, thank you.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.
An easier way of looking at this - well I think so anyway - is as follows:
- Saturday 1200 hrs till Saturday 2400 hrs (12 hours = 0.5 lag)
- Sunday 0000 hrs till Sunday 2400 hrs (24 hours = 1.0 lag)
So the lag in this case is 1.5.
Any comment?
Yes, you are right, Chris.
September 12, 2014 at 7:29 am
ChrisM@Work (9/12/2014)
-- Following on from the last post, here's the same query as above with a couple of amendments.-- Firstly, I've put the remaining filters into place, the two flag columns.
-- Secondly, I've added a new column, the weekday of the BUSINESS_DT - so you don't have
-- to keep looking at your calendar. The date range is saturday to wednesday so it's
-- your scenario 2.
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'
SELECT
d.RangeStart,
d.RangeEnd,
bc.BUSINESS_DT,
BUSINESS_DT_day = DATENAME(WEEKDAY,BUSINESS_DT),
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
AND bc.deleted_flg = 'N'
AND bc.business_type_flg = 'C'
RangeStart RangeEnd BUSINESS_DT BUSINESS_DT_day DaysToCount
2014-07-05 23:30:00.000 2014-07-09 12:29:26.000 2014-07-05 00:00:00.000 Saturday 0.021
2014-07-05 23:30:00.000 2014-07-09 12:29:26.000 2014-07-06 00:00:00.000 Sunday 1.000
I can confirm the result for your query seems correct. It's just that I need to convert this into a function. Thanks.
September 12, 2014 at 8:19 am
Choose how you wish to tackle DaysToCount, round up or down or whatever and incorporate it into the function.
CREATE FUNCTION [dbo].[CountFractionalHolidays]
/*
Put in some explanatory notes here
*/
(
@country VARCHAR(50),
@start_dt DATETIME,
@end_dt DATETIME
)
RETURNS TABLE AS
RETURN
SELECT
DaysToCount = SUM(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 dbo.MG_BANK_CALENDAR bc
ON bc.BUSINESS_DT BETWEEN CAST(RangeStart AS DATE) AND CAST(RangeEnd AS DATE)
INNER JOIN dbo.MG_COUNTRY c
ON c.COUNTRY_CD = bc.COUNTRY_CD
WHERE c.COUNTRY_DSC = @country
AND bc.deleted_flg = 'N'
AND bc.business_type_flg = 'C'
GO
-- Usage
SELECT * FROM [dbo].[CountFractionalHolidays]('United Kingdom', '2014-07-09 12:29:26', '2014-07-05 23:30:00.000')
SELECT m.*, h.DaysToCount
FROM ( -- this could be any table
SELECT COUNTRY_DSC = 'United Kingdom', StartDate = '2014-07-04 23:30:00.000', EndDate = '2014-07-09 12:29:26' UNION ALL
SELECT COUNTRY_DSC = 'United Kingdom', StartDate = '2014-07-05 23:30:00.000', EndDate = '2014-07-09 12:29:26' UNION ALL
SELECT COUNTRY_DSC = 'United Kingdom', StartDate = '2014-07-05 23:30:00.000', EndDate = '2014-07-06 12:29:26' UNION ALL
SELECT COUNTRY_DSC = 'United Kingdom', StartDate = '2014-07-06 23:30:00.000', EndDate = '2014-07-09 12:29:26'
) m
CROSS APPLY [dbo].[CountFractionalHolidays](m.COUNTRY_DSC, m.StartDate, m.EndDate) h
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 15, 2014 at 6:25 am
ChrisM@Work (9/12/2014)
Choose how you wish to tackle DaysToCount, round up or down or whatever and incorporate it into the function.
CREATE FUNCTION [dbo].[CountFractionalHolidays]/*
Put in some explanatory notes here
*/
(
@country VARCHAR(50),
@start_dt DATETIME,
@end_dt DATETIME
)
RETURNS TABLE AS
RETURN
SELECT
DaysToCount = SUM(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 dbo.MG_BANK_CALENDAR bc
ON bc.BUSINESS_DT BETWEEN CAST(RangeStart AS DATE) AND CAST(RangeEnd AS DATE)
INNER JOIN dbo.MG_COUNTRY c
ON c.COUNTRY_CD = bc.COUNTRY_CD
WHERE c.COUNTRY_DSC = @country
AND bc.deleted_flg = 'N'
AND bc.business_type_flg = 'C'
GO
-- Usage
SELECT * FROM [dbo].[CountFractionalHolidays]('United Kingdom', '2014-07-09 12:29:26', '2014-07-05 23:30:00.000')
SELECT m.*, h.DaysToCount
FROM ( -- this could be any table
SELECT COUNTRY_DSC = 'United Kingdom', StartDate = '2014-07-04 23:30:00.000', EndDate = '2014-07-09 12:29:26' UNION ALL
SELECT COUNTRY_DSC = 'United Kingdom', StartDate = '2014-07-05 23:30:00.000', EndDate = '2014-07-09 12:29:26' UNION ALL
SELECT COUNTRY_DSC = 'United Kingdom', StartDate = '2014-07-05 23:30:00.000', EndDate = '2014-07-06 12:29:26' UNION ALL
SELECT COUNTRY_DSC = 'United Kingdom', StartDate = '2014-07-06 23:30:00.000', EndDate = '2014-07-09 12:29:26'
) m
CROSS APPLY [dbo].[CountFractionalHolidays](m.COUNTRY_DSC, m.StartDate, m.EndDate) h
Thanks Chris.
I tried to implement this funtion but as this is a table-valued function, it doesnt fit in the query.
Could you please suggest something to convert this to a scalar-valued function ?
Sorry for being so embarrasingly bad in SQL. Thanks.
September 19, 2014 at 4:52 am
ChrisM@Work (9/12/2014)
Choose how you wish to tackle DaysToCount, round up or down or whatever and incorporate it into the function.
CREATE FUNCTION [dbo].[CountFractionalHolidays]/*
Put in some explanatory notes here
*/
(
@country VARCHAR(50),
@start_dt DATETIME,
@end_dt DATETIME
)
RETURNS TABLE AS
RETURN
SELECT
DaysToCount = SUM(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 dbo.MG_BANK_CALENDAR bc
ON bc.BUSINESS_DT BETWEEN CAST(RangeStart AS DATE) AND CAST(RangeEnd AS DATE)
INNER JOIN dbo.MG_COUNTRY c
ON c.COUNTRY_CD = bc.COUNTRY_CD
WHERE c.COUNTRY_DSC = @country
AND bc.deleted_flg = 'N'
AND bc.business_type_flg = 'C'
GO
-- Usage
SELECT * FROM [dbo].[CountFractionalHolidays]('United Kingdom', '2014-07-09 12:29:26', '2014-07-05 23:30:00.000')
SELECT m.*, h.DaysToCount
FROM ( -- this could be any table
SELECT COUNTRY_DSC = 'United Kingdom', StartDate = '2014-07-04 23:30:00.000', EndDate = '2014-07-09 12:29:26' UNION ALL
SELECT COUNTRY_DSC = 'United Kingdom', StartDate = '2014-07-05 23:30:00.000', EndDate = '2014-07-09 12:29:26' UNION ALL
SELECT COUNTRY_DSC = 'United Kingdom', StartDate = '2014-07-05 23:30:00.000', EndDate = '2014-07-06 12:29:26' UNION ALL
SELECT COUNTRY_DSC = 'United Kingdom', StartDate = '2014-07-06 23:30:00.000', EndDate = '2014-07-09 12:29:26'
) m
CROSS APPLY [dbo].[CountFractionalHolidays](m.COUNTRY_DSC, m.StartDate, m.EndDate) h
Thanks a lot , Chris !! Did some manipulation to your suggested code and it worked perfectly !
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply