Help with "selection" - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help with "selection"

Author

Topic

mcnika
Starting Member

6 Posts

Posted - 2009-08-18 : 08:19:25

Suppose I have a table

ID userName fyears tyears
1 nika 2007 2009
2 nika 2005 2009
3 nika 2000 2005
4 nika 1999 2000
5 nika 1996 1997

fyears is when the user nika started to work, tyears-ended
I need to select the total work experience, now it is =11

SELECT sum(tyears-fyears) FROM WE
I did in this way but it works inappropriately

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-18 : 08:33:43

how do you get 11 ?

KH
[spoiler]Time is always against us[/spoiler]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-18 : 08:40:43

I think , he has for id = 1 ( 2007 - 2009 ) and id = 2 (2005 - 2009) this case already count is there. so 2 years minus. that's why he want 11

mcnika
Starting Member

6 Posts

Posted - 2009-08-18 : 08:43:56

2005-2009 is 4 year work experience
2000-2005 is 5 year work experience
1999-2000 is 1 year work experience
1996-1997 is 1 year work experience

2007-2009 I don't calculate, because they are exists in 2005-2009

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-18 : 08:58:13

declare @ta table( ID int, userName varchar(32),fyears int,tyears int)
insert into @ta select
1, 'nika', 2007, 2009 union all select
2, 'nika', 2005, 2009 union all select
3, 'nika', 2000 ,2005 union all select
4, 'nika', 1999, 2000 union all select
5, 'nika', 1996, 1997

select username, count(distinct fyears+number) as val
from @ta inner join master..spt_values m on type = 'p' and number > 0
where fyears+number <= tyears
group by username

mcnika
Starting Member

6 Posts

Posted - 2009-08-18 : 13:32:41

Please explain what is written here
I don't understand "number" and "master..spt_values m on type = 'p' "

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-18 : 23:49:42

master..spt_values is system table , and it used for Loop purpose

select * from master..spt_values where type = 'p'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-19 : 00:02:31

[code]
declare @ta table( ID int, username varchar(32),fyears int,tyears int)
insert into @ta select
1, 'nika', 2007, 2009 union all select
6, 'nika', 2005, 2007 union all select
2, 'nika', 2005, 2009 union all select
3, 'nika', 2000 ,2005 union all select
4, 'nika', 1999, 2000 union all select
5, 'nika', 1996, 1997

select username, sum(tyears - fyears)
from @ta t
where not exists
(
select *
from @ta x
where x.username = t.username
and x.ID <> t.ID
and t.fyears >= x.fyears
and t.tyears <= x.tyears
)
group by username
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mcnika
Starting Member

6 Posts

Posted - 2009-08-19 : 02:37:05

Thanks a lot for your reply.

In this case
when table contains only this values
1, 'nika', 2007, 2009
2, 'nika', 2007, 2009

Returned an empty result row.
In other cases it works perfectly.
Please help with this too.
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-19 : 03:38:27

[code]

; with data
as
(
select ID = min(ID), username, fyears, tyears
from @ta
group by username, fyears, tyears
)
select username, sum(tyears - fyears)
from data t
where not exists
(
select *
from data x
where x.username = t.username
and x.ID <> t.ID
and t.fyears >= x.fyears
and t.tyears <= x.tyears
)
group by username
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mcnika
Starting Member

6 Posts

Posted - 2009-08-19 : 03:56:43

I don't understand this

; with data
as
(

......

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-19 : 04:34:55

hi, did u verify the query given by me

;with data as (
see commontableexpression then u can understand this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-19 : 04:42:07