asked on

T-sql Code

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.

pssandhu

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

chapmandew

shouldn't student 876 be 28 months?

chapmandew

select abs(datediff(m, cast(month as varchar(2)) + '/1/' + cast(year as varchar(4)), dateadd(m, 0, startdate)))
from tablename

ASKER CERTIFIED SOLUTION

Rob Farley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Deepmath

ASKER

worked 4 me