Need to calc start and end date from single effective date

Question

I am trying to write SQL to calculate the start and end date from a single date called effective date for each item. Below is a idea of how my data looks. There are times when the last effective date for an item will be in the past so I want the end date for that to be a year from today. The other two items in the table example have effective dates in the future so no need to create and end date of a year from today.

I have tried a few ways but always run into bad data. Below is an example of my query and the bad results

select distinct tb1.itemid,tb1.EffectiveDate as startdate
, case 
when dateadd(d,-1,tb2.EffectiveDate) < getdate() 
or tb2.EffectiveDate is  null
then getdate() +365 
else dateadd(d,-1,tb2.EffectiveDate) 
end as enddate
from #test tb1

left join #test as tb2 on (tb2.EffectiveDate > tb1.EffectiveDate 
   or tb2.effectivedate is null) and tb2.itemid = tb1.itemid

left join #test tb3 on (tb1.EffectiveDate < tb3.EffectiveDate 
  andtb3.EffectiveDate <tb2.EffectiveDate or tb2.effectivedate is null) 
  and tb1.itemid = tb3.itemid

left join #test tb4 on tb1.effectivedate = tb4.effectivedate \
  and tb1.itemid = tb4.itemid

where tb1.itemID in (62741,62740, 65350)

Results - there is an extra line for 62740 Bad Results

I expect to see below since the first two items have a future end date no need to create an end date of today + 365 but the last one only has one effective date so we have to calculate the end date.

Answer 1

you should use the case when statement..
[wrong query because a misunderstand of the requirements]

SELECT 
       ItemID AS Item,
       StartDate,
       CASE WHEN EndDate < Sysdate THEN Sysdate + 365 ELSE EndDate END AS EndDate 
FROM 
(
  SELECT tabStartDate.ItemID, tabStartDate.EffectiveDate AS StartDate, tabEndDate.EffectiveDate AS EndDate
  FROM TableItems tabStartDate
  JOIN TableItems tabEndDate on tabStartDate.ItemID = tabEndDate.ItemID 
) TableDatesPerItem
WHERE StartDate < EndDate

update after clarifications in the OP and some comments

I found a solution quite portable, because it doesn't make use of partioning but endorses on a sort of indexing rule that make to correspond the dates of each item with others with the same id, in order of time's succession.

The portability is obviously related to the "difficult" part of query, while row numbering mechanism and conversion go adapted, but I think that it isn't a problem.

I sended a version for MySql that it can try on SQL Fiddle..

Table

CREATE TABLE ITEMS 
      (`ItemID` int, `EffectiveDate` Date);

INSERT INTO ITEMS
      (`ItemID`, `EffectiveDate`)
VALUES
  (62741, DATE(20160625)),
  (62741, DATE(20160604)),
  (62740, DATE(20160709)),
  (62740, DATE(20160625)),
  (62740, DATE(20160604)),
  (62750, DATE(20160528))
;

Query

SELECT 
     RESULT.ItemID AS ItemID, 
     DATE_FORMAT(RESULT.StartDate,'%m/%d/%Y') AS StartDate, 
     CASE WHEN RESULT.EndDate < CURRENT_DATE
      THEN DATE_FORMAT((CURRENT_DATE + INTERVAL 365 DAY),'%m/%d/%Y') 
      ELSE DATE_FORMAT(RESULT.EndDate,'%m/%d/%Y') 
     END AS EndDate
FROM
(
   SELECT 
     tabStartDate.ItemID AS ItemID, 
     tabStartDate.StartDate AS StartDate, 
     tabEndDate.EndDate
     ,tabStartDate.IDX,
     tabEndDate.IDX AS IDX2
     FROM
     (
      SELECT 
         tabStartDateIDX.ItemID AS ItemID, 
         tabStartDateIDX.EffectiveDate AS StartDate,
         @rownum:=@rownum+1 AS IDX
      FROM ITEMS AS tabStartDateIDX
      ORDER BY tabStartDateIDX.ItemID,  tabStartDateIDX.EffectiveDate
     )AS tabStartDate 
     JOIN
     (
       SELECT 
         tabEndDateIDX.ItemID AS ItemID, 
         tabEndDateIDX.EffectiveDate AS EndDate,
         @rownum:=@rownum+1 AS IDX
       FROM ITEMS AS tabEndDateIDX 
       ORDER BY tabEndDateIDX.ItemID, tabEndDateIDX.EffectiveDate
      )AS tabEndDate
      ON tabStartDate.ItemID = tabEndDate.ItemID AND (tabEndDate.IDX - tabStartDate.IDX = ((select count(*) from ITEMS)+1) )
     ,(SELECT @rownum:=0) r
  UNION
  (
    SELECT 
       tabStartDateSingleItem.ItemID AS ItemID, 
       tabStartDateSingleItem.EffectiveDate AS StartDate,
       tabStartDateSingleItem.EffectiveDate AS EndDate
       ,0 AS IDX,0 AS IDX2
     FROM ITEMS AS tabStartDateSingleItem
    Group By tabStartDateSingleItem.ItemID
    HAVING  Count(tabStartDateSingleItem.ItemID) = 1
  )
) AS RESULT
;

Answer 2

I did this exercise for Items that have multiple EffectiveDate in the table

you can create this view

CREATE view [VW_TESTDATA] 
AS ( SELECT * FROM  
    (SELECT ROW_NUMBER() OVER (ORDER BY Item,CONVERT(datetime,EffectiveDate,110)) AS ID, Item, DATA
FROM MyTable ) AS Q
)

so use a select to compare the same Item

select * from [VW_TESTDATA] as A inner join [VW_TESTDATA] as B on A.Item = B.Item and A.id = B.id-1

in this way you always minor and major Date

I did not understand how to handle dates with only one Item , but it seems the simplest thing and can be added to this query with a UNION ALL, because the view not cover individual Item

You also need to figure out how to deal with Item with two equal EffectiveDate

Answer 3

This should do it:

SELECT itemid
      ,effective_date AS "Start"
       ,(SELECT MIN(effective_date)
          FROM effective_date_tbl
         WHERE effective_date > edt.effective_date
           AND itemid = edt.itemid) AS "End"
  FROM effective_date_tbl edt
 WHERE effective_date <
       (SELECT MAX(effective_date) FROM effective_date_tbl WHERE itemid = edt.itemid)
 UNION ALL
 SELECT itemid
       ,effective_date AS "Start"
       ,(SYSDATE + 365) AS "End"
 FROM effective_date_tbl edt
 WHERE 1 =  ( SELECT COUNT(*) FROM effective_date_table WHERE itemid = edt.itemid )      
 ORDER BY 1, 2, 3;

Answer 4

I think I've read your question correctly. If you could provide your expected output it would help a lot.

Test Data

CREATE TABLE #TestData (itemID int, EffectiveDate date)
INSERT INTO #TestData (itemID, EffectiveDate)
VALUES
(62741,'2016-06-25')
,(62741,'2016-06-04')
,(62740,'2016-07-09')
,(62740,'2016-06-25')
,(62740,'2016-06-04')
,(65350,'2016-05-28')

Query

SELECT
a.itemID
,MIN(a.EffectiveDate) StartDate
,MAX(CASE WHEN b.MaxDate > GETDATE() THEN b.MaxDate ELSE CONVERT(date,DATEADD(yy,1,GETDATE())) END) EndDate
FROM #TestData a
JOIN (SELECT itemID, MAX(EffectiveDate) MaxDate FROM #TestData GROUP BY itemID) b
ON a.itemID = b.itemID
GROUP BY a.itemID

Result

itemID  StartDate   EndDate
62740   2016-06-04  2016-07-09
62741   2016-06-04  2016-06-25
65350   2016-05-28  2017-06-24