Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Table Structure, StartDate and EndDate Expand / Collapse
Author
Message
Posted Tuesday, September 21, 2010 1:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 4:47 AM
Points: 9, Visits: 66
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,
Post #990023
Posted Tuesday, September 21, 2010 4:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:44 PM
Points: 12,904, Visits: 31,978
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #990099
Posted Tuesday, September 21, 2010 4:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 27, 2012 3:11 AM
Points: 6, Visits: 120
Dear Lowell,

this an example from the current scenario:
offer 1:
Tariff_id Prefix Destination rate startdate endDate
127 3360042 France-Bouygues Mobile 0.xxx 2010-05-22 2079-01-01

offer 2:
I got new rate:
Tariff_id Prefix Destination rate startdate endDate
127 3360042 France-Bouygues Mobile 0.xxx 2010-05-22 2010-06-03
127 3360042 France-Bouygues Mobile 0.yyy 2010-06-04 2079-01-01

offer 3: date 2010-06-15
Code removed from the offer for this destination
Tariff_id Prefix Destination rate startdate endDate
127 3360042 France-Bouygues Mobile 0.xxx 2010-05-22 2010-06-03
127 3360042 France-Bouygues Mobile 0.yyy 2010-06-04 2010-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?


Post #990118
Posted Tuesday, September 21, 2010 6:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:20 PM
Points: 11,219, Visits: 12,976
I agree with Lowell on putting the Discounts in a separate table.

How are you importing the data from the spreadsheet?




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #990180
Posted Tuesday, September 21, 2010 6:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 4:47 AM
Points: 9, Visits: 66
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


Post #990187
Posted Tuesday, September 21, 2010 6:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:20 PM
Points: 11,219, Visits: 12,976
I recommend reading this article 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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #990199
Posted Tuesday, September 21, 2010 7:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:44 PM
Points: 12,904, Visits: 31,978
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #990218
Posted Tuesday, September 21, 2010 7:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 27, 2012 3:11 AM
Points: 6, Visits: 120
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 ?
Post #990274
Posted Tuesday, September 21, 2010 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 27, 2012 3:11 AM
Points: 6, Visits: 120
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'


Post #990308
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse