Post reply

complex statement! not even sure if it is even possible....

  • April 17, 2006 at 2:25 pm

    #171177

    Hi,

    I have a system whereby a user requests some consultancy on a certain day, the stored procedure (below) finds any consultants with the specifed ability and that are available on that day. I have created a page that checks this, and redirects to another page if NO consultants are free (no rows returned)

    the problem is on this next page i want another stored procedure that will perform the same function as below BUT for the 3 days either side of the date specified. The other requirement (even harder part) is that it outputs the rows in order of proximity to the date specified.

    so if user requests a consultant on 6th april and there are no consultants available, this new stored procedure will perform the checks and output the results in this order

    5th,

    7th,

    4th,

    8th,

    3rd

    9th

    is this even possible?? thanks in advance if anyone can help me

    ben

    http://pastebin.com/665729

    CREATE proc ben_sp_bringBackConsultantNull

    @daterequireddatetime,

    @consultancytypeidnumeric(9),

    @leveltinyint,

    @clientidnumeric(9)

    as

    SELECT top 10 (left(Thedate,11) + ' - ' + consultantname ) as 'Consultant Name', Thedate 'Date'

    FROM WeekEndsAndHolidays, vw_consultant_ability

    WHERE dayname <> 'Saturday'

    AND dayname <> 'Sunday'

    AND Thedate = @daterequired

    AND consultancytypeid = @consultancytypeid

    AND consultancytypelevel >= @level

    AND consultantid NOT IN

    -- no disputes

    (

    SELECT consultantid

    FROM client_consultant_unavailability

    WHERE clientid = @clientid

    )

    ORDER BY Date

    GO

  • April 17, 2006 at 3:00 pm

    #632838

    CREATE proc ben_sp_bringBackConsultantNull

    @daterequired datetime,

    @consultancytypeid numeric(9),

    @level tinyint,

    @clientid numeric(9)

    as

    SELECT top 10 (left(Thedate,11) + ' - ' + consultantname ) as 'Consultant Name', Thedate 'Date'

    FROM WeekEndsAndHolidays, vw_consultant_ability

    WHERE dayname <> 'Saturday'

    AND dayname <> 'Sunday'

    AND Thedate between @daterequired -3 and @daterequired + 3

    AND consultancytypeid = @consultancytypeid

    AND consultancytypelevel >= @level

    AND consultantid NOT IN

    -- no disputes

    (

    SELECT consultantid

    FROM client_consultant_unavailability

    WHERE clientid = @clientid

    )

    order by abs( datediff ( day, TheDate ,@daterequired)), TheDate

     


    * Noel

  • April 17, 2006 at 3:35 pm

    #632844

    WOW, that works a treat!!

    thanks a lot noeld

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply