Returning ID's with broken series

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 

0
M
3/3/2010 7:15:37 AM
sqlserver.programming 1873 articles. 0 followers. Follow

15 Replies
874 Views

Similar Articles

[PageSpeed] 32

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@yahoo.com> wrote in message 
news:%23DmJWFquKHA.4908@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 


0
Uri
3/3/2010 9:25:52 AM
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 <--
0
Stefan
3/3/2010 12:46:22 PM
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@ste5an.de> wrote in message 
news:ur4sA#suKHA.800@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 <--  

0
M
3/3/2010 1:40:45 PM
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@yahoo.com> wrote in message 
news:eOrdjctuKHA.6124@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@ste5an.de> wrote in message 
> news:ur4sA#suKHA.800@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 <-- 
> 


0
Uri
3/3/2010 1:51:50 PM
Change
(MAX(year) + MIN(year)) * COUNT(*) / 2 g
to
(MAX(year) + MIN(year)) * COUNT(*) / 2. g

and it will return the correct data.

To make it return distinct staff_ids, just modify the select clause:

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




M.K wrote:
> 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@ste5an.de> wrote in message
> news:ur4sA#suKHA.800@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 <--

-- 
HTH,
Bob Barrows


0
Bob
3/3/2010 2:07:01 PM
Bob
> Change
> (MAX(year) + MIN(year)) * COUNT(*) / 2 g
> to
> (MAX(year) + MIN(year)) * COUNT(*) / 2. g

Where is a  difference in above?-))))






"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message 
news:en7DNrtuKHA.5316@TK2MSFTNGP05.phx.gbl...
> Change
> (MAX(year) + MIN(year)) * COUNT(*) / 2 g
> to
> (MAX(year) + MIN(year)) * COUNT(*) / 2. g
>
> and it will return the correct data.
>
> To make it return distinct staff_ids, just modify the select clause:
>
> WITH gauss AS (
> SELECT
> staff_id,
> SUM(year) s,
> (MAX(year) + MIN(year)) * COUNT(*) / 2. g
> FROM test GROUP BY staff_id
> )
> SELECT distinct t.staff_id FROM test t
> INNER JOIN gauss g
> ON g.staff_id = t.staff_id AND g.s <> g.g
>
>
>
>
> M.K wrote:
>> 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@ste5an.de> wrote in message
>> news:ur4sA#suKHA.800@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 <--
>
> -- 
> HTH,
> Bob Barrows
>
> 


0
Uri
3/3/2010 2:11:46 PM
Thanks Uri and Bob both solutions are perfect.

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message 
news:en7DNrtuKHA.5316@TK2MSFTNGP05.phx.gbl...
> Change
> (MAX(year) + MIN(year)) * COUNT(*) / 2 g
> to
> (MAX(year) + MIN(year)) * COUNT(*) / 2. g
>
> and it will return the correct data.
>
> To make it return distinct staff_ids, just modify the select clause:
>
> WITH gauss AS (
> SELECT
> staff_id,
> SUM(year) s,
> (MAX(year) + MIN(year)) * COUNT(*) / 2. g
> FROM test GROUP BY staff_id
> )
> SELECT distinct t.staff_id FROM test t
> INNER JOIN gauss g
> ON g.staff_id = t.staff_id AND g.s <> g.g
>
>
>
>
> M.K wrote:
>> 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@ste5an.de> wrote in message
>> news:ur4sA#suKHA.800@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 <--
>
> -- 
> HTH,
> Bob Barrows
>
> 
0
M
3/3/2010 2:12:41 PM
hi Bob,

On 03.03.2010 15:07, Bob Barrows wrote:
> Change
> (MAX(year) + MIN(year)) * COUNT(*) / 2 g
> to
> (MAX(year) + MIN(year)) * COUNT(*) / 2. g
>
> and it will return the correct data.
Of course, an implicit conversion to int happens.

> To make it return distinct staff_ids, just modify the select clause:
Or drop the CTE:

SELECT staff_id
FROM test
GROUP BY staff_id
HAVING (MAX(year) + MIN(year)) * COUNT(*) / 2.0 <> SUM(year);


mfG
--> stefan <--
0
Stefan
3/3/2010 2:23:21 PM
The decimal point after the 2 in the divisor which forces the result to
retain the fraction.

Uri Dimant wrote:
> Bob
>> Change
>> (MAX(year) + MIN(year)) * COUNT(*) / 2 g
>> to
>> (MAX(year) + MIN(year)) * COUNT(*) / 2. g
>
> Where is a  difference in above?-))))
>
>
>
>
>
>
> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:en7DNrtuKHA.5316@TK2MSFTNGP05.phx.gbl...
>> Change
>> (MAX(year) + MIN(year)) * COUNT(*) / 2 g
>> to
>> (MAX(year) + MIN(year)) * COUNT(*) / 2. g
>>
>> and it will return the correct data.
>>
>> To make it return distinct staff_ids, just modify the select clause:
>>
>> WITH gauss AS (
>> SELECT
>> staff_id,
>> SUM(year) s,
>> (MAX(year) + MIN(year)) * COUNT(*) / 2. g
>> FROM test GROUP BY staff_id
>> )
>> SELECT distinct t.staff_id FROM test t
>> INNER JOIN gauss g
>> ON g.staff_id = t.staff_id AND g.s <> g.g
>>
>>
>>
>>
>> M.K wrote:
>>> 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@ste5an.de> wrote in message
>>> news:ur4sA#suKHA.800@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 <--
>>
>> --
>> HTH,
>> Bob Barrows

-- 
HTH,
Bob Barrows


0
Bob
3/3/2010 2:23:48 PM
Haha, my mistake Bob....





"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message 
news:OvHll0tuKHA.4752@TK2MSFTNGP04.phx.gbl...
> The decimal point after the 2 in the divisor which forces the result to
> retain the fraction.
>
> Uri Dimant wrote:
>> Bob
>>> Change
>>> (MAX(year) + MIN(year)) * COUNT(*) / 2 g
>>> to
>>> (MAX(year) + MIN(year)) * COUNT(*) / 2. g
>>
>> Where is a  difference in above?-))))
>>
>>
>>
>>
>>
>>
>> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
>> news:en7DNrtuKHA.5316@TK2MSFTNGP05.phx.gbl...
>>> Change
>>> (MAX(year) + MIN(year)) * COUNT(*) / 2 g
>>> to
>>> (MAX(year) + MIN(year)) * COUNT(*) / 2. g
>>>
>>> and it will return the correct data.
>>>
>>> To make it return distinct staff_ids, just modify the select clause:
>>>
>>> WITH gauss AS (
>>> SELECT
>>> staff_id,
>>> SUM(year) s,
>>> (MAX(year) + MIN(year)) * COUNT(*) / 2. g
>>> FROM test GROUP BY staff_id
>>> )
>>> SELECT distinct t.staff_id FROM test t
>>> INNER JOIN gauss g
>>> ON g.staff_id = t.staff_id AND g.s <> g.g
>>>
>>>
>>>
>>>
>>> M.K wrote:
>>>> 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@ste5an.de> wrote in message
>>>> news:ur4sA#suKHA.800@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 <--
>>>
>>> --
>>> HTH,
>>> Bob Barrows
>
> -- 
> HTH,
> Bob Barrows
>
> 


0
Uri
3/3/2010 2:31:57 PM
Nice one Stefan
I wonder which one  would be faster on huge table? :-))))




"Stefan Hoffmann" <ste5an@ste5an.de> wrote in message 
news:eVTHN0tuKHA.2436@TK2MSFTNGP04.phx.gbl...
> hi Bob,
>
> On 03.03.2010 15:07, Bob Barrows wrote:
>> Change
>> (MAX(year) + MIN(year)) * COUNT(*) / 2 g
>> to
>> (MAX(year) + MIN(year)) * COUNT(*) / 2. g
>>
>> and it will return the correct data.
> Of course, an implicit conversion to int happens.
>
>> To make it return distinct staff_ids, just modify the select clause:
> Or drop the CTE:
>
> SELECT staff_id
> FROM test
> GROUP BY staff_id
> HAVING (MAX(year) + MIN(year)) * COUNT(*) / 2.0 <> SUM(year);
>
>
> mfG
> --> stefan <-- 


0
Uri
3/3/2010 2:33:03 PM
hi Uri,

On 03.03.2010 15:33, Uri Dimant wrote:
> Nice one Stefan
> I wonder which one  would be faster on huge table? :-))))
My statement uses one table scan as yours needs two, but on the other 
side if the actual query plan is right, my HAVING clause requires a sort 
which requires ~20 times more I/O.


mfG
--> stefan <--
0
Stefan
3/3/2010 3:20:34 PM
Here is another solution:

SELECT staff_id
FROM test
GROUP BY staff_id
HAVING MAX(year) - MIN(year) <> COUNT(DISTINCT year) - 1;

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
3/3/2010 3:21:20 PM
Stefan
SET STATISTICS IO on shows that my query is more expencive in terms of 
logical reads, yes  you are right the SORT operations is pretty costly in 
that case





"Stefan Hoffmann" <ste5an@ste5an.de> wrote in message 
news:uJV1LUuuKHA.4220@TK2MSFTNGP05.phx.gbl...
> hi Uri,
>
> On 03.03.2010 15:33, Uri Dimant wrote:
>> Nice one Stefan
>> I wonder which one  would be faster on huge table? :-))))
> My statement uses one table scan as yours needs two, but on the other side 
> if the actual query plan is right, my HAVING clause requires a sort which 
> requires ~20 times more I/O.
>
>
> mfG
> --> stefan <-- 


0
Uri
3/3/2010 3:32:50 PM
hi Plamen,

On 03.03.2010 16:21, Plamen Ratchev wrote:
> SELECT staff_id
> FROM test
> GROUP BY staff_id
> HAVING MAX(year) - MIN(year) <> COUNT(DISTINCT year) - 1;
Ah, easier to understand than the Gauss approach.


mfG
--> stefan <--
0
Stefan
3/3/2010 3:53:03 PM
Reply:

Similar Artilces:

Is debt reduction broken in M07
Hello, Money 207 wants to set payments well above my needs. I think this is throwing off the payoff date. How can I tell Money to make the payments I want? Thanks Frank It seems that I was entering Bills and then telling Money Debt Reduction that I also wanted to pay x dollars per month. Once I removed this and just used the bills, the figures changed to normal. Oh well.... "Frank" <farocco@hotmail.com> wrote in message news:e2yHJmSJHHA.780@TK2MSFTNGP03.phx.gbl... > Hello, > > Money 207 wants to set payments well above my needs. > I think this is throwin...

PA falta tipo moneda en ventana "Return from Proyect Entry"
La ventana de "Return from Proyect Entry" tiene que ser multimoneda ya que todo el sistema es Multimoneda. La ruta para acceder a esa ventana es Transacciones/Proyecto/PA Purchasing/Return from Proyect Entry. Ver incidente 8404987 ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree"...

Vlookup
Quick question on returning the 2nd match of a vlookup . I am tracking multiple major league baseball teams' win/loss streaks, and have the results listed by date. I was doing fine, until the Mets and Dodgers had a double-header yesterday, and now had 2 entries for that date. I know I can test for multiple dates using Countif(A1:A4 4/27/2010) but don't know how to return the 2nd match of the row of the vlookup. A B 4/26/2010 POSTPONED 4/27/2010 W 4-0 4/27/2010 W 10-5 4/28/2010 W 7-3 vlookup(4/27/2010, a1:b4, 2, false) so that I get W 10-5 (where t...

Change order of series in chart
Hello there, I have built up a rather complicated customized chart with about 10 series. Now I added an 11th one, whose datapoints overlay some of the earlier series. Is there a way to determine the order of dataseries once a chart has been created without starting from scratch ? Thank you in advance. Kind regards, H.G. Lamy Double click any series, then in the resulting 'Format Data Series' dialog box, select the 'Series Order' tab. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office producti...

Exchange ActiveSync and Event ID 3005
Hello, I am trying to sync a Treo 650 to Exchange Server 2003 (not sure which service pack). I keep getting an error: There was a problem syncing messages. (006)HTTP/1.1 500 Internal Server Error Date: ***** 16:26:23 GMT Server: Microsoft IIS/6.0 On the server (Windows 2003 SBS), I get event ID: 3005 Unexpected Exchange mailbox Server error: [servername.domain.local] User: [myusername@domain.com] HTTP status code: [501]. Verify that the exchange mailbox Server is working correctly. I tried to access the mailbox from OWA (Forms based authentication is NOT enabled) with no problems. I am able...

Event ID 9646 Mapi Session Error
I'm receiving multiple error with message Event ID: 9646 Mapi session "/o=sqg/ou=klt/cn=Recipients/cn=OMOHO" exceeded the maximum of 32 objects of type "session". This account is a normal user account and not server service account, so I'm not sure why we are getting these error messages. I've seen a couple things cause this: 1. An errant mapi application in the environment that is opening more than 32 sessions. 2. A network issue (W2K3 SP1 without kb 898060 for example) taht causes a client to repeatedly loose and open sessions. "Minesh&quo...

URL link is broken in Rich Edit Box if different language characters are used
Hi I have built an application using RichEdit20 class for displaying rich edit box. When I enter any URL (e.g http:\\dummy.com) it is highlighted and I can click the URL to open it. But if I introduce some other language characters in URL e.g. Japanese language characters then link is broken i.e. it is not highlighted. Example If I write http:\\dummy.com then this URL is highlighted properly but if I write http:\\dumボタンにmy.com where ボタンに are Japanese characters then link is broken after http:\\dum How do I get the whole URL highlighted in rich edit box even if I use cha...

One column returns value based on other column
I have a column for the employee's name and it is a lookup field. I want a second column to return the department that is in that record of the table that the lookup field is based on. I.E. If we put John Smith in the employee field then automatically the department Accounting is returned in the department table. Is this possible? Keithbetsey, You would use a Query, which includes both your main table and the thable that the lookup field is based on. Your statement "I have a column for the employee's name and it is a lookup field" is probably not true. It is probably...

Contract Integration for Field Service Series
The ability to use integration manager to import contracts into the field service series. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=37d802cd-b978-4717-878b-127ba7f206...

Logic for Placing Multiple field Returns in a Cell
Looking for the logic to concatenate cells, however I need one of those cells to duplicate X number of times: Such as CONCATENATE(d1, b1, b3) B3 being the value I want to return multiple times: d1=1 b1=2 b3=4 I would like my results to come out: 1244444444 Any help? Use the following UDF: Function Mergit(a, b, c) Mergit = a & b & c & c & c & c & c & c & c & c End Function -- Gary's Student "SCHNYDES" wrote: > Looking for the logic to concatenate cells, however I need one of those cells > to duplicate X number of times: > ...

chart x-axis values appear as a series
The x-axis values do not appear on the x-axis but appear as another series on the chart. How do I specify the x-axis values for a chart? Existing chart: Chart menu > Source Data > Series tab. For a new chart, fix the data first. Put the X values in the first column and Y values in the columns to the right. Put a label (series name) in the row above the first row of data for each set of Y values, but keep the cell atop the X values blank. Select the entire range, including X values, series names, and blank cell, then start the chart wizard. The blank cell helps Excel interpret t...

Auto-update Fill Series in column that has randomly spaced blank c
Hi, I have a worksheet of projects listed by quarter. A cell (say A6) contains QTR 1, followed by cells in column A filled with a series (1,2,3...say up to 12). Then comes a blank row followed by a row containing words "QTR 2" and then the fill series continues from where it left under QTR 1 i.e. 13,14,15...say up to 18. And so on for QTR 3 and QTR 4. When a new project is added, I want to be able to add a row and with minimal steps, want the fill series below this added new project, to update. When I delete a row (project), I want the fill series to automatically ...

How do I Return a Value Using Multiple Criteria?
I need to return a value (a $AMOUNT) based on the following information: TITLE, LEVEL 1 2 3 or 4, and a percentage range. Ex: If the [TITLE, ie: Store Manager] exceeds their sales plan by a percent in the [PERCENTAGE RANGE, ie: 5-6.99%], then they receive a certain bonus [PAYOUT AMOUNT] based on their [BONUS LEVEL]. The static information is currently set up in a chart as follows: TITLE/BONUS LEVEL/INCREASE OVER SALES PLAN % RANGE/PAYOUT AMOUNT Store Manager 1 5.00%-6.99% $600.00 Store Manager 1 7.00%-8.99% $800.00 Store Manager 1 9.00%-10.99% $1,000.00 Store Manager ...

change last serie to be line in the line-column Excel chart in Wor
Hi, I'd like to insert the Excel chart in Word doc. When I insert an Excel chart with 4 series in the type of line-column. The second serie always to be line, I want to the last serie to be line rather than second one. I know how change it when it happens in Excel. But I cannot find the all of excel Menus in Word windows when I edit chart in the word, so I donot know how change it? Thanks a lot for anyone offering help. Hi, That depends on how you copied the chart into word? -- Thanks, Shane Devenshire "Eric" wrote: > Hi, I'd like to insert the Excel chart in ...

Caller ID
I need a solution (caller Id) I have to make a data base of my 5000 customers, And I need to get pop up info and different rings when my customer calls on my telephone number. I need to get a provision to get the customer location Map with cusomer details. I dont have a PABX and I have only the normal telephone connection. Could you please give me a solution ? ...

Preview Problems have returned!
http://discussions.apple.com/thread.jspa?threadID=2358880&tstart=0 In this thread the advice was given to remove ~/Library/Preferences/com.apple.preview.plist I did this and once done MS Entourage 2004 was able to open attached images once again. This worked for a few weeks until recently. I once again tried removing that file, but this time it did not work. It looks like something deeper is going on with Preview and Entourage. I have submitted bug reports to Apple and we shall see if they fix it in their latest OSX release. -- Are there errors in the Bible? How should...

Better Return Function
1. When Recall Transaction For Return, instead of bringing up everything that was purchased in return mode ( and having to delete the ones not being return), there should be an option to make the POS to ask the user to scan/enter the item to be return. Then the program has to check that the item scanned does exists in that transaction. 2. Returned item from that transaction should not be allowed to be return again. 3. In Recall Transaction For Return mode, have the ability to block user from giving credit/cash back to customer. 4. In Recall Transaction For Return mode, do not pref...

System Manager snap-in broken
Not really sure what caused this, there was nothing done to the system that I am aware of, but today I received the following error when attempting to run System Manager: Snap-in failed to initialize. Name: Exchange System CLSID:{1B600AEA-10BA-11D2-9F28-00C04FA37610}. The system is Windows 2003 SBS SP1 with Exchange 6.5. Anyone have any idea on how this could be repaired? Any help would be appreciated. ...

Formula to return All Teams that.......
Greetings, I am trying to write a formula that will return the team name of ALL teams that had Kitchen duty the day before they have Garden duty (and various other combinations) Without using a macro!!!! For example using a small sample of the data A B C D E 1 2 Date Cleaning Garden Kitchen Day Off 3 12/05/2005 Team A Team B Team C Team D 4 13/05/2005 Team C Team D Team B Team A 5 14/05/2005 Team C Team B Team D Team A 6 15/05/2005 Team A Team D Team B Team C 7 16/05/2005 Team C Team D Team A Team B 8 17/05/2005 Team D Team A Team B Team C 9 1...

Find carriage return character in a cell
Does anyone know how find a row in a worksheet which contains carriage return character (usually a square) in its cell ? I've given you the response below yesterday to your post, re: http://tinyurl.com/2esbm -------- Maybe CHAR(10) ? (carriage return via Alt + Enter) Assume you want to check range A1:C10 in Sheet1 for cells with carriage return In a new Sheet2: Put in A1: =IF(ISERROR(SEARCH(CHAR(10),Sheet1!A1)),"","Yes") Copy A1 across to C1, then down to C10 to cover the same range as in Sheet1 A1:C10 in Sheet2 will return "Yes" for corresponding c...

Event id 2003 SBS 2008
Started receiving this event "The configuration information of the performance library "C:\Windows\system32\sqlctr90.dll" for the "MSSQL$SBSMONITORING" service does not match the trusted performance library information stored in the registry. The functions in this library will not be treated as trusted." Hello David, Thanks for your post. These messages are related to performance counters. Additionally, these messages resemble the following message: Event ID: 2003 Detail Text: The configuration information of the performance library (DLL ...

In macros, an instruction that returns the position of the Active Cell
In a macro, how can I make it return the position of the Active Cell Colum & Line. Or how can I set that position as a Condition; e.g. If ActiveCell i B7 Then. Or If ActiveCell belongs to Column G Then. Thanks in advance for any help. AC -- ac ----------------------------------------------------------------------- aca's Profile: http://www.msusenet.com/member.php?userid=385 View this thread: http://www.msusenet.com/t-187368645 Hi Sub test() Dim ws1 As Worksheet Set ws1 = Workbooks("Tester.xls").Sheets("Sheet2") With ws1 MsgBox ("Cell address is &qu...

Graph with 2 X axis
Hi, I'm trying to graph one data series on an X axis against 5 other data series plotted against a second X axis. The graph only displays 4 data series - if I delete one series and add it again, the 'new' series is displayed but one of the other series is not displayed. Is there a limit on the no. of data series that can be displayed for this chart type? I can see all data series in the source data part of the chart wizard but the chart displays only 4 series....thanks for any advice....DavidC Wel, you haven't told us which chart type, or which version of Excel, but ...

where can I find avery return address label template 18195 or 819.
Avery doesn't have your number on the US or the UK web pages. Are you sure you typed it correctly. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "petsarebeautiful" <petsarebeautiful@discussions.microsoft.com> wrote in message news:B74EA87E-DD68-4048-95C4-E38D9B0599F9@microsoft.com... > ...

MSExchangeMTA Cycling Event ID's 3200,9158,9159
I ran into a problem this morning with one of our exchange servers. We have a group of three servers on our network all on the same domain. The server that is causing us problems is the server set up for our OWA and Public Information Store. I am getting a cycle of three EventID errors about every two seconds and then the system eventually IIS crashes and causes a Dr. Watson error due to the log file corrupting. A restart resolves the problem for a few hours and then eventually the problem will happen again. The Event ID's are: Event ID: 9159 Source: MSExchange MTA (BASE IL INCOMING...