Need to calculate number of years

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need to calculate number of years

Author

Topic

damuchinni
Starting Member

29 Posts

Posted - 2007-05-22 : 03:19:16

Hi,

dis is damodar
Here i was troubled by this issue plzz provide me the way to fix it.

this is a applicant table contains following columns

case 1:

applicnt_id seq_num from_year(YYYY) to_Year(YYYY) is_primary_ind
1 1 2007 Null Y
1 2 2006 2007 Null
1 3 2004 2005 Null
1 4 2002 2004 Null
1 5 2002 2002 Null
1 6 2000 2001 Null

case 2:

applicnt_id seq_num from_year(YYYY) to_Year(YYYY) is_primary_ind
1 1 2007 Null Y
1 2 2006 2007 Null
1 3 2004 2006 Null
1 4 2002 2004 Null
1 5 2002 2002 Null
1 6 2000 2002 Null

case 3:

applicnt_id seq_num from_year(YYYY) to_Year(YYYY) is_primary_ind
1 1 2007 Null Y
1 2 2000 2007 Null
1 3 1997 2000 Null
1 4 1990 1997 Null

case 4:

applicnt_id seq_num from_year(YYYY) to_Year(YYYY) is_primary_ind
1 1 1980 Null Y

actual the issue is that i want to calculate 7 years of address history, that may be greater than or equal to 7 years of address history. Plzz provide me a query that satisfy all the cases that i mentioned above.

there in the table i specified column is_primery_ind, if the aplicnt enters current address check bos is checked then the that particular column will be inserted by 'Y' otherwise as NULL . bt every aplicant has to enter first current address then only he will be allowed to enter for there records.

thanks & regrads
damodar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 04:07:25

quote:
i want to calculate 7 years of address history

Can you explain more on this ? Don't quite understand what you want here. For example Case 1, you have 6 rows of records there. What do you expect the query to return ?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-22 : 04:56:59

Explain it clearly else give the required output u want...

damuchinni
Starting Member

29 Posts

Posted - 2007-05-22 : 05:05:26

Hi khtan,

from the above case i want past 7 years of address, the condition is that aplicant should enter 7 yesrs of address in the table. i think u was blocked at , ther is no address column rite, only fields that we have to depent is above what i mentioned.

"We do not need to identify address gaps other than by comparing the years - so as long as a year is not skipped then there is no gap."

for example

aplicnt_id seq_num from_year to_year primary_ind

1 1 2007 null Y
1 2 2005 2007 null
1 3 1998 2000 null

above there i skipped the year 1998 to 2000. in this case the applicnt need nt be allowed to next or previous page.

i think i provided full data .

damuchinni
Starting Member

29 Posts

Posted - 2007-05-22 : 23:47:34

Hi gud morning all,

Plzz Post me Answer for the above issue

Regrads & thanks

Damodar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 23:54:18

quote:
i want to calculate 7 years of address history, that may be greater than or equal to 7 years of address history

How do you mean by "calcualte 7 years of address history" ? What to calculate ? Calculate what ?

quote:
i think i provided full data.

But you have not tell us what and how do you want the output / result like ?

can you explain what will be the expected result for this case

quote:

case 1:

applicnt_id seq_num from_year(YYYY) to_Year(YYYY) is_primary_ind
1 1 2007 Null Y
1 2 2006 2007 Null
1 3 2004 2005 Null
1 4 2002 2004 Null
1 5 2002 2002 Null
1 6 2000 2001 Null

damuchinni
Starting Member

29 Posts

Posted - 2007-05-23 : 00:04:55

Hi Khtan,

Actually the problem is calcuating of applicant address past 7 years . our client requirment is that there should be any gap betwenn to consequtive to years that is if first records have this way

2005 to 2007 second as
2000 to 2003

in the above there is a gap btween two records i.e from year of firest record 2005 and to year of second record is 2003 there is two years of gap. so we should nt consider that case

if it is in this case i.e

2005 to 2007
2000 to 2004

we can consider
i hope u under stand .

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-05-23 : 00:14:37

Hi damodar,
can u provide the desired output and inputs clearly.

damuchinni
Starting Member

29 Posts

Posted - 2007-05-23 : 00:26:32

Hi PeterNeo,

the inputs are

case 1:

applicnt_id seq_num from_year(YYYY) to_Year(YYYY) is_primary_ind
1 1 2007 Null Y
1 2 2006 2007 Null
1 3 2004 2005 Null
1 4 2002 2004 Null
1 5 2002 2002 Null
1 6 2000 2001 Null

case 2:

applicnt_id seq_num from_year(YYYY) to_Year(YYYY) is_primary_ind
1 1 2007 Null Y
1 2 2006 2007 Null
1 3 2004 2006 Null
1 4 2002 2004 Null
1 5 2002 2002 Null
1 6 2000 2002 Null

case 3:

applicnt_id seq_num from_year(YYYY) to_Year(YYYY) is_primary_ind
1 1 2007 Null Y
1 2 2000 2007 Null
1 3 1997 2000 Null
1 4 1990 1997 Null

case 4:

applicnt_id seq_num from_year(YYYY) to_Year(YYYY) is_primary_ind
1 1 1980 Null Y

if we calcuate max(from_year)-min(from_year)=>7 and "so as long as a year is not skipped then there is no gap". then we can say that he has the 7 years of address history. and in the above example theere is no sipping of years.

i think u understood????

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-23 : 03:00:31

this query will give the application id having 7 years history and having missed year, if no missed year gives no row.

declare @t table (applicnt_id int, seq_num int, from_year int,to_Year int, is_primary_ind varchar(1))
Insert @t
select 1,1,2007,Null,'Y' union
select 1,2,2006,2007,Null union
select 1,3,2004,2005,Null union
select 1,4,2002,2004,Null union
select 1,5,2002,2002,Null union
select 1,6,2000,2001,Null union
select 2,1,1998, 2000, NULL

Select c.applicnt_id , 'Year Missing' = cast(c.to_year as varchar(4)) + '-' + cast(d.from_year as varchar(4))
from @t c join
(Select a.applicnt_id, seq_num, from_year, to_Year, is_primary_ind
from @t a left outer join
(Select applicnt_id from @t group by applicnt_id
having Max(to_year) - Min(from_year) >= 7) as b on a.applicnt_id = b.applicnt_id) as d
on c.applicnt_id = d.applicnt_id and c.seq_num = d.seq_num +1 and c.to_year - d.from_year <> 0

damuchinni
Starting Member

29 Posts

Posted - 2007-05-23 : 04:43:29

Hi pbguy,

Thanks a lot. i dnt checked yet. if it fine i will intimate u.

Thanks
Damodar

damuchinni
Starting Member

29 Posts

Posted - 2007-05-24 : 01:32:02

hi pbguy,

Im sorry to say this, from ur query im getting the missing year accordign to ur example there is no skipped year. bt it showing skipped year. will u plzz change the query

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-24 : 01:42:00

post some data and required output please

damuchinni
Starting Member

29 Posts

Posted - 2007-05-24 : 01:50:22

Hi pbguy

here im posting u an example

1 1 2007 Null Y
1 2 2006 2007 Null
1 3 2004 2005 Null
1 4 2002 2004 Null
1 5 2002 2002 Null
1 6 2000 2001 Null
1 7 1988 2000 null

in the above example there is no gap of years. u may ask that when compared 2 and 3 record there is the gap between 2005 to 2006. bt applcnt may think of this way jan 2006 to sone 2007 and 2004 to december 2005 there fore there is no gap.

and one more thing is that max(from_year)-min(from_year)>=7 and there should nt be any gapps while entering the years to navigate to next page

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-24 : 02:10:53

i am leaving if the gap is 0 or 1...try this

Select c.applicnt_id , 'Year Missing' = cast(c.to_year as varchar(4)) + '-' + cast(d.from_year as varchar(4))
from @t c join
(Select a.applicnt_id, seq_num, from_year, to_Year, is_primary_ind
from @t a left outer join
(Select applicnt_id from @t group by applicnt_id
having Max(to_year) - Min(from_year) >= 7) as b on a.applicnt_id = b.applicnt_id) as d
on c.applicnt_id = d.applicnt_id and c.seq_num = d.seq_num +1 and abs(c.to_year - d.from_year) > 1

damuchinni
Starting Member

29 Posts

Posted - 2007-05-24 : 08:43:56

Hi Pbguy,

Ur thinking is excelent, morvolus. bt there was a minor issue again. i.e if at all applicant delete a record then seq_num will change and then that query will die.

can u plzz change according to rowid of a particular roe in a table so that we can reach what we r thinking, im using sql 2000. i think in oracle and sql 2000 we have a separate functionas as gterow_number() for sql 2005 and physudo column as row_id for every table i dnt about sql 2000.

so will u plzz change the query.

Thanks and regrads

Damodar

damuchinni
Starting Member

29 Posts

Posted - 2007-05-25 : 01:35:40

Hi All,

just say the idea in what way have to go that i can do it

THanks & Regrads
Damodar

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-25 : 01:52:10

Damothar...
What i feel is u can do this using a stored procedure..creation of sequence number is not a problem..for each applicant id move the records from original table to a temporary table and process it.

below query will help to move the data of applicant_id 1 to a temporary table..

declare @t table (applicnt_id int, seq_num int, from_year int,to_Year int, is_primary_ind varchar(1))
Insert @t
select 1,1,2007,Null,'Y' union
select 1,2,2006,2007,Null union
select 1,3,2004,2004,Null union
select 1,4,2002,2004,Null union
select 1,5,2002,2002,Null union
select 1,6,2000,2001,Null union
select 1,7,1998, 2000, NULL

declare @tt table(seq_id int identity(1,1), applicnt_id int, seq_num int, from_year int,to_Year int, is_primary_ind varchar(1))

insert @tt(applicnt_id, seq_num, from_year, to_year, is_primary_ind) select
applicnt_id, seq_num, from_year, to_year, is_primary_ind from @t where applicnt_id =1

select * from @tt

damuchinni
Starting Member

29 Posts

Posted - 2007-05-28 : 01:29:20

hi pbguy,

with out using stored peocedure cant make it fine.

can v modify that query without using sequence number.

Thanks & regrades

Damodar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources