Solved

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

Posted on 2003-11-12
4,205 Views
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

:-)
0
Question by:jfreeland
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4

LVL 9

Expert Comment

ID: 9733090

Difference in survey scores with regards to what  ... date ?
0

Author Comment

ID: 9733191
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 Comment

ID: 9733216
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

LVL 9

Expert Comment

ID: 9733246
Thanks. That's what I needed to know.

Let me put my SQL hat on ... back soon.
0

LVL 9

Expert Comment

ID: 9733653
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

LVL 9

Expert Comment

ID: 9733686
U using SQL server 2000?

If not I could munge this for another db.
0

Author Comment

ID: 9733933
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 Comment

ID: 9735364
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

LVL 9

Accepted Solution

_ys_ earned 100 total points
ID: 9738263
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

LVL 9

Expert Comment

ID: 9742542
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Windowâ€¦
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and â€¦
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
###### Suggested Courses
Course of the Month3 days, 12 hours left to enroll