Reputation: 21
Need a stored procedure to find missing gaps and insert rows
I have a small dev challenge here. I have the following table CURR_RATES in SQL Server:
SELECT *
FROM (VALUES
(1001300, 'USD', 13, '8/1/2013', 1.31),
(1001301, 'USD', 13, '8/2/2013', 1.32),
(1001302, 'USD', 13, '8/5/2013', 1.33),
(1001303, 'USD', 13, '8/6/2013', 1.34),
(1001304, 'USD', 13, '8/7/2013', 1.35),
(1001305, 'USD', 13, '8/8/2014', 1.31),
(1001306, 'USD', 13, '8/9/2013', 1.32),
(1001307, 'USD', 13, '8/12/2013', 1.33),
(1001308, 'USD', 13, '8/13/2013', 1.34),
(1001309, 'USD', 13, '8/14/2013', 1.35),
(1001310, 'USD', 13, '8/15/2013', 1.36),
(1001311, 'USD', 13, '8/16/2013', 1.37),
(1001312, 'USD', 13, '8/19/2013', 1.38),
(1001313, 'USD', 13, '8/20/2013', 1.38),
(1001314, 'USD', 13, '8/21/2013', 1.37),
(1001315, 'USD', 13, '8/22/2013', 1.36),
(1001316, 'USD', 13, '8/23/2013', 1.35),
(1001317, 'USD', 13, '8/26/2013', 1.34),
(1001318, 'USD', 13, '8/27/2013', 1.33),
(1001319, 'USD', 13, '8/28/2013', 1.31),
(1001320, 'USD', 13, '8/29/2013', 1.32),
(1001321, 'USD', 13, '8/30/2013', 1.33)) as t(DATA_ID, CURR, CODE, [DATE], [RATE])
And using a range, for example 2013-08-01 to 2013-08-31, i need to find which days are = to saturday and sunday (weekends). I can easily do this with the datename(dw, CURR_RATES.DATE) function.
datename(dw, CURR_RATES.DATE) as weekday from CURR_RATES
DATA_ID CURR CODE DATE RATE Weekday
----------- ---- ----------- --------- ----- -----------
1001300 USD 13 8/1/2013 1.31 Thursday
1001301 USD 13 8/2/2013 1.32 Friday
1001302 USD 13 8/5/2013 1.33 Monday
1001303 USD 13 8/6/2013 1.34 Tuesday
1001304 USD 13 8/7/2013 1.35 Wednesday
1001305 USD 13 8/8/2014 1.31 Friday
1001306 USD 13 8/9/2013 1.32 Friday
1001307 USD 13 8/12/2013 1.33 Monday
1001308 USD 13 8/13/2013 1.34 Tuesday
1001309 USD 13 8/14/2013 1.35 Wednesday
1001310 USD 13 8/15/2013 1.36 Thursday
1001311 USD 13 8/16/2013 1.37 Friday
1001312 USD 13 8/19/2013 1.38 Monday
1001313 USD 13 8/20/2013 1.38 Tuesday
1001314 USD 13 8/21/2013 1.37 Wednesday
1001315 USD 13 8/22/2013 1.36 Thursday
1001316 USD 13 8/23/2013 1.35 Friday
1001317 USD 13 8/26/2013 1.34 Monday
1001318 USD 13 8/27/2013 1.33 Tuesday
1001319 USD 13 8/28/2013 1.31 Wednesday
1001320 USD 13 8/29/2013 1.32 Thursday
1001321 USD 13 8/30/2013 1.33 Friday
What I need to do is write a stored procedure to find the missing Saturdays and Sundays and insert rows for them setting the value of column RATE equal to the previous Friday. The table can contain data from other months so i need to be able to specify a range. Also, the DATA_ID column needs to include a value for the new added rows. For The first inserted row the value of the DATA_ID column should be the value of the last row DATA_ID +1 - The second inserted row should be the DATA_ID of the newly inserted row +1, and so on....so the S.P. needs to figure out the DATA_ID of the last row in the table as well.
Hope this is clear enough - will appreciate any feedback or ideas on how to implement this.
Thank you very much in advance.
R00ty
Upvotes: 1
Views: 652
Answers (1)
Reputation: 1715
You may need to tinker with the case in the dbo.BuildDateRange to adjust for how your server is configured (SET DATEFIRST)
a function to build a table of all dates between a date range:-
create function dbo.BuildDataRange
(
@StartDate date,
@EndDate date
)
returns @returntable table
(
[Date] date,
PreviousFriday date
)
as
begin
while @StartDate<=@EndDate begin
insert into @returntable ([Date],PreviousFriday)
values (
@StartDate,
dateadd(d,
case datepart(dw,@StartDate)
when 1 then -2
when 2 then -3
when 3 then -4
when 4 then -5
when 5 then -6
when 6 then -7
when 7 then -1
end,@StartDate)
)
set @StartDate=DATEADD(day,1,@StartDate)
end
return
end
go
a table of test data:-
create table #curr_rates (
data_id int,
curr char(3),
code int,
[date] datetime,
rate decimal(10,5)
)
go
with some data in it:-
insert into #curr_rates values
(1001299,'usd',13,'2013-07-31',1.25),
(1001300,'usd',13,'2013-08-01',1.31),
(1001301,'usd',13,'2013-08-02',1.32),
(1001302,'usd',13,'2013-08-05',1.33),
(1001303,'usd',13,'2013-08-06',1.34),
(1001304,'usd',13,'2013-08-07',1.35),
(1001305,'usd',13,'2013-08-08',1.31),
(1001306,'usd',13,'2013-08-09',1.32),
(1001307,'usd',13,'2013-08-12',1.33),
(1001308,'usd',13,'2013-08-13',1.34),
(1001309,'usd',13,'2013-08-14',1.35),
(1001310,'usd',13,'2013-08-15',1.36),
(1001311,'usd',13,'2013-08-16',1.37),
(1001312,'usd',13,'2013-08-19',1.38),
(1001313,'usd',13,'2013-08-20',1.38),
(1001314,'usd',13,'2013-08-21',1.37),
(1001315,'usd',13,'2013-08-22',1.36),
(1001316,'usd',13,'2013-08-23',1.35),
(1001317,'usd',13,'2013-08-26',1.34),
(1001318,'usd',13,'2013-08-27',1.33),
(1001319,'usd',13,'2013-08-28',1.31),
(1001320,'usd',13,'2013-08-29',1.32),
(1001321,'usd',13,'2013-08-30',1.33)
go
an SP to insert missing data (will fill any gaps with the previous Fridays rate, so make sure only weekends are missing...
create procedure dbo.InsertWeekends
(
@from date,
@to date
) as
begin
declare @watermark int
select @watermark=max(data_id)
from #curr_rates
insert into #curr_rates
select @watermark+ROW_NUMBER() over(order by dr.[date]) as data_id,
cr.curr,
cr.code,
dr.[date],
cr.rate
from dbo.BuildDataRange(@from,@to) dr
join #curr_rates cr on cr.date=dr.PreviousFriday
where not exists(
select *
from #curr_rates csq
where csq.[date]=dr.[Date]
and csq.curr=cr.curr
and csq.code=cr.code
)
end
go
usage:-
exec dbo.InsertWeekends '2013-08-01', '2013-08-31'
go
so that this:-
select *
from #curr_rates
order by [date]
go
produces:-
Upvotes: 1
Related Questions
- SQL Server Filling in Missing Record
- Insert dummy rows to fill missing values into a SQL Table
- SQL - Insert rows on missing value with previous row information
- TSQL - Fill missing records
- TSQL: How do i detect and insert missing records
- How to get missing values in gaps of Table Data
- TSQL: Inserting missing records into table
- how to fill up the gaps in Rows in a table
- Filling in missing data
- Create a query to fill the gaps in a table due to bad data