?
Solved

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

Posted on 2003-11-12
10
Medium Priority
?
4,209 Views
Last Modified: 2007-12-19
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

Please let me know if you need more information.

Thanks in advance.
:-)
0
Comment
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
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 9

Expert Comment

by:_ys_
ID: 9733090
Need a little more info.

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

Author Comment

by:jfreeland
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

by:jfreeland
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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 9

Expert Comment

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

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

Expert Comment

by:_ys_
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

by:_ys_
ID: 9733686
U using SQL server 2000?

If not I could munge this for another db.
0
 

Author Comment

by:jfreeland
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

by:jfreeland
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

by:
_ys_ earned 400 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

by:malharone
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question