We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

T-sql Code

Deepmath
Deepmath asked
on
Medium Priority
304 Views
Last Modified: 2012-05-07
Hi,

I have a table which looks like dis


StudentInfo table
Studentid       Month  Year      StartDate =====> all are in varchar format

876               8         2002     08/17/2002

876               9         2002     08/17/2002

876               10        2002     08/17/2002


876               12         2004     08/17/2002

1136               8         2004     08/17/2004

1136               9         2004     08/17/2004

I need to derive a new column (STAYMONTH)  which will have the Number of months for each student since the start date

Output Expected

StudentInfotable
Studentid       Month  Year      StartDate          StAYMONTH(New Column)

876               8         2002     08/17/2002         0

876               9         2002     08/17/2002         1

876               10        2002     08/17/2002        2


876               12         2004     08/17/2002       26  (difference between 12/2004- 10/2002)

1136               8         2004     08/17/2004       0

1136               9         2004     08/17/2004       0



Here's the logic to achieve this

@EM=Month(startdate)
@EY=Year(startdate)

@Current MOnth= Month
@Current Year= Year

@A=12*(@EY-1900)
@B=12*(@CurrentYear-1900)


The NEw column StayMOnth=@B-@A +(@Current MOnth  - @EM)

I am Uisng t-sql for the first time....How do i code this.




Comment
Watch Question

Commented:
I have not vetted your logic but assumming it is correct, you can do something like this-
Select *, (12*(Year(GetDate())-1900))-(12*(Year(StartDate)-1900))+(MONTH(GetDate())-Month(startdate)) as StayMonth
From StudentInfoTable
P
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
shouldn't student 876 be 28 months?
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
select abs(datediff(m, cast(month as varchar(2)) + '/1/' + cast(year as varchar(4)), dateadd(m, 0, startdate)))
from tablename
Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
worked 4 me
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.