Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2011
    Posts
    5

    Unanswered: calculate Age Sql 2005

    Hi

    I want to calculate age between two dates.
    E.g
    ID DOB
    22 01-01-2009
    23 04-04-2010
    34 05-05-2010

    Reporting period between 01-04-2011 and 30-06-2011.

    Can someone assist me, I want to know how clients are age 1 between the reporting period.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by HUS View Post
    I want to know how clients are age 1 between the reporting period.
    huh?

    could you please rephrase the question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Convert dates to yyyymmdd.
    if startdate-dob <10000 and enddate -dob >10000 then dob is within range.
    You may have to test for dob on startdate and enddate.
    Last edited by kitaman; 07-13-11 at 17:53. Reason: reversed < and >

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  ptp  20110713  Calculate the delta between two dates
    
    CREATE FUNCTION dbo.DateDelta(@pd1 DATETIME
    ,  @pd2 DATETIME)
    RETURNS @retDeltas TABLE
    (
       years		INT		NOT NULL
    ,  months		INT		NOT NULL
    ,  days			INT		NOT NULL
    )
    AS
    BEGIN
       DECLARE @iMonths		INT
       SET @iMonths = DateDiff(MONTH, @pd1, @pd2)
       
       SET @iMonths = @iMonths 
    +     CASE
             WHEN DateAdd(month, @iMonths, @pd1) < @pd2
                THEN CASE WHEN @pd1 < @pd2 THEN 0 ELSE 1 END
                ELSE CASE WHEN @pd1 < @pd2 THEN -1 ELSE 0  END
          END
          
       INSERT INTO @retDeltas
          SELECT @iMonths / 12
    ,        @iMonths % 12
    ,        DateDiff(DAY, DateAdd(month, @iMonths, @pd1), @pd2)
    
       RETURN   
    END
    GO
    
    --  Prove we got it correct for past dates
    
    SELECT *
       FROM (SELECT DateAdd(day, -5 * number, GetDate()) AS d
          FROM master.dbo.spt_values
          WHERE  'p' = type) AS z1
       CROSS APPLY dbo.DateDelta(d, GetDate())
       
    --  Prove we got it correct for future dates
    
    SELECT *
       FROM (SELECT DateAdd(day, -5 * number, GetDate()) AS d
          FROM master.dbo.spt_values
          WHERE  'p' = type) AS z1
       CROSS APPLY dbo.DateDelta(GetDate(), d)
    -PatP
    Last edited by Pat Phelan; 07-13-11 at 20:16. Reason: Cleaned up delta comp and formatting
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Pat Phelan View Post
    Code:
    SELECT *
       FROM (SELECT DateAdd(day, -5 * number, GetDate()) AS d
          FROM master.dbo.spt_values
          WHERE  'p' = type) AS z1
       CROSS APPLY dbo.DateDelta(d, GetDate())
    It's been a long time since I last saw a CROSS APPLY used, in this forum. It is an operator that I have never used before. I did a Google search on it, but I find the explanations confusing. Can you explain it a bit or direct me to a good source of information?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Basically, cross apply is the same as an inner join (and as such it should be called inner apply instead). Let me try to explain:

    We have a function returning a recordset, and we want to call it for each row in a table with one of the table columns as parameter, and join the row data and the function result with an "inner join". This is what the cross apply operator does.

    Similarily, we have an outer apply which does the same, but always returns the data from the table, even if the function does not return anything (like an outer join).

    In this example, we have a "table" z1 with a column d, and we want to join (that is cross apply) with the function datedelta, using this column as the first parameter.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Wim View Post
    It's been a long time since I last saw a CROSS APPLY used, in this forum. It is an operator that I have never used before. I did a Google search on it, but I find the explanations confusing. Can you explain it a bit or direct me to a good source of information?
    The Microsoft doc gives what I think is a good description at Using APPLY

    The paragraph just before the code sample says:
    Quote Originally Posted by SQL BOL
    There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Pat, Roac,

    Thank you for your responses.

    When I wrote
    but I find the explanations confusing
    I was referring to the very paragraph you (Pat) quoted, saying
    what I think is a good description
    This can only mean that you are way more intelligent than me. I'm saying this loud and clear, just in case someone out there was still in doubt.

    The explanation of Roac is more to my level than that of BOL.
    Anyway, I'll just have to play with it a bit before I'll be able to grok it.

    Thank you guys.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ooops, my bad then!

    If you think about the table valued function in terms of the result set that it retourns then a CROSS APPLY on the function behaves like an INNER JOIN on the result set. Following the same line of reasoning, an OUTER APPLY behaves like a FULL OUTER JOIN on the result set.

    Do I get at least a sip of beer this time ???

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

  10. #10
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by Wim View Post
    When I wrote I was referring to the very paragraph you (Pat) quoted, saying This can only mean that you are way more intelligent than me. I'm saying this loud and clear, just in case someone out there was still in doubt.
    I don't think so. The ability to understand technical documentation/text has nothing to do with intelligence, but has to be learned. As Microsoft Certified Trainer I've seen brilliant minds struggling with technical documentation.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Pat Phelan View Post
    Do I get at least a sip of beer this time ???
    Definitely! You and Roac deserve a whole pint of it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by roac View Post
    I don't think so. The ability to understand technical documentation/text has nothing to do with intelligence, but has to be learned. As Microsoft Certified Trainer I've seen brilliant minds struggling with technical documentation.
    I feel my self esteem coming back my way. Extra beer for Roac!
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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