天道酬勤,学无止境

T-SQL Updating current row to next row's value issue

I am looking for some advice on what type of update would be the best in a certain scenario for t-sql DML.

came into a data issue where end dates are one day before the start date on a current record, and need to set the end date to the start date of the next in row for multiple entities

For example

rowid     entity    id  record_type start_date  end_date
214       250       1   H           2015-01-01  2014-12-31
329       250       1   H           2015-04-25  2015-04-24
533       250       1   C           2015-11-01  NULL
11        250       2   H           2015-06-01  2014-05-29
292       250       2   H           2015-09-11  2015-09-10
987       250       2   C           2015-10-01  NULL

What I need to do is update the first record end_date to the next record startdate - 1 on each employee/entity.

Currently, there are over 5K entities affected by this, so I am trying to get this updated on each record somehow to save time on this.

What I can do but is taking a lot of time, is to 1. get a max rowcount of history records for all companies into one number 2. create the same amount of temp tables for the number of total rows 3. insert minimum start date values into first temp table 4. insert minimum value not in temp table 1 into table 2, and so on 5. then update temp table 1's end date to temptable 2's startdate -1 day 6. from there, run an update on the actual table with multiple update statements for each temp table, joined on rowid.

Final output would look like:

rowid     entity    id  record_type start_date  end_date
214       250       1   H           2015-01-01  2014-04-24
329       250       1   H           2015-04-25  2015-10-31
533       250       1   C           2015-11-01  NULL
11        250       2   H           2015-06-01  2014-09-10
292       250       2   H           2015-09-11  2015-9-31
987       250       2   C           2015-10-01  NULL

Any suggestions besides my long list of temp tables/updates would be greatly appreciated! I was thinking something along the lines of possibly a cursor, but I am not too sure if this would be a quicker way of writing an update for this scenario.

评论

I think updatable CTEs are the way to go. In SQL Server 2012+, you can use lead():

with toupdate as (
      select t.*,
             lead(start_date) over (partition by entity order by start_date) as next_start_date
      from t
     )
update toupdate
     set end_date = dateadd(day, -1, next_start_date)
     where end_date = dateadd(day, -1, start_date);

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • 如何从SQL返回结果页面?(How to return a page of results from SQL?)
    问题 许多应用程序都有网格,一次显示一页数据库表中的数据。 他们中的许多人还允许用户选择每页的记录数,按任何列排序以及在结果之间来回导航。 在不将整个表提交给客户端然后过滤客户端上的数据的情况下,实现此模式的好算法是什么? 您如何只将要显示的记录带给用户? LINQ是否简化了解决方案? 回答1 在MS SQL Server 2005及更高版本上,ROW_NUMBER()似乎可以工作: T-SQL:使用ROW_NUMBER()分页 DECLARE @PageNum AS INT; DECLARE @PageSize AS INT; SET @PageNum = 2; SET @PageSize = 10; WITH OrdersRN AS ( SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum ,OrderID ,OrderDate ,CustomerID ,EmployeeID FROM dbo.Orders ) SELECT * FROM OrdersRN WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize ORDER BY OrderDate ,OrderID; 回答2 我建议要么使用LINQ
  • 使用T-SQL汇总仅相邻记录(Aggregate adjacent only records with T-SQL)
    问题 我(在示例中简化了)一个包含以下数据的表 Row Start Finish ID Amount --- --------- ---------- -- ------ 1 2008-10-01 2008-10-02 01 10 2 2008-10-02 2008-10-03 02 20 3 2008-10-03 2008-10-04 01 38 4 2008-10-04 2008-10-05 01 23 5 2008-10-05 2008-10-06 03 14 6 2008-10-06 2008-10-07 02 3 7 2008-10-07 2008-10-08 02 8 8 2008-10-08 2008-11-08 03 19 日期表示一个时间段,ID是该时间段内系统所处的状态,金额是与该状态有关的值。 我想做的是汇总具有相同ID号的相邻行的金额,但保持相同的总体顺序,以便可以合并连续的运行。 因此,我想得出的数据如下: Row Start Finish ID Amount --- --------- ---------- -- ------ 1 2008-10-01 2008-10-02 01 10 2 2008-10-02 2008-10-03 02 20 3 2008-10-03 2008-10-05 01 61 4 2008-10-05 2008-10-06
  • T-SQL 中的悲观锁(Pessimistic lock in T-SQL)
    问题 如果我在 MS SQL Server 中选择一行进行更新,并希望将其锁定,直到我更新或取消,哪个选项更好:- 1) 使用像 UPDLOCK 这样的查询提示 2) 对事务使用 REPEATABLE READ 隔离级别 3) 任何其他选项。 谢谢,查克。 回答1 两者都不。 当您的用户输入数据时,您几乎不想让事务保持打开状态。 如果您必须实现这样的悲观锁,人们通常会通过滚动自己的功能来实现。 考虑你正在做的事情的全部后果。 我曾经在一个系统上工作过,它实现了这样的锁定。 您经常会遇到大量陈旧的锁,当您强加给他们时,您的用户很快就会感到困惑和愤怒。 在我们的案例中,我们的解决方案是完全删除此锁定功能。 回答2 如果您正在等待其他资源(例如最终用户),请采纳 Dave Markle 的建议,不要这样做。 否则,请尝试以下 T-SQL 代码: BEGIN TRAN SELECT * FROM authors AU WITH (HOLDLOCK, ROWLOCK) WHERE AU.au_id = '274-80-9391' /* Do all your stuff here while the row is locked */ COMMIT TRAN HOLDLOCK提示礼貌地要求 SQL Server 保持锁定,直到您提交事务。 ROWLOCK提示礼貌地要求 SQL Server
  • 简单的T-SQL而不是触发器(Simple t-sql instead of trigger)
    问题 有人可以通过板载触发器帮助解决简单的t-sql脚本问题吗? 我使用非常简单的触发器将数据从一个表复制到另一个表(这些表之间没有关系)。 当我尝试后直接触发创建(来自同一个脚本),我得到想要的结果插入在第一次数据,但所有未来的尝试得到了失败,下一个提示:” The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. ``我很困惑那是什么意思。 查看下面的触发器: CREATE TRIGGER AuthorInsert ON Author INSTEAD OF INSERT AS BEGIN -- //- 1 -// --***************** if insert was correct ******************** IF (SELECT COUNT(*) FROM INSERTED) > 0 BEGIN -
  • Office.js Word Add-In: Performance Issue with Updating Values in Large Tables
    Summary: Updating values in large Word tables (larger than 10 by 10) is very slow. Performance gets exponentially worse with table size. I'm using myTable.values = arrNewValues. I've also tried myTable.addRows("end", rows, arrNewValues). Where arrNewValues is a 2D array. I've also tried using updating via getOoxml() and insertOoxml(), but ran into other issues I haven't been able to resolve, but has good performance. Slow performance seems to be caused by "ScreenUpdating" (same issue exists in VBA and is solved via ScreenUpdating=false). I believe it is critically important to add the ability
  • T-SQL 如何从表中只选择第二行?(T-SQL How to select only Second row from a table?)
    问题 我有一张表,我需要检索第二行的 ID。 如何做到这一点? 通过Top 2我选择前两行,但我只需要第二行 回答1 假设的SQL Server 2005+的例子,如何得到公正的第二行(我想你可能会问-而之所以top ?会为你不工作) set statistics io on ;with cte as ( select * , ROW_NUMBER() over (order by number) as rn from master.dbo.spt_values ) select * from cte where rn = 2 /* Just to add in what I was running RE: Comments */ ;with cte as ( select top 2 * , ROW_NUMBER() over (order by number) as rn from master.dbo.spt_values ) select * from cte where rn = 2 回答2 在 SQL Server 2012+ 中,您可以使用 OFFSET...FETCH: SELECT <column(s)> FROM <table(s)> ORDER BY <sort column(s)> OFFSET 1 ROWS -- Skip this number of
  • 如何在 SSIS 中使用 foreach 循环容器遍历多维数组?(How can I use a foreach loop container in SSIS to iterate through a multidimensional array?)
    问题 我有一个 SSIS 包,它使用脚本和一个 foreach 容器以及其他一些东西来更新几个表。 我正在使用SqlConnection和SqlCommand在 foreach 容器之前的脚本任务中调用此查询: SELECT TOP 10 DirectoryID, DirectoryPath FROM ProjectDirectory ORDER BY LastAudit 并使用SqlDataReader将它们添加到多维数组 [10,2]。 我将数组分配给一个对象变量DirectoryList以便我可以在 foreach 循环容器中遍历它。 它应该看起来像 { {"1", "C:\Folder1"}, {"2", "C:\Folder2"}, ...} 我相信。 问题是我几天前才开始使用这个软件,对 foreach 循环容器如何循环遍历DirectoryList变量感到困惑。 目前,在容器的集合选项卡中,我将 Enumerator 设置为 Foreach From Variable Enumerator,变量设置为User::DirectoryList 。 我将变量User::DirectoryID设置为索引 0,并将User::DirectoryPath设置为索引 1。我试图让它循环遍历数组的顶层,以便在每个循环中 ID 和路径变量被分配给“1”和“C:\Folder1”
  • T-SQL计算移动平均值(T-SQL calculate moving average)
    问题 我正在使用SQL Server 2008 R2,试图计算移动平均值。 对于我认为的每个记录,我想收集以前的250条记录的值,然后计算此选择的平均值。 我的视图列如下: TransactionID | TimeStamp | Value | MovAvg ---------------------------------------------------- 1 | 01.09.2014 10:00:12 | 5 | 2 | 01.09.2014 10:05:34 | 3 | ... 300 | 03.09.2014 09:00:23 | 4 | TransactionID是唯一的。 对于每个TransactionID ,我想计算前250条记录的列值的平均值。 因此,对于TransactionID 300,请收集前250行的所有值(视图按TransactionID降序排列),然后在MovAvg列中写入这些值的平均值。 我希望收集一系列记录中的数据。 回答1 与更高版本相比,SQL 2008中的窗口功能非常有限,如果我记得正确,则只能分区,不能使用任何行/范围框架限制,但我认为这可能是您想要的: ;WITH cte (rn, transactionid, value) AS ( SELECT rn = ROW_NUMBER() OVER (ORDER BY
  • 【T-SQL基础】03.子查询
    概述: 本系列【T-SQL基础】主要是针对T-SQL基础的总结。 本篇主要是对子查询基础的总结。以前总是追求新东西,发现基础才是最重要的,今年主要的目标是精通SQL查询和SQL性能优化。本系列【T-SQL基础】主要是针对T-SQL基础的总结。【T-SQL基础】01.单表查询-几道sql查询题【T-SQL基础】02.联接查询【T-SQL基础】03.子查询【T-SQL基础】04.表表达式-上篇【T-SQL基础】04.表表达式-下篇【T-SQL基础】05.集合运算【T-SQL基础】06.透视、逆透视、分组集【T-SQL基础】07.数据修改【T-SQL基础】08.事务和并发【T-SQL基础】09.可编程对象 ----------------------------------------------------------【T-SQL进阶】01.好用的SQL TVP~~独家赠送[增-删-改-查]的例子 ----------------------------------------------------------【T-SQL性能调优】01.TempDB的使用和性能问题【T-SQL性能调优】02.Transaction Log的使用和性能问题【T-SQL性能调优】03.执行计划【T-SQL性能调优】04.死锁分析持续更新......欢迎关注我! 练习题:1.写一条查询语句
  • t-sql loop through all rows and sum amount from column until value is reached
    I have a table containing the below test data: I now would like to fill a restaurant with 12 seating spaces. This should result in: Basically, I need to loop from top to bottom through all rows and add the AmountPersons until I have filled the restaurant. In this example: (first few rows: AmountPersons) 3+1+2+4 = 10 UserId 52 can't be added because they reserved for 3 persons, which would result in 13 occupied places and there are only 12 available. In the next row it notices a reservation for 1. This can be added to the previous 10 we already found. NewTotal is now 11. UserId 79 and 82 can't
  • 基础很重要~~04.表表达式-下篇
    本篇主要是对表表达式中视图和内联表值函数基础的总结。 以前总是追求新东西,发现基础才是最重要的,今年主要的目标是精通SQL查询和SQL性能优化。本系列【T-SQL基础】主要是针对T-SQL基础的总结。【T-SQL基础】01.单表查询-几道sql查询题【T-SQL基础】02.联接查询【T-SQL基础】03.子查询【T-SQL基础】04.表表达式-上篇【T-SQL基础】04.表表达式-下篇【T-SQL基础】05.集合运算【T-SQL基础】06.透视、逆透视、分组集【T-SQL基础】07.数据修改【T-SQL基础】08.事务和并发【T-SQL基础】09.可编程对象 ----------------------------------------------------------【T-SQL进阶】01.好用的SQL TVP~~独家赠送[增-删-改-查]的例子 ----------------------------------------------------------【T-SQL性能调优】01.TempDB的使用和性能问题【T-SQL性能调优】02.Transaction Log的使用和性能问题【T-SQL性能调优】03.执行计划【T-SQL性能调优】04.死锁分析持续更新......欢迎关注我!概述:本篇主要是对表表达式中视图和内联表值函数基础的总结。表表达式包含四种:  1
  • 如何忽略T-SQL(SQL Server)中的“重复键”错误(How to Ignore “Duplicate Key” error in T-SQL (SQL Server))
    问题 我有一个包含多个SQL语句(INSERT,UPDATE和/或DELETES)的事务。 执行时,我想忽略Duplicate Error语句,然后继续执行下一条语句。 最好的方法是什么? 回答1 尽管我对您的重要建议是构造SQL,以便不要尝试重复插入(Philip Kelley的代码段可能就是您所需要的),但我想提一句语句中的错误不一定会导致回滚。 除非XACT_ABORT为ON ,否则遇到错误时,事务将不会自动回滚,除非它严重到足以杀死连接为止。 XACT_ABORT默认为OFF 。 例如,以下sql成功将三个值插入到表中: create table x ( y int not null primary key ) begin transaction insert into x(y) values(1) insert into x(y) values(2) insert into x(y) values(2) insert into x(y) values(3) commit 除非您设置XACT_ABORT ,否则客户端将引发错误并导致回滚。 如果出于某种可怕的原因而无法避免插入重复项,则应该能够将错误捕获到客户端上并忽略它。 回答2 我认为您正在索引上寻找IGNORE_DUP_KEY选项。 请查看http://msdn.microsoft.com/zh-cn/library
  • T-SQL Pivot - 总行数和动态列数(T-SQL Pivot - Total Row and Dynamic Columns)
    问题 让我们直接跳进去。 这是代码 SELECT [prov], [201304], [201305], [201306], [201307] FROM ( SELECT [prov], [arrival], [Amount] FROM [tblSource]) up PIVOT (SUM([Amount]) FOR [arrival] IN ([201304], [201305], [201306], [201307])) AS pvt GO 它给我带回了一张如此可爱的桌子。 我想知道如何将每个“日期”列的总数显示在附加的最后一行中? 此外,基础表将添加更多数据,特别是更多日期。 这意味着接下来将添加201308 ,然后是201309等 这意味着目前我必须每个月修改上面的代码以反映添加内容。 有没有办法解决? 回答1 您可以使用动态 SQL 动态创建列,但是,我真的建议在为其设计的层(例如 SSRS 或 excel)中处理动态枢轴。 DECLARE @SQL NVARCHAR(MAX) = '', @SQL2 NVARCHAR(MAX) = '', @SQL3 NVARCHAR(MAX) = ''; -- COMPILE THE UNIQUE VALUES FOR ARRIVAL THAT NEED TO BE PIVOTED SELECT @SQL = @SQL + ',' +
  • 好用的SQL TVP~~独家赠送[增-删-改-查]的例子
    表值参数Table-Value Parameter (TVP) 提供一种将客户端应用程序中的多行数据封送到 SQL Server 的简单方式,而不需要多次往返或特殊服务器端逻辑来处理数据。可以使用表值参数来包装客户端应用程序中的数据行,并使用单个参数化命令将数据发送到服务器。 传入的数据行存储在一个表变量中,然后您可以通过使用 Transact-SQL 对该表变量进行操作。 以前总是追求新东西,发现基础才是最重要的,今年主要的目标是精通SQL查询和SQL性能优化。 本系列主要是针对T-SQL的总结。【T-SQL基础】01.单表查询-几道sql查询题【T-SQL基础】02.联接查询【T-SQL基础】03.子查询【T-SQL基础】04.表表达式-上篇【T-SQL基础】04.表表达式-下篇【T-SQL基础】05.集合运算【T-SQL基础】06.透视、逆透视、分组集【T-SQL基础】07.数据修改【T-SQL基础】08.事务和并发【T-SQL基础】09.可编程对象 ----------------------------------------------------------【T-SQL进阶】01.好用的SQL TVP~~独家赠送[增-删-改-查]的例子 ----------------------------------------------------------【T
  • T-SQL Pivot - Total Row and Dynamic Columns
    Let's jump straight into it. Here's the code SELECT [prov], [201304], [201305], [201306], [201307] FROM ( SELECT [prov], [arrival], [Amount] FROM [tblSource]) up PIVOT (SUM([Amount]) FOR [arrival] IN ([201304], [201305], [201306], [201307])) AS pvt GO It brings me back an ever so lovely table. I was wondering how I would get the totals for each "date" column to show in an appended last row? In addition, the underlying table will have more data added, specifically more dates. This means that 201308 will be added next, then 201309 etc This will mean that currently I will have to amend the code
  • Game of Life - Overwriting the current generation instead of updating to the next
    Below I have added my game of life code. The rules are defined correctly, and it runs smoothly. However, the game does not work as supposed. It is not updating to the next generation, but it seems to be overwriting the current generation. As an example: Three horizontal dots are supposed to turn into three vertical dots in the next generation, but this does not happen. The solution: I have two generations, the current and the next generation. It has to apply the rules to the current generation and update them in the next generation. Then it has to overwrite the current generation with the next
  • 详解SQL集合运算
    本篇主要是对集合运算中并集、交集、差集运算基础的总结。 以前总是追求新东西,发现基础才是最重要的,今年主要的目标是精通SQL查询和SQL性能优化。本系列【T-SQL基础】主要是针对T-SQL基础的总结。【T-SQL基础】01.单表查询-几道sql查询题【T-SQL基础】02.联接查询【T-SQL基础】03.子查询【T-SQL基础】04.表表达式-上篇【T-SQL基础】04.表表达式-下篇【T-SQL基础】05.集合运算【T-SQL基础】06.透视、逆透视、分组集【T-SQL基础】07.数据修改【T-SQL基础】08.事务和并发【T-SQL基础】09.可编程对象 ----------------------------------------------------------【T-SQL进阶】01.好用的SQL TVP~~独家赠送[增-删-改-查]的例子 ----------------------------------------------------------【T-SQL性能调优】01.TempDB的使用和性能问题【T-SQL性能调优】02.Transaction Log的使用和性能问题【T-SQL性能调优】03.执行计划【T-SQL性能调优】04.死锁分析持续更新......欢迎关注我!概述:本篇主要是对集合运算中并集、交集、差集运算基础的总结。集合运算包含三种:  1
  • 【T-SQL基础】02.联接查询
    概述: 本系列【T-SQL基础】主要是针对T-SQL基础的总结。 本篇主要是对多表查询基础的总结。概述:本系列【T-SQL基础】主要是针对T-SQL基础的总结。【T-SQL基础】01.单表查询-几道sql查询题【T-SQL基础】02.联接查询【T-SQL基础】03.子查询【T-SQL基础】04.表表达式-上篇【T-SQL基础】04.表表达式-下篇【T-SQL基础】05.集合运算【T-SQL基础】06.透视、逆透视、分组集【T-SQL基础】07.数据修改【T-SQL基础】08.事务和并发【T-SQL基础】09.可编程对象 ----------------------------------------------------------【T-SQL进阶】01.好用的SQL TVP~~独家赠送[增-删-改-查]的例子 ----------------------------------------------------------【T-SQL性能调优】01.TempDB的使用和性能问题【T-SQL性能调优】02.Transaction Log的使用和性能问题【T-SQL性能调优】03.执行计划【T-SQL性能调优】04.死锁分析持续更新......欢迎关注我! 本篇主要是对多表查询基础的总结。查询语句的FROM字句在逻辑上是第一条要处理的字句
  • Calculating value using previous value of a row in T-SQL
    I got following table and want to calculate value of Column2 on each row using the value of the same column (Column2) from the previous row in a sql without using cursor or while loop. Id Date Column1 Column2 1 01/01/2011 5 5 => Same as Column1 2 02/01/2011 2 18 => (1 + (value of Column2 from the previous row)) * (1 + (Value of Column1 from the current row)) i.e. (1+5)*(1+2) 3 03/01/2011 3 76 => (1+18)*(1+3) = 19*4 and so on Any thoughts?
  • MySQL 语法 LIMIT x, y 的 T-SQL 等价物是什么? [复制](What is the T-SQL equivalent of MySQL syntax LIMIT x, y? [duplicate])
    问题 这个问题已经在这里有了答案: 8年前关闭。 可能重复: 如何在SQL Server中编写(MySQL)“ LIMIT”? 如何使用LIMIT Inside更改SQL Server的查询? 代码: SELECT apretiz FROM tableApoint WHERE price = '$newprice' LIMIT 5; 许多事情不起作用,所以只是寻求帮助 我如何通过示例更改LIMIT 5,10? 我不能用 TOP 吗? 回答1 正如我不到一小时前所说的那样,您必须使用 TOP ! (LIMIT用于MYSQL) 因此,尝试删除LIMIT 5并执行SELECT TOP(5) apretiz 。 另外,尝试添加 order by(与以前相同的原因)。 在询问问题之前,请先进行搜索。 链接到旧问题 回答2 从SQL Server 2012开始,您可以编写 ... ORDER BY thisColumn, thatColumn OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY 回答3 使用TOP关键字: SELECT TOP 5 pretiz FROM tableApoint WHERE price = '$newprice' 使用LIMIT 5、10不是SQL标准的一部分,仅在MySQL中可用。 您可以将ROW_NUMBER(