Is this sql problem unsolvable? please advise

Posted by Trytobebetter under Sql Server on 2/22/2012 | Points: 10 | Views : 1780 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hello all,

I'm obviously inexperienced.

Here is my problem. I have data stored in one BIG microsoft access table that tracks clients, session date, session ID and scores (see sample below). We are trying to find ALL clients that have a score1 value >= 10 points higher than when they came for session 0 which is the first session (Hopefully that means the sessions improve them!). I have been trying various sql codes without success.

Can anyone write a query that will display only those records that have score1 value > or = 10 points higher than that for the same client at session 0 (like client N0020 in session 13)? The clients are all at different sessions as highlighted in the sample from the database below.

clientID sessionDate sessionID score1 score2 score3
N0010 9/7/2006 0 84 107 93
N0020 9/12/2006 0 92 140 91
N0020 2/9/2011 13 108 169 112
N0030 12/10/2007 5 114 128 109
N0030 1/15/2009 9 95 153 105
N0040 9/19/2006 0 99 147 152
N0080 9/22/2006 0 92 130 88
N0080 1/25/2008 5 82 132 103
N0090 9/26/2006 0 78 119 81
N0090 1/4/2008 5 87 157 111
N0090 2/2/2009 9 87 108 109
N0090 8/27/2010 13 86.8 148 103
N0100 9/26/2006 0 88 131 111
N0100 12/9/2008 9 88 128 108
N0100 12/9/2009 13 82 112 103

Many thanks!

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Sksamantaray on: 2/22/2012 [Member] Silver | Points: 25

0	What i understood from your question is: you need to show the max scores of each group of sessionids. If so you may use following query, rest all fields you can have using subquery. select sessionid, max(score1) score1 --, score2, score3 from table_1 group by sessionid order by score1 desc Thanks, Sanjay Trytobebetter, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Trytobebetter on: 2/22/2012 [Member] Starter | Points: 25

0	Thats not what I was looking for. to make it simpler this is the format of my table_1: table_1 client____ sessiondate session score1 N0010____ 9/7/2006____ 0____ 84 N0020____ 9/12/2006____ 0____ 92 N0020____ 2/9/2011_____ 13____ 108 N0030____ 9/14/2006____ 0____ 95 N0030____ 12/10/2007____ 5____ 114 N0030____ 1/15/2009____ 9____ 95 N0040____ 9/19/2006____ 0____ 99 N0080____ 9/22/2006____ 0____ 92 N0080____ 1/25/2008____ 5____ 82 What I would like to do is select out those records where the clients score1 is at least 10 points higher than it was at session 0. In other words I want to filter based on a logical expression that says something like this: select client, sessiondate, session, score1 where client score1 is >or= 10+(score1 when session is=0). This is very different from just selecting the max score1. I know its a bit tricky, but in the example above the only two clients that fulfil this criteria(and result of the query i'm looking for) are: client____ sessiondate session score1 N0020____ 2/9/2011_____ 13____ 108 N0030____ 12/10/2007____ 5____ 114 Is there anyay to select this? Many thanks Trytobebetter, if this helps please login to Mark As Answer. \| Alert Moderator

Thats not what I was looking for. to make it simpler this is the format of my table_1:
table_1
client____ sessiondate session score1
N0010____ 9/7/2006____ 0____ 84
N0020____ 9/12/2006____ 0____ 92
N0020____ 2/9/2011_____ 13____ 108
N0030____ 9/14/2006____ 0____ 95
N0030____ 12/10/2007____ 5____ 114
N0030____ 1/15/2009____ 9____ 95
N0040____ 9/19/2006____ 0____ 99
N0080____ 9/22/2006____ 0____ 92
N0080____ 1/25/2008____ 5____ 82

What I would like to do is select out those records where the clients score1 is at least 10 points higher than it was at session 0.

In other words I want to filter based on a logical expression that says something like this:
select client, sessiondate, session, score1 where client score1 is >or= 10+(score1 when session is=0).

This is very different from just selecting the max score1. I know its a bit tricky, but in the example above the only two clients that fulfil this criteria(and result of the query i'm looking for) are:
client____ sessiondate session score1
N0020____ 2/9/2011_____ 13____ 108
N0030____ 12/10/2007____ 5____ 114

Is there anyay to select this?

Many thanks

Trytobebetter, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 2/22/2012 [Member] Silver | Points: 25

0	you can write a cursor , where each row will be checked for similar client for its score which is atleast 10 more than its value at session '0', and displays it Thanks, Sanjay Trytobebetter, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts