Saturday, July 2, 2011

crystal report db






CREATE TABLE [dbo].[OrderMaster] (
[OrderMaster_id] [int] NOT NULL ,
[OrderMaster_date] [datetime] NULL ,
[OrderMaster_customername] [varchar] (50),
[OrderMaster_createduser] [varchar] (50)
)

CREATE TABLE [dbo].[OrderDetails] (
[OrderDetails_id] [int] NOT NULL ,
[OrderDetails_masterid] [int] NULL ,
[OrderDetails_productid] [int] NULL ,
[OrderDetails_qty] [int] NULL
)

CREATE TABLE [dbo].[Product] (
[Product_id] [int] NOT NULL ,
[Product_name] [varchar] (50) ,
[Product_price] [numeric](18, 2) NULL
)




insert product (product_id, product_name,product_price)
select 1 , 'Product1' , 10.50
union all
select 2 , 'Product2' , 20.25
union all
select 3 , 'Product3' , 30.40
union all
select 4 , 'Product4' , 5.50
union all
select 5 , 'Product5' , 76.50
union all
select 6 , 'Product6' , 45.50
union all
select 7 , 'Product7' , 10.50




insert ordermaster (
OrderMaster_id,
OrderMaster_date,
OrderMaster_customername,
OrderMaster_createduser)
select 1 , getdate() , 'customer1' , 'user'
union all
select 2 , dateadd(day , -1 , getdate()) , 'customer1' , 'user'
union all
select 3 , dateadd(day , -2 ,getdate()) , 'customer2' , 'user'
union all
select 4 , getdate() , 'customer4' , 'user'
union all
select 5 , dateadd(day , -5,getdate()) , 'customer4' , 'user'
union all
select 6 , getdate() , 'customer3' , 'user'



insert orderdetails (
OrderDetails_id,OrderDetails_masterid,OrderDetails_productid,
OrderDetails_qty)
select  1, 1, 2, 10
union all
select  1, 1, 6, 20
union all
select  1, 1, 4, 30
union all
select  1, 1, 2, 60
union all
select  1, 1, 7, 45
union all
select  1, 2, 7, 58
union all
select  1, 2, 4, 22
union all
select  1, 3, 4, 30
union all
select  1, 3, 2, 60
union all
select  1, 3, 7, 45


--DROP TABLE Product
--DROP Table OrderMAster
--DROP Table orderdetails




No comments:

Post a Comment