Results 1 to 7 of 7

Thread: Sql Date

  1. #1
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71

    Unanswered: Sql Date

    Good Day!

    Can you pls help. I have 2 tables
    Table1 (It is all my SA holidays I just named few)
    Holiday
    16 Dec 2003
    25 Dec 2003
    26 Dec 2003
    1 Jan 2004

    Table2 (Transaction Date)
    ID TrDate FYI TrDatePls4
    1 9 Dec 2003 Tue Null
    2 10 Dec 2003 Wed Null
    3 16 Dec 2003 Tue Null
    4 17 Dec 2003 Wed Null
    5 18 Dec 2003 Thu Null
    6 19 Dec 2003 Fri Null

    I want to do this in one statment and i struggle, I want to exclude a TrDate done on a public holiday which is the 16 Dec in my case
    Here is my statement
    Statement 1
    select Trdate from Table2 where Id =16 and Trdate not in (Select Holiday from Table1)
    the statement is write

    NOTE I JUST addes cloumn FYI so that you can know that which day was the TrDate
    Then how can i add this on my statement to make one query?

    I want to say add 4 days to TrDate, but if TrDate (day) is Saturday then add 2 days so that this can be Monday and update column TrDatePls4, or if TrDate (day) is Sunday, then add 2 days so that this can be Monday ALSO and update column TrDatePls4, I dont want my TrDatePls4 to be on Sunday or Saturday.

    Statement 2
    According to my example the above will be
    if ((select DATENAME ( dw , DATEADD ( d , 4, TrDate )) from table2 where Id = 1) = 'Sunday')
    BEGIN
    update table2
    set TrDatePls4 = DATEADD ( d , 1,TrDatePls4 )
    END
    ELSE
    --This statement is write because 9 + 4 =13 and 13 Dec 2003 was Saturday
    if ((select DATENAME ( dw , DATEADD ( d , 4, TrDate)) from table2 where Id = 1) = 'Saturday')
    BEGIN
    update table2
    set TrDatePls4 = DATEADD ( d , 2, P )
    --So I will update table2, column TrDatePls4 with 13+2 which is 15 and is Mon
    END


    My Main problem is to put together this 2 statements in one single query.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Did the professor specify what they want you to do if a holiday occurs on a Monday? That can really complicate this kind of assignment.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are so cynical, Pat! I bet this isn't a homework assignment. The guy's got 50 post already, and the Homework Help seekers usually just have one or two.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    Thanks guys, I used the DTS to do this, so i actually answered myself. All who want to know, DTS is a way to go really

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Blindman: Pat is just earning his title. Part of his contract, you know ;-).

    As for the question at hand, it looks like you may be able to do this with a case statement. Something like
    Code:
    update table
    set trpls4 = case when (select DATENAME ( dw , DATEADD ( d , 4, TrDate )) from table2 where Id = 1) = 'Sunday' then DATEADD ( d , 1,TrDatePls4 )
    when (select DATENAME ( dw , DATEADD ( d , 4, TrDate )) from table2 where Id = 1) = 'Saturday then DATEADD ( d , 2,TrDatePls4 )
    when ......
    Look up Case in books online, and you should get the idea.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, I have become quite cynical. I get annoyed by doing people's homework, and was enraged about a year or so ago when an applicant provided an example of their work that our Director of Development recognized as my coding style! I happened to have a copy of the original exchange, which needless to say doomed that candidate!

    DTS provides the most flexible solution, because it can take a completely iterative approach. That allows it to deal with arbitrary complexity.

    You can code a very flexible solution in pure Transact-SQL that will be much faster, something like:
    Code:
    UPDATE table2
       SET trdateplus4 = DateAdd(day, CASE
          WHEN 7 = DatePart(weekday, trdate)
             AND EXISTS (SELECT * FROM table1 WHERE DateAdd(day, 2, trdate) = Holiday) THEN 3
          WHEN 7 = DatePart(weekday, trdate) THEN 2
          WHEN 1 = DatePart(weekday, trdate)
             AND EXISTS (SELECT * FROM table1 WHERE DateAdd(day, 1, trdate) = Holiday) THEN 2
          WHEN 1 = DatePart(weekday, trdate) THEN 1
          WHEN 6 = DatePart(weekday, trdate)
             AND EXISTS (SELECT * FROM table1 WHERE trdate = Holiday) THEN 3
          WHEN EXISTS (SELECT * FROM table1 WHERE trdate = Holiday) THEN 1
          ELSE 0
       END, trdate)
    This doesn't cope with two holidays in a row, but it catches pretty much everything else (I think).

    -PatP

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't use DTS if you can possibly avoid it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •