akhrot akhrot - 6 months ago 8
SQL Question

Date Calculation based on reference column in sql

Its little critical.... may anyone share your expertise.

DDL:-

CREATE TABLE mytable

(
SUPERID INT,
DATE_RECORDED DATETIME2,
SUPPLIER_SKU VARCHAR (50),
PRICEINCVAT DECIMAL (14,4),
[DESC] VARCHAR (100),
FROM_DATE DATETIME2,
ACTIVE BIT

)

INSERT mytable

SELECT 1000001, '2015-12-18 06:45:00.0000000', '0 986 490 640', '29.5400', 'CAPACITOR', '2015-12-18 06:45:00.0000000',0 UNION ALL
SELECT 1000001, '2015-12-18 06:45:00.0000000', '2347402', '32.6900', 'CAPACITOR', '2015-12-18 06:45:00.0000000', 0 UNION ALL
SELECT 1000001, '2015-12-18 06:45:00.0000000', 'FDB779', '30.9200', 'CAPACITOR', '2015-12-18 06:45:00.0000000',0 UNION ALL
SELECT 1000001, '2015-12-18 06:45:00.0000000', 'GDB1577', '29.3600', 'CAPACITOR', '2015-12-18 06:45:00.0000000',0UNION ALL

SELECT 1000001, '2016-03-03 02:22:00.0000000', '0 986 490 640', '29.5400','CAPACITOR','2016-03-03 02:22:00.0000000' ,1UNION ALL
SELECT 1000001, '2016-03-03 02:22:00.0000000', '2347402', '32.6900', 'CAPACITOR', '2016-03-03 02:22:00.0000000',1UNION ALL
SELECT 1000001, '2016-03-03 02:22:00.0000000', 'FDB779', '30.1800', 'CAPACITOR', '2016-03-03 02:22:00.0000000',1UNION ALL
SELECT 1000001, '2016-03-03 02:22:00.0000000', 'GDB1577','29.3600', 'CAPACITOR', '2016-03-03 02:22:00.0000000',1 UNION ALL


SELECT 1000017, '2015-12-18 06:47:00.0000000', 'GDB1496', '29.5400', 'CAPACITOR', '2015-12-18 06:47:00.0000000', 0 UNION ALL
SELECT 1000017, '2016-03-03 02:24:00.0000000', 'GDB1496', '29.5400', 'CAPACITOR', '2016-03-03 02:24:00.0000000', 1 UNION ALL
SELECT 1000653, '3/8/2016 11:04:00 PM', 'FDB1751', '29.5400', 'CAPACITOR', '3/2/2016 11:04:00 PM', 1 UNION ALL
SELECT 1000653, '12/12/2015 7:12:00 AM', 'FDB1751', '29.5400', 'CAPACITOR', '12/18/2015 7:12:00 AM', 0 UNION ALL

SELECT 1000653, '2015-12-18 07:12:00.0000000','0 986 494 096','29.5400', 'CAPACITOR', '2015-12-18 07:12:00.0000000',0 UNION ALL
SELECT 1000653, '2016-03-02 23:04:00.0000000', '0 986 494 096','29.5400', 'CAPACITOR', '2016-03-02 23:04:00.0000000',1 UNION ALL
SELECT 1000653, '2016-03-02 23:04:00.0000000', '16531','29.5400', 'CAPACITOR', '2016-03-02 23:04:00.0000000',1 UNION ALL
SELECT 1000653, '2015-12-18 07:12:00.0000000','2393501','29.5400', 'CAPACITOR','2015-12-18 07:12:00.0000000',0 UNION ALL
SELECT 1000653, '2016-03-02 23:04:00.0000000', '2393501','29.5400', 'CAPACITOR','2016-03-02 23:04:00.0000000',1 UNION ALL
SELECT 1000653, '2015-12-18 07:12:00.0000000','FDB1751','29.5400', 'CAPACITOR','2015-12-18 07:12:00.0000000',0 UNION ALL
SELECT 1000653, '2016-03-02 23:04:00.0000000','FDB1751','29.5400', 'CAPACITOR','2016-03-02 23:04:00.0000000',1 UNION ALL
SELECT 1000653, '2015-12-18 07:12:00.0000000','GDB1625','29.5400', 'CAPACITOR', '2015-12-18 07:12:00.0000000',0 UNION ALL
SELECT 1000653, '2016-03-02 23:04:00.0000000','GDB1625','29.5400', 'CAPACITOR','2016-03-02 23:04:00.0000000',1 UNION ALL
SELECT 1000653, '2015-12-18 07:12:00.0000000','LP1925','29.5400', 'CAPACITOR', '2015-12-18 07:12:00.0000000',1


i am trying to calculate value for TO_DATE which will be 1 sec less than highest date available in a FROM_DATE in group of SUPERID order by FROM_DATE. where ACTIVE COLUMN value would be 0. but where ACTIVE column = 1 , then it would be default (9999-12-31 00:00:00.0000000)

Expected Output:

SUPERID DATE_RECORDED SUPPLIER_SKU PRICEINCVAT DESC FROM_DATE TO_DATE ACTIVE

1000001 2015-12-18 06:45:00.0000000 0 986 490 640 29.5400 CAPACITOR 2015-12-18 06:45:00.0000000 2016-03-03 02:21:59.0000000 0
1000001 2015-12-18 06:45:00.0000000 2347402 32.6900 CAPACITOR 2015-12-18 06:45:00.0000000 2016-03-03 02:21:59.0000000 0
1000001 2015-12-18 06:45:00.0000000 FDB779 30.9200 CAPACITOR 2015-12-18 06:45:00.0000000 2016-03-03 02:21:59.0000000 0
1000001 2015-12-18 06:45:00.0000000 GDB1577 29.3600 CAPACITOR 2015-12-18 06:45:00.0000000 2016-03-03 02:21:59.0000000 0
1000001 2016-03-03 02:22:00.0000000 0 986 490 640 29.5400 CAPACITOR 2016-03-03 02:22:00.0000000 9999-12-31 00:00:00.0000000 1
1000001 2016-03-03 02:22:00.0000000 2347402 32.6900 CAPACITOR 2016-03-03 02:22:00.0000000 9999-12-31 00:00:00.0000000 1
1000001 2016-03-03 02:22:00.0000000 FDB779 30.1800 CAPACITOR 2016-03-03 02:22:00.0000000 9999-12-31 00:00:00.0000000 1
1000001 2016-03-03 02:22:00.0000000 GDB1577 29.3600 CAPACITOR 2016-03-03 02:22:00.0000000 9999-12-31 00:00:00.0000000 1
1000017 2015-12-18 06:47:00.0000000 GDB1496 29.5400 CAPACITOR 2015-12-18 06:47:00.0000000 2016-03-03 02:23:59.0000000 0
1000017 2016-03-03 02:24:00.0000000 GDB1496 29.5400 CAPACITOR 2016-03-03 02:24:00.0000000 9999-12-31 00:00:00.0000000 1
1000653 2015-12-12 07:12:00.0000000 FDB1751 29.5400 CAPACITOR 2015-12-18 07:12:00.0000000 2016-03-02 23:04:00.0000000 0
1000653 2015-12-18 07:12:00.0000000 0 986 494 096 29.5400 CAPACITOR 2015-12-18 07:12:00.0000000 2016-03-02 23:04:00.0000000 0
1000653 2015-12-18 07:12:00.0000000 2393501 29.5400 CAPACITOR 2015-12-18 07:12:00.0000000 2016-03-02 23:04:00.0000000 0
1000653 2015-12-18 07:12:00.0000000 FDB1751 29.5400 CAPACITOR 2015-12-18 07:12:00.0000000 2016-03-02 23:04:00.0000000 0
1000653 2015-12-18 07:12:00.0000000 GDB1625 29.5400 CAPACITOR 2015-12-18 07:12:00.0000000 2016-03-02 23:04:00.0000000 0
1000653 2015-12-18 07:12:00.0000000 LP1925 29.5400 CAPACITOR 2015-12-18 07:12:00.0000000 9999-12-31 00:00:00.0000000 1
1000653 2016-03-02 23:04:00.0000000 GDB1625 29.5400 CAPACITOR 2016-03-02 23:04:00.0000000 9999-12-31 00:00:00.0000000 1
1000653 2016-03-02 23:04:00.0000000 FDB1751 29.5400 CAPACITOR 2016-03-02 23:04:00.0000000 9999-12-31 00:00:00.0000000 1
1000653 2016-03-02 23:04:00.0000000 2393501 29.5400 CAPACITOR 2016-03-02 23:04:00.0000000 9999-12-31 00:00:00.0000000 1
1000653 2016-03-02 23:04:00.0000000 0 986 494 096 29.5400 CAPACITOR 2016-03-02 23:04:00.0000000 9999-12-31 00:00:00.0000000 1
1000653 2016-03-02 23:04:00.0000000 16531 29.5400 CAPACITOR 2016-03-02 23:04:00.0000000 9999-12-31 00:00:00.0000000 1
1000653 2016-03-08 23:04:00.0000000 FDB1751 29.5400 CAPACITOR 2016-03-02 23:04:00.0000000 9999-12-31 00:00:00.0000000 1


Code i tried but not giving correct result:

;WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY SUPERID ORDER BY FROM_DATE) as RN
FROM mytable m
)
SELECT c.SUPERID, c.DATE_RECORDED, c.SUPPLIER_SKU, c.PRICEINCVAT, c.[DESC], c.FROM_DATE,
CASE
WHEN c.ACTIVE = 0 THEN DATEADD(second, -1, c2.FROM_DATE)
ELSE '9999-12-31'
END as TO_DATE,
c.ACTIVE
FROM cte c
LEFT JOIN cte c2 ON c.SUPERID = c2.SUPERID
AND c.RN = c2.RN - 1


May someone please share if it is possible in SQL.
Thanks

Answer

You should be able to use OUTER APPLY to get the MAX(FROM_DATE) - 1 second.

SELECT  SUPERID,
        DATE_RECORDED,
        SUPPLIER_SKU,
        PRICEINCVAT,
        [DESC],
        FROM_DATE,
        CASE WHEN ACTIVE = 1 THEN '9999-12-31' ELSE TO_DATE END AS TO_DATE,
        ACTIVE
FROM    mytable mt
        OUTER APPLY (   SELECT  MAX(DATEADD(second, -1, FROM_DATE)) TO_DATE 
                        FROM    mytable mt2 
                        WHERE   mt2.SUPERID = mt.SUPERID 
                                AND mt2.SUPPLIER_SKU = mt.SUPPLIER_SKU 
                                AND mt2.From_DATE > mt.From_DATE  ) oa