Insert a new record in the gap

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert a new record in the gap

Author

Topic

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-05-22 : 13:34:47

I am trying to write a query where i can insert addiional record when the endtime of first record is not equal to start time of next record....Below is an example..can someone please help?

-- Current data
CREATE TABLE ##ItemDetail
(ItemID INT, ItemCode VARCHAR(8), Startdate DATETIME, EndTime DATETIME)

INSERT INTO ##ItemDetail(111, 'A', '2013-01-01', '2013-04-01')
INSERT INTO ##ItemDetail(111, 'A', '2014-01-01', '2014-12-31')
INSERT INTO ##ItemDetail(222, 'B', '2014-01-01', '2014-12-31')

-- Below is the output...
SELECT 111 AS ItemID, 'A' AS ItemCode, '2013-01-01' AS StartDate, '2013-04-01' AS Enddate
UNION ALL
SELECT 111 AS ItemID, 'Unknown' AS ItemCode, '2013-04-02' AS StartDate, '2013-12-31' AS Enddate
UNION ALL
SELECT 111 AS ItemID, 'A' AS ItemCode, '2014-01-01' AS StartDate, '2014-12-31' AS Enddate
UNION ALL
SELECT 222 AS ItemID, 'B' AS ItemCode, '2014-01-01' AS StartDate, '2014-12-31' AS Enddate

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-22 : 14:13:00

I don't know if this will cover all the cases you might have, but it works for your sample data:

WITH Cte AS
(
	SELECT 
		*,
		ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY StartDate) AS RowNum
	FROM 
		##ItemDetail
)
--  Existing Rows
SELECT
	ItemID
	,ItemCode
	,StartDate
	,EndTime
FROM
	Cte

UNION ALL

-- Missing Rows
SELECT 
	A.ItemID
	,'Unknown' AS ItemCode
	,CASE WHEN A.StartDate > B.EndTime THEN DATEADD(DAY, 1, B.EndTime) ELSE A.StartDate END AS StartDate
	,DATEADD(DAY, -1, A.StartDate) AS EndTime
FROM 
	Cte AS A
INNER JOIN
	Cte AS B
	ON A.ItemID = B.ItemID
	AND A.RowNum = B.RowNum + 1
ORDER BY
	ItemID,
	StartDate

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-22 : 14:35:39

My version is almost identical. I added some of my own samples to test. What should happen when there is a gap of only 1 day. my code ended upwith a start and end date of the same value.


CREATE TABLE ##ItemDetail
(ItemID INT, ItemCode VARCHAR(8), Startdate DATETIME, EndTime DATETIME)

INSERT INTO ##ItemDetail values (111, 'A', '2013-01-01', '2013-04-01')
INSERT INTO ##ItemDetail values (111, 'A', '2014-01-01', '2014-12-31')
INSERT INTO ##ItemDetail values (222, 'B', '2014-01-01', '2014-12-31')
INSERT INTO ##ItemDetail values (333, 'c', '2014-01-01', '2014-02-15')
INSERT INTO ##ItemDetail values (333, 'c', '2014-02-16', '2014-03-03')
INSERT INTO ##ItemDetail values (333, 'c', '2014-03-04', '2014-05-01')
INSERT INTO ##ItemDetail values (333, 'c', '2014-05-04', '2014-05-20')
INSERT INTO ##ItemDetail values (333, 'c', '2014-05-21', '2014-05-30')
INSERT INTO ##ItemDetail values (333, 'c', '2014-05-31', '2014-06-05')
INSERT INTO ##ItemDetail values (333, 'c', '2014-06-07', '2014-06-30')


;with id (ItemID, ItemCode, Startdate, EndTime, rid)
as
(
       select ItemID
              , ItemCode
              , Startdate
              , EndTime
              , row_number() over (partition by itemid order by startdate)
       from   ##itemdetail
)
insert ##itemDetail
select a.itemid
       ,'Unknown' as ItemCode
       ,dateadd(day, 1, a.endTime) as startDate
       ,dateadd(day, -1, b.startDate) as endTime
from   id a
left outer join id b 
       on b.itemid = a.itemid 
       and b.rid-1 = a.rid
where  datediff(day, a.endtime, b.startdate) > 1

select * from ##itemDetail order by itemID, startDate


OUTPUT:

ItemID      ItemCode Startdate               EndTime
----------- -------- ----------------------- -----------------------
111         A        2013-01-01 00:00:00.000 2013-04-01 00:00:00.000
111         Unknown  2013-04-02 00:00:00.000 2013-12-31 00:00:00.000
111         A        2014-01-01 00:00:00.000 2014-12-31 00:00:00.000
222         B        2014-01-01 00:00:00.000 2014-12-31 00:00:00.000
333         c        2014-01-01 00:00:00.000 2014-02-15 00:00:00.000
333         c        2014-02-16 00:00:00.000 2014-03-03 00:00:00.000
333         c        2014-03-04 00:00:00.000 2014-05-01 00:00:00.000
333         Unknown  2014-05-02 00:00:00.000 2014-05-03 00:00:00.000
333         c        2014-05-04 00:00:00.000 2014-05-20 00:00:00.000
333         c        2014-05-21 00:00:00.000 2014-05-30 00:00:00.000
333         c        2014-05-31 00:00:00.000 2014-06-05 00:00:00.000
333         Unknown  2014-06-06 00:00:00.000 2014-06-06 00:00:00.000
333         c        2014-06-07 00:00:00.000 2014-06-30 00:00:00.000

EDIT:
after looking at Lamprey's version I realize my LEFT OUTER JOIN is essentially an INNER join as I refer to both tables in the WHERE clause - so that should change to INNER. We even named our table aliases the same - great minds think alike?

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-22 : 15:29:25

quote:
Originally posted by TG

EDIT:
after looking at Lamprey's version I realize my LEFT OUTER JOIN is essentially an INNER join as I refer to both tables in the WHERE clause - so that should change to INNER. We even named our table aliases the same - great minds think alike?

Be One with the Optimizer
TG

NICE! :)

Yeah, the sample data was pretty limited. I'd assume my version would need a similar WHERE clause to your version.

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-05-22 : 19:58:06

Good catch about the one day gap. Thanks Lampery and TG. This works like a charm. But here is an another case where i am not sure what to do.....If the EndTime of the last record is less than today, then i wanted to add another record with the StartDate as EndTime of last record + 1...below is an example...

CREATE TABLE ##ItemDetail
(ItemID INT, ItemCode VARCHAR(8), Startdate DATETIME, EndTime DATETIME)

INSERT INTO ##ItemDetail values (111, 'A', '2013-01-01', '2013-04-01')
INSERT INTO ##ItemDetail values (111, 'A', '2014-01-01', '2014-04-30')
INSERT INTO ##ItemDetail values (222, 'B', '2014-01-01', '2014-12-31')
INSERT INTO ##ItemDetail values (333, 'c', '2014-01-01', '2014-02-15')
INSERT INTO ##ItemDetail values (333, 'c', '2014-02-16', '2014-03-03')
INSERT INTO ##ItemDetail values (333, 'c', '2014-03-04', '2014-05-01')
INSERT INTO ##ItemDetail values (333, 'c', '2014-05-04', '2014-05-20')
INSERT INTO ##ItemDetail values (333, 'c', '2014-05-21', '2014-05-30')
INSERT INTO ##ItemDetail values (333, 'c', '2014-05-31', '2014-06-05')
INSERT INTO ##ItemDetail values (333, 'c', '2014-06-07', '2014-06-30')
INSERT INTO ##ItemDetail values (444, 'D', '2013-01-01', '2013-05-31')

-- The output like earlier messages and
UNION ALL
SELECT 111, 'Unknown', '2014-05-01 00:00:00.000', '2099-12-31 00:00:00.000'
UNION ALL
SELECT 444, 'Unknown', '2014-06-01 00:00:00.000', '2099-12-31 00:00:00.000'
ORDER BY ItemID, Startdate

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-23 : 13:24:46

Something like this:

WITH Cte AS
(
	SELECT 
		*,
		ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY StartDate) AS RowNum
	FROM 
		##ItemDetail
)
--  Existing Rows
SELECT
	ItemID
	,ItemCode
	,StartDate
	,EndTime
FROM
	Cte

UNION ALL

-- Missing Rows
SELECT 
	A.ItemID
	,'Unknown' AS ItemCode
	,CASE 
		WHEN A.StartDate > B.EndTime THEN DATEADD(DAY, 1, B.EndTime) 
		WHEN B.EndTime IS NULL THEN DATEADD(DAY, 1, A.EndTime) 
		ELSE A.StartDate 
	END AS StartDate
	,CASE 
		WHEN B.EndTime IS NULL THEN CAST('99991231' AS DATETIME)
		ELSE DATEADD(DAY, -1, A.StartDate) 
	END AS EndTime
FROM 
	Cte AS A
LEFT OUTER JOIN
	Cte AS B
	ON A.ItemID = B.ItemID
	AND A.RowNum + 1 = B.RowNum
WHERE
	DATEDIFF(DAY, A.EndTime, B.StartDate) > 1
	OR
	(
		A.EndTime < GETDATE()
		AND B.ItemID IS NULL
	)
ORDER BY
	ItemID,
	StartDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources