本文介绍了应该显示每月的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
选择 isull(总和(案例 InvoiceMaster.InvoiceDate在@jan_ from 和 @jan_to 然后之间转换十进制( 16 , 4 ),( InvoiceMaster.value-CollectionMaster.Amount)) end ), 0 )如 jan, 无效(sum(情况 InvoiceMaster.InvoiceDate @feb_from之间和 @feb_to 然后 convert(十进制( 16 , 4 ),(InvoiceMaster.value-CollectionMaster.Amount))结束), 0 ) 2月 无效(sum(情况 InvoiceMaster.InvoiceDate @mar_from之间和 @mar_to 然后 convert(十进制( 16 , 4 ),(InvoiceMaster.value-CollectionMaster.Amount))结束), 0 )截至 3月, 无效(sum(情况 InvoiceMaster.InvoiceDate @apr_from之间和 @apr_to 然后 convert(十进制( 16 , 4 ),(InvoiceMaster.value-CollectionMaster.Amount))结束), 0 ) as 4月, 无效(sum(情况 InvoiceMaster.InvoiceDate @may_from之间和 @may_to 然后 convert(十进制( 16 , 4 ),(InvoiceMaster.value-CollectionMaster.Amount))结束), 0 ) 可能, 无效(sum(情况 InvoiceMaster.InvoiceDate @jun_from之间的和 @jun_to 然后 convert(十进制( 16 , 4 ),(InvoiceMaster.value-CollectionMaster.Amount))结束), 0 )作为 无效(sum(情况 InvoiceMaster.InvoiceDate @jul_from之间和 @jul_to 然后 convert(十进制( 16 , 4 ),(InvoiceMaster.value-CollectionMaster.Amount))结束), 0 )如 无效(sum(情况 InvoiceMaster.InvoiceDate @aug_from之间和 @aug_to 然后 convert(十进制( 16 , 4 ),(InvoiceMaster.value-CollectionMaster.Amount))结束), 0 ) as 8月, 无效(sum(情况 InvoiceMaster.InvoiceDate @sep_from之间和 @sep_to 然后 convert(十进制( 16 , 4 ),(InvoiceMaster.value-CollectionMaster.Amount))结束), 0 )如 无效(求和(情况 InvoiceMaster.InvoiceDate @oct_from之间的时间和 @oct_to 然后 convert(十进制( 16 , 4 ),(InvoiceMaster.value-CollectionMaster.Amount))结束), 0 ) 八月, 无效(sum(情况 InvoiceMaster.InvoiceDate @nov_from之间和 @nov_to 然后 convert(十进制( 16 , 4 ),(InvoiceMaster.value-CollectionMaster.Amount))结束), 0 )如 11月, 无效(sum(情况 InvoiceMaster.InvoiceDate在@dec_from之间和 @dec_to 然后 convert(十进制( 16 , 4 ),(InvoiceMaster.value-CollectionMaster.Amount))结束), 0 ) as dec 来自InvoiceMaster,CollectionMaster,其中CollectionMaster.Invoiceid = InvoiceMaster.id
我有两个表的发票主数据和收款主数据,我只希望那些数据在1个月内汇总发票主数据中的所有值,然后从中扣除收款主数据的金额...收款主将invoiceid作为外键.尝试了上面的代码,但它给出了错误的值..有人可以帮帮我吗?
解决方案这种效果应该可以解决问题! 选择 ISNULL( CONVERT ( DECIMAL ( 16 , 4 ),(InvoiceMaster.value-CollectionMaster.Amount)) , 0 ),GETDATE(month,InvoiceMaster.InvoiceDate) AS InvoiceMonth FROM InvoiceMaster,CollectionMaster 位置 CollectionMaster.InvoiceId = InvoiceMaster.id GROUP BY InvoiceMonth
根据您的需求和设计更正查询. 如果这能回答您的问题,请标记为答案"!
亲爱的朋友, 为什么您对这些查询这么多啊,在sql中使用日期函数,您会使其最小化.并始终在数量字段中使用数字值 select isnull(sum (case when InvoiceMaster.InvoiceDate between @jan_from and @jan_to then convert(decimal(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as jan, isnull (sum (case when InvoiceMaster.InvoiceDate between @feb_from and @feb_to then convert(decimal(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as feb, isnull (sum (case when InvoiceMaster.InvoiceDate between @mar_from and @mar_to then convert(decimal(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as mar, isnull (sum (case when InvoiceMaster.InvoiceDate between @apr_from and @apr_to then convert(decimal(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as apr, isnull (sum (case when InvoiceMaster.InvoiceDate between @may_from and @may_to then convert(decimal(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as may, isnull (sum (case when InvoiceMaster.InvoiceDate between @jun_from and @jun_to then convert(decimal(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as jun, isnull (sum (case when InvoiceMaster.InvoiceDate between @jul_from and @jul_to then convert(decimal(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as jul, isnull (sum (case when InvoiceMaster.InvoiceDate between @aug_from and @aug_to then convert(decimal(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as aug, isnull (sum (case when InvoiceMaster.InvoiceDate between @sep_from and @sep_to then convert(decimal(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as sep, isnull (sum (case when InvoiceMaster.InvoiceDate between @oct_from and @oct_to then convert(decimal(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as oct, isnull (sum (case when InvoiceMaster.InvoiceDate between @nov_from and @nov_to then convert(decimal(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as nov, isnull (sum (case when InvoiceMaster.InvoiceDate between @dec_from and @dec_to then convert(decimal(16,4),(InvoiceMaster.value-CollectionMaster.Amount)) end),0)as dec from InvoiceMaster,CollectionMaster where CollectionMaster.Invoiceid=InvoiceMaster.id
i have two tables invoice master and collection master and i want only those data which sum up all values in invoice master during 1 month and then deduct collection master''s amount from it... collection master has invoiceid as foreign key.. i tried above code but it gives wrong value.. can someone help me please???
解决方案 Hi, Something of this effect should do the trick! 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 InvoiceMonthCorrect the query as per your needs and design. Mark as Answer if this answers your question!
Dear Friend, why u right these much of querys ah use date functions in sql u will minimize it. and always use numeric values for amount fields更多推荐
应该显示每月的值
发布评论