在存储过程中将空行添加到表中

发布于 2024-10-05 03:23:26 字数 1124 浏览 3 评论 0原文

我有这个表:

ROW_GROUP           COL_GROUP         CLAIM_COUNT      DENIAL_AMOUNT
NOT MEDIC NEC/PRE-X October 2010    6                591.50
NOT MEDIC NEC/PRE-X November 2010   8                3154.48
DUPLICATE             October 2010    39               7921.78
DUPLICATE             November 2010   35               7484.17
REGISTRATION           October 2010    56               10622.55
REGISTRATION           November 2010   67               7820.69
TIMELY FILING          September 2010  6                239.00
TIMELY FILING          October 2010 67               8389.25
TIMELY FILING         November 2010   6                51127.96

我需要能够将 September(或任何 COL_GROUP 字段)添加到该表中,其中 Claim_count 和 denial_amount 中为空。 ROW_GROUP 和 COL_GROUP 是动态生成的,因此我不能只更新字段,这必须在存储过程中完成。有人提到外部左连接可能会为我创建这些空值,只是不确定如何创建。任何建议都会有帮助。

工作中同事建议用这个sql语句,但是好像不行:

select #TREND.ROW_GROUP, DISTINCTTIME.COL_GROUP, #TREND.CLAIM_COUNT, #TREND.DENIAL_AMOUNT from (select distinct #TREND.COL_GROUP from #TREND) 
AS DISTINCTTIME LEFT OUTER JOIN #TREND ON 
DISTINCTTIME.COL_GROUP = #TREND.COL_GROUP

I have this table:

ROW_GROUP           COL_GROUP         CLAIM_COUNT      DENIAL_AMOUNT
NOT MEDIC NEC/PRE-X October 2010    6                591.50
NOT MEDIC NEC/PRE-X November 2010   8                3154.48
DUPLICATE             October 2010    39               7921.78
DUPLICATE             November 2010   35               7484.17
REGISTRATION           October 2010    56               10622.55
REGISTRATION           November 2010   67               7820.69
TIMELY FILING          September 2010  6                239.00
TIMELY FILING          October 2010 67               8389.25
TIMELY FILING         November 2010   6                51127.96

I need to be able to add September (or any COL_GROUP field) to this table with nulls in claim_count and denial_amount. ROW_GROUP and COL_GROUP are dynamically generated so I can't just update the fields, this has to be done within a stored procedure. It was mentioned that a outer left join might create these nulls for me, just not sure how. Any suggestions would be helpful.

It was suggested to use this sql statement from a colleague at work, but it doesn't seem to work:

select #TREND.ROW_GROUP, DISTINCTTIME.COL_GROUP, #TREND.CLAIM_COUNT, #TREND.DENIAL_AMOUNT from (select distinct #TREND.COL_GROUP from #TREND) 
AS DISTINCTTIME LEFT OUTER JOIN #TREND ON 
DISTINCTTIME.COL_GROUP = #TREND.COL_GROUP

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

寻找一个思念的角度 2024-10-12 03:23:26

我需要这样做的原因是报表生成器没有将日期视为实际日期,并且无法对它们进行趋势分析。我使用 sql 函数解决了这个问题。我在这里为任何处理 MDX 并必须处理奇怪日期的人发布此内容。

USE [DMDS]
GO
/****** Object:  UserDefinedFunction [dbo].[MonthFormat]    Script Date: 12/02/2010 14:02:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[MonthFormat](@date varchar(25)) Returns date as 
    Begin 
        declare @retval as varchar(50) 
        select @retval = case 
            when @date like 'Jan%' then '01/01/' + RIGHT(@Date,4)
            when @date like 'Feb%' then '02/01/' + RIGHT(@Date,4)
            when @date like 'March%' then '03/01/' + RIGHT(@Date,4)
            when @date like 'Apr%' then '04/01/' + RIGHT(@Date,4)
            when @date like 'May%' then '05/01/' + RIGHT(@Date,4)
            when @date like 'Jun%' then '06/01/' + RIGHT(@Date,4)
            when @date like 'Jul%' then '07/01/' + RIGHT(@Date,4)
            when @date like 'Aug%' then '08/01/' + RIGHT(@Date,4)
            when @date like 'Sept%' then '09/01/' + RIGHT(@Date,4)
            when @date like 'Oct%' then '10/01/' + RIGHT(@Date,4)
            when @date like 'Nov%' then '11/01/' + RIGHT(@Date,4)
            when @date like 'Dec%' then '12/01/' + RIGHT(@Date,4)
            End
    return @retval
End

The reason I needed to do this was Report Builder wasn't viewing the dates as actually dates and couldn't trend them. I solved this using a sql function. I'm posting this here for anyone dealing with MDX and having to deal with weird dates.

USE [DMDS]
GO
/****** Object:  UserDefinedFunction [dbo].[MonthFormat]    Script Date: 12/02/2010 14:02:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[MonthFormat](@date varchar(25)) Returns date as 
    Begin 
        declare @retval as varchar(50) 
        select @retval = case 
            when @date like 'Jan%' then '01/01/' + RIGHT(@Date,4)
            when @date like 'Feb%' then '02/01/' + RIGHT(@Date,4)
            when @date like 'March%' then '03/01/' + RIGHT(@Date,4)
            when @date like 'Apr%' then '04/01/' + RIGHT(@Date,4)
            when @date like 'May%' then '05/01/' + RIGHT(@Date,4)
            when @date like 'Jun%' then '06/01/' + RIGHT(@Date,4)
            when @date like 'Jul%' then '07/01/' + RIGHT(@Date,4)
            when @date like 'Aug%' then '08/01/' + RIGHT(@Date,4)
            when @date like 'Sept%' then '09/01/' + RIGHT(@Date,4)
            when @date like 'Oct%' then '10/01/' + RIGHT(@Date,4)
            when @date like 'Nov%' then '11/01/' + RIGHT(@Date,4)
            when @date like 'Dec%' then '12/01/' + RIGHT(@Date,4)
            End
    return @retval
End
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文