SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Perform INSERT between range dates if not exists
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 03/14/2013 :  04:52:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 03/14/2013 :  05:53:28  Show Profile  Visit webfred's Homepage  Reply with Quote

-- 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



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

Edited by - webfred on 03/14/2013 05:56:11
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/14/2013 :  05:57:44  Show Profile  Reply with Quote
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

Edited by - bandi on 03/14/2013 05:59:21
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 03/14/2013 :  06:07:30  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/14/2013 :  06:30:26  Show Profile  Reply with Quote
Thank you Webfred

--
Chandu
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 03/14/2013 :  06:45:44  Show Profile  Reply with Quote
Thank you all; I'll use the Bandi solution for now.

Luigi
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/14/2013 :  07:06:33  Show Profile  Reply with Quote
quote:
Originally posted by Ciupaz

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


welcome

--
Chandu
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 03/14/2013 :  07:30:33  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/14/2013 :  07:46:42  Show Profile  Reply with Quote
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

Edited by - bandi on 03/14/2013 07:47:25
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 03/14/2013 :  09:08:03  Show Profile  Reply with Quote
Sorry Bandy, my fault, I've missed a filter in the NOT IN statement.

L
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 03/14/2013 :  10:19:11  Show Profile  Reply with Quote
A little adding.
How can I write a query that returns me the missing days from TableA (always with StartDate and EndDate parameters)?

L
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/18/2013 :  10:14:08  Show Profile  Reply with Quote
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

Edited by - bandi on 03/18/2013 10:14:34
Go to Top of Page

hasondea
Starting Member

USA
3 Posts

Posted - 03/19/2013 :  04:35:09  Show Profile  Reply with Quote
missing days from TableA (always with StartDate and EndDate parameters)?

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

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/19/2013 :  05:56:02  Show Profile  Reply with Quote
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


--
Chandu

Edited by - bandi on 03/19/2013 05:56:54
Go to Top of Page

sqlsogmen
Starting Member

3 Posts

Posted - 03/19/2013 :  22:30:21  Show Profile  Reply with Quote
It seems that one day is missing.


_________________________
http://www.meinwowgold.de

Edited by - sqlsogmen on 03/19/2013 22:35:47
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/20/2013 :  00:54:45  Show Profile  Reply with Quote
quote:
Originally posted by sqlsogmen

It seems that one day is missing.
_______________________
http://www.meinwowgold.de


How can you say one day is misssing... Explore the issue.
It will be better to get the feasible solution

--
Chandu
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 03/20/2013 :  03:21:12  Show Profile  Reply with Quote
Thank you Bandi, it works correctly.

Luigi
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/20/2013 :  03:23:00  Show Profile  Reply with Quote
quote:
Originally posted by Ciupaz

Thank you Bandi, it works correctly.
Luigi


welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000