在存储过程中将空行添加到表中
我有这个表:
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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我需要这样做的原因是报表生成器没有将日期视为实际日期,并且无法对它们进行趋势分析。我使用 sql 函数解决了这个问题。我在这里为任何处理 MDX 并必须处理奇怪日期的人发布此内容。
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.