按日期范围分组并获得总和 [英] GROUP by Date Ranges and get the Sum

问题描述

我需要通过信用条款获得客户付款的总和。我宣布3个学分条款



- 超过45天

- 45天

- 30天


我为客户发票生成我的系统并发布客户。然后开始天数计数发票创建。上个月发票的客户未付款。我想获得未付款的总和金额使用我的Cridet条款。（单独金额超过45,45天和30天）。



这是我的表。

  SELECT  [InvoiceNo]，[cusCode]，[InvAmt]，[CreateUserID]，[CreateDate]，[CreateTime]，[Confirm]，[ TotalPaid]，[TotalDuePaid] 
  FROM  [tblInvoiceHeader] 

我试试这一个

  SELECT  TotalDuePaid，DATEDIFF（DAY，CreateDate，GETDATE（）） AS  DayDiff  FROM  tblInvoiceHeader  WHERE 确认= '  0'  AND  cusCode = '  1' 

它正在工作但是我想一次选择所有3个条款



EX- CusCode = 1个未付2个发票，这2个发票是2天我想要显示这个未付的数据给出波纹管表。

 CusCode超过45天45天30天
 1 500.00 0.00 560.00 
 

如何操作任何人都可以帮助我？

解决方案

尝试使用此查询。< br $>

 选择 cusCode，SUM（inv1.TotalDuePaid），SUM（inv2） .TotalDuePaid），SUM（inv3.ITotalDuePaid）来自 INVOICES inv 
  left  外部  join  INVOICES inv1  on  inv.INVOICE_ID = inv1.INVOICE_ID 和 inv1.CreateDate   GETDATE（） -  30 和 GETDATE（）
  left  外部 加入 INVOICES inv2  on  inv.INVOICE_ID = inv2.INVOICE_ID 和 inv2.CreateDate 之间 GETDATE（） -  34 和 GETDATE（） -  30 
  left  < span class =code-keyword> outer   join  INVOICES inv3  on  inv.INVOICE_ID = inv3.INVOICE_ID 和 inv3.CreateDate> GETDATE（） -  45 
  group   by  cusCode 

实现这一目标的方法很少：

1. 使用数据透视表 [ ^ ]
SQL Server中的数据透视表。一个简单的样本。 [ ^ ]




2. 使用当时......然后......结束...... [ ^ ]

  SELECT  CusCode， CASE   WHEN  DATEDIF（...） BETWEEN   0   AND   30   THEN  TotalDuePaid  END   AS  [0-30天]，
  CASE   WHEN  DATEDIF（...） BETWEEN   31   AND   45  那么 TotalDuePaid  END   AS  [31-45天] ，
  CASE   WHEN  DATEDIF（...）>  45  那么 TotalDuePaid  END   AS  [ over   45 天]，
 < span class =code-keyword> FROM  TableName 
  WHERE  ... 

试试！

I need a get sum of my Customer Payment in credit Terms. I declare 3 credit Terms

- Over 45 Days
- 45 Days
- 30 Days

I Genarated a Customer Invoice my System and post the customer.then Start Days Count Invoice Create.Same Customer Not Paid last Month of past Invoices.I want to Get sum of The Unpaid Amount Using my Cridet Terms.(Separate Amount Over 45,45 Days And 30 Days).

This Is my Table.

SELECT [InvoiceNo] ,[cusCode],[InvAmt],[CreateUserID],[CreateDate],[CreateTime],[Confirm] ,[TotalPaid],[TotalDuePaid]
 FROM [tblInvoiceHeader]

I try This One

SELECT TotalDuePaid,DATEDIFF(DAY, CreateDate, GETDATE()) AS DayDiff FROM tblInvoiceHeader   WHERE Confirm='0' AND cusCode='1' 

it's Working But i want to Select All 3 Terms in At a 1 time

EX- CusCode=1 unpaid 2 invoices,this 2 invoices are 2 days I want show this unpaid data given bellow table.

CusCode    Over 45 Days     45 Days      30 Days
    1         500.00           0.00         560.00

How to do it Any one can help me?

解决方案

Try with this query.

select cusCode,SUM(inv1.TotalDuePaid),SUM(inv2.TotalDuePaid),SUM(inv3.ITotalDuePaid) from INVOICES inv
left outer join INVOICES inv1 on inv.INVOICE_ID = inv1.INVOICE_ID and inv1.CreateDate between GETDATE()-30 and GETDATE()
left outer join INVOICES inv2 on inv.INVOICE_ID = inv2.INVOICE_ID and inv2.CreateDate between GETDATE()-34 and GETDATE()-30
left outer join INVOICES inv3 on inv.INVOICE_ID = inv3.INVOICE_ID and inv3.CreateDate > GETDATE()-45
group by cusCode

There is few ways to achieve that:

1. using Pivot table[^]
Pivot tables in SQL Server. A simple sample.[^]


2. using CASE WHEN ... THEN ... ELSE ... END[^]

SELECT CusCode, CASE WHEN DATEDIF(...) BETWEEN 0 AND 30 THEN TotalDuePaid END AS [0-30 days],
CASE WHEN DATEDIF(...) BETWEEN 31 AND 45 THEN TotalDuePaid END AS [31-45 days],
CASE WHEN DATEDIF(...) > 45 THEN TotalDuePaid END AS [over 45 days],  
FROM TableName
WHERE ...

Try!

这篇关于按日期范围分组并获得总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！