在SQL Server中,我有这样的源数据.As_of_date|issue_id|issue_status_name|prev_issue_status_name在我的源表中,所需的输出就是我正在寻找的.
基本上需要将Issue_Status_Name与Prev_Issue_Status_Name进行比较,当Prev_Issue_Status_Name在(NULL、OPEN、DRAILT)中,而Issue_Status_NAME在(待定可持续性、待定验证、Closed)中时,我们应该设置AS_OF_DATE并在前面的行中填写,直到发生相反情况.
我try 这样做,首先编写Case语句来填充1,否则填充0,然后递增数字,但我不确定如何处理每次出现的情况.有谁能给我指个方向吗?
as_of_date | issue_id | issue_status_name | prev_issue_status_name | desired_output |
---|---|---|---|---|
12/31/2019 | 123456 | Draft | NULL | NULL |
1/1/2020 | 123456 | Draft | Draft | NULL |
1/2/2020 | 123456 | Draft | Draft | NULL |
1/3/2020 | 123456 | Pending Validation | Draft | 1/3/2020 |
1/4/2020 | 123456 | Pending Validation | Pending Validation | 1/3/2020 |
1/5/2020 | 123456 | Pending Validation | Pending Validation | 1/3/2020 |
1/6/2020 | 123456 | Pending Validation | Pending Validation | 1/3/2020 |
1/7/2020 | 123456 | Open | Pending Validation | NULL |
1/8/2020 | 123456 | Open | Open | NULL |
1/9/2020 | 123456 | Open | Open | NULL |
1/10/2020 | 123456 | Open | Open | NULL |
1/11/2020 | 123456 | Pending Sustainability | Open | 1/11/2020 |
1/12/2020 | 123456 | Pending Sustainability | Pending Sustainability | 1/11/2020 |
1/13/2020 | 123456 | Pending Sustainability | Pending Sustainability | 1/11/2020 |
1/14/2020 | 123456 | Pending Sustainability | Pending Sustainability | 1/11/2020 |
1/15/2020 | 123456 | Pending Validation | Pending Sustainability | 1/11/2020 |
1/16/2020 | 123456 | Pending Validation | Pending Validation | 1/11/2020 |
1/17/2020 | 123456 | Pending Validation | Pending Validation | 1/11/2020 |
1/18/2020 | 123456 | Pending Validation | Pending Validation | 1/11/2020 |
1/19/2020 | 123456 | Pending Validation | Pending Validation | 1/11/2020 |
1/20/2020 | 123456 | Pending Validation | Pending Validation | 1/11/2020 |
1/21/2020 | 123456 | Open | Pending Validation | NULL |
1/22/2020 | 123456 | Open | Open | NULL |
1/23/2020 | 123456 | Open | Open | NULL |
1/24/2020 | 123456 | Open | Open | NULL |
1/25/2020 | 123456 | Open | Open | NULL |
1/26/2020 | 123456 | Open | Open | NULL |
1/27/2020 | 123456 | Closed | Open | 1/27/2020 |
1/28/2020 | 123456 | Closed | Closed | 1/27/2020 |
1/29/2020 | 123456 | Closed | Closed | 1/27/2020 |
1/30/2020 | 123456 | Closed | Closed | 1/27/2020 |
1/31/2020 | 123456 | Closed | Closed | 1/27/2020 |
我try 了以下几种方法:
当Issue_Status_Name in(待定验证、已关闭、待定可持续性)和Prev_Issue_Status_name in(NULL、OPEN、Draft)THEN SUM(情况当PRIV_ISESS_STATUS_NAME不在(待定验证、已关闭、待定可持续性)THEN_OF_DATE ELSE NULL
但它没有给出正确的输出.