SQL -将一个日期列转换为FromDate & ToDate列

Question

我对SQL很陌生，主要使用Power中查询的数据。

我要根据状态将我的日期列拆分为SQL中的两个日期列。(在改变状态的日子)

我有一个SQL查询，它给出了以下简单的结果

SERIAL NO         ENTITY_CODE COMPANY_NAME      STATUS       TRANSACTION_DATE
AB:CD:EF:GH:IJ:KL FRANCIS     MADICAL           Activation      27-04-12 18:30
AB:CD:EF:GH:IJ:KL DEADPOOL    FAST TRANSPORT    Disconnection   27-12-14 15:01
12345678901       DEADPOOL    FAST TRANSPORT    Activation      27-04-12 18:30
12345678901       DEADPOOL    FAST TRANSPORT    Disconnection   17-01-16 16:35
PQ:RS:TU:VW:XY:ZA DEADPOOL    FAST TRANSPORT    Activation      27-04-12 18:31
PQ:RS:TU:VW:XY:ZA DEADPOOL    FAST TRANSPORT    Disconnection   02-12-13 13:26
78901234567       BANDHU      BANDHU CABS       Activation      27-04-12 19:12
78901234567       BANDHU      BANDHU CABS       Disconnection   27-10-13 19:42
78901234567       DEADPOOL    FAST TRANSPORT    Reconnection    25-02-14 13:53
78901234567       DEADPOOL    FAST TRANSPORT    Disconnection   08-04-16 17:14

这是一个简单的'select‘查询，带有一些条件和'Order’。

为了找出，多少天这个系列编号是活动的和DisConnected，我需要这些数据以以下所述的格式。我需要动力BI。

SERIAL NO           ENTITY_CODE  COMPANY_NAME   STATUS          FromDate      ToDATE
AB:CD:EF:GH:IJ:KL   FRANCIS      MADICAL        Activation      27-04-12 18:30  27-12-14 15:01
AB:CD:EF:GH:IJ:KL   DEADPOOL     FAST TRANSPORT Disconnection   27-12-14 15:01  TODAY'S DATE
12345678901         DEADPOOL     FAST TRANSPORT Activation      27-04-12 18:30  17-01-16 16:35
12345678901         DEADPOOL     FAST TRANSPORT Disconnection   17-01-16 16:35  TODAY'S DATE
PQ:RS:TU:VW:XY:ZA   DEADPOOL     FAST TRANSPORT Activation      27-04-12 18:31  02-12-13 13:26
PQ:RS:TU:VW:XY:ZA   DEADPOOL     FAST TRANSPORT Disconnection   02-12-13 13:26  TODAY'S DATE
78901234567         BANDHU       BANDHU CABS    Activation      27-04-12 19:12  27-10-13 19:42
78901234567         BANDHU       BANDHU CABS    Disconnection   27-10-13 19:42  25-02-14 13:53
78901234567         DEADPOOL     FAST TRANSPORT Reconnection    25-02-14 13:53  08-04-16 17:14
78901234567         DEADPOOL     FAST TRANSPORT Disconnection   08-04-16 17:14  TODAY'S DATE

请在ToDate专栏中注明“今日的日期”。这一数据最终将给我的地位，每一个系列的号码，在每一天的数字的生命。

我需要根据每个事务日期划分“事务历史记录”，这样我就能够发现某些序列号从date1到date2是活动的，然后从日期2断开到日期3，依此类推。

有人能帮忙吗？

Answer 1

SELECT  
    SERIAL NO
        ,ENTITY_CODE COMPANY_NAME
        ,STATUS
        ,TRANSACTION_DATE
    ,CASE STATUS
        WHEN 'Activation' THEN <WHAT EVER YOU WANT TO HAPPEN>
        WHEN 'Disconnection' THEN <WHAT EVER YOU WANT TO HAPPEN>
        WHEN 'Reconnection' THEN <WHAT EVER YOU WANT TO HAPPEN>
        ELSE <WHAT EVER YOU WANT TO HAPPEN> 
        END as ToDATE
FROM 
    <Table Name>

Answer 2

看起来您的FromDate与TransactionDate相同，ToDate是下一个序列号相同的TransactionDate。

您应该能够在Power中创建一个带有DAX的计算列来获取您的ToDate。如下所示，Status是表的名称。

ToDate =
    VAR EndDate = MINX(
                      FILTER('Status',
                          'Status'[Serial No] = EARLIER('Status'[Serial No]) &&
                          'Status'[TransactionDate] > EARLIER('Status'[TransactionDate])),
                      'Status'[TransactionDate])
    RETURN IF(ISBLANK(EndDate), TODAY(), EndDate)

另一个使用CALCULATE而不是MINX的版本

ToDate = 
    VAR EndDate = CALCULATE(
                      MIN('Status'[TransactionDate]),
                      ALL('Status'),
                      'Status'[Serial No] = EARLIER('Status'[Serial No]),
                      'Status'[TransactionDate] > EARLIER('Status'[TransactionDate]))
    RETURN IF(ISBLANK(EndDate),TODAY(),EndDate)

Answer 3

看起来，您希望选择大于当前事务日期且具有相同Serial的min事务日期。如果没有匹配(在本例中返回NULL )，则返回今天的日期。

我使用了datetime数据类型，但它显示的示例代码选择了varchar或char。我假设你使用的是正确的数据类型。

如果您有，则可以尝试以下完整示例：

DECLARE @mytable TABLE ([SERIAL NO] varchar(100), ENTITY_CODE varchar(100), COMPANY_NAME varchar(100), [STATUS] varchar(100), TRANSACTION_DATE smalldatetime)
INSERT INTO @mytable VALUES
 ('AB:CD:EF:GH:IJ:KL', 'FRANCIS',  'MADICAL',        'Activation',    '2012-04-27 18:30:00')
,('AB:CD:EF:GH:IJ:KL', 'DEADPOOL', 'FAST TRANSPORT', 'Disconnection', '2014-12-27 15:01:00')
,('12345678901',       'DEADPOOL', 'FAST TRANSPORT', 'Activation',    '2012-04-27 18:30:00')
,('12345678901',       'DEADPOOL', 'FAST TRANSPORT', 'Disconnection', '2016-01-17 16:35:00')
,('PQ:RS:TU:VW:XY:ZA', 'DEADPOOL', 'FAST TRANSPORT', 'Activation',    '2012-04-27 18:31:00')
,('PQ:RS:TU:VW:XY:ZA', 'DEADPOOL', 'FAST TRANSPORT', 'Disconnection', '2013-12-02 13:26:00')
,('78901234567',       'BANDHU',   'BANDHU CABS',    'Activation',    '2012-04-27 19:12:00')
,('78901234567',       'BANDHU',   'BANDHU CABS',    'Disconnection', '2013-10-27 19:42:00')
,('78901234567',       'DEADPOOL', 'FAST TRANSPORT', 'Reconnection',  '2014-02-25 13:53:00')
,('78901234567',       'DEADPOOL', 'FAST TRANSPORT', 'Disconnection', '2016-04-08 17:14:00')

SELECT M1.[SERIAL NO], M1.ENTITY_CODE, M1.COMPANY_NAME, M1.[STATUS], M1.TRANSACTION_DATE AS FromDate
      ,COALESCE(
       (SELECT MIN(TRANSACTION_DATE)
          FROM @mytable M2
         WHERE M2.[SERIAL NO] = M1.[SERIAL NO]
           AND M2.TRANSACTION_DATE > M1.TRANSACTION_DATE
       ) 
               , getDate()
               ) AS ToDATE
  FROM @mytable M1