September 21, 2010 at 1:53 am
hi,
I have this table Structure for international dialing Codes and Tariffs:
CREATE TABLE [dbo].[Tariffs](
[id] [int] IDENTITY(1,1),
[Tariff_id] [int],
[Country_ID] [int],
[prefix] [varchar](20),
[destination_name] [varchar](100),
[rate] [numeric](9, 5),
[StartDate] [datetime],
[EndDate] [datetime] NULL
) ON [PRIMARY]
I'm using ASP.net Application to import excel offers to this table , Each offer contain about 10000 row, so it is a large table (about 3 millions row)
what is the faster scenario in SQL Server 2008 to create a stored-procedure or trigger to change the previous endDate for same tariff same prefix same destination and new rate on insert a new row,
and how to undo saving offer of 10000 rows and get back the table and update records to the previous state
Thank you,
September 21, 2010 at 4:23 am
interesting wissam;
the excel spreadsheet you receive, does the start and end date apply only to the short term offer? so for one month some rate is slightly cheaper, but the table Tariffs contains the regular/default rates?
in that case, i would consider a separate table for the Discounts, and join the two tables together based on the EndDate, using the DiscountRate if todays date is still valid; i don't think you want to update the master table with the rates, and then try to back out those changes after the end period.
Lowell
September 21, 2010 at 4:50 am
Dear Lowell,
this an example from the current scenario:
offer 1:
Tariff_idPrefixDestination rate startdate endDate
1273360042France-Bouygues Mobile0.xxx2010-05-222079-01-01
offer 2:
I got new rate:
Tariff_idPrefixDestination ratestartdate endDate
1273360042France-Bouygues Mobile0.xxx2010-05-222010-06-03
1273360042France-Bouygues Mobile0.yyy2010-06-042079-01-01
offer 3: date 2010-06-15
Code removed from the offer for this destination
Tariff_idPrefixDestination ratestartdate endDate
1273360042France-Bouygues Mobile0.xxx2010-05-222010-06-03
1273360042France-Bouygues Mobile0.yyy2010-06-042010-06-14
Note:
every destination or prefix not exist or changed on the next offer must be ended (even if one letter changed in the destination field)
always the increase rate have a different start date from the decrease
do I have to change my analyses?
Do I need a history table? if yes, and if the enddate is in the future, the current rate will be in history table!
what do you suggest?
it is very complicated right? 🙂
September 21, 2010 at 6:21 am
I agree with Lowell on putting the Discounts in a separate table.
How are you importing the data from the spreadsheet?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2010 at 6:30 am
the import take a long time since each time the number of imported rows is about 10000 and the table have many indexes and I have to check the previous rate on each insert ( I can't use the bulk insert).
I have a scenario that I can get the rate without using EndDate
in this case I can remove the EndDate from the table
Do you recommend this way ?
ALTER FUNCTION [dbo].[GetRate] (@prefix varchar(20),@Date datetime,@Tariff_id int)
RETURNS decimal(18,4)
AS
BEGIN
declare @rate decimal(18,4)
select @rate=rate from Tariffs t1 where
prefix=@prefix and
Tariff_id=@Tariff_id and
startDate =
(select max(startDate) from Tariffs t2
where t1.Tariff_id =t2.Tariff_id and t1.prefix=t2.prefix and t2.StartDate <= @Date )
-- code removed -- @Date >= startDate and @Date < dateadd(dd,1,endDate)
order by prefix desc
return @rate
END
September 21, 2010 at 6:48 am
I recommend reading this article[/url] by Andy Leonard about incremental loads using SSIS. You may not be using SSIS, but I think you can use the same pattern.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2010 at 7:01 am
ok, this example, based on your provided data seems to work for me;
i'm assuming the discount rates are added to the same table...but i hope it is obvious that a join to a discount table would give the exact same results.
I'm making the following assumptions; i thought your example provided TWO discount rates for the same period...so i assume the latest rate is the rate to use...I'm getting all the rates that match the binding time period, and using row_number to order them into the set order i think you are expecting.
does this example represent what you are trying, or did i misread the requirement?
declare @Tariffs TABLE(
[id] [int] IDENTITY(1,1),
[Tariff_id] [int],
[Country_ID] [int],
[prefix] [varchar](20),
[destination_name] [varchar](100),
[rate] [numeric](9, 5),
[StartDate] [datetime],
[EndDate] [datetime] NULL
)
INSERT INTO @Tariffs(Tariff_id,Prefix,destination_name,rate,startdate,endDate)
--the default rate due to the 2079 end date
SELECT '127','3360042','France-Bouygues Mobile','0.15','2010-05-22','2079-01-01' UNION ALL
--one specific discount rate.
SELECT '127','3360042','France-Bouygues Mobile','0.14','2010-05-22','2010-06-03' UNION ALL
--the rate AFTER that new discount
SELECT '127','3360042','France-Bouygues Mobile','0.15','2010-06-04','2079-01-01' UNION ALL
--the rate was update to an even lower discoun t rate
SELECT '127','3360042','France-Bouygues Mobile','0.12','2010-05-22','2010-06-03' UNION ALL
--the rate after the discount is over.
SELECT '127','3360042','France-Bouygues Mobile','0.15','2010-06-04','2010-06-14'
--my assumption is all items get inserted, and you get the LATEST rate that is still valid
--see everything
SELECT ROW_NUMBER() OVER( PARTITION BY Tariff_id, Prefix, destination_name ORDER BY CASE WHEN ENDDATE = '2079-01-01' then 2 ELSE 1 END ,enddate DESC,id desc ) AS RW,
* FROM @Tariffs
where startdate <= '2010-06-01' and enddate >= '2010-06-01'
SELECT * FROM
(
SELECT ROW_NUMBER() OVER( PARTITION BY Tariff_id, Prefix, destination_name ORDER BY CASE WHEN ENDDATE = '2079-01-01' then 2 ELSE 1 END ,enddate DESC,id desc ) AS RW,
* FROM @Tariffs
where startdate <= '2010-06-01' and enddate >= '2010-06-01'
) x
WHERE RW = 1
Lowell
September 21, 2010 at 7:38 am
This is very helpful,
I want to clarify that the endDate does not exist in the Excel offer, I'm using a stored procedure that modify the endDate for the previous offer on one day before the new startdate on insert and I think that this is not logic (I need you help in this) for that I'm asking if I can remove this field from my table.
the query will be:
SELECT @rate=rate FROM
(
SELECT ROW_NUMBER() OVER( PARTITION BY Tariff_id, Prefix, Description ORDER BY StartDate DESC,id desc ) AS RW,
* FROM Tariffs t1
where startDate <= '2010-09-09'
and tariff_id= 1 and prefix ='3360042'
) x
WHERE RW = 1
and since the prefixes can
and Please can you give me an example how to insert the data in 2 tables ?
September 21, 2010 at 8:02 am
due to the Conflict of ideas, I just notice that I need the endDate to be used for removed destination or prefix
Example:
INSERT INTO @Tariffs(Tariff_id,Prefix,destination_name,rate,startdate)
SELECT '127','3360042','France-Bouygues Mobile','0.15','2010-05-22' UNION ALL
--Rate changes
SELECT '127','3360042','France-Bouygues Mobile','0.14','2010-05-27' UNION ALL
--Code changes (3360042 removed and must be ended by using update statement)
SELECT '127','336004','France-Bouygues Mobile','0.14','2010-06-04' UNION ALL
--Destination name changes (France-Bouygues Mobile removed and must be ended by using update statement)
SELECT '127','336004','France-Mobile','0.17','2010-06-18'
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply