Sequential Date Ranges in account

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)

Sequential Date Ranges in account

Author

Topic

Nikkap
Starting Member

8 Posts

Posted - 2009-02-17 : 07:59:39

I use a database like bank account and have a problem to make data ranges

-- Table contains historical data for rates changes
CREATE TABLE #rates
(
id int,
changedate smalldatetime,
rate decimal(8,2)
)

INSERT INTO #rates
SELECT 1, '20070101', 4.50 UNION ALL
SELECT 2, '20070315', 4.60 UNION ALL
SELECT 3, '20070919', 4.85 UNION ALL
SELECT 4, '20080520', 4.80

--this means that from 20070101 upto 20070314 effective rate is 4.50

--Table contains account transactions
CREATE TABLE #transactions
(
id int,
transdate smalldatetime,
debit decimal(18,2),
credit decimal(18,2)
)
INSERT INTO #transactions
SELECT 1, '20070116', 10000, 0 UNION ALL
SELECT 2, '20070320', 25000, 0 UNION ALL
SELECT 3, '20070915', 0, 5000

-- Clean up
DROP TABLE #rates
DROP TABLE #transactions

-- Expected output
-------------------------------------------------------------
Id FromDate ToDate Debit Credit Balance Rate
-------------------------------------------------------------
1 2007-01-16 2007-03-14 10000 0 10000 4.50
2 2007-03-15 2007-03-19 0 0 10000 4.60
3 2007-03-20 2007-09-14 25000 0 35000 4.60
4 2007-09-15 2008-09-18 0 5000 30000 4.60
5 2007-09-19 2008-05-19 0 0 30000 4.85
6 2008-05-20 uptodate 0 0 30000 4.80

So my problem is the date ranges. I need one record when the balance or effective rate changes . After it, will be easier to calculate with the datediff the days * balance *rate/360000 to find interest.

Thank You in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 10:06:39

[code]DECLARE @Rates TABLE
(
ID INT,
ChangeDate SMALLDATETIME,
Rate DECIMAL(8, 2)
)

INSERT @Rates
SELECT 1, '20070101', 4.50 UNION ALL
SELECT 2, '20070315', 4.60 UNION ALL
SELECT 3, '20070919', 4.85 UNION ALL
SELECT 4, '20080520', 4.80

DECLARE @Transactions TABLE
(
ID INT,
TransDate SMALLDATETIME,
Debit DECIMAL(18, 2),
Credit DECIMAL(18, 2)
)

INSERT @Transactions
SELECT 1, '20070116', 10000, 0 UNION ALL
SELECT 2, '20070320', 25000, 0 UNION ALL
SELECT 3, '20070915', 0, 5000

DECLARE @Stage TABLE
(
FromDate DATETIME NOT NULL,
ToDate DATETIME NOT NULL,
Debit DECIMAL(18, 2) DEFAULT 0,
Credit DECIMAL(18, 2) DEFAULT 0,
Balance DECIMAL(18, 2) DEFAULT 0,
Rate DECIMAL(8, 2) DEFAULT 0
)

INSERT @Stage
(
FromDate,
ToDate
)
SELECT MIN(ChangeDate),
CASE
WHEN MAX(ChangeDate) - 1 < MIN(ChangeDate) THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
ELSE MAX(ChangeDate) - 1
END
FROM (
SELECT d.ChangeDate,
ROW_NUMBER() OVER (PARTITION BY w.d ORDER BY d.ChangeDate) - w.d AS recID
FROM (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, ChangeDate), 0) AS ChangeDate
FROM @Rates

UNION

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, TransDate), 0)
FROM @Transactions
) AS d
CROSS JOIN (
SELECT 0 AS d

UNION ALL

SELECT 1
) AS w
WHERE ChangeDate >= (SELECT MIN(DATEADD(DAY, DATEDIFF(DAY, 0, TransDate), 0)) FROM @Transactions)
) AS d
WHERE recID > 0
GROUP BY recID

UPDATE s
SET s.Debit = t.Debit,
s.Credit = t.Credit
FROM @Stage AS s
INNER JOIN (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, TransDate), 0) AS TransDate,
SUM(Debit) AS Debit,
SUM(Credit) AS Credit
FROM @Transactions
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, TransDate), 0)
) AS t ON t.TransDate = s.FromDate

UPDATE s
SET Balance = (SELECT SUM(x.Debit - x.Credit) FROM @Stage AS x WHERE x.FromDate <= s.FromDate),
Rate = (SELECT TOP 1 x.Rate FROM @Rates AS x WHERE x.ChangeDate <= s.FromDate ORDER BY x.ChangeDate DESC)
FROM @Stage AS s

SELECT *
FROM @Stage[/code]

E 12°55'05.63"
N 56°04'39.26"

Nikkap
Starting Member

8 Posts

Posted - 2009-02-17 : 10:58:17

Breathless!!

What can i say Peso?
Thank you , Thank you very much !!

I did't test it to my code ...but the results seems to be exact what i wanted !!

Peso Thank u again !!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 11:17:56

The speed will be moderate to slow, depending on how many records there are.
If you have many records, I believe a CURSOR based approach will be faster in this particular case, due to the number of "triangular joins".

E 12°55'05.63"
N 56°04'39.26"

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2009-02-18 : 07:45:03

Your design is wrong and you will always be writing slow complex SQL until you fix it. Let me use a history table for price changes. The fact is that a price had duration. This is the nature of time and other continuums. So a basic history table looks like this in SQL/PSM

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL -- industry standard
REFERENCES Inventory(upc),
price_prev_date DATE NOT NULL,
price_start_date DATE DEFAULT CURRENT_DATE NOT NULL,
price_end_date DATE, -- null means current price
CHECK(price_start_date < price_end_date),
CHECK (price_start_date = price_prev_date + 1 DAY), -- prevents gaps
PRIMARY KEY (upc, price_start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);

You can enforce the "one null per item" with a trigger. You use a BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.price_start_date
AND COALESCE (price_end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE price_end_date IS NULL;

Now your only problem is to write a stored procedure that will update the table and insert a new row. You can do this with a single MERGE statement, or with a short block of SQL/PSM code:

CREATE PROCEDURE UpdateItemPrice
(IN in_upc CHAR(13), IN new_item_price DECIMAL (12,4))
LANGUAGE SQL
BEGIN ATOMIC
UPDATE PriceHistory
SET price_end_date = CURRENT_DATE
WHERE upc = in_upc;
INSERT INTO PriceHistory (upc, price_prev_date, price_start_date, price_end_date, item_price)
VALUES (in_upc, CURRENT_DATE, CURRENT_DATE + INTERVAL '1' DAY, NULL, new_item_price);
END;

This will make the price change go into effect tomorrow.

There is a common kludge to repair a failure to design a history table properly that you can put in a VIEW if you are not able to set things right. Assume that every day we take a short inventory and put it in a journal. The journal is a clip board paper form that has one line per item per day, perhaps with gaps in the data. We want to get this into the proper format, namely periods shown with a (start_date, end_date) pair for durations where each item had the same quantity on hand. This is due to Alejandro Mesa

CREATE TABLE InventoryJournal
(journal_date DATETIME NOT NULL,
item_id CHAR(2) NOT NULL,
PRIMARY KEY (journal_date, item_id),
onhand_qty INTEGER NOT NULL);

WITH ItemGroups
AS
(SELECT journal_date, item_id, onhand_qty,
ROW_NUMBER() OVER(ORDER BY item_id, journal_date, onhand_qty)
- ROW_NUMBER() OVER(PARTITION BY item_id, onhand_qty
ORDER BY journal_date) AS item_grp_nbr
FROM Journal),

QtyByDateRanges
AS
(SELECT MIN(journal_date) AS start_date,
MAX(journal_date) AS end_date,
item_id, onhand_qty
FROM ItemGroups
GROUP BY item_id, onhand_qty, item_grp_nbr)

SELECT start_date, end_date, item_id, onhand_qty
FROM QtyByDateRanges;

This might be easier to see with some data and intermediate steps

INSERT INTO InventoryJournal
VALUES('2007-01-01', 'AA', 100),('2007-01-01', 'BB', 200),
('2007-01-02', 'AA', 100),('2007-01-02', 'BB', 200),
('2007-01-03', 'AA', 100),('2007-01-03', 'BB', 300);

start_date end_date item_id onhand_qty
==========================================
'2007-01-01' '2007-01-03' 'AA' 100
'2007-01-01' '2007-01-02' 'BB' 200
'2007-01-03' '2007-01-03' 'BB' 300

Now, download the Rick Snodgrass book on Temporal Queries in SQL from the University of Arizona website (it is free).

--CELKO--
Joe Celko, SQL Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources