Perform INSERT between range dates if not exists

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)

Perform INSERT between range dates if not exists

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-14 : 04:52:20

Hello all,
I have a table with this structure:

TableA

DateRif - Field1
2013-02-01 00:00:00 AAAA
2013-02-02 00:00:00 BBBB
2013-02-04 00:00:00 CCCC
2013-02-06 00:00:00 DDDD
2013-02-08 00:00:00 EEEE
----

Now I need to write a stored procedure that has 2 parameters,
DateStart and DateEnd
and I have to perform and INSERT into TableA where missing days
between these 2 parameters.

For example, if I have:

DateStart = '2013-02-01 00:00:00'
DateEnd = '2013-02-11 00:00:00'

I have to insert the records:

2013-02-03 00:00:00
2013-02-05 00:00:00
2013-02-07 00:00:00
2013-02-09 00:00:00
2013-02-10 00:00:00
2013-02-11 00:00:00

and skip the already present days (no matter about the Field1).

How can I write this simil foreach statement?

Thanks in advance.

Luigi

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-14 : 05:53:28

[code]
-- making testdata
declare @TableA table (DateRif datetime, Field1 varchar(30))
insert @TableA
select '20130201', 'AAAA' union all
select '20130202', 'BBBB' union all
select '20130204', 'CCCC' union all
select '20130206', 'DDDD' union all
select '20130208', 'EEEE'

-- show testdata
select * from @TableA

-- begin solution
declare @DateStart datetime
declare @DateEnd datetime
set @DateStart = '20130201'
set @DateEnd = '20130211'

insert @TableA(DateRif, Field1)
select dt.DateValue, null
from
(
SELECT
@DateStart+Number-1 as DateValue
FROM (select
number
from master..spt_values
where type = 'p'
and number between 1 and datediff(day,@DateStart,@DateEnd+1)
)Numbers
WHERE Number<=DATEDIFF(day,@DateStart,@DateEnd)+1
)dt
left join @TableA a on a.DateRif = dt.DateValue
where a.DateRif is null

-- show result
select * from @TableA
order by DateRif[/code]

Too old to Rock'n'Roll too young to die.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 05:57:44


CREATE PROCEDURE USP_Insert_MissingRecords(
	@P_StartDate DATETIME,
	@P_EndDate DATETIME,
	@P_RowsProcessed INT OUT)
AS
BEGIN
	;WITH Dates(sd)
	AS (
		 SELECT @P_StartDate 
		 UNION ALL
		 SELECT DATEADD(D, 1, sd) FROM Dates WHERE sd <= @P_EndDate
	   ) 
	   INSERT INTO TableA(DateRif, Field1)
		SELECT sd, 'Missing'
		FROM Dates
		WHERE sd NOT IN (SELECT DateRif FROM TABLEA);
END

DECLARE @P_RowsProcessed INT
EXEC USP_Insert_MissingRecords '2013-02-01 00:00:00', '2013-02-11 00:00:00', @P_RowsProcessed OUT
SELECT @P_RowsProcessed

SELECT * FROM TableA

EDIT: webfred's Solution is dependent of system table spt_values.....
--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-14 : 06:07:30

quote:

Originally posted by bandi

GO


CREATE PROCEDURE USP_Insert_MissingRecords(
	@P_StartDate DATETIME,
	@P_EndDate DATETIME,
	@P_RowsProcessed INT OUT)
AS
BEGIN
	;WITH Dates(sd)
	AS (
		 SELECT @P_StartDate 
		 UNION ALL
		 SELECT DATEADD(D, 1, sd) FROM Dates WHERE sd <= @P_EndDate
	   ) 
	   INSERT INTO TableA(DateRif, Field1)
		SELECT sd, 'Missing'
		FROM Dates
		WHERE sd NOT IN (SELECT DateRif FROM TABLEA);
END

DECLARE @P_RowsProcessed INT
EXEC USP_Insert_MissingRecords '2013-02-01 00:00:00', '2013-02-11 00:00:00', @P_RowsProcessed OUT
SELECT @P_RowsProcessed

SELECT * FROM TableA

EDIT: webfred's Solution is dependent of system table spt_values.....
--
Chandu

Nice

Too old to Rock'n'Roll too young to die.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 06:30:26

Thank you Webfred

--
Chandu

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-14 : 06:45:44

Thank you all; I'll use the Bandi solution for now.

Luigi

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 07:06:33

quote:
Originally posted by Ciupaz

Thank you all; I'll use the Bandi solution for now.
Luigi

welcome

--
Chandu

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-14 : 07:30:33

A little problem. It seems that one day is missing. I've tried with 01/02/2013 and 05/02/2013 and the 3rd February is not inserted.

Luigi

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 07:46:42

R you getting error?


	;WITH Dates(sd)
	AS (
		 SELECT @P_StartDate 
		 UNION ALL
		 SELECT DATEADD(D, 1, sd) FROM Dates WHERE sd <= @P_EndDate
	   ) 
	   INSERT INTO TableA(DateRif, Field1)
		SELECT sd, 'Missing'
		FROM Dates
		WHERE sd NOT IN (SELECT DateRif FROM TABLEA)
		OPTION (MAXRECURSION 0);

--
Chandu

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-14 : 09:08:03

Sorry Bandy, my fault, I've missed a filter in the NOT IN statement.

L

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-14 : 10:19:11

A little adding.
How can I write a query that returns me the missing days from TableA (always with StartDate and EndDate parameters)?

L

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 10:14:08

means you want to insert missing rows as well as display of only missing rows... right?
If yes...


INSERT INTO TableA(DateRif, Field1)
OUTPUT inserted.* 
	SELECT sd, 'Missing'
	FROM Dates
	WHERE sd NOT IN (SELECT DateRif FROM TABLEA)
	OPTION (MAXRECURSION 0);

--
Chandu

hasondea
Starting Member

3 Posts

Posted - 2013-03-19 : 04:35:09

missing days from TableA (always with StartDate and EndDate parameters)?

_________________
runescape gold|wow gold|RS Gold|buy Runescape Gold

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-19 : 05:56:02

[code]CREATE PROCEDURE USP_Insert_MissingRecords(
@P_StartDate DATETIME,
@P_EndDate DATETIME,
@P_RowsProcessed INT OUT)
AS
BEGIN
;WITH Dates(sd)
AS (
SELECT @P_StartDate
UNION ALL
SELECT DATEADD(D, 1, sd) FROM Dates WHERE sd <= @P_EndDate
)
INSERT INTO TableA(DateRif, Field1)
OUTPUT inserted.DateRif INTO #temptab
SELECT sd, 'Missing'
FROM Dates
WHERE sd NOT IN (SELECT DateRif FROM TABLEA);

SELECT * FROM #tempTab
UNION
SELECT @P_StartDate --to display start date along with missing days
UNION
SELECT @P_EndDate --to display end date along with missing days
END
[/code]

--
Chandu

sqlsogmen
Starting Member

3 Posts

Posted - 2013-03-19 : 22:30:21

It seems that one day is missing.

_________________________
http://www.meinwowgold.de

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-20 : 00:54:45