question

AlexKlap avatar image
AlexKlap asked

Calculation on Date field in 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" But when IsRowActive = 0, then we have to calculate To_Date which should be 1 sec less than next FROMDT in a same group 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
sql-server-2008sqlt-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
From the data in question it looks like the last row in the group always happens to be active. If this is not the case then it is possible that the solution will have to be slightly modified to accommodate this scenario. In SQL Server 2012 or better there are analytical functions [LEAD][1] and [LAG][2] which allow reading from the next or previous rows while reading the current row. Unfortunately, these are not available in SQL Server 2008. The good news is that it is pretty easy to mimic this ability by simply joining the original set with itself on the off-by-one basis. In this case, lets assign each group a "group number" and then sub-number the records within each group ordered by from date. Then it is possible to join the set to itself on the off-by-one basis. This is why the question about whether the last row in each group is marked as row active is important (the last row in the group does not have the "next" counterpart, so there is a risk of the new date values to be null if the last row in the group is not marked as active, and so it does not have a default value of '9999-12-31' to fall back into). Here is the solution which should produce desired result: ;with data as ( select *, dense_rank() over (order by BINGID, INDUSID, COMP1) GroupNumber, row_number() over (partition by BINGID, INDUSID, COMP1 order by FROMDT) RowNumber from @MYTABLE ) select a.BINGID, a.INDUSID, a.DTSEARCH, a.COMP1, a.LISTPRICE, a.FROMDT, , a.ISROWACTIVE, cast( case when a.IsRowActive = 1 then '99991231' else dateadd(second, -1, b.FROMDT) end as datetime2 ) NEW_TO_DATE from data a left join data b on a.GroupNumber = b.GroupNumber and a.RowNumber = b.RowNumber - 1; To help with understanding on how it works, highlight the part of the select inside of the CTE definition and run it. Hope this helps. Oleg [1]: https://msdn.microsoft.com/en-us/library/hh213125.aspx [2]: https://msdn.microsoft.com/en-us/library/hh231256.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Oleg. In a Group ISROWACTIVE =0 only be available when ISROWACTIVE = 1 Present.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.