T-SQL help

  • For the below SQL code, I need the output as:

    person dob Age

    Frank3/20/199023 Years

    Joey12/31/200012 Years

    Sue2/6/20121 Years

    Mary4/1/20131 Months

    Bil5/3/20121 Years

    Bob1/1/20134 Months

    Will10/1/20127 Months

    declare @people as table (

    person varchar(20),

    dob date

    )

    declare @SDate as date = '5/3/2013';

    insert into @people(person,dob)

    values ('Frank','3/20/1990'),

    ('Joey','12/31/2000'),

    ('Sue','2/6/2012'),

    ('Mary','4/1/2013'),

    ('Bil','5/3/2012'),

    ('Bob','1/1/2013'),

    ('Will','10/1/2012')

    SELECT person,

    dob,

    DATEDIFF(YEAR, dob, @SDate) -

    CASE WHEN((MONTH(dob)*100 + DAY(dob)) > (MONTH(@SDate)*100 + DAY(@SDate))) THEN 1 ELSE 0 END age

    FROM @people

  • The natural thing to do is calculate the age in years in a cte, then calculate teh age in months for cases where the age in years is 0. That's pretty straight forward:

    with years(person,dob,age_y) as

    (select person,

    dob,

    DATEDIFF(YEAR, dob, @SDate) -

    CASE WHEN((MONTH(dob)*100 + DAY(dob)) > (MONTH(@SDate)*100 + DAY(@SDate)))

    THEN 1

    ELSE 0

    END as age_y

    FROM @people)

    select person, dob,

    case when age_y > 0

    then LTRIM(STR(age_y)) + ' Yrs'

    else LTRIM(STR(DATEDIFF(MM,dob,@SDate) -

    case when DATEPART(DD,dob)>DATEPART(DD,@SDATE) then 1 else 0

    end)) + ' Mths'

    end as age

    from years

    Tom

  • Thank you!!

    But I also need to get the persons WHERE age BETWEEN '4 Mths' AND '12 Yrs'.

  • But I also need to get the persons [WHERE age BETWEEN '4 Mths' AND '12 Yrs'/code]

  • etirem (5/4/2013)


    Thank you!!

    But I also need to get the persons WHERE age BETWEEN '4 Mths' AND '12 Yrs'.

    Is this an additional requirement to only display individuals that are between 4 months old and 12 years old? I didn't see that in your original post.

  • Yes..This is an additional requirement. I need to retrieve the Persons based on the AGE range BETWEEN months and years.

  • This:

    declare @people as table (

    person varchar(20),

    dob date

    )

    declare @SDate as date = '5/3/2013';

    insert into @people(person,dob)

    values ('Frank','3/20/1990'),

    ('Joey','12/31/2000'),

    ('Sue','2/6/2012'),

    ('Mary','4/1/2013'),

    ('Bil','5/3/2012'),

    ('Bob','1/1/2013'),

    ('Will','10/1/2012')

    SELECT person,

    dob,

    case when datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end < 12

    then cast(datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end as varchar) + ' Months'

    else cast((datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end) / 12 as varchar) + ' Years'

    end as age

    FROM @people

    WHERE case when datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end < 12

    then datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end

    else ((datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end) / 12) * 12

    end between 4 and 144

  • This also should work..

    declare @people as table (

    person varchar(20),

    dob date

    )

    declare @SDate date

    set @SDate= '5/3/2013'

    insert into @people(person,dob)

    values ('Frank','3/20/1990'),

    ('Joey','12/31/2000'),

    ('Sue','2/6/2012'),

    ('Mary','4/1/2013'),

    ('Bil','5/3/2012'),

    ('Bob','1/1/2013'),

    ('Will','10/1/2012')

    SELECT person,

    dob,

    case when DATEDIFF(MONTH,dob,@SDate)/12>0 then convert(varchar,DATEDIFF(YEAR,dob,@SDate))+ ' Years' else convert(varchar,DATEDIFF(MONTH,dob,@SDate)) + ' Months' end as age

    FROM @people

    where DATEDIFF(MONTH,dob,@SDate)>=4 and DATEDIFF(YEAR,dob,@SDate)<=12

  • But I have different scenarios to get the persons retrieved based on the parameters for BEGIN AGE and END AGE in WHERE Clause:

    Like :

    SELECT person, dob,

    case when DATEDIFF(MONTH,dob,@SDate)/12>0 then convert(varchar,DATEDIFF(YEAR,dob,@SDate))+ ' Years' else convert(varchar,DATEDIFF(MONTH,dob,@SDate)) + ' Months' end as age

    FROM @people

    where --DATEDIFF(MONTH,dob,@SDate)>=1 and DATEDIFF(YEAR,dob,@SDate)<=7

    --Age BETWEEN @BEGINAge AND @ENDAge

    Query1: Age BETWEEN 1 Months AND 1 Years

    Query2: Age BETWEEN 4 Months AND 13 Years

    Query3: Age BETWEEN 1 Years AND 23 Years

  • etirem (5/6/2013)


    But I have different scenarios to get the persons retrieved based on the parameters for BEGIN AGE and END AGE in WHERE Clause:

    Like :

    SELECT person, dob,

    case when DATEDIFF(MONTH,dob,@SDate)/12>0 then convert(varchar,DATEDIFF(YEAR,dob,@SDate))+ ' Years' else convert(varchar,DATEDIFF(MONTH,dob,@SDate)) + ' Months' end as age

    FROM @people

    where --DATEDIFF(MONTH,dob,@SDate)>=1 and DATEDIFF(YEAR,dob,@SDate)<=7

    --Age BETWEEN @BEGINAge AND @ENDAge

    Query1: Age BETWEEN 1 Months AND 1 Years

    Query2: Age BETWEEN 4 Months AND 13 Years

    Query3: Age BETWEEN 1 Years AND 23 Years

    Is this the last change in your requirements?

    Also, looking above it looks like this even changes your last change for 4 months to 12 years.

  • Oh, and how is this information passed to query?

  • Sorry, this is the last requirement.

    The values are passed to the query from a drop-down list from SSRS report.

    Both drop down lists are sames values:

    1Months

    2Months

    .

    .

    12Months

    1Years

    2Years

    3Years

    .

    100Years

    BeginAge: 2Months EndAge: 48Years

  • Maybe this:

    declare @StartPeriod varchar(10),

    @EndPeriod varchar(10);

    select @StartPeriod = '4Months', @EndPeriod = '12Years';

    select

    cast(substring(@StartPeriod, 1, patindex('%[^0-9]%',@StartPeriod) - 1) as int) * case when patindex('%Months%',@StartPeriod) > 0 then 1 when patindex('%Years%',@StartPeriod) > 0 then 12 else 0 end,

    cast(substring(@EndPeriod, 1, patindex('%[^0-9]%',@EndPeriod) - 1) as int) * case when patindex('%Months%',@EndPeriod) > 0 then 1 when patindex('%Years%',@EndPeriod) > 0 then 12 else 0 end

    declare @people as table (

    person varchar(20),

    dob date

    )

    insert into @people(person,dob)

    values ('Frank','3/20/1990'),

    ('Joey','12/31/2000'),

    ('Sue','2/6/2012'),

    ('Mary','4/1/2013'),

    ('Bil','5/3/2012'),

    ('Bob','1/1/2013'),

    ('Will','10/1/2012')

    SELECT person,

    dob,

    case when datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end < 12

    then cast(datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end as varchar) + ' Months'

    else cast((datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end) / 12 as varchar) + ' Years'

    end as age

    FROM @people

    WHERE case when datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end < 12

    then datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end

    else ((datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end) / 12) * 12

    end between cast(substring(@StartPeriod, 1, patindex('%[^0-9]%',@StartPeriod) - 1) as int) * case when patindex('%Months%',@StartPeriod) > 0 then 1 when patindex('%Years%',@StartPeriod) > 0 then 12 else 0 end

    and cast(substring(@EndPeriod, 1, patindex('%[^0-9]%',@EndPeriod) - 1) as int) * case when patindex('%Months%',@EndPeriod) > 0 then 1 when patindex('%Years%',@EndPeriod) > 0 then 12 else 0 end

    select @StartPeriod = '3Years', @EndPeriod = '50Years';

    select

    cast(substring(@StartPeriod, 1, patindex('%[^0-9]%',@StartPeriod) - 1) as int) * case when patindex('%Months%',@StartPeriod) > 0 then 1 when patindex('%Years%',@StartPeriod) > 0 then 12 else 0 end,

    cast(substring(@EndPeriod, 1, patindex('%[^0-9]%',@EndPeriod) - 1) as int) * case when patindex('%Months%',@EndPeriod) > 0 then 1 when patindex('%Years%',@EndPeriod) > 0 then 12 else 0 end

    SELECT person,

    dob,

    case when datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end < 12

    then cast(datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end as varchar) + ' Months'

    else cast((datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end) / 12 as varchar) + ' Years'

    end as age

    FROM @people

    WHERE case when datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end < 12

    then datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end

    else ((datediff(month, dob, getdate()) - case when dateadd(month, datediff(month, dob, getdate()), dob) > getdate() then 1 else 0 end) / 12) * 12

    end between cast(substring(@StartPeriod, 1, patindex('%[^0-9]%',@StartPeriod) - 1) as int) * case when patindex('%Months%',@StartPeriod) > 0 then 1 when patindex('%Years%',@StartPeriod) > 0 then 12 else 0 end

    and cast(substring(@EndPeriod, 1, patindex('%[^0-9]%',@EndPeriod) - 1) as int) * case when patindex('%Months%',@EndPeriod) > 0 then 1 when patindex('%Years%',@EndPeriod) > 0 then 12 else 0 end

  • Try this (function posted below) the script:

    DECLARE @people AS TABLE

    (person VARCHAR(20)

    ,dob DATE)

    DECLARE @SDate AS DATE = '5/3/2013' ;

    INSERT INTO @people (person,dob)

    VALUES

    ('Frank','3/20/1990'),('Joey','12/31/2000'),('Sue','2/6/2012'),('Mary','4/1/2013'),

    ('Bil','5/3/2012'),('Bob','1/1/2013'),('Will','10/1/2012')

    SELECT

    *

    FROM

    @People p

    CROSS APPLY

    dbo.itvfCalcAge(p.dob,@SDate)

    CREATE FUNCTION dbo.itvfCalcAge

    (

    @StartDate DATETIME

    ,@EndDate DATETIME

    )

    RETURNS TABLE

    WITH SCHEMABINDING

    AS

    RETURN

    (

    -- CREATE DATE DEFAULTS

    WITH cteDefaults

    (

    CurrDate,

    CurrYrStartDate,

    FirstDOMCurrMonth,

    LastDOMCurrMonth,

    FirstDOMStartDate,

    LastDOMEndDate,

    FirstDOYCurrYr,

    LastDOYCurrYr

    )

    AS

    (

    SELECT

    GETDATE() AS CurrDate

    ,(CASE

    WHEN DATEDIFF(DAY,DATEADD(YEAR,(YEAR(GETDATE())-YEAR(@StartDate)),@StartDate),GETDATE()) > 0

    THEN DATEADD(YEAR,(YEAR(GETDATE())-YEAR(@StartDate)),@StartDate)

    ELSE

    DATEADD(YEAR,-1,DATEADD(YEAR,(YEAR(GETDATE())-YEAR(@StartDate)),@StartDate))

    END)

    AS CurrYrStartDate

    ,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) AS FirstDOMCurrMonth

    ,DATEADD(MILLISECOND,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)) AS LastDOMCurrMonth

    ,DATEADD(MONTH,DATEDIFF(MONTH,0,@StartDate),0) AS FirstDOMStartDate

    ,DATEADD(MILLISECOND,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,@EndDate)+1,0)) AS LastDOMEndDate

    ,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0) AS FirstDOYCurrYr

    ,DATEADD(MILLISECOND,-3,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE())+1,0)) AS LastDOYCurrYr

    ),

    -- DETERMINE THE MOST RECENT ANNIVERSARY DATE AND NEXT ANNIVERSARY DATE.

    -- THIS WILL BE USED FOR DETERMINING THE DECIMAL PART OF AGE.

    cteAnniversaries (LastAnniversary,NextAnniversary)

    AS

    (

    SELECT

    CurrYrStartDate AS LastAnniversary

    ,DATEADD(YEAR,1,CurrYrStartDate) AS NextAnniversary

    FROM

    cteDefaults

    ),

    -- DETERMINE THE NUMBER OF DAYS BETWEEN THE ANNIVERSARY DATES.

    -- THIS WILL TAKE INTO ACCOUNT LEAP YEARS

    cteDaysPerYear(NumDaysCurrYear,TotalYears,TotalMonths,TotalDays,RecentAnniversary)

    AS

    (

    SELECT

    DATEDIFF(DAY, a.LastAnniversary, a.NextAnniversary)

    AS NumDaysCurrYear

    ,DATEDIFF(YEAR,@StartDate,@EndDate)

    AS TotalYears

    ,DATEDIFF(MONTH,a.LastAnniversary,@EndDate)

    AS TotalMonths

    ,(DATEDIFF(DAY,DATEADD(DAY,-(DAY(@EndDate)-1),@EndDate),@EndDate))+1

    AS TotalDays

    ,a.LastAnniversary

    FROM

    cteAnniversaries a

    )

    -- NOW GET THE DECIMAL PART AND ADD TOGETHER

    SELECT

    (CASE

    WHEN

    dpy.TotalYears

    + CAST(DATEDIFF(DAY,dpy.RecentAnniversary,@EndDate) AS NUMERIC(6,2))

    / ISNULL(CAST(dpy.NumDaysCurrYear AS NUMERIC(6,2)),1) > 0

    THEN

    dpy.TotalYears

    + CAST(ROUND(CAST(DATEDIFF(DAY,dpy.RecentAnniversary,@EndDate) AS NUMERIC(6,2))

    / ISNULL(CAST(dpy.NumDaysCurrYear AS NUMERIC(6,2)),1),4) AS NUMERIC(6,4))

    ELSE 0

    END) AS TotalAge

    ,(CASE

    WHEN dpy.TotalYears < 0 THEN 0

    ELSE dpy.TotalYears

    END) AS Years

    ,(CASE

    WHEN dpy.TotalMonths < 0 THEN 0

    ELSE dpy.TotalMonths

    END) AS Months

    ,(CASE

    WHEN dpy.TotalDays < 0 THEN 0

    ELSE dpy.TotalDays

    END) AS Days

    FROM

    cteDaysPerYear AS dpy

    )

    GO

    Output

    <?xml version="1.0" ?>

    <RESULTS1>

    <RECORD>

    <person>Frank</person>

    <dob>1990-03-20</dob>

    <TotalAge>23.1205</TotalAge>

    <Years>23</Years>

    <Months>2</Months>

    <Days>3</Days>

    </RECORD>

    <RECORD>

    <person>Joey</person>

    <dob>2000-12-31</dob>

    <TotalAge>13.3370</TotalAge>

    <Years>13</Years>

    <Months>5</Months>

    <Days>3</Days>

    </RECORD>

    <RECORD>

    <person>Sue</person>

    <dob>2012-02-06</dob>

    <TotalAge>1.2356</TotalAge>

    <Years>1</Years>

    <Months>3</Months>

    <Days>3</Days>

    </RECORD>

    <RECORD>

    <person>Mary</person>

    <dob>2013-04-01</dob>

    <TotalAge>0.0877</TotalAge>

    <Years>0</Years>

    <Months>1</Months>

    <Days>3</Days>

    </RECORD>

    <RECORD>

    <person>Bil</person>

    <dob>2012-05-03</dob>

    <TotalAge>1.0000</TotalAge>

    <Years>1</Years>

    <Months>0</Months>

    <Days>3</Days>

    </RECORD>

    <RECORD>

    <person>Bob</person>

    <dob>2013-01-01</dob>

    <TotalAge>0.3342</TotalAge>

    <Years>0</Years>

    <Months>4</Months>

    <Days>3</Days>

    </RECORD>

    <RECORD>

    <person>Will</person>

    <dob>2012-10-01</dob>

    <TotalAge>1.5863</TotalAge>

    <Years>1</Years>

    <Months>7</Months>

    <Days>3</Days>

    </RECORD>

    </RESULTS1>

Viewing 14 posts - 1 through 13 (of 13 total)

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