Hello all,

I'm using the Calendar table described in this article (http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html).

I'm trying to formulate a query which will return the day number given an input parameter of how many days from now I want the returned day. I want it to return only week days (business days) and the problem I'm having with it is that it doesn't return anything if the day found is a weekend day.

I want this to work on only week days so if I'm on a friday and I want the week day three days from now it will return the next Wednesday, which would be three work days from now.

Can anyone help me with this?

Here is my current query.

DECLARE 
@Date datetime, 
@Days smallint 


SET @Date = GETDATE() 
SET @Days = 5 


SELECT 
        DT, 
        [Month], 
        [Day], 
        CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME) Today 
FROM 
        SharedInfo.dbo.Calendar 
WHERE 
        [DT] = CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME) + @Days 
AND 
        IsWeekDay = 1

Oops, that subject was an accident. This was supposed to be something like "Need help with Calendar query".

tprohas wrote:

Oops, that subject was an accident. This was supposed to be something like "Need help with Calendar query".

fix0red

Thanks for the article link, good one! How about this one (if i understood the requirements right):

SELECT  t.DT, 
        t.[M], 
        t.[D], 
        CAST(FLOOR(CAST(@Date AS FLOAT))AS DATETIME) Today  FROM
(
    SELECT ROW_NUMBER ( ) over (Order by dt) diff, *
    FROM Calendar
    WHERE IsWeekDay = 1
    AND dt >= @Date
) t
WHERE t.diff = @days

It's not ansi-sql but it works. If i'll get some time i'll try to make one without Row_Number() and sub queries instead, and maybe even Linq for LLBLGen Pro one

Hmmm, it was trickier than i thought, but eventually i came up with this one too:

SELECT  c1.DT, 
        c1.[M], 
        c1.[D], 
        CAST(FLOOR(CAST(@Date AS FLOAT))AS DATETIME) Today 
FROM Calendar c1
WHERE 
    c1.isWeekDay = 1
AND
    (
    SELECT count(*)
    FROM Calendar c2
    WHERE c2.dt <= c1.dt
    AND c2.dt >= @Date
    AND c2.isWeekDay = 1) = @Days

Which is about 100 times slower (still in the few ms), but it doesn't use an mssql function

Does anyone have an idea how to do this efficiently without enumerating the rows the way ROW_NUMBER() does? (not that i think there's any major reason not too) Amitay

AmitayD wrote:

Hmmm, it was trickier than i thought, but eventually i came up with this one too:

SELECT  c1.DT, 
        c1.[M], 
        c1.[D], 
        CAST(FLOOR(CAST(@Date AS FLOAT))AS DATETIME) Today 
FROM Calendar c1
WHERE 
    c1.isWeekDay = 1
AND
    (
    SELECT count(*)
    FROM Calendar c2
    WHERE c2.dt <= c1.dt
    AND c2.dt >= @Date
    AND c2.isWeekDay = 1) = @Days

Which is about 100 times slower (still in the few ms), but it doesn't use an mssql function

Does anyone have an idea how to do this efficiently without enumerating the rows the way ROW_NUMBER() does? (not that i think there's any major reason not too) Amitay

Thanks for the reply and the query. I did finally get this figured out using the following queries.

CREATE FUNCTION dbo.GetCalendarDayXDaysFromNow(@CurrentDate datetime, @Days smallint)
    RETURNS datetime
BEGIN
-- VERSION: 200806121437
DECLARE
@VisitDate datetime,
@JulianDayToday smallint

-- SET @CurrentDate = GETDATE()
-- SET @Days = 7

--// Get Julian Day Today
SET @JulianDayToday = (SELECT JulianBusinessDay FROM SharedInfo.dbo.Calendar WHERE [DT] = CAST(FLOOR(CAST(@CurrentDate AS FLOAT)) AS DATETIME))

SELECT
    @VisitDate = [DT]
FROM
    SharedInfo.dbo.Calendar
WHERE
    JulianBusinessDay = @JulianDayToday + @Days
AND
    IsWeekDay = 1

RETURN @VisitDate
END

DECLARE
@Date datetime

SET @Date = GETDATE()

SELECT
    vr.RequestorFirstName,
    vr.RequestorLastName,
    vr.FacilityName,
    sv.EventName,
    vr.StartTime,
    VisitDate = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, vr.StartTime)))
FROM
    dbo.VisitRequest vr
LEFT JOIN
    dbo.ScheduledVisit sv ON vr.ScheduledVisitID = sv.ScheduledVisitID
WHERE
    CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, vr.StartTime))) = CDGVisitProgram.dbo.GetCalendarDayXDaysFromNow(@Date, 5)
AND
    (sv.Canceled IS NULL OR sv.Canceled = 0)
ORDER BY
    vr.StartTime

Now I just have to figure out how to call CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, vr.StartTime))) as a LLBLGen DbFunctionCall. I was using DAY(vr.StartTime) as a DbFunctionCall but I am not sure that calling multiple nested functions will work.