Calculating student age as of one of two reference dates

Question

Calculating student age as of one of two reference dates

ken.mulvihill

SSC Rookie

Points: 34
More actions
November 19, 2015 at 4:32 pm

#313243

I am working with a view in SQL Server 2012
Calculating student age as of one of two reference dates
For fall term use reference date 1
For spring term use reference date 2
This seems very straight forward. I have separate queries using each date and they both work when run individually so the problem is not with the date calculations.
I have problems when I try to combine these into an If statement. I also tried a CASE statement.
Here is my latest rendition:
[font="Courier New"]Declare @RefDate1 varchar(5) = '9/15/'
Declare @RefDate2 varchar(5) = '2/25/'
Select
Birthdate,
Record_Period,
Left(Record_Period,1) AS RecordTerm,
RIGHT(Record_Period,4) AS RecordYear,
If Left(Record_Period,1) <> '4'
BEGIN
DATEDIFF(YY, Birthdate, @RefDate1 + RIGHT(Record_Period,4)) AS Years,
-- Add the Years from above to the birthdate as if the DOB was this year
DATEADD(YY, DATEDIFF(YY, Birthdate, @RefDate1 + RIGHT(Record_Period,4)), Birthdate) AS New_DOB,
-- Now compare New_DOB to ReferenceDate
DATEDIFF(YY, Birthdate, @RefDate1 + RIGHT(Record_Period,4)) -
Case
When DATEADD(YY, DATEDIFF(YY, Birthdate, @RefDate1 + RIGHT(Record_Period,4)), Birthdate)
>@RefDate1 + RIGHT(Record_Period,4) Then 1
Else 0
End AS SURDS_Age
END
Else
BEGIN
-- Add the Years from above to the birthdate as if the DOB was this year
DATEADD(YY, DATEDIFF(YY, Birthdate, @RefDate2 + RIGHT(Record_Period,4)), Birthdate) AS New_DOB,
-- Now compare New_DOB to ReferenceDate
DATEDIFF(YY, Birthdate, @RefDate2 + RIGHT(Record_Period,4)) -
Case
When DATEADD(YY, DATEDIFF(YY, Birthdate, @RefDate2 + RIGHT(Record_Period,4)), Birthdate)
>@RefDate2 + RIGHT(Record_Period,4) Then 1
Else 0
End AS SURDS_Age
END
End if
From dbo.View[/font]
Thanks very much
Ken
May 12, 2020 at 9:46 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

ken.mulvihill (11/19/2015)
I am working with a view in SQL Server 2012
Calculating student age as of one of two reference dates
For fall term use reference date 1
For spring term use reference date 2
This seems very straight forward. I have separate queries using each date and they both work when run individually so the problem is not with the date calculations.
I have problems when I try to combine these into an If statement. I also tried a CASE statement.
Here is my latest rendition:
[font="Courier New"]Declare @RefDate1 varchar(5) = '9/15/'
Declare @RefDate2 varchar(5) = '2/25/'
Select
Birthdate,
Record_Period,
Left(Record_Period,1) AS RecordTerm,
RIGHT(Record_Period,4) AS RecordYear,
If Left(Record_Period,1) <> '4'
BEGIN
DATEDIFF(YY, Birthdate, @RefDate1 + RIGHT(Record_Period,4)) AS Years,
-- Add the Years from above to the birthdate as if the DOB was this year
DATEADD(YY, DATEDIFF(YY, Birthdate, @RefDate1 + RIGHT(Record_Period,4)), Birthdate) AS New_DOB,
-- Now compare New_DOB to ReferenceDate
DATEDIFF(YY, Birthdate, @RefDate1 + RIGHT(Record_Period,4)) -
Case
When DATEADD(YY, DATEDIFF(YY, Birthdate, @RefDate1 + RIGHT(Record_Period,4)), Birthdate)
>@RefDate1 + RIGHT(Record_Period,4) Then 1
Else 0
End AS SURDS_Age
END
Else
BEGIN
-- Add the Years from above to the birthdate as if the DOB was this year
DATEADD(YY, DATEDIFF(YY, Birthdate, @RefDate2 + RIGHT(Record_Period,4)), Birthdate) AS New_DOB,
-- Now compare New_DOB to ReferenceDate
DATEDIFF(YY, Birthdate, @RefDate2 + RIGHT(Record_Period,4)) -
Case
When DATEADD(YY, DATEDIFF(YY, Birthdate, @RefDate2 + RIGHT(Record_Period,4)), Birthdate)
>@RefDate2 + RIGHT(Record_Period,4) Then 1
Else 0
End AS SURDS_Age
END
End if
From dbo.View[/font]
Thanks very much
Ken

First, you can't use an IF in a SELECT clause, you do need to use the CASE.

Second, it could be that I am just tired, but the code doesn't quite make sense to me. Could you step through a couple of examples that demonstrates what you are trying to accomplish with this query? Be sure to show beginning state and ending state for both reference dates using the same student's birth dates.

ken.mulvihill SSC Rookie Points: 34 More actions · Answer 2

Lynn

I should have known better re: an IF. I was getting desperate.

The following query works on it's own

The goal is to find out how old a student was at the time the registered for class. We use a cut-off date (RefDate) for Fall registration 9/15 and one for Spring registration 2/15.

Step one: Subtract birth year from the year they registered. That would be the students age except that they may not have had a birthday as of 9/15. The # of years old would be wrong.

Step two: Figure out the birthday as if they were born this year.

Step three: Is their birthday this year (New DOB) after the reference date? If so, that means they haven't had a birthday yet so subtract 1 year from the # of year. If not, then subtract 0 (nothing) from the # of years and you have the correct date.

So I have two queries that work for Fall Term and one fro Spring Term. I would like to combine them to first determine which term are we in, and use the appropriate reference date.

In a nutshell this is what I was trying to do.

Case

When term = fall

Then run the query that uses reference date 9/15

Else run the query that uses reference date 2/15

End AS Age

But it didn't work and by now I don't remember the errors. I hope this helps.

Thanks

Ken

[font="Courier New"]Declare @RefDate1 varchar(5) = '9/15/'

Select

Birthdate,

Student_ID_Number,

Record_Period,

Left(Record_Period,1) AS RecordTerm,

RIGHT(Record_Period,4) AS RecordYear,

DATEDIFF(YY, Birthdate, @RefDate1 + RIGHT(Record_Period,4)) AS Years,

-- Add the Years from above to the birthdate as if the DOB was this year

DATEADD(YY, DATEDIFF(YY, Birthdate, @RefDate1 + RIGHT(Record_Period,4)), Birthdate) AS New_DOB,

-- Now compare New_DOB to ReferenceDate

DATEDIFF(YY, Birthdate, @RefDate1 + RIGHT(Record_Period,4)) -

Case

When DATEADD(YY, DATEDIFF(YY, Birthdate, @RefDate1 + RIGHT(Record_Period,4)), Birthdate)

>@RefDate1 + RIGHT(Record_Period,4) Then 1

Else 0

End AS SURDS_Age

From dbo.vDemographics

Where Record_Period= '22014'

[/font]

BirthdateRecord_Period Record_Term Record_Year #Years New_DOB SURDS_Age

10/12/1986 22014 2 2014 28 10/12/2014 27

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Would it be easier to add a minimal required age to student's birthday and see if the returned date (date when the student reach the required age) <= of the cut-off date?

_____________
Code for TallyGenerator

Phil Parkin SSC Guru Points: 246992 More actions · Answer 4

First, you can't use an IF in a SELECT clause, you do need to use the CASE.

A possible alternative is iff(), which can be used in a SELECT:

select iif(datename(weekday, getdate()) = 'Friday', 'Hurray it''s Friday', 'Dull. It''s not Friday :-( ');

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 5

ken.mulvihill (11/19/2015)
Lynn
In a nutshell this is what I was trying to do.
Case
When term = fall
Then run the query that uses reference date 9/15
Else run the query that uses reference date 2/15
End AS Age

Use cross apply to define and compute intermediate variables for further complex computations.

Select

-- some stuff which depends on x.RefDate1

From dbo.vDemographics

CROSS APPLY (SELECT RefDate1 = CASE term WHEN 'fall' THEN '9/15' ELSE '2/15' END) AS x

Where Record_Period= '22014'

ken.mulvihill SSC Rookie Points: 34 More actions · Answer 6

Thanks everyone for responding.

How to calculate the age is not the issue. I know there are many was to do so, but somehow I got my head wrapped around this one and it works. As I said, a query with either refdate1 or refdate2 returns exactly what I need.

Phil - I have use an iif before but switched to CASE since they are easier to read. I'll have a look at it again.

Serg - I am not familiar with APPLY, but the code snippet looks good. Now once I have defined the variable refdate1, would I then continue with my DATEDIFF stuff (technical term)?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

I'm not sure if I should have read all the replies before, but this seems like a good option. I started by using a CASE for each column, but this is a lot cleaner.

SELECT

v.Birthdate,

v.Record_Period,

Left(v.Record_Period,1) AS RecordTerm,

RIGHT(v.Record_Period,4) AS RecordYear,

DATEDIFF(YY, v.Birthdate, x.RefDate + RIGHT(v.Record_Period,4)) AS Years,

-- Add the Years from above to the birthdate as if the DOB was this year

DATEADD(YY, DATEDIFF(YY, v.Birthdate, v.RefDate + RIGHT(v.Record_Period,4)), v.Birthdate) AS New_DOB,

-- Now compare New_DOB to ReferenceDate

DATEDIFF(YY, v.Birthdate, x.RefDate + RIGHT(v.Record_Period,4))

- CASE WHEN DATEADD(YY, DATEDIFF(YY, v.Birthdate, x.RefDate + RIGHT(v.Record_Period,4)), v.Birthdate) > x.RefDate + RIGHT(v.Record_Period,4)

THEN 1

ELSE 0 END AS SURDS_Age

FROM dbo.View v

CROSS APPLY ( SELECT CASE WHEN Left(v.Record_Period,1) <> '4'

THEN @RefDate1

ELSE @RefDate2 END) AS x(RefDate)

To understand APPLY, check the following articles:

http://www.sqlservercentral.com/articles/APPLY/69953/

http://www.sqlservercentral.com/articles/APPLY/69954/

As a summary, APPLY is like a JOIN on steroids.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ken.mulvihill SSC Rookie Points: 34 More actions · Answer 8

Luis - Thanks for the clarification of the APPLY command. The links were very helpful. I also appreciate the link to how to post code for a better response. I am not sure I understood, but I will figure it out before I post again.

Thanks