| Author |
 Topic  |
|
|
Ciupaz
Posting Yak Master
Italy
176 Posts |
 Posted - 03/14/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 03/14/2013 : 05:53:28
|
-- 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 |
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/14/2013 : 05:57:44
|
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 |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 03/14/2013 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/14/2013 : 06:30:26
|
Thank you Webfred
--
Chandu |
|
|
|
Ciupaz
Posting Yak Master
Italy
176 Posts |
Posted - 03/14/2013 : 06:45:44
|
Thank you all; I'll use the Bandi solution for now.
Luigi |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/14/2013 : 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
Italy
176 Posts |
Posted - 03/14/2013 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/14/2013 : 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 |
Edited by - bandi on 03/14/2013 07:47:25 |
|
|
|
Ciupaz
Posting Yak Master
Italy
176 Posts |
Posted - 03/14/2013 : 09:08:03
|
Sorry Bandy, my fault, I've missed a filter in the NOT IN statement.
L |
|
|
|
Ciupaz
Posting Yak Master
Italy
176 Posts |
Posted - 03/14/2013 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/18/2013 : 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 |
Edited by - bandi on 03/18/2013 10:14:34 |
|
|
|
hasondea
Starting Member
USA
3 Posts |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/19/2013 : 05:56:02
|
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 |
|
|
|
sqlsogmen
Starting Member
2 Posts |
Posted - 03/19/2013 : 22:30:21
|
It seems that one day is missing.
_________________________
http://www.meinwowgold.de |
Edited by - sqlsogmen on 03/19/2013 22:35:47 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/20/2013 : 00:54:45
|
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 |
|
|
|
Ciupaz
Posting Yak Master
Italy
176 Posts |
Posted - 03/20/2013 : 03:21:12
|
Thank you Bandi, it works correctly.
Luigi |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/20/2013 : 03:23:00
|
quote:
Originally posted by Ciupaz
Thank you Bandi, it works correctly.
Luigi
welcome
--
Chandu |
|
|
| |
Topic |
|
Perform INSERT between range dates if not exists
