时间戳

首页 > 解决方案 > 每个月有条件地选择一行 - SQL Server

sql - 每个月有条件地选择一行 - SQL Server

问题描述

我有一张桌子，tblTRX看起来像这样：

| Person | Counter | Transaction |   Tdate   | Ttype | Inventory |
|:------:|:-------:|:-----------:|:---------:|:-----:|:---------:|
|  Bill  |    D    |      -4     |  1/1/2019 |  SALE |     99    |
|  Bill  |    DA   |      -3     | 1/15/2019 |  SALE |     96    |
|  Susan |    DA   |      -3     | 1/21/2019 |  SALE |     93    |
|  Bill  |    AA   |      -2     | 2/22/2019 |  SALE |     91    |
| George |    AC   |      -2     |  3/4/2019 |  EMP  |     89    |
|  Susan |    AC   |      -2     |  3/5/2019 |  SALE |     87    |
|  Susan |    AA   |      -1     |  3/6/2019 |  SALE |     86    |
|  Susan |    D    |      -2     |  3/7/2019 |  SALE |     84    |
|  Susan |    AA   |      -4     |  3/8/2019 |  SALE |     80    |
|  Susan |    AB   |      -4     |  3/9/2019 |  SALE |     76    |
|  Susan |    AC   |      -4     | 3/10/2019 |  EMP  |     72    |
|  Susan |    D    |      -4     |  4/6/2019 |  SALE |     68    |
|  Susan |    D    |      -3     |  5/1/2019 |  SALE |     65    |
|  Susan |    AB   |      -3     |  5/9/2019 |  SALE |     62    |
| George |    DA   |      -1     | 5/22/2019 |  SALE |     61    |
| George |    AA   |      -4     | 5/23/2019 |  SALE |     57    |
| George |    DA   |      -3     | 6/11/2019 |  EMP  |     54    |
|  Bill  |    AA   |      -3     | 6/19/2019 |  EMP  |     51    |
| George |    AC   |      -4     | 6/20/2019 |  SALE |     47    |
|  Susan |    AB   |      -2     | 6/21/2019 |  SALE |     45    |
|  Bill  |    D    |      -4     |  7/2/2019 |  SALE |     41    |
|  Bill  |    DA   |      -4     |  7/3/2019 |  SALE |     37    |
| George |    D    |      -4     |  7/4/2019 |  EMP  |     33    |
|  Bill  |    AB   |      -1     |  8/2/2019 |  SALE |     32    |
|  Bill  |    AC   |      -2     | 9/20/2019 |  SALE |     30    |
|  Susan |    D    |      -3     | 9/23/2019 |  EMP  |     27    |

我想做的是，每个月，为每位员工添加流失率。它们被放在一张桌子上，tblATTR看起来像这样：

|  Employee  |  AttrRate |
|:----------:|:---------:|
| Bill       |  -5       |
| George     |  2        |
|  Susan     | -10       |

每月一次，我需要每个员工的流失率，作为交易添加到tblTRX.

目标结果如下所示：

| Person | Counter | Transaction |   Tdate   | Ttype | Inventory |
|:------:|:-------:|:-----------:|:---------:|:-----:|:---------:|
|  Bill  |    D    |      -4     |  1/1/2019 |  SALE |     99    |
|  Bill  |   NULL  |      -5     |  1/1/2019 |  ATTR |     94    |
| George |   NULL  |      2      |  1/1/2019 |  ATTR |     96    |
|  Susan |   NULL  |     -10     |  1/1/2019 |  ATTR |     86    |
| George |    DA   |      -3     | 1/15/2019 |  SALE |     83    |
|  Susan |    DA   |      -3     | 1/21/2019 |  SALE |     80    |
|  Bill  |   NULL  |      -5     |  2/1/2019 |  ATTR |     75    |
| George |   NULL  |      2      |  2/1/2019 |  ATTR |     77    |
|  Susan |   NULL  |     -10     |  2/1/2019 |  ATTR |     67    |
|  Bill  |    AA   |      -2     | 2/22/2019 |  SALE |     78    |
|  Bill  |   NULL  |      -5     |  3/1/2019 |  ATTR |     73    |
| George |   NULL  |      2      |  3/1/2019 |  ATTR |     75    |
|  Susan |   NULL  |     -10     |  3/1/2019 |  ATTR |     65    |
| George |    AC   |      -2     |  3/4/2019 |  EMP  |     63    |
|  Susan |    AC   |      -2     |  3/5/2019 |  SALE |     61    |
|  Susan |    AA   |      -1     |  3/6/2019 |  SALE |     60    |
|  Susan |    D    |      -2     |  3/7/2019 |  SALE |     58    |
|  Susan |    AA   |      -4     |  3/8/2019 |  SALE |     54    |
|  Susan |    AB   |      -4     |  3/9/2019 |  SALE |     50    |
|  Susan |    AC   |      -4     | 3/10/2019 |  EMP  |     46    |
|  etc…  |         |             |           |       |           |

如果您查看我的帖子历史记录，我通常会尝试使用诚实的代码段发布问题。我什至不知道如何尝试这个。

我怎样才能达到预期的效果？每 30 天定期插入一行。我想一些伪代码看起来像：

for each MONTH in tblTRX:
    on month/01/year, insert attrition rates for each employee
    recalculate inventory

我知道我可以通过调用使用该SUM函数ROW_NUMBER()来继续对当前事务中的库存和之前行中的库存求和，因此这不像定期将​​行插入表数据流中那样令人担忧。

标签： sqlsql-server

解决方案

你可以试试这个

INSERT INTO tblTRX (Person, [Transaction], Tdate, Ttype)
SELECT B.Employee, B.AttrRate, CONCAT(M,'/1/',Y) , 'ATTR' 
FROM
    (SELECT DISTINCT YEAR(Tdate) Y, MONTH(TDate) M  FROM tblTRX) AS A 
    CROSS JOIN tblATTR B
WHERE 
    NOT EXISTS( SELECT * FROM tblTRX X WHERE X.Person = B.Employee AND X.Ttype = 'ATTR' AND X.Tdate = CONCAT(M,'/1/',Y) );

WITH CTE AS
(SELECT 
        *, FIRST_VALUE(Inventory) OVER(ORDER BY TDate, ID) 
          - FIRST_VALUE([Transaction]) OVER(ORDER BY TDate, ID) 
          + SUM([Transaction]) OVER(ORDER BY TDate, ID) as NewInventory
 FROM tblTRX )
UPDATE CTE set Inventory = NewInventory

推荐阅读