USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99( type char(10),[date] datetime, price money
, PRIMARY KEY (type, [date]))
GO

INSERT INTO myTable99(type,[date],price)
SELECT 'bat' ,'1/1/2001',10.00 UNION ALL
SELECT 'bat' ,'1/1/2002',20.00 UNION ALL
SELECT 'bat' ,'1/1/2003',30.00 UNION ALL
SELECT 'bat' ,'1/1/2004',40.00 UNION ALL
SELECT 'ball','1/1/2001',40.00 UNION ALL
SELECT 'ball','1/1/2002',30.00 UNION ALL
SELECT 'ball','1/1/2003',20.00 UNION ALL
SELECT 'ball','1/1/2004',10.00
GO


What I need, is to create view
with the result


type date (date from) price date_to
ball 2001-01-01 40.0000 2001-12-31 (next date -1 day)
ball 2002-01-01 30.0000 2002-12-31
ball 2003-01-01 20.0000 2003-12-31
ball 2004-01-01 10.0000 today
bat 2001-01-01 10.0000 2001-12-31
bat 2002-01-01 20.0000 2002-12-31
bat 2003-01-01 30.0000 2003-12-31
bat 2004-01-01 40.0000 today





I've made sth like

select c1.koszt_zak,c1.produktkey,c1.cena_od,
cena_do = (select c2.cena_od -1 from CENY_ZAK_TOW c2
where c1.produktkey=c2.produktkey and c1.cena_od<c2.cena_od)
from CENY_ZAK_TOW c1


bat it works only for to dates per 1 type
it's needed to be able to add new dates, and prices