tsql - SQL查询按月汇总分类帐数据，按客户分组

Question

我正在尝试以以下格式编写对数据的查询：

顾客	库存	日期
鲍勃	3000	2018-01-01
鲍勃	-1200	2019-01-05
鲍勃	-1100	2019-02-06
鲍勃	4000	2019-03-01
吉姆	25000	2018-01-05
吉姆	-2000	2019-02-05

我正在寻找的输出是给定年份的，按以下格式按月汇总总库存：

顾客	简	二月
鲍勃	1800	700
吉姆	25000	23000

我已经成功地让支点正常工作，但是我编写查询的方式是在一个月内总结股票，而不包括过去的历史。我当前的查询如下所示：

select 
        customer,
        [1] as Jan, 
        [2] as Feb, 
        [3] as Mar, 
        [4] as Apr, 
        [5] as May, 
        [6] as Jun, 
        [7] as Jul, 
        [8] as Aug, 
        [9] as Sep, 
        [10] as Oct, 
        [11] as Nov, 
        [12] as Dec
    FROM
    (
        SELECT
        customer,
        stock,
        datepart(mm, date) as TMonth
    from customer_stock 
    where datepart(yyyy, date) <= @year 
    ) source
    PIVOT
    (
        sum(stock)
        FOR TMonth IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
    ) as pvtMonth
    order by customer

我知道要在特定月份获得特定客户，我可以这样做：

SELECT Customer, Sum(Stock) From customer_stock WHERE date <= '2019-03-01' 

我应该如何修改我的查询，以便我以这种方式汇总到给定月份的库存，而不是在一个月内汇总库存？我假设我需要包含一个窗口函数。

标签： tsqlwindow-functions