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

Gogolo gravatar image

Gogolo
323 22 26 27

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

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 74 78 108

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

Kev Riley gravatar image

Kev Riley ♦♦
52.7k 47 49 76

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

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x577

asked: Aug 10, 2011 at 03:34 AM

Seen: 656 times

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