Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2012
    Posts
    31

    Unanswered: Limit Result Set based upon Current Date

    Hello-
    I wish to limit the result set to the previous week based upon the current date.

    Can't quite get it, and do I put it in the Select statement or the Where clause?

    What I want is to return rows where the D_TMR_END date is within 7 days of the Current Date. I have an And/Or statement that runs in the Where clause based upon the day of the week, and that runs fine, but I need the D_TMR_END field to fall within the past 7 days.

    Or, maybe I incorporate that need into my And/Or statement?

    Here's all the code so far:
    SELECT TMD.I_TRIMED_SYS_KEY, TMD.D_CREATED, TMD.C_TMR_TYPE,
    TMD.D_TMR_BEGIN, TMD.D_TMR_END ,
    Dayofweek(TMD.D_TMR_END) AS "DAY#",
    Case Dayofweek(TMD.D_TMR_END) when 1 then 'Sunday' when 2 then 'Monday' when 3 then 'Tuesday' when 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' end AS Day
    FROM DB2PROD.TMDTRPTMR TMD
    WHERE TMD.C_SBU = '35'

    AND ((Dayofweek(Date(Current Date))=3)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END))In(6,7,1))
    OR (Dayofweek(Date(Current Date))=2)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END)) = 4)
    OR (Dayofweek(Date(Current Date))=4)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END)) = 2)
    OR (Dayofweek(Date(Current Date))=5)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END)) = 2)
    OR (Dayofweek(Date(Current Date))=6)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END)) = 2))
    AND TMD.D_CREATED > '2013-09-01'
    AND TMD.C_TMR_TYPE = 'PC'



    Thanks for any help!

    Laura

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that it might be enough to add a predicate at the last, like...
    AND TMD.D_TMR_END >= Current Date - 7 DAYs

  3. #3
    Join Date
    Jul 2012
    Posts
    31
    Hi-
    thanks for your reply.
    When I added that code, I get dates in November.
    I want to only have dates in the D_TMR_END field for a 7 day date span moving back from the current date.

    AND ((TMD.D_TMR_END >= Current Date - 7 DAYs)
    AND (Dayofweek(Date(Current Date))=3)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END))In(6,7,1))
    OR (Dayofweek(Date(Current Date))=2)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END)) = 4)
    OR (Dayofweek(Date(Current Date))=4)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END)) = 2)
    OR (Dayofweek(Date(Current Date))=5)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END)) = 2)
    OR (Dayofweek(Date(Current Date))=6)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END)) = 2))

  4. #4
    Join Date
    Jul 2012
    Posts
    31
    Hi- just as an FYI, I think this code gets me what I want-

    AND Current Date - TMD.D_TMR_END Between 1 and 7

    we'll see tomorrow!

    Thanks

    Laura

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by laurastreng View Post
    Hi-
    thanks for your reply.
    When I added that code, I get dates in November.
    I want to only have dates in the D_TMR_END field for a 7 day date span moving back from the current date.

    AND ((TMD.D_TMR_END >= Current Date - 7 DAYs)
    AND (Dayofweek(Date(Current Date))=3)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END))In(6,7,1))
    OR (Dayofweek(Date(Current Date))=2)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END)) = 4)
    OR (Dayofweek(Date(Current Date))=4)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END)) = 2)
    OR (Dayofweek(Date(Current Date))=5)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END)) = 2)
    OR (Dayofweek(Date(Current Date))=6)
    AND (DAYOFWEEK(DATE(TMD.D_TMR_END)) = 2))

    Is the problem that TMD.D_TMR_END can lay in the future? In that case you can modify Tonkumas answer to:

    Code:
    and TMD.D_TMR_END between current_date - 7 days and current_date
    --
    Lennart

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Laura, Maye you are not getting all of your requirement across. There is no reason to do all this day of week stuff to get current date - 7 days.
    Dave

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some thoughts
    (1) It may be better not to use unnecessary extra parentheses.
    (2) DAYNAME might be used insead of CASE expression, depending on your locale(or character set? I'm not sure...).
    (3) Some predicates connected by OR might be combined into one.

    Also considering Lennart's suggestion, the following example might be worth to try.

    Code:
    SELECT TMD.I_TRIMED_SYS_KEY
         , TMD.D_CREATED
         , TMD.C_TMR_TYPE
         , TMD.D_TMR_BEGIN
         , TMD.D_TMR_END
         , DAYOFWEEK(TMD.D_TMR_END) AS "DAY#"
         , DAYNAME(TMD.D_TMR_END)   AS Day 
     FROM  DB2PROD.TMDTRPTMR TMD
     WHERE TMD.C_SBU = '35'
      AND
      (    DAYOFWEEK(Current Date)  = 2
       AND DAYOFWEEK(TMD.D_TMR_END) = 4
       OR
           DAYOFWEEK(Current Date)  = 3
       AND DAYOFWEEK(TMD.D_TMR_END) In (6 , 7 , 1)
       OR
           DAYOFWEEK(Current Date)  IN (4 , 5 , 6)
       AND DAYOFWEEK(TMD.D_TMR_END) = 2
      )
      AND  TMD.D_CREATED  > '2013-09-01'
      AND  TMD.C_TMR_TYPE = 'PC'
      AND  TMD.D_TMR_END BETWEEN Current Date - 7 DAYs AND Current Date
    ;

  8. #8
    Join Date
    Jul 2012
    Posts
    31
    Thank you very much! I knew the case statement was "messy", couldn't quite neaten it up so thanks. Couldn't get DAYNAME to work (unrecognized) - I'll look for an equivalent. I appreciate the help!

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The following ideas might be not practical(code generality? execution performance?).
    Just fun with SQL.

    Use DECODE instead of AND/OR logic.
    Example 1:
    Code:
     WHERE TMD.C_SBU = '35'
      AND
    /*
      (    DAYOFWEEK(Current Date)  = 2
       AND DAYOFWEEK(TMD.D_TMR_END) = 4
       OR
           DAYOFWEEK(Current Date)  = 3
       AND DAYOFWEEK(TMD.D_TMR_END) In (6 , 7 , 1)
       OR
           DAYOFWEEK(Current Date)  IN (4 , 5 , 6)
       AND DAYOFWEEK(TMD.D_TMR_END) = 2
      )
    */
           DECODE(  DAYOFWEEK(current date)
                  , 2 , 1
                  , 3 , 2
                  , 1 , 0 , 7 , 0
                  , 3
                 )
         = DECODE(  DAYOFWEEK(tmd.d_tmr_end)
                  , 4 , 1
                  , 2 , 3
                  , 3 , 4 , 5 , 4
                  , 2
                 )
      AND  TMD.D_CREATED  > '2013-09-01'
      ...
    If DECODE was not supported in your DB2 Version/Release and platform OS, please try.
    Example 2:
    Code:
     WHERE TMD.C_SBU = '35'
      AND
           CASE DAYOFWEEK(current date)
           WHEN 2 THEN 1
           WHEN 3 THEN 2
           WHEN 1 THEN 0 WHEN 7 THEN 0
           ELSE        3
           END
         = CASE DAYOFWEEK(tmd.d_tmr_end)
           WHEN 4 THEN 1
           WHEN 2 THEN 3
           WHEN 3 THEN 4 WHEN 5 THEN 4
           ELSE        2
           END
      AND  TMD.D_CREATED  > '2013-09-01'
      ...

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another idea.

    Example 3:
    Code:
     WHERE TMD.C_SBU = '35'
      AND
           EXISTS
           (SELECT 0
             FROM  (VALUES
                       ( 2 , 4 )
                     , ( 3 , 6 ) , ( 3 , 7 ) , ( 3 , 1 )
                     , ( 4 , 2 ) , ( 5 , 2 ) , ( 6 , 2 )
                   ) p ( c , d )
             WHERE c = DAYOFWEEK(current_date)
               AND d = DAYOFWEEK(tmd.d_tmr_end)
           )
      AND  TMD.D_CREATED  > '2013-09-01'
      ...
    Last edited by tonkuma; 10-20-13 at 17:13.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Trickey.
    But, shorter than other examples.

    Example 4:
    Code:
     WHERE TMD.C_SBU = '35'
      AND
           MIN( MOD( DAYOFWEEK(current_date) , 7 ) , 4 ) - 2
         = MOD( MIN( NULLIF( DAYOFWEEK_ISO(tmd.d_tmr_end) , 4 ) , 4 ) + 1 , 4 )
      AND  TMD.D_CREATED  > '2013-09-01'
      ...

Tags for this Thread

Posting Permissions

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