CODEWITHSUNDEEP
sqlsql-server
user7385063
user7385063

Reputation: 13

SQL- get the next starting date - 1 day if ending date is minimum value

recently has problem where my table is 

Item  |  Price  | StartDate  | EndDate
 A    |   50    | 2015-01-01 | 2015-01-15
 A    |   60    | 2015-01-16 | 2015-02-07
 A    |   40    | 2015-02-08 | 1753-01-01
 A    |   45    | 2015-02-20 | 2015-03-10
 A    |   50    | 2015-03-11 | 1753-01-01

and when i create a view i want the EndDate value with "1753-01-01" which still has next StartDate value to be 'Next StartDate value - 1 day' and for the EndDate value with '1753-01-01' which dont have any data after it then convert it to today date

the final view will be 

Item  |  Price  | StartDate  | EndDate
 A    |   50    | 2015-01-01 | 2015-01-15
 A    |   60    | 2015-01-16 | 2015-02-07
 A    |   40    | 2015-02-08 | 2015-02-19 (get next start date - 1 day)
 A    |   45    | 2015-02-20 | 2015-03-10
 A    |   50    | 2015-03-11 | 2017-04-08 (today date)

i have try using over partition by but still can't figure out how to use it in this case. Thanks in advance.

Upvotes: 0

Views: 79

Answers (3)

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

js fiddle

Try this

Select a.item,a.price,a.startdate,
dateadd(day, -1,min(isnull(b.startdate,getdate()))) 
   as enddate
from
test a
Left join test b on 
a.startdate < b.startdate
Group by a.item,a.price,a.startdate

Upvotes: 0

Chris Albert
Chris Albert

Reputation: 2507

CREATE TABLE #test
(
    Item varchar(50),
    Price money,
    StartDate date,
    EndDate date
)

INSERT INTO #test VALUES
 ('A', 50, '2015-01-01', '2015-01-15'),
 ('A', 60, '2015-01-16', '2015-02-07'),
 ('A', 40, '2015-02-08', '1753-01-01'),
 ('A', 45, '2015-02-20', '2015-03-10'),
 ('A', 50, '2015-03-11', '1753-01-01')

SELECT
    Item,
    Price,
    StartDate,
    CASE
        WHEN EndDate = '1753-01-01' AND LEAD(StartDate, 1) OVER (ORDER BY StartDate) IS NOT NULL
            THEN DATEADD(DAY, -1, LEAD(StartDate, 1) OVER (ORDER BY StartDate))
        WHEN EndDate = '1753-01-01' AND LEAD(StartDate, 1) OVER (ORDER BY StartDate) IS NULL
            THEN CAST(GETDATE() AS date)
        ELSE EndDate
        END AS 'EndDate'
FROM
    #test

DROP TABLE #test

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You can use lead():

select . . .,  -- the other columns you want
       (case when enddate = '1753-01-01' and next_startdate is null
             then cast(getdate() as date)
             when enddate = '1753-01-01'
             then dateadd(day, -1, next_startdate)
             else enddate
        end) as enddate
from (select t.*,
             lead(startdate) over (partition by item order by startdate) as next_startdate
      from t
     ) t;

It is a strange data representation that has "infinite" end dates as the earliest date in the data, rather than the latest.

Upvotes: 1

Related Questions