November 19, 2015 at 4:32 pm
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
November 19, 2015 at 5:02 pm
ken.mulvihill (11/19/2015)
I am working with a view in SQL Server 2012Calculating 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.
November 19, 2015 at 6:28 pm
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
November 19, 2015 at 9:26 pm
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
November 20, 2015 at 12:38 am
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 :-( ');
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 20, 2015 at 1:42 am
ken.mulvihill (11/19/2015)
LynnIn 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'
November 20, 2015 at 11:15 am
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)?
November 20, 2015 at 12:26 pm
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.
November 25, 2015 at 1:45 pm
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
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