Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > General > General Chat> Need help with Calendar query
 

Pages: 1
General Chat
Need help with Calendar query
Page:1/1 

  Print all messages in this thread  
Poster Message
tprohas
User



Location:
Tucson, AZ
Joined on:
23-Mar-2004 00:00:43
Posted:
257 posts
# Posted on: 11-Jun-2008 00:48:12.  
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.

Code:

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
Aaron Prohaska
http://www.verdesoft.com/
 
Top
tprohas
User



Location:
Tucson, AZ
Joined on:
23-Mar-2004 00:00:43
Posted:
257 posts
# Posted on: 11-Jun-2008 00:49:16.  
Oops, that subject was an accident. This was supposed to be something like "Need help with Calendar query".

Aaron Prohaska
http://www.verdesoft.com/
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37409 posts
# Posted on: 11-Jun-2008 09:45:20.  
tprohas wrote:
Oops, that subject was an accident. This was supposed to be something like "Need help with Calendar query".

fix0red Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
AmitayD
User



Location:
Israel
Joined on:
22-Aug-2007 12:29:26
Posted:
45 posts
# Posted on: 12-Jun-2008 20:42:05.  
Thanks for the article link, good one!
How about this one (if i understood the requirements right):

Code:

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 Wink


  Top
AmitayD
User



Location:
Israel
Joined on:
22-Aug-2007 12:29:26
Posted:
45 posts
# Posted on: 12-Jun-2008 21:17:45.  
Hmmm, it was trickier than i thought, but eventually i came up with this one too:

Code:

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 Tongue

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
  Top
tprohas
User



Location:
Tucson, AZ
Joined on:
23-Mar-2004 00:00:43
Posted:
257 posts
# Posted on: 13-Jun-2008 01:22:00.  
AmitayD wrote:
Hmmm, it was trickier than i thought, but eventually i came up with this one too:

Code:

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 Tongue

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.

Code:

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


Code:

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.


Aaron Prohaska
http://www.verdesoft.com/
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.