Start -> From-Till date conversion

Danny (danny.bosschaerts@scarlet.be) writes:
> Have a table containg some data, one field being the 'StartDate'
> When another registration is done, it receives again a 'StartDate'.
>
> For some application I need a From-Till date instead of only a
> StartDate.
>
> There is no uninterrupted sequential field to get the 'next record':
> table = Building int, Unit int, Owner int, StartDate date
>
> So the shorthand for this would be:
>
> Select StartDate As FromDate, datediff(d,-1,isnull([StartDate of next
> record],[some big date])) as TillDate from table where
> Building=@BuildingID and Unit=@UnitID
> (the [some big date] is a dummy for the last registration where there
> is no [next record])
>
> I managed to do it, but the query is so big/ugly I doubt it is the
> only/correct way.
> Aother way might be to build in a trigger and update a 'TillDate'
> field after every change of 'StartDate', but I think I would need the
> same logic.

It's not entirely easy to understand your post, but something like:

SELECT a.StartDate, b.StartDate
FROM tbl a
OUTER APPLY (SELECT TOP 1
FROM tbl b
WHERE b.Building = a.Building
AND b.Unit = a.Unit
AND b.StartDate > a.StartDate
ORDER BY b.StartDate) AS b
WHERE a.Building = @BuildingID
AND a.Unit = @UnitID

OUTER APPLY is like an outer join, but you can refer to the outer table
in the derived table. If there is no later date, you will get NULL
back.


--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Start -> From-Till date conversion 

Have a table containg some data, one field [sic: columns are not
fields] being the 'registration_start_date' When another registration
is done, it receives again a 'registration_start_date'.

For some application I need a From-Till date instead of only a
"registration_start_date".

There is no uninterrupted sequential field [sic] to get the 'next
record [sic: rows are not records]':

Please post real DDL; that is minimal netiquete in SQL forums and
groups. The provlem is that your DDL is wrong:

CREATE TABLE Registrations
(building_nbr INTEGER NOT NULL,
unit_nbr INTEGER NOT NULL,
owner_duns CHAR(9) NOT NULL,
registration_start_date DATE NOT NULL,
registration_end_date DATE);

A NULL in registration_end_date means it is still current. You will
use COALESCE (registration_end_date, CURRENT_DATE) in queries. This is
the standard design pattern for history tables. It is based on the
fact that time is a continuum.

>> (the [some big date] is a dummy for the last registration where there
is 00000 no [next record [sic]]) <<

NO! That was a 1950's COBOL programming trick of using all 9's in an
SQL party dress. Also, SQL is a set-oriented language so the word
"next" is fundamentally wrong; sets have no ordering.

>> I managed to do it, but the query is so big/ugly I doubt it is the only/correct way. <<

Yep, bad DDL leads to realllllllllllly bad DML! Your incorrect choice
of basic terms says that you don't view SQL's three sub-languages as
an integrated whole that have to work together.

Get a copy of THINKING IN SETS to help fix your mindset.

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 + INTERVAL 1 DAY), --
prevents gaps
PRIMARY KEY (upc, price_start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);

You use a BETWEEN predicate to get the appropriate price. You can
enforce the "one null per item" with a trigger but techically this
should work:

CHECK (COUNT(*) OVER (PARTITION BY upc)
= COUNT(price_end_date) OVER (PARTITION BY upc) +1)

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

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).
 >> Stay informed about: Start -> From-Till date conversion 

On 24 jun, 23:13, Erland Sommarskog wrote:
> Danny (danny.bosschae...@scarlet.be) writes:
> > Have a table containg some data, one field being the 'StartDate'
> > When another registration is done, it receives again a 'StartDate'.
>
> > For some application I need a From-Till date instead of only a
> > StartDate.
>
> > There is no uninterrupted sequential field to get the 'next record':
> > table = Building int, Unit int, Owner int, StartDate date
>
> > So the shorthand for this would be:
>
> > Select StartDate As FromDate, datediff(d,-1,isnull([StartDate of next
> > record],[some big date])) as TillDate from table where
> > Building=@BuildingID and Unit=@UnitID
> > (the [some big date] is a dummy for the last registration where there
> > is no [next record])
>
> > I managed to do it, but the query is so big/ugly I doubt it is the
> > only/correct way.
> > Aother way might be to build in a trigger and update a 'TillDate'
> > field after every change of 'StartDate', but I think I would need the
> > same logic.
>
> It's not entirely easy to understand your post, but something like:
>
> SELECT a.StartDate, b.StartDate
> FROM   tbl a
> OUTER  APPLY (SELECT TOP 1
>               FROM   tbl b
>               WHERE  b.Building  = a.Building
>                 AND  b.Unit      = a.Unit
>                 AND  b.StartDate > a.StartDate
>               ORDER  BY b.StartDate) AS b
> WHERE  a.Building = @BuildingID
>   AND  a.Unit     = @UnitID
>
> OUTER APPLY is like an outer join, but you can refer to the outer table
> in the derived table. If there is no later date, you will get NULL
> back.
>
> --
> Erland Sommarskog, SQL Server MVP, esq... RemoveThis @sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

Thanks, after some tweaking, worked perfectly: (there was an error as
there was no field after the 'select top 1.... from)
dateadd -1 to get 'Till'
needed to add 'Distinct' after adding Building and Unit

SELECT DISTINCT
f.Building, f.Unit, f.Contact, f.StartDate AS
FromDate, isnull(t .StartDate, dateadd(y, 10, GETDATE())) AS TillDate
FROM Owners f OUTER APPLY
(SELECT TOP 1 dateadd(d, - 1,
t .StartDate) AS StartDate
FROM Owners t
WHERE t .Building = f.Building AND
t .Unit = f.Unit AND t .StartDate > f.StartDate
ORDER BY t .StartDate) AS t
 >> Stay informed about: Start -> From-Till date conversion