存储过程中的T-SQL求和错误结果 [英] T-SQL sum error result in stored procedur
问题描述
这是我的主表：
BJASB作为k MP Ma F
31 32 31 32 31 30 30 30 29 29 30
C
31
我用查询来生成这个表：
年份开始日期结束日期月份总和天数
2072 2015-04-14 00:00:00 2016-04-12 00 ：00：00 32 0
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 31 32
2072 2015-04- 14 00:00:00 2016-04-12 00:00:00 30 63
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 30 93
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 29 123
2072 2015-04-14 00:00:00 2016 -04-12 00:00:00 30 152
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 29 182
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 30 211
2072 2015-04-14 00:00:00 2016-04-12 00:00 ：00 30 241
2073 2016-04-13 00:00:00 2017-04-13 00： 00:00 31 272
2073 2016-04-13 00:00:00 2017-04-13 00:00:00 32 303
2073 2016-04-13 00:00:00 2017-04-13 00:00:00 31 335
这是临时表。这里总和天数是从查询计算出来的。查询计算金额
选择'2073'为[年]，'2016-04-13 00:00:00'作为开始日期，'2016-04-12 00:00:00'作为结束日期，CONVERT（INT，（S + B + As + K + M + P + Ma + F + C））作为MasterTable GROUP的总和天数BY S + B + As + K + M + P + Ma + F + C
在此查询中，出现了具有总和天数= 272的临时表的问题。这里总和天数是30 + 241的总和天数（上述记录月份和总和天数总和）。结果是271但是为查询生成了272。我想要271.
任何人都可以帮助我。
我尝试过的事情：
我用sum来得到结果但它仍然显示相同的输出。
This is my master table :
B J A S B As k M P Ma F
31 32 31 32 31 30 30 30 29 29 30
C
31
I am using query to generate this table :
Year Start Date End Date Month Sum Days
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 32 0
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 31 32
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 30 63
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 30 93
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 29 123
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 30 152
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 29 182
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 30 211
2072 2015-04-14 00:00:00 2016-04-12 00:00:00 30 241
2073 2016-04-13 00:00:00 2017-04-13 00:00:00 31 272
2073 2016-04-13 00:00:00 2017-04-13 00:00:00 32 303
2073 2016-04-13 00:00:00 2017-04-13 00:00:00 31 335
This is temp table. Here sum days is calculated from query . The query to calculate sum
SELECT '2073' as [YEAR],'2016-04-13 00:00:00' as Start Date, '2016-04-12 00:00:00' as End Date, CONVERT(INT,(S+B+As+K+M+P+Ma+F+C)) as Sum Days FROM MasterTable GROUP BY S+B+As+K+M+P+Ma+F+C
In this query, issue arises for temp table which has sum days = 272. Here sum days is the sum days of 30+241, (the above records month and sum days sum). the result is to be 271 but it is 272 generated for query. I want 271.
Can any one help me out.
What I have tried:
I had used sum to get result but it is still showing same output.
推荐答案
嗨朋友，
你确实在日期上错了。检查您在Store程序中与日期不同的代码。
Hi Friend,
You did mistake in date different. Check you code where you do date different in your Store procedure.
我找到了原因。这是因为proc错误的月份值导致了这个问题。
感谢您的回复..
I found out the cause. It was because proc was taking wrong month value causing the issue.
Thanks for reply..
这篇关于存储过程中的T-SQL求和错误结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
