Вычисление с использованием функции Date в SQL Server 2008

Ответ 1

Попробуйте это простое и читаемое решение, используйте CTE и Self Join

with cte as  
( 
  SELECT
  ROW_NUMBER() over (order by BINGID,INDUSID,DTSEARCH,COMP1,LISTPRICE,FROMDT) 
      as rowno, -- It is good if you have identity column here
      BINGID,
      INDUSID,    
      DTSEARCH,   
      COMP1,  
      LISTPRICE,  
      FROMDT,
      IsRowActive
      FROM @MYTABLE mt 
)
select c1.*,
 CASE WHEN c1.IsRowActive = 1 THEN '9999-12-31' ELSE DATEADD(second, -1, c2.FROMDT) END
 AS TO_DATE
 from cte c1 left join cte c2
 on c1.rowno+1 = c2.rowno 

Ответ 2

Мне очень нравится @Chhanukya ответ. Но поскольку вы используете 2008, вы не сможете использовать функцию LEAD. Вместо этого вы можете использовать самоподключение:

-- SQL Server 2008.
SELECT
    c.*,
    CASE c.IsRowActive 
        WHEN 1 THEN '9999-12-31'
        ELSE DATEADD(SECOND, -1, MIN(p.FROMDT))
    END AS TO_DATE
FROM
    @MYTABLE AS c
        LEFT OUTER JOIN @MYTABLE AS p       ON  p.BINGID    = c.BINGID
                                            AND p.INDUSID   = c.INDUSID
                                            AND p.FROMDT    > c.FROMDT
GROUP BY
    c.BINGID,
    c.INDUSID,
    c.DTSEARCH,
    c.COMP1,
    c.LISTPRICE,
    c.FROMDT,
    c.IsRowActive
ORDER BY
    c.FROMDT
;

Логика похожа на внешнюю, но должна работать лучше. Это связано с тем, что корреляция.

Примеры данных представляли собой небольшую проблему. Поскольку есть две записи с FROMDT of 2016-07-20 06:44:37.0000000, вы можете утверждать, что мои результаты неверны.

Ответ 3

DECLARE @MYTABLE TABLE
(
BINGID  int,
INDUSID int,
DTSEARCH datetime2, 
COMP1 varchar(100),
LISTPRICE numeric(15,5),    
FROMDT  datetime2,
IsRowActive int

)
insert @MYTABLE

SELECT 1002285  ,1002   ,'2016-03-03 04:10:58.0000000', '0026PU009163-031', 77.7600 ,'2015-12-19 12:51:49.0000000', 0 UNION ALL
SELECT 1002285  ,1002   ,'2016-05-27 12:14:53.0000000', '0026PU009163-031', 85.2200 ,'2016-05-27 12:14:53.0000000', 0  UNION ALL
SELECT 1002285  ,1002   ,'2016-07-20 06:44:37.0000000', '0026PU009163-031', 90.3900 ,'2016-07-20 06:44:37.0000000', 0  UNION ALL
SELECT 1002285  ,1002   ,'2016-11-09 13:37:13.0000000', '0026PU009163-031', 131.4500,'2016-07-20 06:44:37.0000000', 1


select BINGID,DTSEARCH,COMP1,LISTPRICE,FROMDT,CASE WHEN IsRowActive = 0 THEN lead(DATEADD(SS,-1,FROMDT)) OVER (ORDER BY FROMDT) ELSE  '9999-12-31' END AS expected_date

FROM @MYTABLE mt

Выход

BINGID  DTSEARCH    COMP1   LISTPRICE   FROMDT  expected_date
1002285 2016-03-03 04:10:58.0000000 0026PU009163-031    77.76000    2015-12-19 12:51:49.0000000 2016-05-27 12:14:52.0000000
1002285 2016-05-27 12:14:53.0000000 0026PU009163-031    85.22000    2016-05-27 12:14:53.0000000 2016-07-20 06:44:36.0000000
1002285 2016-07-20 06:44:37.0000000 0026PU009163-031    90.39000    2016-07-20 06:44:37.0000000 2016-07-20 06:44:36.0000000
1002285 2016-11-09 13:37:13.0000000 0026PU009163-031    131.45000   2016-07-20 06:44:37.0000000 9999-12-31 00:00:00.0000000

Ответ 4

Вместо MAX используйте TOP(1) с соответствующим ORDER BY в OUTER APPLY.

Кроме того, вы сказали, что хотите группировать по BINGID, INDUSID, COMP1, поэтому используйте все эти столбцы в предложении WHERE в OUTER APPLY. Почему вы опустили COMP1 в своем запросе?

Примеры данных

DECLARE @MYTABLE TABLE
(
    BINGID INT,
    INDUSID INT,
    DTSEARCH DATETIME2,
    COMP1 VARCHAR (100),
    LISTPRICE NUMERIC(10,2),
    FROMDT DATETIME2,
    IsRowActive INT
)

INSERT INTO @MYTABLE
SELECT 1002285, 1002, '2016-03-03 04:10:58', '0026PU009163-031',  77.7600, '2015-12-19 12:51:49', 0 UNION ALL
SELECT 1002285, 1002, '2016-05-27 12:14:53', '0026PU009163-031',  85.2200, '2016-05-27 12:14:53', 0 UNION ALL
SELECT 1002285, 1002, '2016-07-20 06:44:37', '0026PU009163-031',  90.3900, '2016-07-20 06:44:37', 0 UNION ALL
SELECT 1002285, 1002, '2016-11-09 13:37:13', '0026PU009163-031', 131.4500, '2016-10-18 13:49:10', 1 UNION ALL
SELECT 1002285, 1002, '2015-12-19 12:51:41', '10122374',          65.1400, '2015-12-19 12:51:41', 0 UNION ALL
SELECT 1002285, 1002, '2016-03-03 04:11:01', '10122374',         117.2100, '2016-03-03 04:11:01', 0 UNION ALL
SELECT 1002285, 1002, '2016-05-27 12:14:45', '10122374',          53.5500, '2016-05-27 12:14:45', 0 UNION ALL
SELECT 1002285, 1002, '2016-07-20 06:44:29', '10122374',          48.5000, '2016-07-20 06:44:29', 0 UNION ALL
SELECT 1002285, 1002, '2016-10-18 13:49:00', '10122374',          75.6800, '2016-10-18 13:49:00', 0 UNION ALL
SELECT 1002285, 1002, '2016-11-09 13:37:02', '10122374',          68.2400, '2016-11-09 13:37:02', 1 UNION ALL
SELECT 1000001, 1002, '2016-03-03 02:22:09', '161GDB1577',        37.1700, '2015-12-18 06:45:05', 0 UNION ALL
SELECT 1000001, 1002, '2016-03-03 02:22:18', '0392347402',        41.9100, '2015-12-18 06:45:14', 0 UNION ALL
SELECT 1000001, 1002, '2016-05-26 14:54:28', '161GDB1577',        46.7100, '2016-05-26 14:54:28', 0 UNION ALL
SELECT 1000001, 1002, '2016-05-26 14:54:42', '0392347402',        54.7100, '2016-05-26 14:54:42', 0 UNION ALL
SELECT 1000001, 1002, '2016-07-15 06:34:33', '161GDB1577',        52.4800, '2016-07-15 06:34:33', 0 UNION ALL
SELECT 1000001, 1002, '2016-07-15 06:34:45', '0392347402',        81.7100, '2016-07-15 06:34:45', 0 UNION ALL
SELECT 1000001, 1002, '2016-10-17 11:26:45', '161GDB1577',        61.6400, '2016-10-17 11:26:45', 0 UNION ALL
SELECT 1000001, 1002, '2016-11-09 02:21:17', '0392347402',        81.9200, '2016-10-17 11:26:58', 1 UNION ALL
SELECT 1000001, 1002, '2016-11-09 02:21:05', '161GDB1577',        78.3500, '2016-11-09 02:21:05', 1 UNION ALL
SELECT 1000005, 1002, '2018-11-09 02:21:05', '556556GHB',         78.3500, '2018-11-09 02:21:05', 1

Query

SELECT
    BINGID,
    INDUSID,
    DTSEARCH,
    COMP1,
    LISTPRICE,
    FROMDT,
    CASE WHEN IsRowActive = 1 THEN '9999-12-31' ELSE oa.TO_DATE END AS TO_DATE,
    IsRowActive
FROM
    @MYTABLE AS mt
    OUTER APPLY 
    (   
        SELECT TOP(1) DATEADD(second, -1, FROMDT) AS TO_DATE
        FROM @MYTABLE AS mt2
        WHERE
            mt2.BINGID = mt.BINGID 
            AND mt2.INDUSID = mt.INDUSID 
            AND mt2.COMP1 = mt.COMP1
            AND mt2.FROMDT > mt.FROMDT
        ORDER BY mt2.FROMDT
    ) AS oa
WHERE
    mt.INDUSID = '1002'
ORDER BY BINGID, INDUSID, COMP1, FROMDT;

Результат

+---------+---------+-----------------------------+------------------+-----------+-----------------------------+-----------------------------+-------------+
| BINGID  | INDUSID |          DTSEARCH           |      COMP1       | LISTPRICE |           FROMDT            |           TO_DATE           | IsRowActive |
+---------+---------+-----------------------------+------------------+-----------+-----------------------------+-----------------------------+-------------+
| 1000001 |    1002 | 2016-03-03 02:22:18.0000000 | 0392347402       | 41.91     | 2015-12-18 06:45:14.0000000 | 2016-05-26 14:54:41.0000000 |           0 |
| 1000001 |    1002 | 2016-05-26 14:54:42.0000000 | 0392347402       | 54.71     | 2016-05-26 14:54:42.0000000 | 2016-07-15 06:34:44.0000000 |           0 |
| 1000001 |    1002 | 2016-07-15 06:34:45.0000000 | 0392347402       | 81.71     | 2016-07-15 06:34:45.0000000 | 2016-10-17 11:26:57.0000000 |           0 |
| 1000001 |    1002 | 2016-11-09 02:21:17.0000000 | 0392347402       | 81.92     | 2016-10-17 11:26:58.0000000 | 9999-12-31 00:00:00.0000000 |           1 |
| 1000001 |    1002 | 2016-03-03 02:22:09.0000000 | 161GDB1577       | 37.17     | 2015-12-18 06:45:05.0000000 | 2016-05-26 14:54:27.0000000 |           0 |
| 1000001 |    1002 | 2016-05-26 14:54:28.0000000 | 161GDB1577       | 46.71     | 2016-05-26 14:54:28.0000000 | 2016-07-15 06:34:32.0000000 |           0 |
| 1000001 |    1002 | 2016-07-15 06:34:33.0000000 | 161GDB1577       | 52.48     | 2016-07-15 06:34:33.0000000 | 2016-10-17 11:26:44.0000000 |           0 |
| 1000001 |    1002 | 2016-10-17 11:26:45.0000000 | 161GDB1577       | 61.64     | 2016-10-17 11:26:45.0000000 | 2016-11-09 02:21:04.0000000 |           0 |
| 1000001 |    1002 | 2016-11-09 02:21:05.0000000 | 161GDB1577       | 78.35     | 2016-11-09 02:21:05.0000000 | 9999-12-31 00:00:00.0000000 |           1 |
| 1000005 |    1002 | 2018-11-09 02:21:05.0000000 | 556556GHB        | 78.35     | 2018-11-09 02:21:05.0000000 | 9999-12-31 00:00:00.0000000 |           1 |
| 1002285 |    1002 | 2016-03-03 04:10:58.0000000 | 0026PU009163-031 | 77.76     | 2015-12-19 12:51:49.0000000 | 2016-05-27 12:14:52.0000000 |           0 |
| 1002285 |    1002 | 2016-05-27 12:14:53.0000000 | 0026PU009163-031 | 85.22     | 2016-05-27 12:14:53.0000000 | 2016-07-20 06:44:36.0000000 |           0 |
| 1002285 |    1002 | 2016-07-20 06:44:37.0000000 | 0026PU009163-031 | 90.39     | 2016-07-20 06:44:37.0000000 | 2016-10-18 13:49:09.0000000 |           0 |
| 1002285 |    1002 | 2016-11-09 13:37:13.0000000 | 0026PU009163-031 | 131.45    | 2016-10-18 13:49:10.0000000 | 9999-12-31 00:00:00.0000000 |           1 |
| 1002285 |    1002 | 2015-12-19 12:51:41.0000000 | 10122374         | 65.14     | 2015-12-19 12:51:41.0000000 | 2016-03-03 04:11:00.0000000 |           0 |
| 1002285 |    1002 | 2016-03-03 04:11:01.0000000 | 10122374         | 117.21    | 2016-03-03 04:11:01.0000000 | 2016-05-27 12:14:44.0000000 |           0 |
| 1002285 |    1002 | 2016-05-27 12:14:45.0000000 | 10122374         | 53.55     | 2016-05-27 12:14:45.0000000 | 2016-07-20 06:44:28.0000000 |           0 |
| 1002285 |    1002 | 2016-07-20 06:44:29.0000000 | 10122374         | 48.50     | 2016-07-20 06:44:29.0000000 | 2016-10-18 13:48:59.0000000 |           0 |
| 1002285 |    1002 | 2016-10-18 13:49:00.0000000 | 10122374         | 75.68     | 2016-10-18 13:49:00.0000000 | 2016-11-09 13:37:01.0000000 |           0 |
| 1002285 |    1002 | 2016-11-09 13:37:02.0000000 | 10122374         | 68.24     | 2016-11-09 13:37:02.0000000 | 9999-12-31 00:00:00.0000000 |           1 |
+---------+---------+-----------------------------+------------------+-----------+-----------------------------+-----------------------------+-------------+

Ответ 5

Использование CTE + Joins:

Наконец, решение здесь. Данные результата окончательно точны в соответствии с вашим требованием, вы также можете изменить последовательность, используя порядок, по имени столбца.

Код:

      with cte as  
                ( 
                      SELECT 
                      ROW_NUMBER( ) OVER ( partition by  COMP1 ORDER BY (SELECT 1))
                      rowno,
                      BINGID,
                      INDUSID,    
                      DTSEARCH,   
                      COMP1,  
                      LISTPRICE,  
                      FROMDT,
                      IsRowActive
                      FROM @MYTABLE mt 
                )
                select c1.BINGID,c1.INDUSID, c1.DTSEARCH,  c1.COMP1, c1.LISTPRICE, c1.FROMDT, c1.IsRowActive,
                CASE WHEN c1.IsRowActive = 1 THEN '9999-12-31' ELSE case when ( c2.rowno is null ) THEN '9999-12-31' 
                else DATEADD(second, -1, coalesce(c2.FROMDT,'9999-12-31') ) End END
                AS TO_DATE
                from  cte c1   left join cte c2 
                on c1.rowno+1= c2.rowno and c1.COMP1=c2.COMP1

                order by c1.BINGID,DTSEARCH

также проверьте Демо.