合并&合并几行日期 [英] Consolidate & Merge Several Rows of Dates

问题描述

给出下表：

展开 | 选择 | Wrap | 行号

推荐答案

@MahaCoder


Hey MahaCoder，


从您所描述的内容来看，这听起来像是在尝试将数据规范化。没有任何查询可以开箱即用。您是必须编写VBA并使用已处理的数据创建新表，这是正确的。如果您使用DB2数据源作为永不改变的源，那么如果您一直在进行转换，那么您将会遇到大量的进程延迟问题。


如果这是一个纠正错误的过程，那么规范化数据将是非常值得的。但至于继续使用当前数据集的工作模型在没有大量性能影响的情况下是不可取的。


如果在编写VBA和SQL语句时需要帮助，请让我知道。另外，请提供您已经测试过的任何工作，目前是否正在工作。


谢谢，


Joe P 。

@MahaCoder

Hey MahaCoder,

From what you are describing, it sounds like you''re trying to normalize data here. There isn''t going to be any query that is going to be able to do this out of the box. You are correct in the fact that you are going to have to write VBA and create a new table with the processed data. If you''re using the DB2 datasource as your never changing source, you''re going to have a great deal of latency issues with the processes, if you''re converting on the fly all the time.

If this is a process to correct errors, it would be more than worth it to normalize the data. But as for a working model to continue with the current dataset isn''t adviseable without taking a massive performance hit.

If you need help in writing the VBA and SQL statements, please let me know. Also please provide any work that you''ve already tested and is or isn''t work currently.

Thanks,

Joe P.

感谢Joe P对这个项目感兴趣。


数据实际输入的是另一个密钥，[State]，我已经删除并且我正在尝试压缩日期，以便按员工，公司在全国范围内开始和停止日期。数据在状态级别维护，因此每个EMPL_ID的CO_ID'中的每一个都可以用于不同的状态。这就是为什么数据看起来不规范，这不是为了纠正数据。这将是一个月度执行，以便可以在全国范围内评估数据。


这是我开始使用的VBA，但有600,000行来评估这显然是处理密集。我的想法是将员工/公司的所有开始/停止日期加载到字典中，然后将字典加载到数组中并循环并评估要保留的行和新的开始/停止日期。然后可以将校正的阵列加载回表中。这是我错过逻辑的地方。我认为我的工作要困难得多。


我提到了我的不同平台，因为我知道DB2 UDB或OLAP中的函数可以评估之前的和下一行，并希望在DB2 Z / OS的查询中有类似的东西。

Thanks Joe P for your interest in this project.

The data is actually entered with an additional key, [State], which I have removed and am trying to condense the dates up to to get a nationwide start and stop date by employee, by company. The data is maintained at the State level, so each one of those CO_ID''s per EMPL_ID could be for different states. That''s why the data does not look normalized, and this is not an attempt to correct data. This would be a monthly execution so that the data can be evaluated at a nationwide level.

Here is the VBA that I have started with, but with 600,000 rows to evaluate this is clearly going to be processing intensive. My idea was to load all start/stop dates by employee/company into a dictionary and then load the dictionary into an array and loop through and evaluate the rows to keep and the new start/stop dates. The corrected array could then be loaded back into the table. This is where I''m missing the logic. I think I''m making it much more difficult then I need to.

I mentioned my different platforms because I know there are functions in DB2 UDB or OLAP that can evaluate the previous and next rows, and was hoping there was something similar that I could do in a query in DB2 Z/OS.

展开 | 选择 | 换行 | 行号

好的我今天一直在处理代码并发现我当前代码的几个问题并且相信我有一个可用的版本。以下是我更新的代码。我需要添加6个月的日期检查，而不是从数组中删除元素，我只是跟踪每次运行的最小和最大日期，一旦我到达运行结束，我将该行加载到表中并继续下一次运行，重置我的最小和最大开始和停止日期。


然而，这需要太长时间才能运行......我仍然试图通过以下方式来完成此任务一系列DB2语句，以便在服务器上运行处理，甚至DB2存储过程可能是一个想法？

Ok I''ve been working on the code today and discovered several issues with my current code and believe I have a working version. Below is my updated code. I needed to add my date check for 6 months, and instead of removing elements from the array I just tracked the min and max dates on each run, and once I hit the end of a run I loaded that row into the table and continued onto the next run, resetting my min and max start and stop dates.

However, this takes way too long to run... I''m still set on trying to accomplish this with a series of DB2 statements so that processing is run on the server, or even a DB2 stored procedure might be an idea?

这篇关于合并&合并几行日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！