Returning ID's with broken series
in [Programming SQL]
|
From: Bob Barrows on 3 Mar 2010 09:07 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(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 <-- -- HTH, Bob Barrows
From: Uri Dimant on 3 Mar 2010 09:11 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(a)NOyahoo.SPAMcom> wrote in message news:en7DNrtuKHA.5316(a)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(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 <-- > > -- > HTH, > Bob Barrows > >
From: M.K on 3 Mar 2010 09:12 Thanks Uri and Bob both solutions are perfect. "Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in message news:en7DNrtuKHA.5316(a)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(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 <-- > > -- > HTH, > Bob Barrows > >
From: Stefan Hoffmann on 3 Mar 2010 09:23 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 <--
From: Bob Barrows on 3 Mar 2010 09:23 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(a)NOyahoo.SPAMcom> wrote in message > news:en7DNrtuKHA.5316(a)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(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 <-- >> >> -- >> HTH, >> Bob Barrows -- HTH, Bob Barrows
First
|
Prev
|
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 |