T-SQL without a cursor

I need subtract the first record from each subsequent record, then move to the next number and subtract each subsequent record from that point on.  I would like to know if there is a way of doing this without a cursor.  I have attached an example of what the resultset would look like.

also here the script to insert the sample records:


 Create table #Example
 (ID int identity (1,1)
 ,Number1 numeric(10,2)
 ,Number2 numeric(10,2))

insert into #Example
select 5140.39      ,7930 union all
select 5148.17      ,7922 union all
select 5165.65      ,7905 union all
select 5204.42      ,7866 union all
select 5239.80      ,7830 union all
select 5243.09      ,7827 union all
select 5281.90      ,7788 union all
select 5320.76      ,7749
Example.xlsx
yanci1179Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UnifiedISCommented:
Join the table back to itself with an incremented ID

SELECT *
FROM Table A
LEFT OUTER JOIN Table B
ON B.ID = A.ID + 1
Brian CroweDatabase AdministratorCommented:
First of all thank you for the DML and insert scripts they are always appreciated.  Unfortunately I found myself confused by your resultset(s).  There appear to be three separate resultsets that are trying to communicate the methodology but no final output other than the original input.
Olaf DoschkeSoftware DeveloperCommented:
Take a look at LAG() and LEAD()

Bye, Olaf.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

yanci1179Author Commented:
Hi UnifiedIS,

I may have done a poor explanation.  The first record is constant until it subtracts each subsequent records, once that is finished, the second record becomes constant and it is subtracted from each subsequent record...and so forth.  

I have an example that I loaded, but result-set basically looks like:

Number 1      Number 2      DeltaNumber1      DeltaNumber2
5,140.39                       7930            
5,148.17                       7922                         5148.17-5140.39      7922-7930
5,165.65                       7905                         5165.65-5140.39      7905-7930
5,204.42                       7866                          5204.42-5140.39      7866-7930
5,239.80                       7830                          5239.80-5140.39      7830-7930
5,243.09                       7827                          5243.09-5140.39      7827-7930
5,281.90                       7788                          5281.90-5140.39      7788-7930
5,320.76                       7749                          5320.76-5140.39      7749-7930

then move to the next record
Number 1      Number 2      DeltaNumber1      DeltaNumber2
5,140.39                        7930            
5,148.17                        7922            
5,165.65                        7905                         5165.65-5148.17      7905-7922
5,204.42                        7866                         5204.42-5148.17      7866-7922
5,239.80                        7830                         5239.80-5148.17      7830-7922
5,243.09                        7827                         5243.09-5148.17      7827-7922
5,281.90                        7788                         5281.90-5148.17      7788-7922
5,320.76                        7749                         5320.76-5148.17      7749-7922
Brian CroweDatabase AdministratorCommented:
It's no clearer to me than before.  The result set is the FINAL output not a logical representation of how they are calculated.  Unless you want 2 new columns (DeltaNumber1 & DeltaNumber2) with text "5165.65-5149.17" etc.
yanci1179Author Commented:
DeltaNumber1 is the difference between first number and next first number.  in example below, 5148.17 is being subtracted from each number.  then I would move to number 5165.65 and do the same for all remaining records.

Number 1      Number 2      DeltaNumber1      DeltaNumber2
5,148.17                       7922            
5,165.65                        7905                          17.48                          -17.58
5,204.42                        7866                          56.25                           -56.57
5,239.80                         7830                           91.63                          -92.16
5,243.09                         7827                           94.92                            -95.47
5,281.90                           7788                          133.73                           -134.50
5,320.76                          7749                           172.59                              -173.58
Brian CroweDatabase AdministratorCommented:
I have a feeling I'm still missing something.

SELECT E.ID, E.Number1, E.Number2,
	E.Number1 - E1.Number1 AS DeltaNumber1,
	E.Number2 - E1.Number2 AS DeltaNumber2
FROM #Example AS E
CROSS APPLY 
(
	SELECT ID, Number1, Number2
	FROM #Example
	WHERE ID = 1
) AS E1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
    Create table #Example
     (ID int identity (1,1)
     ,Number1 numeric(10,2)
     ,Number2 numeric(10,2))
    
    insert into #Example
    select 5140.39      ,7930 union all 
    select 5148.17      ,7922 union all
    select 5165.65      ,7905 union all
    select 5204.42      ,7866 union all
    select 5239.80      ,7830 union all 
    select 5243.09      ,7827 union all
    select 5281.90      ,7788 union all
    select 5320.76      ,7749
    

Open in new window

for the result seen below I used a CROSS JOIN. Note however this query does not display rows that do not have DeltaNumber1 & DeltaNumber2 results, that could be modified using a UNION ALL but perhaps you don't need those rows. I have included more columns to display the numbers behind the calculations
    SELECT
          e1.id
        , e2.id as e2id
        , e1.Number1 
        , e2.Number1 as e2num1
        , e1.Number1 - e2.Number1 AS DeltaNumber1
        , e1.Number2 
        , e2.Number2 as e2num2
        , e1.Number2 - e2.Number2 AS DeltaNumber2
    FROM #Example AS e1
          CROSS JOIN #Example AS e2
    WHERE e2.id < e1.id
    ORDER BY
          e2.id
        , e1.id

Open in new window

Results
    | id | e2id | Number1 |  e2num1 | DeltaNumber1 | Number2 | e2num2 | DeltaNumber2 |
    |----|------|---------|---------|--------------|---------|--------|--------------|
    |  2 |    1 | 5148.17 | 5140.39 |         7.78 |    7922 |   7930 |           -8 |
    |  3 |    1 | 5165.65 | 5140.39 |        25.26 |    7905 |   7930 |          -25 |
    |  4 |    1 | 5204.42 | 5140.39 |        64.03 |    7866 |   7930 |          -64 |
    |  5 |    1 |  5239.8 | 5140.39 |        99.41 |    7830 |   7930 |         -100 |
    |  6 |    1 | 5243.09 | 5140.39 |        102.7 |    7827 |   7930 |         -103 |
    |  7 |    1 |  5281.9 | 5140.39 |       141.51 |    7788 |   7930 |         -142 |
    |  8 |    1 | 5320.76 | 5140.39 |       180.37 |    7749 |   7930 |         -181 |
    |  3 |    2 | 5165.65 | 5148.17 |        17.48 |    7905 |   7922 |          -17 |
    |  4 |    2 | 5204.42 | 5148.17 |        56.25 |    7866 |   7922 |          -56 |
    |  5 |    2 |  5239.8 | 5148.17 |        91.63 |    7830 |   7922 |          -92 |
    |  6 |    2 | 5243.09 | 5148.17 |        94.92 |    7827 |   7922 |          -95 |
    |  7 |    2 |  5281.9 | 5148.17 |       133.73 |    7788 |   7922 |         -134 |
    |  8 |    2 | 5320.76 | 5148.17 |       172.59 |    7749 |   7922 |         -173 |
    |  4 |    3 | 5204.42 | 5165.65 |        38.77 |    7866 |   7905 |          -39 |
    |  5 |    3 |  5239.8 | 5165.65 |        74.15 |    7830 |   7905 |          -75 |
    |  6 |    3 | 5243.09 | 5165.65 |        77.44 |    7827 |   7905 |          -78 |
    |  7 |    3 |  5281.9 | 5165.65 |       116.25 |    7788 |   7905 |         -117 |
    |  8 |    3 | 5320.76 | 5165.65 |       155.11 |    7749 |   7905 |         -156 |
    |  5 |    4 |  5239.8 | 5204.42 |        35.38 |    7830 |   7866 |          -36 |
    |  6 |    4 | 5243.09 | 5204.42 |        38.67 |    7827 |   7866 |          -39 |
    |  7 |    4 |  5281.9 | 5204.42 |        77.48 |    7788 |   7866 |          -78 |
    |  8 |    4 | 5320.76 | 5204.42 |       116.34 |    7749 |   7866 |         -117 |
    |  6 |    5 | 5243.09 |  5239.8 |         3.29 |    7827 |   7830 |           -3 |
    |  7 |    5 |  5281.9 |  5239.8 |         42.1 |    7788 |   7830 |          -42 |
    |  8 |    5 | 5320.76 |  5239.8 |        80.96 |    7749 |   7830 |          -81 |
    |  7 |    6 |  5281.9 | 5243.09 |        38.81 |    7788 |   7827 |          -39 |
    |  8 |    6 | 5320.76 | 5243.09 |        77.67 |    7749 |   7827 |          -78 |
    |  8 |    7 | 5320.76 |  5281.9 |        38.86 |    7749 |   7788 |          -39 |

Open in new window

also see: http://sqlfiddle.com/#!6/9eecb7/4061
Vitor MontalvãoMSSQL Senior EngineerCommented:
yanci1179, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.