Prev: Am I supposed to see the OUTPUT parameter's value in SQL Mgmt Stud
Next: Whoever posted this
From: M.K on 3 Mar 2010 02:15
Table 'TEST' has information on 4 staff members. For Staff_ids 1 and 4 there
is no missing (brake) year while for staff_id 2 and 3 there is brake in
years.
Can someone help me with a query to return only the staff_ids for which one
or more years are missing in between.

CREATE TABLE [dbo].[TEST](
[Staff_id] [int] NOT NULL,
[Year] [int] NOT NULL
) ON [PRIMARY]

GO

insert into TEST
select 1,2005
insert into TEST
select 1,2006
insert into TEST
select 1,2007
insert into TEST
select 1,2008
insert into TEST
select 2,2005
insert into TEST
select 2,2007
insert into TEST
select 2,2008
insert into TEST
select 3,2005
insert into TEST
select 3,2008
insert into TEST
select 3,2009
insert into TEST
select 4,2005
insert into TEST
select 4,2006
insert into TEST
select 4,2007
insert into TEST
select 4,2008

From: Uri Dimant on 3 Mar 2010 04:25
SELECT [Staff_id],[Year] FROM

(

SELECT *, MAX([Staff_id])OVER ()mx

,COUNT (*) OVER (PARTITION BY [Staff_id]) AS cn

FROM TEST[Staff_id]

)AS D WHERE cn<mx









"M.K" <mianksaeed(a)yahoo.com> wrote in message
news:%23DmJWFquKHA.4908(a)TK2MSFTNGP06.phx.gbl...
> Table 'TEST' has information on 4 staff members. For Staff_ids 1 and 4
> there is no missing (brake) year while for staff_id 2 and 3 there is brake
> in years.
> Can someone help me with a query to return only the staff_ids for which
> one or more years are missing in between.
>
> CREATE TABLE [dbo].[TEST](
> [Staff_id] [int] NOT NULL,
> [Year] [int] NOT NULL
> ) ON [PRIMARY]
>
> GO
>
> insert into TEST
> select 1,2005
> insert into TEST
> select 1,2006
> insert into TEST
> select 1,2007
> insert into TEST
> select 1,2008
> insert into TEST
> select 2,2005
> insert into TEST
> select 2,2007
> insert into TEST
> select 2,2008
> insert into TEST
> select 3,2005
> insert into TEST
> select 3,2008
> insert into TEST
> select 3,2009
> insert into TEST
> select 4,2005
> insert into TEST
> select 4,2006
> insert into TEST
> select 4,2007
> insert into TEST
> select 4,2008


From: Stefan Hoffmann on 3 Mar 2010 07:46
hi Uri,

On 03.03.2010 10:25, Uri Dimant wrote:
> SELECT [Staff_id],[Year] FROM
> (
> SELECT *, MAX([Staff_id])OVER ()mx
> ,COUNT (*) OVER (PARTITION BY [Staff_id]) AS cn
> FROM TEST[Staff_id]
> )AS D WHERE cn<mx
This does not work when adding more test data:

insert into TEST select 1,2004
insert into TEST select 10,2004
insert into TEST select 11,2004
insert into TEST select 12,2004

The Gauss algorithm should work:

WITH gauss AS (
SELECT
staff_id,
SUM(year) s,
(MAX(year) + MIN(year)) * COUNT(*) / 2 g
FROM test GROUP BY staff_id
)
SELECT t.*
FROM test t
INNER JOIN gauss g
ON g.staff_id = t.staff_id AND g.s <> g.g


mfG
--> stefan <--
From: M.K on 3 Mar 2010 08:40
Hi Stefan,

Thanks for your help. insert few more rows

insert into TEST select 8,2003
insert into TEST select 8,2004
insert into TEST select 8,2006


Now there is no record for staff_id 8 for year 2005. I also want this ID to
added in my list.
It is not returning row to show that staff_id 8 is having a missing
year(i.e. 2005).
I don't want to get missing years but list of ID's with having any year
missing.
The ID with only one row (data for only one year) is not required to be
returned.

Thanks.


"Stefan Hoffmann" <ste5an(a)ste5an.de> wrote in message
news:ur4sA#suKHA.800(a)TK2MSFTNGP04.phx.gbl...
> hi Uri,
>
> On 03.03.2010 10:25, Uri Dimant wrote:
>> SELECT [Staff_id],[Year] FROM
>> (
>> SELECT *, MAX([Staff_id])OVER ()mx
>> ,COUNT (*) OVER (PARTITION BY [Staff_id]) AS cn
>> FROM TEST[Staff_id]
>> )AS D WHERE cn<mx
> This does not work when adding more test data:
>
> insert into TEST select 1,2004
> insert into TEST select 10,2004
> insert into TEST select 11,2004
> insert into TEST select 12,2004
>
> The Gauss algorithm should work:
>
> WITH gauss AS (
> SELECT
> staff_id,
> SUM(year) s,
> (MAX(year) + MIN(year)) * COUNT(*) / 2 g
> FROM test GROUP BY staff_id
> )
> SELECT t.*
> FROM test t
> INNER JOIN gauss g
> ON g.staff_id = t.staff_id AND g.s <> g.g
>
>
> mfG
> --> stefan <--

From: Uri Dimant on 3 Mar 2010 08:51
How about
select *

from

(

select staff_id,year,

(select min(year)

from TEST as b

where b.year > a.year

and b.staff_id=a.staff_id) as nextn

from TEST as a

) as d where nextn - year >1

"M.K" <mianksaeed(a)yahoo.com> wrote in message
news:eOrdjctuKHA.6124(a)TK2MSFTNGP04.phx.gbl...
> Hi Stefan,
>
> Thanks for your help. insert few more rows
>
> insert into TEST select 8,2003
> insert into TEST select 8,2004
> insert into TEST select 8,2006
>
>
> Now there is no record for staff_id 8 for year 2005. I also want this ID
> to added in my list.
> It is not returning row to show that staff_id 8 is having a missing
> year(i.e. 2005).
> I don't want to get missing years but list of ID's with having any year
> missing.
> The ID with only one row (data for only one year) is not required to be
> returned.
>
> Thanks.
>
>
> "Stefan Hoffmann" <ste5an(a)ste5an.de> wrote in message
> news:ur4sA#suKHA.800(a)TK2MSFTNGP04.phx.gbl...
>> hi Uri,
>>
>> On 03.03.2010 10:25, Uri Dimant wrote:
>>> SELECT [Staff_id],[Year] FROM
>>> (
>>> SELECT *, MAX([Staff_id])OVER ()mx
>>> ,COUNT (*) OVER (PARTITION BY [Staff_id]) AS cn
>>> FROM TEST[Staff_id]
>>> )AS D WHERE cn<mx
>> This does not work when adding more test data:
>>
>> insert into TEST select 1,2004
>> insert into TEST select 10,2004
>> insert into TEST select 11,2004
>> insert into TEST select 12,2004
>>
>> The Gauss algorithm should work:
>>
>> WITH gauss AS (
>> SELECT
>> staff_id,
>> SUM(year) s,
>> (MAX(year) + MIN(year)) * COUNT(*) / 2 g
>> FROM test GROUP BY staff_id
>> )
>> SELECT t.*
>> FROM test t
>> INNER JOIN gauss g
>> ON g.staff_id = t.staff_id AND g.s <> g.g
>>
>>
>> mfG
>> --> stefan <--
>


 |  Next  |  Last
Pages: 1 2 3 4
Prev: Am I supposed to see the OUTPUT parameter's value in SQL Mgmt Stud
Next: Whoever posted this