Start Date Comparisons in Event Based System

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Start Date Comparisons in Event Based System

Author

Topic

IRBradles
Starting Member

5 Posts

Posted - 2007-10-30 : 20:29:21

Am working with a sales database that stores events and states (At Timestamp "x", The Meter "M" was read and the value was "V") (At Timestamp "X" the Amount we charged on Tarif "T" was increased to "R")... Am attempting to work with Tarifs (Charge per unit sold), but the rate of a Tarif is indexed to inflation and a particular Meter can change between Tarifs (Including Changing to the same Tarif in the case of a renewal).

Data is Stored as;

Tarif Table
TARIF_ID, Rate Per Unit [Rate], Date Rate is to be applied From [Start_Date]

Tarif_Meter Table
METER_ID, TARIF_ID, Date Meter was Placed onto Tarif [TStart_Date]

I am attempting to generate a Table reporting;
METER_ID, TARIF_ID, Rate, Date to apply appropriate Rate From (TStart Date OR Start Date).

My difficulty at present is getting SQL to return a row whenever a Tarif Rate has changed OR A Meter has switched Tarifs, and then to pick up the appropriate rate (The Tarif at the time if the change). Have Tried Unions, Subqueries and Queries from Queries, each falls over at one point or another.

Tarif Table Sample
T001, 01,01/1900, $0 <----- "Day 0" Initilization (Database Requires 1-N Entries per Tarif)
T002, 01/01/1900, $0 <----- "Day 0" Initilization (Database Requires 1-N Entries per Tarif)
T001, 01/01/2000, $1.34
T001, 01/01/2004, $1.46
T001, 01,01/2005, $1.75
T002, 01/01/2001, $0.98
T002, 01/01/2006, $1.11
T002, 01/01/2008, $1.13

Tarif_Meter Table
M001, T001, 01/06/2004
M001, T002, 01/06/2006
M001, T001, 01/07/2006
M002, T002, 01/07/2006
M002, T001, 01/07/2007
M002, T001, 01/07/2008

The last entry can be ignored as no change is effected, but I'm not worried if it is reported, will just slow performance fractionally and this isn't a big enought system to worry about that level of code effeciency.

IRBradles
Starting Member

5 Posts

Posted - 2007-10-31 : 00:03:30

Managed to figure out the Unions and Subqueries for this. If anyone else has a more subtle or effecient answer I'll gladly replace this mess...

1st SELECT Reports all Rates where any Tarif Changed
2nd SELECT Reports all Rates where any Meter Changed Tarif Type


SELECT TM.METER_ID, TM.Tarif, T.Start_Date AS [Tarif Start Date], T.Rate
FROM Tarifs AS T, Tarif_Meter AS TM
WHERE TM.Tarif=T.Tarif
  AND TM.Start_Date = (SELECT MAX(TM2.Start_Date) 
                         FROM Tarifs T2, Tarif_Meter TM2 
                        WHERE TM2.Start_Date <= T.Start_Date AND TM2.Meter_ID=TM.Meter_ID)

UNION 

SELECT TM.METER_ID, TM.Tarif, TM.Start_Date AS [Tarif Start Date], T.Rate
FROM Tarifs AS T, Tarif_Meter AS TM
WHERE TM.Tarif=T.Tarif
  AND T.Start_Date <= TM.Start_Date
  AND T.Start_Date = (SELECT MAX(T2.Start_Date) 
                        FROM Tarifs T2, Tarif_Meter TM2 
                       WHERE TM.Tarif=TM2.Tarif AND T.Tarif=T2.Tarif AND T2.Start_Date <= TM.Start_Date)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources