Juan Velez's questions

我正在使用 SQL Server 2016

我有一个表，其中每月包含 1 行，将患者分配给特定的提供者。

一年中可以将一名患者分配给多个提供者。

如何派生日期跨度（开始日期和结束日期）来表示将患者分配给每个提供者的时间。

我的表如下所示：

+----------+---------------+------------+-----------+
| Provider | Patient       | StartDate  | EndDate  | 
+----------+---------------+------------+-----------+
| 1922157  | 12345         | 20191201  | 20191231 | 
| 1904176  | 12345         | 20191101  | 20191201 |
| 1904176  | 12345         | 20191001  | 20191101 |
| 1904176  | 12345         | 20190901  | 20191001 | 
| 1904176  | 12345         | 20190801  | 20190901 |
| 1904176  | 12345         | 20190701  | 20190801 |
| 1904176  | 12345         | 20190601  | 20190701 |
| 1904176  | 12345         | 20190501  | 20190601 |
| 1904176  | 12345         | 20190401  | 20190501 |
| 1904176  | 12345         | 20190301  | 20190401 |
| 1904176  | 12345         | 20190201  | 20190301 |
| 1922157  | 12345         | 20190101  | 20190201 |
| 1922157  | 56789         | 20190101  | 20190201 |
+----------+---------------+------------+-----------+

在这种情况下，患者 12345 被分配到 2 个不同的提供者。一个用于 2 个月，1 月，然后是 12 月，另一个用于一年中的其余时间（10 个月）2 月至 11 月。患者 56789 仅分配给 1 个提供者 (1922157) 1 个月（12 月）。

我正在努力做到这一点，所以我的输出如下表所示，但我遇到了我认为的问题，因为患者在一年中的 2 个不同时间被分配到同一个 pcp。我尝试使用 lag 函数，但我只在某些情况下得到正确的结果，但并非所有情况都得到正确的结果，例如这种特殊情况。

+----------+---------------+------------+-----------+
| Provider | Patient       | StartDate  | EndDate  | 
+----------+---------------+------------+-----------+
| 1922157  | 12345         | 20190101  | 20190201  | 
| 1904176  | 12345         | 20190201  | 20191201  | 
| 1922157  | 12345         | 20191201  | 20191231  | 
| 1922157  | 56789         | 20191201  | 20191231  |
+----------+---------------+------------+-----------+

更新：正在做更多的研究，发现了以下帖子：

https://stackoverflow.com/questions/35900765/ms-sql-combine-date-rows-into-start-end-date

我只是将我的表格放入上述问题答案中的代码中，并针对我的一些案例进行了测试，看起来它可能会完成工作。不幸的是，我的基表有 140k 行日期需要计算，所以我不确定运行需要多长时间。现在已经运行了 6 分钟，我将返回结果。

我在 SQL Server 表中有几个日期跨度。

我正在尝试计算日期跨度中给定年份的日期跨度中的天数。

对于我的所有示例，我都在尝试计算日期跨度所涵盖的 2019 年的天数。

我有以下代码可以计算日期范围内的总天数，但有没有办法只计算 2019 年该日期范围内的天数？

下面的第一个日期跨度涵盖了整个 2019 年，所以我希望返回一个 365 左右的数字。

第二个日期跨度仅涵盖 2019 年的 30 天，因此我希望查询返回 30。

SELECT DATEDIFF(DAY,CAST(CAST(20160609 AS VARCHAR(30)) AS DATE), cast (CAST(20191231 AS VARCHAR(30)) AS DATE)) AS [Days] 

SELECT DATEDIFF(DAY,CAST(CAST(20160609 AS VARCHAR(30)) AS DATE), cast (CAST(20190130 AS VARCHAR(30)) AS DATE)) AS [Days] 

我不确定为什么下面的连接查询没有返回任何结果。

SELECT * FROM table1 JOIN table2  ON table1.fieldid = table2.idfield 

下面的 2 个查询返回结果，所以当我可以清楚地看到两个表具有共同的连接字段值时，我不确定为什么连接不会拾取任何行。

SELECT *  FROM table1 WHERE fieldid =  900399530  (returns rows)
SELECT *  FROM table2 WHERE idfield =  900399530  (returns rows)

有任何想法吗？

以下是 2 列的数据类型：

fieldid [nvarchar] (50) 整理 SQL_Latin1_General_CP1_CI_AS NULL

idfield [nvarchar] (255) 整理 SQL_Latin1_General_CP1_CI_AS NULL

我有一个将 650 个字段插入到表中的存储过程。插入因截断错误而失败。

这是一个简单的

INSERT INTO
SELECT (a bunch of fields) 
FROM (a bunch of tables)

以下是错误消息：

消息 8152，级别 16，状态 14，过程 DSP_Procedure，第 1075 行字符串或二进制数据将被截断。

有没有一种快速的方法可以识别导致截断错误的字段？

要插入到表中的 select 语句有 650 个字段，这一事实使得很难确定哪个字段导致了截断错误。

我想我可以一次注释掉字段块，以便让 SP 一次插入 100 个字段，然后运行 ​​SP 6 或 7 个不同的时间，直到我至少可以缩小到一组 100 个字段这将包含导致截断错误的字段。

或者，我在想，也许我可以只是SELECT INTO一个新表，然后比较表中的数据长度与我试图在我的 SP 中插入的目标表的数据长度，以查看哪个字段包含比预期更长的字段长度。 ..

我正在使用 SQL Server 2014。

有更简单的选择吗？

我需要取消透视下表，以便输出如下图所示。

这是否需要我在数据集上执行两次 UNPIVOT，或者我可以通过使用 UNPIVOT 一次并指定所有可用的月份和值列来完成我的预期输出？

我的脚本应该类似于以下内容来完成我需要的吗？

 Select ID, Name, Age, Gender,Month,Value
    FROM
    (Select ID, Name, Age, Gender,Month1,Month2,Month3,Month4,Value1,Value2,Value3,Value4
    FROM MyTable
    ) as cp
    UNPIVOT 
    (
      Month FOR Months IN (Month1, Month2, Month3,Month4),
      Value for Values IN (Value1,Value2,Value3,Value4)
    ) AS up;

我的 SSRS 报告中有以下文本框表达式，它工作正常。

  = First(Fields!FirstName.Value) +" " + First(Fields!LastName.Value) + ", MD"

当我将文本框移动到报告标题区域时，我开始收到“需要范围”错误消​​息

我做了一些研究，发现我需要指定数据集。一旦我在下面包含数据集（PPR），错误就消失了。

  = First(Fields!FirstName.Value,"PPR") +" " + First(Fields!LastName.Value, "PPR") + ", MD"

SSRS 现在给出了同样的错误，但另一个文本框/表达式（如下）在我将它移到标题之前工作正常。

="Interim Score:   " & CINT((sum(CINT(left(fields!Points.value,1))) / sum(CINT(right(fields!Points.value,1))))*100) & "%"

我尝试了以下 2 项修改以在表达式中包含范围 (PPR)，但我仍然遇到相同的错误。这只是我的语法错误还是我试图做一些在标题中无法完成的事情？

没用

="Interim Score:   " & CINT((sum(CINT(left((fields!Points.value,"PPR"),1))) / sum(CINT(right((fields!Points.value,"PPR"),1))))*100) & "%"

没用

="Interim Score:   " & CINT((sum(CINT(left(fields!Points.value,"PPR",1))) / sum(CINT(right(fields!Points.value,"PPR",1))))*100) & "%"

以下是我收到的完整错误消息：

文本框“Textbox6”的值表达式引用了聚合表达式中的字段，没有范围。页面页眉或页脚中引用字段的所有聚合都需要范围。

我的报告中只有 1 个数据集 (PPR)。

我正在阅读下面的页面，但我似乎无法确定我做错了什么。任何建议将不胜感激。

https://technet.microsoft.com/en-us/library/dd220421%28v=sql.110%29.aspx

下面的代码不应该返回 0 吗？

SELECT REPLACE(ISNULL('',0),'',0)

这也不返回 0....

SELECT REPLACE('','',0)

我在包含数值但有时为空（非空）的表中有一个 nvarchar 字段。

我有一个查询，检查该字段是否 < 另一个字段中的数字，但我得到

“将数据类型 nvarchar 转换为数字时出错。”

如果该字段为空，则会出错。我试图在查询执行期间将空白值 ('') 转换为零，这样我就不会收到转换错误，但我刚刚意识到

SELECT REPLACE('','',0)

不会将空白字段转换为 0。

我尝试使用下面的代码来尝试避免转换错误，但是当阈值字段为空时它没有像我预期的那样评估为 0：

SELECT CAST(REPLACE(ISNULL(Threshold,0),'',0) as decimal(4,2))

当阈值字段为空时，如何让代码评估为 0。

我猜想我可以编写一个 if 或 case 语句来跳过空白 ('') 值阈值，但我认为必须有一种方法可以内联完成所有操作，就像我试图用上面的代码做的那样.

我在查询中有以下 2 个命令（插入和删除）。当我运行查询时，我收到一条错误消息：

字符串或二进制数据将被截断。

当我去检查源表时，我发现该表不存在。它被 drop 语句删除了。我以为第二条语句不会执行，因为第一条（插入）失败了。

我如何编写下面的代码才能使整个查询失败并且不会删除表，除非第一部分（插入）成功？

我需要使用 Begin、End 和 Go 吗？

INSERT INTO SomeTable
SELECT * FROM SomeOtherTable

DROP table SomeOtherTable

我需要对 Sql Server 表进行 1 次更新。

该表有一个唯一的 ID，包含一个成员编号字段和一个文件名字段，它告诉我们成员数据来自哪个文件。

我需要做的是将 [Filename] 值更新为与历史文件中多次出现的特定会员编号相关的所有记录都不同。

因此，在下图中，编号为 123 的会员在表中有 3 条记录，其 FileName 值作为历史记录。我需要每个成员编号记录的文件名值是唯一的，因此我需要该成员的文件名值是 History、History_2 和 History_3，而不是所有 3 条记录的 History。

我很确定这可能与 SQL 有关并且目前正在处理它，但想知道是否有人遇到过类似的表更新需求并且有一些建议或代码我可以稍微修改以获得我想要的结果。

这是我用来识别需要更新记录的会员编号的代码：

SELECT Member_Number, COUNT(*) CountNumber
                   FROM mytable
                   WHERE [FileName] = 'History' 
                   GROUP BY Member_Number
                   HAVING COUNT(*) > 1

进度更新：

到目前为止，我得到了以下代码，但我必须指定实际的会员号码。我想我需要找到一种方法使下面的代码循环遍历所有会员号码。表中大约有1000+个会员号需要更新。

  SELECT *, t1.[FileName] + '_' + CAST(RN AS VARCHAR(50)) FROM 
  (
  SELECT (ROW_NUMBER() OVER (ORDER BY(SELECT 1))) RN ,* FROM mytable WHERE Member_Number = 'BAY391'
  ) AS t1
  WHERE RN > 1

我有一个 Foreach 循环容器，它循环访问文件夹中的文件。我以为 Loop Container 只会查看文件 1 次，但显然它会从顶部重新开始并拾取它已经再次查看过的文件。

我的循环容器中有一个脚本，如果文件满足特定条件，它将处理该文件。如果没有，那么它将简单地跳过该文件。

得到处理的文件被移动到另一个文件夹。我遇到的问题是，一旦它遍历所有文件，它就会重新回到文件列表的顶部，因此如果有一个文件被跳过并且没有移出到另一个文件夹，它将无限期地继续循环.

我知道我可以做到这一点，而不是跳过文件，我的进程将文件移动到另一个文件夹，以便 foreach 循环容器可以完成循环并且包可以完成，但我只是想我会先仔细检查一下是否有只让循环容器循环文件一次的方法，这样如果一个文件已经被识别，它就不会被循环容器再次检测到。

我正在将来自不同来源的平面文件导入 SQL Server 中的表中。我正在使用提取中的字段组合创建一个复合主键，这将为我提供每一行的唯一键。

我现在的做法是从 1 个字段开始，然后继续连接字段，直到找到一个对所有记录都是唯一的键。这可能会有点耗时，或者我最终可能会连接比我真正需要的更多的列来获取唯一键。

是否有某种 SQL 脚本可以在表上运行，它可以为我提供最少数量的字段（名称），我需要连接以获得唯一键？因此，如果表中有 1 个字段对所有记录都是唯一的，则将返回该 1 个字段名称。如果我需要连接 [memberid]、[claimid] 和 [date of service] 以获得唯一键，那么这 3 个字段名称将是脚本的结果。

有没有办法可以将下面的SELECT语句变成一个DELETE？

我想从[ETL].[Stage_Claims]表中删除相应的返回记录。

由于我使用了派生表，因此无法引用该Stage_Claims表。

总而言之，以下查询中使用的 2 个物理表具有相同的结构。唯一的区别是DUPS_Claims的一个子集Stage_Claims。

DUPS_Claims包含在 中找到的重复记录Stage_Claims。如果一条记录在 中存在 3 次Stage_Claims，那么我们也会在 中拥有该记录 3 次DUPS_Claims。

Stage_Claims包含所有记录，包括 中的重复记录DUPS_Claims。

我想删除重复记录，以免Stage_Claims为每条重复记录留下 1 条唯一记录。

Stage_Claims仅有 100 万行，所以我不想在整个表上使用 Row_Number / Partition，因为它需要超过 2 分钟才能运行。

下面的查询我运行了大约 15 秒，并且仅成功识别了重复记录（不包括我们要保留的原始唯一记录），但我无法弄清楚如何删除从 SC 返回的记录。

有可能还是我应该采取不同的方法？

SELECT *
FROM (
    SELECT RN = ROW_NUMBER() OVER (
            PARTITION BY SC.ID ORDER BY SC.id
            )
        ,SC.*
    FROM [ETL].[Stage_Claims] SC
    WHERE ID IN (
            SELECT ID
            FROM (
                SELECT RN = ROW_NUMBER() OVER (
                        PARTITION BY ID ORDER BY id
                        )
                    ,ID
                FROM [ETL].[DUPS_Claims]
                ) AS t1
            WHERE RN > 1
            )
    ) AS t2
WHERE RN > 1

我有 2 个不同的查询来识别重复（如下）。两个查询之间的唯一区别是一个使用 CTE，另一个使用 #Temp 表。

有谁知道为什么 CTE 比 #Temp 表快得多（0:20 秒 VS. 1:22）？

我宁愿使用 CTE，但我需要使用 CTE 运行 2 个语句（从 CTE 删除，然后从 CTE 插入表），但 SQL Server 只允许您在 CTE 上编写一个语句。

查询一：

WITH DUPS AS(
   Id,
   Column1,
   Column2,
   Column3,
   RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id)
   FROM mytable
)
  Select top 1 * FROM DUPS WHERE RN > 1 

查询 2：

 SELECT    
           Id,
           Column1,
           Column2,
           Column3,
           RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id)
       INTO #DUPS
       FROM mytable

       Select top 1 * FROM #DUPS WHERE RN > 1 

我想知道是否有任何人用来在 SSIS 平面文件连接管理器中映射固定宽度的平面文件的工具（或特殊技术）？

通常我只使用平面文件连接管理器编辑器（高级选项卡）并开始为所有列一一添加列信息（名称、宽度、数据类型等）。

过去这对我来说效果很好，但现在我必须映射一个具有接近 500 列的固定宽度文件......

我在想（希望）应该有一种工具或技术我可以使用，这将使我能够以更流线型的方式完成映射......

使用我为文件提供的数据字典，我可以轻松地创建一个 excel（或文本）文档，例如其中包含 SSIS 需要的必要信息（列名、分隔符、InputColumnWidth、OutpuColumnWidth、DataType）的几列来映射文件.

我想知道是否有一个 SSIS 工具或组件可以读取此文件并自动在平面文件连接管理器中为我创建映射...

如果没有这样的工具，是否有人有任何技巧或提示可以帮助我以最有效的方式映射文件？

我想知道是否可以修改 SSIS 包 xml 文件，这样我可以创建一些代码，将所有列的列信息脚本化为 xml 格式，然后我可以手动将其复制并粘贴到包 xml文件...不确定这是否可行....

注意：在 SSIS 中映射文件后，它将被加载到 SQL Server 表中

我创建了一个将 excel 文件导入 SQL Server 表的 SSIS 包。

当我在我的机器上本地运行 SSIS 包时，它运行没有任何问题，但是当我在将安排包的服务器上运行它时，我收到以下错误（从文本文件我输出错误以使用 SSIS 日志记录）。

经过研究，我能找到的唯一建议是将 Run64BitRuntime 属性设置为 false，我这样做了，但仍然没有运气。我怀疑这是导致我的错误的原因，因为错误没有指定任何关于 64 位的内容（就像我发现的文章中的情况一样）。

我还认为可能是服务器没有适当的 Excel 驱动程序，但我不认为是这种情况，因为通常错误消息会说明驱动程序未注册。

我目前无权远程访问服务器。我只能将包上传到文件夹，然后由应用程序运行，所以我能看到的唯一错误消息是我创建的文本错误日志中的内容。

错误代码 DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER。对连接管理器“Envision”的 AcquireConnection 方法调用失败，错误代码为 0xC0209303。在此之前可能会发布错误消息，其中包含有关 AcquireConnection 方法调用失败原因的更多信息。

“Envision”是我的 Excel 连接管理器的名称。

我使用表达式填充 Excel 文件路径和连接字符串。

连接字符串表达式如下所示：

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::SourceFilePath] +";Extended Properties=\"EXCEL 12.0 XML;HDR=YES\";"

SSIS Pacakge 由 Windows 用户名/帐户执行。我认为它可能是一个网络服务帐户。(BDS_sprtIIS)

有没有人有任何解决方案或建议来解决这个包的问题，​​这个问题只能在我的本地机器上工作，而不是在包将被部署到的实际服务器上？

我在另一个论坛上找到了以下答案，这可能是导致我的问题的原因吗？他们基本上是说 Excel 连接管理器出于某种原因尝试访问用户临时文件夹，如果它无权访问该文件夹，那么它将失败：

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/da77919c-0161-4eb5-bf89-7107d839435a/the-acquireconnection-method-call-to-the-connection-manager-excel-connection-经理失败了？forum=sqlintegrationservices

我还注意到 Microsoft.JET.OLEDB.4.0 驱动程序将尝试读取登录用户配置文件下的临时目录。

.

...我们使用较低级别的域帐户运行我们的 SQL 代理，并使用代理帐户运行我们的 SSIS 包。你是正确的，因为 Procmon 也为我证实了这一点。我将代理帐户权限授予配置文件的临时目录（C:\Documents and Settings\SQLAgentDomainAccount\Local Settings\Temp）并且它有效！

我没有使用 SQL Server 作业或代理帐户。该程序包很可能由 Windows 帐户通过命令行脚本简单地执行。

Windows 帐户可以访问该文件，但我不确定它是否可以访问它的“TEMP”文件夹（我从未在包中引用它，所以我不知道为什么它需要访问该文件夹） ...

我创建了一个需要访问共享驱动器上的文件夹的 SSIS 包。

该程序包在我的本地计算机上成功运行，但是当我使用不同的用户名在不同的服务器上运行它时，出现访问被拒绝的错误。

我为 IT 创建了一张票证，以授予对正在执行程序包的用户名的访问权限，但程序包仍然失败，对路径的相同访问被拒绝错误。

IT 人员告诉我，我需要重新启动 (windows) 用户名​​正在使用的服务，以便传播更改。

我的问题是，我需要重新启动哪些服务，以便用户名将传播新的访问权限，以便我的包运行（访问文件夹）？

我需要重新启动包所在的服务器吗？包含共享文件夹的服务器是否需要重新启动？是否需要重新启动某些 SQL Server 服务？将权限传播到用户名的最简单方法是什么？

我正在运行 SQL Server 2014 并使用 SSMS。

有没有办法编写特定模式（存储过程、视图、表等）中的所有对象，以便我可以在另一个数据库中生成对象？

有没有办法将其缩小到仅编写某个模式中的所有表的脚本？

我找不到有关复制文件操作的 SSIS 文件系统任务组件OverWriteDestination属性功能的任何文档。

我了解它的用途，但是当 OverWriteDestination 设置为 false 时，它​​实际上如何导致组件运行？

如果设置为 False，组件将直接跳过文件而不尝试复制它，还是使用不同的名称复制它，可能是一个数字附加到被复制的文件名的末尾（例如 filename(1).text )?

当我创建 SSRS 报告时，我创建了存储过程以将数据导入到我的报告中。当我创建报告参数时，我只是将 SQL 代码直接嵌入到参数/SSRS 报告中。

我还应该为我的参数数据源创建存储过程吗？

为报告中的每个参数/数据源创建一个 SP 似乎有点矫枉过正，但我​​想如果您希望能够修改该参数用于加载下拉值的 SQL 代码，而无需打开实际报告，那么我可能想和 SP 一起做所有事情……

我创建了一个导入每月提取声明文件的 SSIS Pacakge。我有一个简单的数据流任务，平面文件到 OLEDB 目标，中间有一个派生列组件，用于生成主键（基于提取文件中 3 列的组合）。

如果可能的话，我想做的是让 SSIS 包仅根据主键导入全新的记录，以便如果正在导入的文件包含目标表中已经存在的（生成的）主键，然后我希望包删除表中的现有记录，然后从正在导入的当前文件中添加新记录。

我知道我可以通过有一个暂存表来加载文件，然后使用一些 sql 查询 (sp) 适当地删除记录并将记录添加到表中，但我正在尝试完成导入而不需要创建暂存表，因为我目前正在填充的表已经是一个临时表......

使用 SSIS 和仅 1 个目标表，我想要完成的工作是否可行，或者我需要创建一个额外的临时表？