Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 T-SQL help Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, May 03, 2013 4:06 PM
 Valued Member Group: General Forum Members Last Login: Friday, November 08, 2013 9:22 AM Points: 52, Visits: 242
 For the below SQL code, I need the output as:person dob AgeFrank 3/20/1990 23 YearsJoey 12/31/2000 12 YearsSue 2/6/2012 1 YearsMary 4/1/2013 1 MonthsBil 5/3/2012 1 YearsBob 1/1/2013 4 MonthsWill 10/1/2012 7 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 ageFROM @people `
Post #1449367
 Posted Friday, May 03, 2013 6:52 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 7:06 PM Points: 7,945, Visits: 8,369
 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'S iomadh doigh a th’ air cu a mharbhadh gun a thachdadh le ìme
Post #1449377
 Posted Saturday, May 04, 2013 10:15 AM
 Valued Member Group: General Forum Members Last Login: Friday, November 08, 2013 9:22 AM Points: 52, Visits: 242
 Thank you!!But I also need to get the persons `WHERE age BETWEEN '4 Mths' AND '12 Yrs'`.
Post #1449437
 Posted Monday, May 06, 2013 10:47 AM
 Valued Member Group: General Forum Members Last Login: Friday, November 08, 2013 9:22 AM Points: 52, Visits: 242
 But I also need to get the persons [code="sql"][WHERE age BETWEEN '4 Mths' AND '12 Yrs'/code]
Post #1449798
 Posted Monday, May 06, 2013 10:53 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 6:19 PM Points: 22,097, Visits: 29,037
 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.
Post #1449800
 Posted Monday, May 06, 2013 11:29 AM
 Valued Member Group: General Forum Members Last Login: Friday, November 08, 2013 9:22 AM Points: 52, Visits: 242
 Yes..This is an additional requirement. I need to retrieve the Persons based on the AGE range BETWEEN months and years.
Post #1449815
 Posted Monday, May 06, 2013 11:49 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 6:19 PM Points: 22,097, Visits: 29,037
 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 ageFROM @peopleWHERE 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`
Post #1449821
 Posted Monday, May 06, 2013 12:12 PM
 Valued Member Group: General Forum Members Last Login: Tuesday, December 03, 2013 2:38 PM Points: 54, Visits: 297
 This also should work..declare @people as table ( person varchar(20), dob date) declare @SDate dateset @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 ageFROM @people where DATEDIFF(MONTH,dob,@SDate)>=4 and DATEDIFF(YEAR,dob,@SDate)<=12
Post #1449831
 Posted Monday, May 06, 2013 1:41 PM
 Valued Member Group: General Forum Members Last Login: Friday, November 08, 2013 9:22 AM Points: 52, Visits: 242
 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 ageFROM @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 YearsQuery2: Age BETWEEN 4 Months AND 13 YearsQuery3: Age BETWEEN 1 Years AND 23 Years`
Post #1449862
 Posted Monday, May 06, 2013 2:07 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 6:19 PM Points: 22,097, Visits: 29,037
 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 ageFROM @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 YearsQuery2: Age BETWEEN 4 Months AND 13 YearsQuery3: 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.
Post #1449872

 Permissions