应该显示每月的值

本文介绍了应该显示每月的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

选择 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 InvoiceMonth

Correct 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