A / R老化查询痛苦。 [英] A/R Aging query misery.

问题描述

希望有人能够让我走上正确的道路。我有以下MS SQL A / R Aging报告查询。基本的想法是返回客户名称，获得未使用信用额的总和，然后将相应的发票到期值放入老化桶中。



一切正常。除非客户从未有过发票但确实有信用卡。遗憾的是，FROM子句创建了这种依赖。我已经尝试了各种各样的联盟和联盟，但是我的知识缺乏，或者我只是做错了。

  SELECT  contacts.ID，ISNULL（contacts.LName + ' ，'，' '）+ ISNULL（contacts.FName， ' '） AS  [名称]， 
（ SELECT  SUM（c1） FROM  
（
  SELECT  SUM（总到期） AS  c1  FROM 发票 WHERE  customerID = contacts.ID 
  UNION   ALL   SELECT  SUM（-amount） AS  c1  FROM 事务 WHERE  FromID = contacts.ID  AND （[类型] = ' < span class =code-string> Payment'  OR  [ Type ] = ' 退款'）
  UNION  < span class =code-keyword> ALL   SELECT  SUM（金额） AS  c1 < span class =code-keyword> FROM 事务 WHERE  ToID = contacts.ID  AND  [类型]  LIKE  '  Credit %%' 
） AS  q1） AS  '  Credits'，
 < span class =code-keyword> CONVERT （ money ，SUM（ CASE   WHEN  DATEDIFF（day，orderDate，GETDATE（））=  0  那么截止日期 ELSE   0   END  ）） AS  ' 当前'，
  CONVERT （ money ，SUM（ CASE   WHEN  DATEDIFF（day，orderDate，GETDATE（）） BETWEEN   1   AND   30  那么 due  ELSE   0   END ） ） AS  '  1-30'， 
  CONVERT （ money ，SUM（ CASE   WHEN  DATEDIFF（day，orderDate，GETDATE（）） BETWEEN   31   AND   60  那么 due  ELSE   0   END ） ） AS  '  31-60'， 
  CONVERT （ money ，SUM（ CASE   WHEN  DATEDIFF（day，orderDate，GETDATE（）） BETWEEN   61   AND   90  那么到期 ELSE   0   END ）） AS  '  61-90'，
  CONVERT （ money ，SUM（ CASE   WHEN  DATEDIFF（day，orderDate，GETDATE（））>  90  那么到期 ELSE   0   END ）） AS  ' > 90'，' '  AS 总计
  FROM 发​​票，联系人 WHERE 总计<>  0   AND  orderDate< '  2014-07-26 00:00:00'  AND  contacts.ID = customerID 
  GROUP   BY  contacts.ID，contacts。 LName，contacts.FName  ORDER   BY  contacts.LName，contacts.FName 

我所遇到的另一个问题不太重要，但令我难过的是如何有效地添加桶内容以获得总行数。现在我有一些C ++代码来迭代记录并以这种方式计算，但这意味着对每个报告行执行操作似乎不是最佳的。我在想也可能有某种方法在查询中执行该操作。



非常感谢任何帮助。

解决方案

Hopefully someone may be able to set me on the right path. I have the following MS SQL A/R Aging report query. The basic idea is to return customer names, get the sum of their unused credits and then put corresponding invoice due values in aging buckets.

It all works. Except when the customer has never had an invoice but does have a credit. The FROM clause unfortunately creates that dependency. I've tried all manner of Unions and Joins that I can think of but either my knowledge is lacking or I'm just plain doing it wrong.

SELECT contacts.ID, ISNULL(contacts.LName + ', ','') + ISNULL(contacts.FName,'') AS [Name],
(SELECT SUM(c1) FROM
(
    SELECT SUM(total-due) AS c1 FROM invoices WHERE customerID = contacts.ID
    UNION ALL SELECT SUM(-amount) AS c1 FROM transactions WHERE FromID = contacts.ID AND ([Type] = 'Payment' OR [Type] = 'Refund')
    UNION ALL SELECT SUM(amount) AS c1 FROM transactions WHERE ToID = contacts.ID AND [Type] LIKE 'Credit%%'
) AS q1) AS 'Credits',
CONVERT(money,SUM(CASE WHEN DATEDIFF(day,orderDate,GETDATE()) = 0 THEN due ELSE 0 END)) AS 'Current',
CONVERT(money,SUM(CASE WHEN DATEDIFF(day,orderDate,GETDATE()) BETWEEN 1 AND 30 THEN due ELSE 0 END)) AS '1-30',
CONVERT(money,SUM(CASE WHEN DATEDIFF(day,orderDate,GETDATE()) BETWEEN 31 AND 60 THEN due ELSE 0 END)) AS '31-60',
CONVERT(money,SUM(CASE WHEN DATEDIFF(day,orderDate,GETDATE()) BETWEEN 61 AND 90 THEN due ELSE 0 END))  AS '61-90',
CONVERT(money,SUM(CASE WHEN DATEDIFF(day,orderDate,GETDATE()) > 90 THEN due ELSE 0 END)) AS '> 90', '' AS Total
FROM invoices, contacts WHERE Total <> 0 AND orderDate < '2014-07-26 00:00:00' AND contacts.ID = customerID
GROUP BY contacts.ID, contacts.LName, contacts.FName ORDER BY contacts.LName, contacts.FName

Another problem I have that is less significant but has me stumped is how to efficiently add the "bucket" contents to get a row total. Right now I have some C++ code to iterate the records and calculate that way, but it means performing operations on each report row which seems less than optimal. I was thinking there may be some way to perform that operation inside the query as well.

Any help is greatly appreciated.

解决方案

这篇关于A / R老化查询痛苦。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！