Create Difference Query

I have a table that has participant scores for each performance and I want
to select all those instances where the score changed by 3.0 or more (up or
down) points in a 2 w period. A participant may be scored twice on any
given day. To try to clarify, the table includes the following basic
information:
Unit Name
Performance Date
Score
What I want to get is:
"Unit A1", 3/12/2005, 57.7
"Unit A1", 3/13/2005, 62.4
"Unit A2", 3/5/2005, 62.2
"Unit A2", 3/5/2005, 63.5
"Unit A2", 3/12/2005, 58.8
etc.
I cannot get my head around this? Should I look at an SP instead of just a
query?
Any help is much appreciated.
WayneBTW, here is what I am trying right now:
Select Distinct y.Unit, y.Class, y.Contest, y.Date, y.[Gross Score] From YTD
y
Inner Join YTD y2 on y.Unit = y2.Unit
Where (ABS(y.[Gross Score]-y2.[Gross Score]) > 3)
AND y.Class = y2.Class
AND DATEDIFF(day, y.[Date], y2.[date]) < 14
Order By y.Class, y.Unit, y.[Date]
But the result set is including cases where the score difference is less
than 3?
Wayne
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> wrote in message
news:uqCNGC$JFHA.2212@.TK2MSFTNGP12.phx.gbl...
> I have a table that has participant scores for each performance and I want
> to select all those instances where the score changed by 3.0 or more (up
or
> down) points in a 2 w period. A participant may be scored twice on any
> given day. To try to clarify, the table includes the following basic
> information:
> Unit Name
> Performance Date
> Score
> What I want to get is:
> "Unit A1", 3/12/2005, 57.7
> "Unit A1", 3/13/2005, 62.4
> "Unit A2", 3/5/2005, 62.2
> "Unit A2", 3/5/2005, 63.5
> "Unit A2", 3/12/2005, 58.8
> etc.
> I cannot get my head around this? Should I look at an SP instead of just a
> query?
> Any help is much appreciated.
> Wayne
>|||On Sun, 13 Mar 2005 11:24:41 -0700, Wayne Wengert wrote:

>BTW, here is what I am trying right now:
>Select Distinct y.Unit, y.Class, y.Contest, y.Date, y.[Gross Score] From YT
D
>y
>Inner Join YTD y2 on y.Unit = y2.Unit
>Where (ABS(y.[Gross Score]-y2.[Gross Score]) > 3)
>AND y.Class = y2.Class
>AND DATEDIFF(day, y.[Date], y2.[date]) < 14
>Order By y.Class, y.Unit, y.[Date]
>But the result set is including cases where the score difference is less
>than 3?
Hi Wayne,
Am I correct that you didn't get a reply yet? Or did my newsreader miss
it?
Anyway, my first guess would be to add
AND y2.[date] > y.[date]
somewhere in the query.
If that doesn't fix it, then I need to be able to reproduce it. For
that, I need a CREATE TABLE statement (including all constraints and
properties, but excluding irrelevant columns), some chosen rows of
sample data (as INSERT statements - and make sure to include both rows
that should and rows that should not be returned), and the expected
output. Check out www.aspfaq.com/5006 as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the reply Hugo. Yours is the only one I got. I ended up breaking
it into two queries and added the results to a temp table. Kludgy but it got
the job done.
Maybe later I'll come back to see if I can figure out the right way to do
it.
Wayne
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7poe31l1icu89gd1q7a8lsj1lnclum2eb2@.
4ax.com...
> On Sun, 13 Mar 2005 11:24:41 -0700, Wayne Wengert wrote:
>
YTD
> Hi Wayne,
> Am I correct that you didn't get a reply yet? Or did my newsreader miss
> it?
> Anyway, my first guess would be to add
> AND y2.[date] > y.[date]
> somewhere in the query.
> If that doesn't fix it, then I need to be able to reproduce it. For
> that, I need a CREATE TABLE statement (including all constraints and
> properties, but excluding irrelevant columns), some chosen rows of
> sample data (as INSERT statements - and make sure to include both rows
> that should and rows that should not be returned), and the expected
> output. Check out www.aspfaq.com/5006 as well.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)