Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Calculation using Date function in SQL Server 2008

Tags:

sql

sql-server

sql-server-2008

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.

like image 896
Rohini Mathur Avatar asked Nov 28 '16 11:11

Rohini Mathur

People also ask

What does date () do in SQL?

MySQL DATE() Function The DATE() function extracts the date part from a datetime expression.

How can I calculate days between two dates in SQL?

The DATEDIFF() function returns the difference between two dates.

4 Answers

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.

like image 103
David Rushton Avatar answered Oct 17 '22 21:10

David Rushton

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
like image 35
Munavvar Avatar answered Oct 17 '22 23:10

Munavvar

 
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
like image 1
Chanukya Avatar answered Oct 17 '22 23:10

Chanukya

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 |
+---------+---------+-----------------------------+------------------+-----------+-----------------------------+-----------------------------+-------------+
like image 1
Vladimir Baranov Avatar answered Oct 17 '22 22:10

Vladimir Baranov
Sign in to Comment

Related questions

Semantics of INSERT SELECT FOR UPDATE ON CONFLICT DO NOTHING RETURNING
BigQuery replaced most of my Spark jobs, am I missing something?
Tricky postgresql query optimization (distinct row aggregation with ordering)
Compute 2,3 quartile average in SQL
Visual Studio 2012 Schema Compare: Column Order and Constraint Names
Parsing SQL Statement With Irony
SqlConnection vs Sql Session. Do their lifetimes coincide?
try....catch in mysql for transaction?
RSConfig generates a Dsn Connection String doesn't work
SQL Server 2008 Hierarchy Data Type Performance?
Oracle hierarchical query on non-hierarchical data
How expensive are MySQL events?
PostgreSQL cast record to composite type
How to efficiently get a range of ranked users (for a leaderboard) using Postgresql
IndexedDB, WebSQL in 4 Months
Alternatives to SQL? (Alternative declarative query languages for relational databases?) [closed]
Migrating from MySQL to SQL Server, issues with constraints
Allen's Interval Algebra operations in SQL
Oracle IN vs Exists difference?
SQL - Relationship between a SubQuery and an Outer Table

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!