CODEWITHSUNDEEP
sqlsql-serverselectinner-join
i.n.n.m
i.n.n.m

Reputation: 3046

Inner join in Select Statement to create a variable in SQL Server

I have a claimstable like the following. 

Time             Terminal_ID  Claims   Count
-------------------------------------------
2017-10-19 06:03:00     1      451        1
2017-10-19 06:04:00     1      452        2
2017-10-19 06:05:00     1      452.3      2
2017-10-19 06:06:00     1      458        2
2017-10-19 06:03:00     9      459        6 
2017-10-19 06:04:00     9      461.2      5
2017-10-19 06:05:00     9      462        2
2017-10-19 06:06:00     9      463        1

I select time, claim and create a new column Claims_Change from my table. After this, I insert it into a temporary table.

My expected output would be:

          Time       Terminal_ID  Claims   Count Claims_change
    ---------------------------------------------------------
    2017-10-19 06:03:00     1      451        1     Null
    2017-10-19 06:04:00     1      452        2     1
    2017-10-19 06:05:00     1      452.3      2     0.3
    2017-10-19 06:06:00     1      458        2     5.7
    2017-10-19 06:03:00     9      459        6     Null
    2017-10-19 06:04:00     9      461.2      5     2.2
    2017-10-19 06:05:00     9      462        2     0.8
    2017-10-19 06:06:00     9      463        1     1

This is my query:

select
    [Time], Terminal_ID, Claims, 
    convert(decimal(12, 3), y.[Claims] - (select t1.[Claims] 
                                          from claimstable t1 
                                          where t1.Terminal_ID = y.Terminal_ID 
                                            and t1.[Time] = (select max([Time])
                                                             from claimstable t2
                                                             where  t2.Terminal_ID = t1.Terminal_ID 
                                                               and t2.[Time] < y.[Time])
                    )) as Claims_change
into 
    #temptable
from 
    claimstable as y

When I tested this for 5000 rows, it works fine, however, when I test this with 100,000 rows, I get an error like this,

Msg 512, Level 16, State 1, Line 42
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I was reading some older posts, the recommended answer was to use inner join.

Also, in my case, I don't know how to exactly write to get Claims_change variable using inner join.

Any help would be awesome.

Upvotes: 0

Views: 2160

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can try this.

select
    [Time], Terminal_ID, y.Claims, convert(decimal(12,3), y.[Claims]- t2.[Claims] ) as Claims_change
into 
    #temptable
from 
    claimstable as y
    OUTER APPLY ( SELECT TOP 1 t1.[Claims] FROM claimstable t1 where
            t1.Terminal_ID = y.Terminal_ID AND y.[Time] > t1.[Time] ORDER BY [Time] DESC ) as  t2

Result:

Time                    Terminal_ID Claims       Claims_change
----------------------- ----------- ------------ ---------------
2017-10-19 06:03:00.000 1           451.00       NULL
2017-10-19 06:04:00.000 1           452.00       1.000
2017-10-19 06:05:00.000 1           452.30       0.300
2017-10-19 06:06:00.000 1           458.00       5.700
2017-10-19 06:03:00.000 9           459.00       NULL
2017-10-19 06:04:00.000 9           461.20       2.200
2017-10-19 06:05:00.000 9           462.00       0.800
2017-10-19 06:06:00.000 9           463.00       1.000

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

If the version of SQL Server you are on supports LAG, use 

select t.*,t.claims-lag(t.claims) over(partition by terminal_id order by time) as claims_change
from claimstable t

LAG gets the value of a specified column from the previous row based on a specified ordering and partitioning. LAG for first row (in a partition) will be NULL. You can use the optional argument to specify a default value.

Upvotes: 1

Related Questions