Solved

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

Posted on 2003-11-12
10
4,184 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
  • 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 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

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Navigation is an important part of web design from a usability perspective. But it is often a pain when it comes to a developer’s perspective. By navigation, it often means menuing. This is less theory and more practical of how to get a specific gro…
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 theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now