Calculation using Date function in SQL Server 2008

Question

I am trying to compute TO_DATE column for a group of BINGID, INDUSID, COMP1.

When IsRowActive = 1 then TO_DATE = "9999-12-31" , which is being returned correctly.

But when IsRowActive = 0, then we have to calculate To_Date which should be 1 sec less than next FROMDT

Data :

    DECLARE @MYTABLE TABLE

(
BINGID INT,
INDUSID INT,
DTSEARCH DATETIME2,
COMP1 VARCHAR (100),
LISTPRICE NUMERIC(10,2),
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-10-18 13:49:10.0000000',1 UNION ALL
SELECT 1002285, 1002,   '2015-12-19 12:51:41.0000000',  '10122374', 65.1400,    '2015-12-19 12:51:41.0000000',  0  UNION ALL
SELECT 1002285, 1002,   '2016-03-03 04:11:01.0000000',  '10122374', 117.2100,   '2016-03-03 04:11:01.0000000',  0  UNION ALL
SELECT 1002285, 1002,   '2016-05-27 12:14:45.0000000',  '10122374', 53.5500,    '2016-05-27 12:14:45.0000000',  0  UNION ALL
SELECT 1002285, 1002,   '2016-07-20 06:44:29.0000000',  '10122374', 48.5000,    '2016-07-20 06:44:29.0000000',  0  UNION ALL
SELECT 1002285, 1002,   '2016-10-18 13:49:00.0000000',  '10122374', 75.6800,    '2016-10-18 13:49:00.0000000',  0  UNION ALL
SELECT 1002285, 1002,   '2016-11-09 13:37:02.0000000',  '10122374', 68.2400,    '2016-11-09 13:37:02.0000000',  1 UNION ALL

SELECT 1000001, 1002,   '2016-03-03 02:22:09.0000000',  '161GDB1577',   37.1700,    '2015-12-18 06:45:05.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-03-03 02:22:18.0000000',  '0392347402',   41.9100,    '2015-12-18 06:45:14.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-05-26 14:54:28.0000000',  '161GDB1577',   46.7100,    '2016-05-26 14:54:28.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-05-26 14:54:42.0000000',  '0392347402',   54.7100,    '2016-05-26 14:54:42.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-07-15 06:34:33.0000000',  '161GDB1577',   52.4800,    '2016-07-15 06:34:33.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-07-15 06:34:45.0000000',  '0392347402',   81.7100,    '2016-07-15 06:34:45.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-10-17 11:26:45.0000000',  '161GDB1577',   61.6400,    '2016-10-17 11:26:45.0000000',0  UNION ALL
SELECT 1000001, 1002,   '2016-11-09 02:21:17.0000000',  '0392347402',   81.9200,    '2016-10-17 11:26:58.0000000',1  UNION ALL
SELECT 1000001, 1002,   '2016-11-09 02:21:05.0000000',  '161GDB1577',   78.3500,    '2016-11-09 02:21:05.0000000',1  UNION ALL
SELECT 1000005, 1002,   '2018-11-09 02:21:05.0000000',  '556556GHB',    78.3500,    '2018-11-09 02:21:05.0000000',1

Query I tried - unfortunately it is returning the wrong data :

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

Expected output

  BINGID    INDUSID DTSEARCH    COMP1   LISTPRICE   FROMDT  NEW_TO_DATE IsRowCurrent
1000001 1002    2016-03-03 02:22:09.0000000 161GDB1577  37.1700 2015-12-18 06:45:05.0000000 2016-05-26 14:54:27.0000000 0
1000001 1002    2016-03-03 02:22:18.0000000 0392347402  41.9100 2015-12-18 06:45:14.0000000 2016-05-26 14:54:41.0000000 0
1000001 1002    2016-05-26 14:54:28.0000000 161GDB1577  46.7100 2016-05-26 14:54:28.0000000 2016-07-15 06:34:32.0000000 0
1000001 1002    2016-05-26 14:54:42.0000000 0392347402  54.7100 2016-05-26 14:54:42.0000000 2016-07-15 06:34:44.0000000 0
1000001 1002    2016-07-15 06:34:33.0000000 161GDB1577  52.4800 2016-07-15 06:34:33.0000000 2016-10-17 11:26:44.0000000 0
1000001 1002    2016-07-15 06:34:45.0000000 0392347402  81.7100 2016-07-15 06:34:45.0000000 2016-10-17 11:26:57.0000000 0
1000001 1002    2016-10-17 11:26:45.0000000 161GDB1577  61.6400 2016-10-17 11:26:45.0000000 2016-11-09 02:21:04.0000000 0
1000001 1002    2016-11-09 02:21:17.0000000 0392347402  81.9200 2016-10-17 11:26:58.0000000 9999-12-31 00:00:00.0000000 1
1000001 1002    2016-11-09 02:21:05.0000000 161GDB1577  78.3500 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.3500 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.7600',  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.2200',  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.3900',  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.4500', 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.1400,    '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.2100,   '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.5500,    '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.5000,    '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.6800,    '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.2400,    '2016-11-09 13:37:02.0000000',  9999-12-31 00:00:00.0000000 1

Thanks.

Btw: This is a good question! Copy'n'pasteable test scenario, own effort, expected result... +1 from my side — Gottfried Lesigang
– Gottfried Lesigang, Commented Nov 28, 2016 at 12:11
@Rohini Mathur Expected output require any order by or as it. or i got result but ordering sequnce only miss matched. — Mr. Bhosale
– Mr. Bhosale, Commented Nov 30, 2016 at 10:12

David Rushton · Accepted Answer · 2016-11-28 12:56:13Z

2

I really like @Chanukya's answer. But as you are using 2008 you will cannot use the LEAD function. Instead, you can use a self-join:

-- 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
;

The logic is similar to your outer apply, but it should perform better. This is because there is no correlation.

The sample data presented a small challenge. Because there are two records with a FROMDT of 2016-07-20 06:44:37.0000000 you could argue my results are wrong.

edited Nov 28, 2016 at 12:56

answered Nov 28, 2016 at 12:23

David Rushton

5,0501 gold badge19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Rushton Over a year ago

Thank you @VladimirBaranov. You are right, my attempt was horrible. Update to fix. Like your answer, well written. Easy to follow.

Munavvar · Accepted Answer · 2016-12-01 05:23:33Z

2

Try this Simple and readable solution, Use CTE and 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

edited Dec 1, 2016 at 5:23

answered Nov 28, 2016 at 12:21

Munavvar

8312 gold badges11 silver badges33 bronze badges

5 Comments

Mr. Bhosale Over a year ago

voted. For simple solution try . but still miss match in results.

Mr. Bhosale Over a year ago

check with another sample data listed in below comments.

Vladimir Baranov Over a year ago

order by BINGID,INDUSID,DTSEARCH,COMP1,LISTPRICE,FROMDT can't be right.

Munavvar Over a year ago

Yes but.. Query shows correct result. also mention --- It is good if you have identity column here

Vladimir Baranov Over a year ago

@Munavvar, actually, no. Your query doesn't return expected result. That's why downvote. Hint: there's better be PARTITION BY as well.

Chanukya · Accepted Answer · 2016-11-28 12:55:18Z

1

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

output

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

edited Nov 28, 2016 at 12:55

answered Nov 28, 2016 at 12:14

Chanukya

5,8911 gold badge24 silver badges39 bronze badges

7 Comments

Rohini Mathur Over a year ago

Thanks But This is not giving correct result. The TO_DATE should be 1 sec less than the next FMDT.

Vladimir Baranov Over a year ago

LEAD is not available in SQL Server 2008.

Chanukya Over a year ago

i edited my answer can u check once ...@RohiniMathur

Chanukya Over a year ago

then what is correct to keep in order by ..@VladimirBaranov

Rohini Mathur Over a year ago

I am not very sure whether it is possible in sql........ may some one please share their expertise. Thanks

|

Vladimir Baranov · Accepted Answer · 2016-12-02 13:39:27Z

Instead of MAX use TOP(1) with appropriate ORDER BY in the OUTER APPLY.

Also, you said that you want to group by BINGID, INDUSID, COMP1, so use all of these columns in the WHERE clause in the OUTER APPLY. Why did you omit COMP1 in your query?

Sample data

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;

Result

+---------+---------+-----------------------------+------------------+-----------+-----------------------------+-----------------------------+-------------+
| 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 |
+---------+---------+-----------------------------+------------------+-----------+-----------------------------+-----------------------------+-------------+

Thanks but i am looking to have value as " 2016-07-20 06:44:36.0000000" in the Third TO_DATE instead of NULL.
@RohiniMathur, please explain what logic should be there, if there is no "next" row.
When there is no next row Than It did the Calculation From The Column "IsRowActive = 1" So Calculation will be done on FROMDT column where TO_DATE = 9999-12-31 00:00:00.0000000
@RohiniMathur, if you want to have some value in the TO_DATE when FROMDT has exactly the same value in two rows, then you need to define unambiguous order for these rows. Quite often people use unique row ID in addition to timestamp, if timestamps can have duplicates. Does you table have such ID? At the moment, there is no row that has FROMDT that is larger than 2016-07-20 06:44:37, so "next" row is undefined.
Thanks........ you are absolutely right Vladimir. i am changing the table structure.

Mr. Bhosale · Accepted Answer · 2016-12-09 05:42:40Z

-1

Using CTE + Joins :

Finally, Solution is here. Result Data is finally accurate as per your requirement also you can change sequence using order by [column name].

Code :

      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

also check Demo.

edited Dec 9, 2016 at 5:42

answered Nov 30, 2016 at 11:10

Mr. Bhosale

3,1161 gold badge19 silver badges37 bronze badges

5 Comments

Rohini Mathur Over a year ago

Sorry Mr. Bhosale. This is not giving correct result. 2 errors were there, 1st: table have only 2000 records but in output it is giving 5000 records, 2nd: its giving 9999-12-31 00:00:00.0000000 for even IsRowActive='0'

Rohini Mathur Over a year ago

may i expand the sample data with expected output

Rohini Mathur Over a year ago

Added some more sample data.

Mr. Bhosale Over a year ago

it means COMP1 values changes.You want separate result according to 'COMP1' and To_date must be next FROMDT of respected COMP1 group whether sRowActive='1' or '0'. Is it correct ? what should be 'To_date' for last record which IsRowActive='1' ?

Rohini Mathur Over a year ago

Bhosale: absolutely you are right. the last To_date should be 9999-12-31.

Collectives™ on Stack Overflow

Calculation using Date function in SQL Server 2008

5 Answers 5

1 Comment

5 Comments

7 Comments

7 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

5 Comments

7 Comments

7 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related