Query suggestion

Hello All,
I have a table with User_Id, Group_ID,Score, Date
The data is as
1, a, 0,Jan 01 2005
1, a, 10,Jan 02 2005
1, a, 15,Jan 03 2005
1, b, 1,Jan 01 2005
1, b, 3,Jan 02 2005
2, a, 4,Jan 01 2005
I want the data as
1, a, 0,Jan 01 2005
1, a, 10,Jan 02 2005
1, a, 5,Jan 03 2005
1, b, 1,Jan 01 2005
1, b, 2,Jan 02 2005
2, a, 4,Jan 01 2005
The difference in two dataset is that in first set the Score is cummulative
, In the resultset I would like to have score done separately for each day
grouped by UserID and GroupID. for ex the cummulative score for 1, a, 15,Jan
03 2005 is 15 but the actual score done on that day is 15-10 =5
any suggestion will be highly appreciated,
cheers,
siajTry This...
Select T.User_ID, T.Group_ID,
T.Score - IsNull(P.Score, 0) Score
From Table T Left Join Table P
On P.User_ID = T.User_ID
And P.Group_ID = T.Group_ID
And P.Date = (Select Max(Date) From Table
Where User_ID = T.User_ID
And Group_ID = T.Group_ID
And Date < T.Date)
"siaj" wrote:

> Hello All,
> I have a table with User_Id, Group_ID,Score, Date
> The data is as
> 1, a, 0,Jan 01 2005
> 1, a, 10,Jan 02 2005
> 1, a, 15,Jan 03 2005
> 1, b, 1,Jan 01 2005
> 1, b, 3,Jan 02 2005
> 2, a, 4,Jan 01 2005
>
> I want the data as
> 1, a, 0,Jan 01 2005
> 1, a, 10,Jan 02 2005
> 1, a, 5,Jan 03 2005
> 1, b, 1,Jan 01 2005
> 1, b, 2,Jan 02 2005
> 2, a, 4,Jan 01 2005
>
> The difference in two dataset is that in first set the Score is cummulativ
e
> , In the resultset I would like to have score done separately for each day
> grouped by UserID and GroupID. for ex the cummulative score for 1, a, 15,J
an
> 03 2005 is 15 but the actual score done on that day is 15-10 =5
>
> any suggestion will be highly appreciated,
> cheers,
> siaj|||Do:
SELECT t1.Userid, t1.group_id, t1.score - COALESCE(
( SELECT t2.score FROM tbl t2
WHERE t2.Userid = t1.Userid
AND t2.Group_id = t1.Group_id
AND t2.dt = t1.dt - 1 ), 0 ), t1.dt
FROM tbl t1
ORDER BY Userid, Group_ID ;
Anith|||Thanks Anith/CBretana,
The Query was simply fantastic...
Although I used the query of CBretana as I had some duplicates in my data
which could not be handled by Subquery rules. It was a great help.
Thanks again,
siaj
"Anith Sen" wrote:

> Do:
> SELECT t1.Userid, t1.group_id, t1.score - COALESCE(
> ( SELECT t2.score FROM tbl t2
> WHERE t2.Userid = t1.Userid
> AND t2.Group_id = t1.Group_id
> AND t2.dt = t1.dt - 1 ), 0 ), t1.dt
> FROM tbl t1
> ORDER BY Userid, Group_ID ;
> --
> Anith
>
>