# SQl - Calculate difference between rows for same column - reset calculation for next id

Hi all,

I'm new to your site so please forgive any faux pas.  I have an SQL table where I need to calculate the difference in survey scores from between rows for the same column.  I'm new to SQL.  The following are examples of how the data looks in the table.

USERID     Surveydate        SurveyScore
123      01/13/02           6.75
123      02/16/03              5.10
234      05/14/02              2.75
234      06/12/03              3.00
234      07/01/02              2.88
345      02/14/02           1.82
456      01/01/03
456      02/15/03              4.35
456      03/21/03              3.25

:-)
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:

Difference in survey scores with regards to what  ... date ?
0
Author Commented:
We need to subtract the next survey score from the previous date's score:

234     05/14/02             2.75 (no previous date so = 0)
234     07/01/02             2.88 (2.88 - 2.75 = .13)
234     06/12/03             3.00 (3.00 - 2.88 = .12)

123     01/13/02           6.75
123     02/16/03             5.10

0
Author Commented:
Sorry - sent it before I was done.

123     01/13/02             6.75 (no previos date so = 0)
123     02/16/03             5.10 (5.10 - 6.75 = -1.65)

Hope this helps.
Thanks again.
0
Commented:
Thanks. That's what I needed to know.

Let me put my SQL hat on ... back soon.
0
Commented:
Personally I use SQL server 2000, so this may have to be tailored to fit your actual scripting needs.

-------
-- Create my own representation of your table
-------
DECLARE      @tblSurveyScore TABLE
(
USERID smallint NOT NULL,
SurveyDate smalldatetime NOT NULL,
SurveyScore dec(3,2) NOT NULL
)

INSERT
INTO      @tblSurveyScore
VALUES      (123, '01/13/02', 6.75)

INSERT
INTO      @tblSurveyScore
VALUES      (123, '02/16/03', 5.10)

INSERT
INTO      @tblSurveyScore
VALUES      (234, '05/14/02', 2.75)

INSERT
INTO      @tblSurveyScore
VALUES      (234, '06/12/03', 3.00)

INSERT
INTO      @tblSurveyScore
VALUES      (234, '07/01/02', 2.88)

INSERT
INTO      @tblSurveyScore
VALUES      (345, '02/14/02', 1.82)

-------
-- actual work starts here
-------
DECLARE      @tmpTblSurveyScore TABLE
(
USERID smallint NOT NULL,
SurveyDate smalldatetime NOT NULL,
SurveyScore dec(3,2) NOT NULL
)

INSERT
INTO      @tmpTblSurveyScore
SELECT      USERID, SurveyDate, SurveyScore
FROM      @tblSurveyScore
ORDER BY USERID, SurveyDate ASC

DECLARE      @tmpTblSurveyScoreDiff TABLE
(
USERID smallint NOT NULL,
SurveyDate smalldatetime NOT NULL,
Diff dec(3,2) NOT NULL
)

SELECT      *
FROM      @tmpTblSurveyScore

DECLARE       @nUserId smallint
,@dtSurveyDate smalldatetime
,@fSurveyScore dec(3,2)
,@fSurveyScoreDiff dec(3,2)

SELECT      TOP 1
@nUserId = USERID
,@dtSurveyDate = SurveyDate
,@fSurveyScore = SurveyScore
FROM      @tmpTblSurveyScore

WHILE (0 <> @@ROWCOUNT)
BEGIN
SELECT TOP 1
@fSurveyScoreDiff = SurveyScore - @fSurveyScore
FROM      @tmpTblSurveyScore
WHERE      USERID = @nUserId
AND      SurveyDate <> @dtSurveyDate

IF 0 = @@ROWCOUNT
BEGIN
PRINT 'No previous to diff'
SET @fSurveyScoreDiff = 0
END

INSERT
INTO      @tmpTblSurveyScoreDiff
VALUES      (@nUserId, @dtSurveyDate, @fSurveyScoreDiff)

DELETE
FROM      @tmpTblSurveyScore
WHERE      USERID = @nUserId
AND      SurveyDate = @dtSurveyDate

SELECT      TOP 1
@nUserId = USERID
,@dtSurveyDate = SurveyDate
,@fSurveyScore = SurveyScore
FROM      @tmpTblSurveyScore
END

-------
-- final result
-------
SELECT      *
FROM      @tmpTblSurveyScoreDiff
0
Commented:
U using SQL server 2000?

If not I could munge this for another db.
0
Author Commented:
Yes, indeed, we are using SQL server 2000 but, alas, I am a newbie.  Thanks for the code you posted - I'm about to test it now.  Will let you know results as soon as I can - it certainly looks very promising.

:-)
0
Author Commented:
Hello again,

Your code looks great, but I still have two problems. (I apologize if my initial question was garbled.  I've been struggling with this for three days and I'm a bit numb.)  Hopefully these aren't insurmountable problems.

1. I need to save the results as a view to use in a Crystal Report.  Query Analyzer let me run the code, but I can't seem to save it as a view either there or in Enterprise Manager.  Can you point me in the right direction?

2. Is it possible to have the "0" values show in the first row and the calculations in the subsequent rows?  Sorry to be a pain, but I suspect it will be cleaner to fix it here rather than in Crystal.  Example data below:

Userid       SurveyDate                  SurveyScore   Diff
123         2002-03-13 00:00:00        3.63               -.38 (0)
123          2003-03-19 00:00:00        3.25               .00  (-.38)
234          2002-06-20 00:00:00        4.25               .25  (0)
234          2002-12-31 00:00:00        4.50               .25  (.25)
234          2003-03-26 00:00:00        4.75               .00  (.25)

Again, thanks for all your help - it's greatly appreciated.
:-)
0
Commented:
Q1.
Create this code snippet as a stored procedure instead of a straight script. The results from a stored procedure are bindable within crystal reports - you don't need to use an intermediatory storage.

Q2.
Sure. Np. Always do as much as possible within SQL before passing over to Crystal - it's generally more efficient.

Complete snippet below - this will create a stored procedure as well.

CREATE PROCEDURE [dbo].[surveryScoreDifferences]
AS
BEGIN
DECLARE      @tmpTblSurveyScore TABLE
(
USERID smallint NOT NULL,
SurveyDate smalldatetime NOT NULL,
SurveyScore dec(3,2) NOT NULL
)

-- reorder the existing table, for use within the WHILE loop
INSERT
INTO      @tmpTblSurveyScore
SELECT      USERID, SurveyDate, SurveyScore
FROM      @tblSurveyScore -- [this is the name of your existing table/view]
ORDER BY USERID, SurveyDate ASC

-- this is to be our result set [it's column sizes should match the original table]
DECLARE      @tmpTblSurveyScoreDiff TABLE
(
USERID smallint NOT NULL,
SurveyDate smalldatetime NOT NULL,
SurveyScore dec(3,2) NOT NULL,
Diff dec(3,2) NOT NULL
)

DECLARE       @nUserId smallint
,@dtSurveyDate smalldatetime
,@fSurveyScore dec(3,2)
,@fSurveyScoreDiff dec(3,2) -- [again, these should match the original table]

-- select the first row to be processed
SELECT      TOP 1
@nUserId = USERID
,@dtSurveyDate = SurveyDate
,@fSurveyScore = SurveyScore
FROM      @tmpTblSurveyScore

-- while we still have rows to process
WHILE (0 < @@ROWCOUNT)
BEGIN
SELECT TOP 1
@fSurveyScoreDiff = SurveyScore - @fSurveyScore
FROM      @tmpTblSurveyScore
WHERE      USERID = @nUserId
AND      SurveyDate <> @dtSurveyDate

-- if no previous score, difference should be 0
IF 0 = @@ROWCOUNT
BEGIN
PRINT 'No previous to diff' -- [debug purposes only]
SET @fSurveyScoreDiff = 0
END

-- we have a row for our result set
INSERT
INTO      @tmpTblSurveyScoreDiff
VALUES      (@nUserId, @dtSurveyDate, @fSurveyScore, @fSurveyScoreDiff)

-- row has been processed, delete it ...
DELETE
FROM      @tmpTblSurveyScore
WHERE      USERID = @nUserId
AND      SurveyDate = @dtSurveyDate

-- ... and get the next
SELECT      TOP 1
@nUserId = USERID
,@dtSurveyDate = SurveyDate
,@fSurveyScore = SurveyScore
FROM      @tmpTblSurveyScore
END

-- return the results
SELECT      *
FROM      @tmpTblSurveyScoreDiff
END
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
why dont you just create the dataset yourself and set the dataset manually?

use the following link... it explains how to "push" data to your report instead of the "report" pulling data from the database. The "push" method may be complicated, but offers great flexibility and functionality -- which may NOT be built in as a SQL function.
http://www.freevbcode.com/source/WalkthroughCrystalReport.zip

0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.