应该显示每月的值 [英] monthwise values should displayed

问题描述

 选择 isull(总和(案例 和 @jan_to 然后之间转换十进制( 16 ， 4 )，( InvoiceMaster.value-CollectionMaster.Amount)) end )， 0 )情况   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 位置 CollectionMaster.InvoiceId = InvoiceMaster.id  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

这篇关于应该显示每月的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！