应显示按月计算的数值[英] monthwise values should displayed

问题描述

select isnull(sum (case  InvoiceMaster.InvoiceDate在@jan_from  @jan_to 然后之间转换(十进制(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)作为一月,isnull (sum (case when InvoiceMaster.InvoiceDate 之间 @feb_from  @feb_to 然后 convert(十进制(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as 二月,isnull (sum (case when InvoiceMaster.InvoiceDate 之间 @mar_from  @mar_to 然后 convert(十进制(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as mar,isnull (sum (case when InvoiceMaster.InvoiceDate 之间 @apr_from  @apr_to 然后 convert(十进制(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as apr,isnull (sum (case when InvoiceMaster.InvoiceDate between @may_from  @may_to 然后 convert(十进制(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as 可能,isnull (sum (case when InvoiceMaster.InvoiceDate 之间 @jun_from  @jun_to 然后 convert(十进制(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as 君,isnull (sum (case when InvoiceMaster.InvoiceDate 之间 @jul_from  @jul_to 然后 convert(十进制(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as 七月,isnull (sum (case when InvoiceMaster.InvoiceDate 之间 @aug_from  @aug_to 然后 convert(十进制(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as 八月,isnull (sum (case when InvoiceMaster.InvoiceDate 之间 @sep_from  @sep_to 然后 convert(十进制(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as sep,isnull (sum (case when InvoiceMaster.InvoiceDate 之间 @oct_from  @oct_to 然后 convert(十进制(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as oct,isnull (sum (case when InvoiceMaster.InvoiceDate 之间 @nov_from  @nov_to 然后 convert(十进制(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as nov,isnull (sum (case when InvoiceMaster.InvoiceDate 之间 @dec_from  @dec_to 然后 convert(十进制(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as dec从 InvoiceMaster,CollectionMaster where CollectionMaster.Invoiceid=InvoiceMaster.id



我有两张表发票主表和收款主表,我只想要那些汇总 1 个月内发票主表中所有值的数据,然后从中扣除收款主表的金额...收款主表有 invoiceid 作为外键..我尝试了上面的代码,但它给出了错误的值..有人可以帮我吗???
解决方案
你好,

这种效果应该可以解决问题!

SELECT ISNULL(CONVERT(DECIMAL(16,4),(InvoiceMaster.value-CollectionMaster.Amount)),0), GETDATE(month,InvoiceMaster.InvoiceDate) AS InvoiceMonth FROM InvoiceMaster,CollectionMaster WHERE CollectionMaster.InvoiceId=InvoiceMaster.id GROUP BY InvoiceMonth


根据您的需要和设计更正查询.
如果这回答了您的问题,请标记为答案!

亲爱的朋友,
为什么你要对这么多查询啊在sql中使用日期函数你会最小化它.并始终对金额字段使用数值

本文地址:https://www.itbaoku.cn/post/1395151.html