Thanks for the reply.

Here is the sql

CREATE TABLE table1(

[CARDNO] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DateTime1] [datetime] NULL,

[CHANNEL_NO] [smallint] NULL

) ON [PRIMARY]

CREATE TABLE table2(

[CARDNO] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[DateTime1] [datetime] NOT NULL,

[action] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

CREATE VIEW view1 AS

SELECT CARDNO AS cardno, datetime1, channel_no as action FROM table1

UNION ALL

SELECT CARDNO AS cardno, datetime1, action FROM table2

INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 10:45:00.000',1)

INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 12:45:00.000',2)

INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 13:10:00.000',1)

INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 16:10:00.000',2)

INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 16:30:00.000',1)

INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222' ,'2013-02-04 11:45:00.000',1)

INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222','2013-02-04 13:00:00.000',2)

INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222','2013-02-04 13:45:00.000',1)

INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222','2013-02-04 16:10:00.000',2)

INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222','2013-02-04 16:30:00.000',1)

INSERT INTO table2 ([CARDNO] ,[DateTime1],[action]) VALUES('11111' ,'2013-02-04 08:45:00.000',1)

INSERT INTO table2 ([CARDNO] ,[DateTime1],[action]) VALUES('11111' ,'2013-02-04 18:30:00.000',1)