x

select and calculate

Hi all,

I have a question and I'm not sure that I can explain my problem good enough. I need to select a record and to minus a value from previous record? I will send you data example what does the table contain:

Previous records are as follow 18,0,31,275.

 Date              Fread        Sread    Thread   Fread
 2011-7-1 00:15    25                0       37       282
 2011-7-1 00:30    27                0        41       293
 2011-7-1 00:45    29                 0        49       298

Results should upear as below:

  Date              Fread        Sread    Thread   Fread
  2011-7-1 00:15      7               0         6        17
  2011-7-1 00:30      2               0         4        11
  2011-7-1 00:45      2               0         8        5

If you have any suggestion or solution you are welcome

Thanks in advanced

more ▼

asked Aug 10, 2011 at 03:34 AM in Default

avatar image

Gogolo
323 24 26 31

It might be better to give the 'raw' data, and the query you have used to get your results

Aug 10, 2011 at 03:38 AM Kev Riley ♦♦

Dear KEV, this is a content of table, I didn't use any query for it just a simple select *

Aug 10, 2011 at 03:40 AM Gogolo

I'm still not sure which value is being calculated from other values?

Aug 10, 2011 at 03:42 AM Kev Riley ♦♦

I will try to explain, value should minus with previous value for ex: Fread 27-25 29-27 etc..

Aug 10, 2011 at 03:48 AM Gogolo
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I hope this gives you the idea of what is needed

 -- test table to work on
 DECLARE @Table TABLE
     (
       myID INT IDENTITY ,
       myVal DECIMAL(6, 2)
     )
 
 -- random test data
 INSERT  INTO @table
         ( myVal
         )
         SELECT TOP ( 100 )
             CONVERT(DECIMAL(6, 2), ABS(CHECKSUM(NEWID())) % 20.0 + 1)
         FROM
             sys.all_columns ac1
         CROSS JOIN sys.all_columns ac2 ;
 
 -- list the test data
 SELECT
     *
 FROM
     @Table AS t
 
 -- join table to itself with an offset of 1 implemented in the JOIN
 SELECT
     [t].myId AS [Record ID] ,
     [t].[myVal] AS [Record Value] ,
     ISNULL([t2].[myVal], 0) AS [Prev Record Value] ,
     [t2].[myVal] - [t].[myVal] AS [Current minus prev]
 FROM
     @Table AS t
 JOIN 
     @Table AS t2
 ON  [t].[myID] = [t2].[myID] + 1
more ▼

answered Aug 10, 2011 at 03:52 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

Very close what I looked for... I will try it now.

Aug 10, 2011 at 03:59 AM Gogolo

Done..thanks a lot

Aug 10, 2011 at 05:00 AM Gogolo
(comments are locked)
10|1200 characters needed characters left

A horrible RBAR cte solution........

 declare @YourTable table (
     [Date] datetime,
     Fread int,
     Sread int,
     Thread int,
     Fread2 int)
     
 insert into @YourTable select '1 july 2011 00:00',18,0,31,275
 insert into @YourTable select '1 july 2011 00:15',25,0,37,282
 insert into @YourTable select '1 july 2011 00:30',27,0,41,293
 insert into @YourTable select '1 july 2011 00:45',29,0,49,298
 
 ;with cte as (
 select  row_number() over (order by date) as rownum, 
          Date, Fread, Sread, Thread,Fread2 
 from @YourTable
 )
 
 select
     date,
     isnull(Fread - (select Fread from cte c2 where c2.rownum = c1.rownum - 1),0) as Fread,
     isnull(Sread - (select Sread from cte c2 where c2.rownum = c1.rownum - 1),0) as Sread,
     isnull(Thread - (select Thread from cte c2 where c2.rownum = c1.rownum - 1),0) as Thread,
     isnull(Fread2 - (select Fread2 from cte c2 where c2.rownum = c1.rownum - 1),0) as Fread2
 
 from cte c1
more ▼

answered Aug 10, 2011 at 04:00 AM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

  • for horrible!

Aug 10, 2011 at 04:15 AM Fatherjack ♦♦

Great solution too, thanks a lot for your effort.

Aug 10, 2011 at 05:53 AM Gogolo
(comments are locked)
10|1200 characters needed characters left

This doesn't help you right now, but I just want to give you something to wait for.

In the next version of SQL server (Denali) you would be able to calculate this with OVER clause like this, without any CTE, RBAR or cursor solutions:

 SELECT 
 [ProductID]
 ,[StartDate]
 ,[EndDate]
 ,[ListPrice]
 ,SUM([ListPrice]) OVER(Partition BY ProductID ORDER BY ModifiedDate ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
 ,[ModifiedDate]
 FROM [Production].[ProductListPriceHistory]

Clean and simple! :) If you like to try it, download CTP3 of Denali.

more ▼

answered Aug 10, 2011 at 04:17 AM

avatar image

Håkan Winther
16.5k 37 45 57

Can you explain me more about "CTP3 of Denali" please

Aug 10, 2011 at 05:54 AM Gogolo

The next version of SQL server is called Denali and the community technology preview is availible for download from www.microsoft.com/sql

Aug 10, 2011 at 06:09 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x749

asked: Aug 10, 2011 at 03:34 AM

Seen: 853 times

Last Updated: Aug 10, 2011 at 03:35 AM

Copyright 2016 Redgate Software. Privacy Policy