Author |
Topic |
Ciupaz
Posting Yak Master
232 Posts |
Posted - 2013-03-14 : 04:52:20
|
Hello all,I have a table with this structure: TableADateRif - Field12013-02-01 00:00:00 AAAA2013-02-02 00:00:00 BBBB2013-02-04 00:00:00 CCCC2013-02-06 00:00:00 DDDD2013-02-08 00:00:00 EEEE----Now I need to write a stored procedure that has 2 parameters, DateStart and DateEndand 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:002013-02-05 00:00:002013-02-07 00:00:002013-02-09 00:00:002013-02-10 00:00:002013-02-11 00:00:00and 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 testdatadeclare @TableA table (DateRif datetime, Field1 varchar(30))insert @TableAselect '20130201', 'AAAA' union allselect '20130202', 'BBBB' union allselect '20130204', 'CCCC' union allselect '20130206', 'DDDD' union allselect '20130208', 'EEEE'-- show testdataselect * from @TableA-- begin solutiondeclare @DateStart datetimedeclare @DateEnd datetimeset @DateStart = '20130201'set @DateEnd = '20130211'insert @TableA(DateRif, Field1)select dt.DateValue, nullfrom(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)dtleft join @TableA a on a.DateRif = dt.DateValuewhere a.DateRif is null-- show resultselect * from @TableAorder 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
|
GOCREATE PROCEDURE USP_Insert_MissingRecords( @P_StartDate DATETIME, @P_EndDate DATETIME, @P_RowsProcessed INT OUT)ASBEGIN ;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);ENDDECLARE @P_RowsProcessed INTEXEC USP_Insert_MissingRecords '2013-02-01 00:00:00', '2013-02-11 00:00:00', @P_RowsProcessed OUTSELECT @P_RowsProcessedSELECT * 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 GOCREATE PROCEDURE USP_Insert_MissingRecords( @P_StartDate DATETIME, @P_EndDate DATETIME, @P_RowsProcessed INT OUT)ASBEGIN ;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);ENDDECLARE @P_RowsProcessed INTEXEC USP_Insert_MissingRecords '2013-02-01 00:00:00', '2013-02-11 00:00:00', @P_RowsProcessed OUTSELECT @P_RowsProcessedSELECT * 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 |
|
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)ASBEGIN ;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 daysEND[/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
|
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
232 Posts |
Posted - 2013-03-20 : 03:21:12
|
Thank you Bandi, it works correctly. Luigi |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-20 : 03:23:00
|
quote: Originally posted by Ciupaz Thank you Bandi, it works correctly. Luigi
welcome--Chandu |
|
|
|