How to add and update data using the date range period (date from and to)?

I have a table like below

Stage 1

Table Name : Product

Date_From   Date_To     Available
01/03/2011  05/03/2011  5
06/03/2011  15/03/2011  6

Stage 2

If I update above table with below data

Date_From   Date_To     Available
04/03/2011  08/03/2011  4
10/03/2011  18/03/2011  2

Stage 3

I need output of Product table (Stage 1) like this, when update with stage 2 data at same time

Date_From   Date_To     Available
01/03/2011  03/03/2011  5
04/03/2011  08/03/2011  4
06/03/2011  15/03/2011  6
10/03/2011  18/03/2011  2

Hoping for your help

EDIT:

create table t (dt_from datetime,dt_to datetime, Available int)

insert into t values ('20110301','20110305',5)
insert into t values ('20110306','20110315',6)

run after trigger is created

--insert into t values ('20110304','20110308',4)
---insert into t values ('20110310','20110318',2)



SELECT *,ROW_NUMBER() OVER (ORDER BY dt_from) rn FROM t

CREATE TRIGGER my_tr ON t FOR INSERT
AS

UPDATE t SET dt_to=(SELECT TOP 1  DATEADD(d,-1,dt_from) FROM inserted t1 WHERE dt_from
                    BETWEEN t.dt_from AND t.dt_to AND t.Available<>t1.Available)
WHERE EXISTS (SELECT * FROM  inserted t1 WHERE dt_from
                    BETWEEN t.dt_from AND t.dt_to AND t.Available<>t1.Available)
UPDATE t SET dt_from=(SELECT TOP 1 DATEADD(d,1,dt_to) FROM inserted t1 WHERE dt_to
                    BETWEEN t.dt_from AND t.dt_to AND t.Available<>t1.Available)
WHERE EXISTS (SELECT 开发者_如何学运维* FROM  inserted t1 WHERE dt_to
                    BETWEEN t.dt_from AND t.dt_to AND t.Available<>t1.Available)

the above code is working fine , i get the below result

Date_From   Date_To     Available
01/03/2011  03/03/2011  5
04/03/2011  08/03/2011  4
09/03/2011  09/03/2011  6
10/03/2011  18/03/2011  2

---

But only one problem i facing is

insert into t values ('20110301','20110318',5)

it not get the correct result

i need result like below

Date_From   Date_To     Available
01/03/2011  18/03/2011  5

Hoping your help

---

You should probably have a stored procedure that takes your values, and then decides whether an INSERT or UPDATE is needed, based on the dates...

CREATE PROCEDURE dbo.InsertOrUpdateData
    @FromDate DATE, @ToDate DATE, @NewAvail INT
AS 
   IF EXISTS(SELECT * FROM dbo.YourTable
             WHERE Date_From = @FromDate AND Date_To = @ToDate)

      UPDATE dbo.YourTable
      SET Available = Available + @NewAvail
      WHERE Date_From = @FromDate AND Date_To = @ToDate

   ELSE

      INSERT INTO dbo.YourTable(Date_From, Date_To, Availability)
      VALUES(@FromDate, @ToDate, @NewAvail)

With this stored procedure, you can call

EXEC dbo.InsertOrUpdateData 
     @FromDate = '20110301', @ToDate = '20110305', @NewAvail = 42

If those dates already exists, that row will be updated, if those dates don't exist, a new row will be inserted. No trigger voodoo or anything like that needed....

Is that what you're looking for??

---

When you get a new row you need to do the following:

Using the new ValidFrom and ValidTo dates - Checking existing data for anything that either:

• Has a ValidFrom < new Validfrom and ValidTo >= new ValidFrom - if found, ValidTo should be set to 1 day before new ValidFrom.
• Has a ValidFrom >= new ValidFrom and ValidTo <= new ValidTo - if found, You might need to do check again to see if moving the dates would overlap previous entries
• Has a ValidFrom >= new ValidFrom and ValidFrom <= new ValidTo and ValidTo >= new ValidTo. This record might need to be split in two.
• etc - any other concievable combination of Date overlaps ...

I would rather recommend using a new table with day - available, giving one row per product per day, with the available measure. This way, any new entries with validfrom / validto can just write the available value for the relevant days for the relevant products.

DayID ProductId Available