临时表上的sql update查询

I have a temporary table like below :

我有一个如下的临时表:

  Date    Fund  NotAdded    Dividend
1/1/2017    A     0           0
1/2/2017    A     0           0
1/3/2017    A   100           0
1/4/2017    A     0         200
1/5/2017    A    50          50
1/1/2017    B     0           0
1/2/2017    B   100           0
1/3/2017    B     0         200
1/4/2017    B     0           0

I want to run a update query on above table in a way that I will get result like below:

我想在上面的表格上运行更新查询,我将获得如下结果:

Date      Fund  NotAdded    Dividend   Notional
1/1/2017    A     0           0         5000 (this value is known)
1/2/2017    A     0           0         5000
1/3/2017    A   100           0         5100
1/4/2017    A     0         200         5300
1/5/2017    A    50          50         5400
1/1/2017    B     0           0         2000
1/2/2017    B   100           0         2100  
1/3/2017    B     0         200         2300
1/4/2017    B     0           0         2300

For each fund, default Notional value is known. i.e. in above example 5000 for fund A and 2000 for fund B.

对于每个基金,默认的名义值是已知的。即在上面的例子中,基金A为5000,基金为2000。

I tried but not able to get the desired output. Any help!!!

我尝试但无法获得所需的输出。任何帮助!!!

3 个解决方案

#1

You seem to want a cumulative sum of NotAdded and Dividend plus some random number. That latter I can't help with.

您似乎想要NotAdded和Dividend的累积总和加上一些随机数。后者我无法帮助。

In SQL Server 2012+, you would use the cumulative sum function. In SQL Server 2008, you can use a later join (apply) or correlated subquery:

在SQL Server 2012+中,您将使用累积和函数。在SQL Server 2008中,您可以使用以后的联接(应用)或相关子查询:

select t.*,
       (select sum(t2.NotAdded) + sum(t2.Dividend)
        from #temp t2
        where t2.fund = t.fund and
              t2.date <= t.date
       ) as Cumulative
from #temp t;

You can add the "known" value to cumulative to get the final value.

您可以将“已知”值添加到累计以获取最终值。

If you want this as an update:

如果您想将此作为更新:

update t
    set notional = notional + 
                   (select sum(t2.NotAdded) + sum(t2.Dividend)
                    from #temp t2
                    where t2.fund = t.fund and
                          t2.date <= t.date
                   )
    from #temp t;

This assumes that notional starts with the value in question.

这假定名义从有问题的值开始。

#2

You may use a COMPUTED column with a LAG function to SUM your columns considering the preceding row value..

考虑到前面的行值,您可以使用带有LAG函数的COMPUTED列来对列进行求和。

Just OVER against your Fund and Date columns. The use of a primary key would be preferred, but if you've got none I guess it'll do the job for now.

仅针对您的基金和日期列。主键的使用将是首选,但如果你没有,我想它现在可以完成这项工作。

#3

Assuming that this is once off issue and not a permanent issue. I have come up with the following solution based on the following link :

假设这是一次性问题而不是永久性问题。我基于以下链接提出了以下解决方案:

How to get cumulative sum

如何获得累计金额

I will do brief explanation of what I have done and then you can look at the code below.

我将简要解释我所做的事情,然后你可以查看下面的代码。

First I created a temp table to imitate your temp table with one change, I insert an identity(1,1). This is required for the calculation part.

首先,我创建了一个临时表来模拟你的临时表,只有一个变化,我插入一个标识(1,1)。这是计算部分所必需的。

Then I inserted the data you had with the "known value" for the first row with fund type a (5000) and the first fund type b (2000).

然后我插入了第一行的“已知值”的数据,基金类型为a(5000),第一个基金类型为b(2000)。

Then there is two inserts into a temp table where the first main table joins onto itself and adds its NotAdded + Dividend + Notional values together from the second higher id (identity field) to the lower id.

然后在临时表中有两个插入,其中第一个主表连接到自身,并将其NotAdded + Dividend + Notional值从第二个较高的id(标识字段)添加到较低的id。

After that all that is left to do is update your original table where the id's match. Please see code below.

之后剩下要做的就是更新id匹配的原始表。请参阅下面的代码。

--create temp table
DECLARE @TEMP TABLE 
(
    [id] INT IDENTITY(1,1), 
    [DATE] [datetime] NULL,
    [Fund] [varchar](1) NULL,
    [NotAdded] [int] NULL,
    [Dividend] [int] NULL,
    [Notional] [int] NULL   
)


--insert into temp table with first known value
INSERT INTO @TEMP ([DATE], [Fund], [NotAdded], [Dividend], Notional)
VALUES  ('1/1/2017','A',0,0,5000), --First value of A is known
        ('1/2/2017','A',0,0,0),
        ('1/3/2017','A',100,0,0),
        ('1/4/2017','A',0,200,0),
        ('1/5/2017','A',50,50,0),
        ('1/1/2017','B',0,0,2000), --First value of B is known
        ('1/2/2017','B',100,0,0),
        ('1/3/2017','B',0,200,0),
        ('1/4/2017','B',0,0,0)

--select into temp table for type a so that we can update the @temp table later 
SELECT T.id, T.Fund, T.NotAdded, T.Dividend, SUM(T2.NotAdded + T2.Dividend + T2.Notional)  AS Notional_Sum
INTO #TEMP_A
FROM @TEMP T 
    INNER JOIN @TEMP t2 on T.id >= t2.id
                        AND T2.Fund = T.Fund
WHERE 1=1
    AND T.Fund = 'A'
GROUP BY T.id, T.Fund,T.NotAdded, T.Dividend
ORDER BY T.id

--select into temp table for type b so that we can update the @temp table later 
SELECT T.id, T.Fund, T.NotAdded, T.Dividend, SUM(T2.NotAdded + T2.Dividend + T2.Notional)  AS Notional_Sum
INTO #TEMP_B
FROM @TEMP T 
    INNER JOIN @TEMP t2 on T.id >= t2.id
                        AND T2.Fund = T.Fund
WHERE 1=1
    AND T.Fund = 'B'
GROUP BY T.id, T.Fund,T.NotAdded, T.Dividend
ORDER BY T.id

--finally do updates
UPDATE T
SET T.Notional = TTS.Notional_Sum
FROM @TEMP T
        INNER JOIN #TEMP_A TTS ON TTS.id = T.id
WHERE 1=1

UPDATE T
SET T.Notional = TTS.Notional_Sum
FROM @TEMP T
        INNER JOIN #TEMP_B TTS ON TTS.id = T.id
WHERE 1=1


--select to view
SELECT  * FROM @TEMP

--drop temp tables
DROP TABLE #TEMP_A, #TEMP_B

#1