Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Oct 2009
    Posts
    93

    Unanswered: 5 day expired query

    Hi folks,

    I have a datefield (ReturnDate) and i need to run a report to find all entries where the datefield (ReturnDate) has expired 5 days or more and exclude weekends in the count.

    Any help appreciated.
    Sully

  2. #2
    Join Date
    Oct 2009
    Posts
    93
    I tried this but no joy

    WHERE (ReturnDate <= DATEADD(dd, 5, ReturnDate))

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    An awful lots depends on what...
    Quote Originally Posted by sullyman View Post
    has expired 5 days or more and exclude weekends in the count.
    ...means to your business. I'd start with:
    Code:
    SELECT d, DATEADD(day, CASE DatePart(dw, d)
       WHEN 1 THEN -6
       WHEN 7 THEN -5
       ELSE -7
       END, DATEADD(day, DateDiff(day, 0, d), 0))
       FROM (SELECT CONVERT(DATETIME, '2011-01-02') AS d
          UNION ALL SELECT '2011-01-03'
          UNION ALL SELECT '2011-01-04'
          UNION ALL SELECT '2011-01-05'
          UNION ALL SELECT '2011-01-06'
          UNION ALL SELECT '2011-01-07'
          UNION ALL SELECT '2011-01-08') AS z
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2009
    Posts
    93
    Pat, you're confusing me


    I got this working for the 5 day expiry part.

    WHERE (DATEDIFF(Day, ReturnDate, GETDATE()) > 5)


    This may be sufficient as trying to understand your query will give me a headache

  5. #5
    Join Date
    Oct 2009
    Posts
    93
    What you think Pat. Would that code be ok ?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by sullyman View Post
    What you think Pat. Would that code be ok ?
    It doesn't consider weekends, so on Monday it will return anything that was due after Wednesday instead of backing up five business days to the prior Monday.

    Run my query, see if the results look like what you want. It backs up five business days from whatever day is being shown in the "D" column. See if that is the "right" answer for your business.

    If that produces the correct due date, then I'll mangle it into a query for you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Oct 2009
    Posts
    93
    Thanks Pat. I ran your query (exactly as shown) and i get a d and z column results as shown

    d
    2/1/2011
    3/1/2011
    4/1/2011
    5/1/2011
    6/1/2011
    7/1/2011
    8/1/2011

    z
    27/12/2010
    27/12/2010
    28/12/2010
    29/12/2010
    30/12/2010
    31/12/2010
    03/01/2011

    Do you want me to switch d with my field "ReturnDate" ? I'm a bit confused to be honest Pat
    Last edited by sullyman; 01-14-11 at 18:59.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does what my query returns match what you expect for:
    Quote Originally Posted by sullyman View Post
    has expired 5 days or more and exclude weekends in the count.
    If my sample query returns the correct dates for your business problem, then I'll construct a query based on your tables to get that data for you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    On second thought, try this modified query instead:
    Code:
    SELECT d, DATEADD(day, CASE DatePart(dw, d)
       WHEN 1 THEN -6
       WHEN 7 THEN -5
       ELSE -7
       END, DATEADD(day, DateDiff(day, 0, d), 0))
       FROM (SELECT DATEADD(day, -number, GETDATE()) AS D
          FROM master.dbo.spt_values
          WHERE  'P' = [type]
             AND number BETWEEN 0 AND 90) AS z
    The process is identical, but it returns a larger span of dates so you can more clearly see the "stutter" that is induced by excluding weekends from the count.

    I THINK that this is what you want, but I'm not certain. I want you to confirm that I've got the "cut off days" correct before I twist it up into a query for you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Oct 2009
    Posts
    93
    thanks Pat... I'll browse google instead of being your Friday nite project Your queries are too good for me to understand

  11. #11
    Join Date
    Oct 2009
    Posts
    93
    Pat,

    my table is schecks
    my datefield is returndate

    incase you want to help me and put them into your query and understand it

    I only need a simple report that shows expired items. It is not a mission critcal system or report etc.

  12. #12
    Join Date
    Oct 2009
    Posts
    93
    This is nearly working Pat. The second result is a bit off slightly.

    SELECT ReturnDate, DATEADD(day, CASE DatePart(dw, ReturnDate) WHEN 1 THEN - 6 WHEN 7 THEN - 5 ELSE - 7 END, DATEADD(day, DATEDIFF(day, 0,
    ReturnDate), 0)) AS Expr1
    FROM dbo.SChecks

    8/1/2011 - 3/1/2011
    4/1/2011 - 28/12/2010

    e.g.
    If the item's return date is 7/1/2011 - it should be listed on the report on the 14/1/2011
    If the item's return date is 4/1/2011 - it should be listed on the report on the 11/1/2011

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not asking you to understand my query (yet), just to run it and verify that the dates it spits out match what you want. The problem is that English isn't good for specifying exactly what you want done in terms of iterative set processing and date management, so we could talk for ages and might never understand one another.

    My query produces a result that says (in essence), on date "d" the cutoff date should be one column to the right. Every day has a cut off date that shows when things get "interesting" for your query, and I think that ought to be five weekdays earlier.

    By giving you this query, you can actually SEE what I think you want. You can then say "Yes, that's right" or "No, date YYYY-MM-DD should have a cut-off date of YYYY-MM-DD". Once I can produce a query that gives the dates that you really want, then I can quickly and easily twist that up into the query that you need.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What day's report should be the first to show items due on the first (a Saturday) ?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Oct 2009
    Posts
    93
    Thanks Pat.

    The cut off date should be 5 days after the Return date.

    It's only a simple report to show if items have not been checked after 5 days. Nothing critical as i mentioned above.

    Something like the following... Hmmm is this the answer?

    Select * from schecks where returndate = (todaysdate-returndate(exclude weekends) = >=5))
    Last edited by sullyman; 01-14-11 at 19:44.

Posting Permissions

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