SQL查询以显示支出历史记录

Bevan 发表于 Dev

贝凡

我被要求创建一个使债务人花费历史记录表的查询。

数据源：MYOB Exonet有上级帐户，但该表应为下级帐户。但是，子帐户交易实际上并不在子级别进行交易，而是在父级别进行。但我只希望报告一些子帐户。

表格如下：
DR_ACCS (debtor accounts)

有一个“视图”提供子帐户的交易。它没有出现在数据库中，但是在输入查询时，它显示为

AnaylticsSalesInvoices

之间的连接DR_ACCS和AnalyticsSalesInvoices为DR_ACCS.ACCNO = AnalyticsSalesInvoices.accountNumberAnalysis。

因此，现在我需要去“获取具有该字段DR_ACCS.X_TOPCUSTOMER打勾的帐户的所有交易'Y'，并将其分为“本月”，“上个月”，“ 2个月前”和“最后12个月”。

我想要的是这样的结果：

> Customer Name | This Month | Last Month | 2 Months Ago | 12 Month |    
> Jones         |       $235 |       $756 |          $49 |    $2800 |   
> Smith         |        $14 |         $0 |         $459 |     $629 |  
> Lewis         |       $520 |        $35 |           $0 |    $9206 |

有人可以协助吗？我尝试了各种方式，迷路了！

程序员43229

DECLARE
    @this_start DATETIME,
    @this_end   DATETIME,
    @last_start DATETIME,
    @last_end   DATETIME,
    @l2_start   DATETIME,
    @l2_end     DATETIME,
    @year_start DATETIME,
    @year_end   DATETIME

SELECT
    @this_start = DATEADD(m,DATEDIFF(m,0,GETDATE()),0),
    @this_end   = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),
    @last_start = DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),
    @last_end   = DATEADD(s,-1,@this_start),
    @l2_start   = DATEADD(m,-2,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),
    @l2_end     = DATEADD(s,-1,@last_start),
    @year_start = DATEADD(m,-12,@this_start),
    @year_end   = @this_end

-- just a check of the variable values:
SELECT              -- GETDATE() = 2014-09-15 10:50:15.000
    @this_start,    -- 2014-09-01 00:00:00.000
    @this_end,      -- 2014-09-30 23:59:59.000
    @last_start,    -- 2014-08-01 00:00:00.000
    @last_end,      -- 2014-08-31 23:59:59.000
    @l2_start,      -- 2014-07-01 00:00:00.000
    @l2_end,        -- 2014-07-31 23:59:59.000
    @year_start,    -- 2013-09-01 00:00:00.000
    @year_end       -- 2014-09-30 23:59:59.000

SELECT 
    dr_accs.NAME,
    [This Month]   = SUM(CASE WHEN b.date BETWEEN @this_start AND @this_end THEN b.total ELSE 0 END),
    [Last Month]   = SUM(CASE WHEN b.date BETWEEN @last_start AND @last_end THEN b.total ELSE 0 END),
    [2 Months Ago] = SUM(CASE WHEN b.date BETWEEN @l2_start AND @l2_end THEN b.total ELSE 0 END),
    [12 Month]     = SUM(b.total)

FROM dr_accs JOIN AnalyticsSalesInvoices b ON DR_ACCS.ACCNO = b.accountNumberAnalysis
WHERE dr_accs.x_topcustomer = 'Y'
AND b.date_field BETWEEN @year_start AND @year_end