/****** all table creation script with insert test data ******/
/****** Object:  Table [dbo].[CustomerColor]    Script Date: 02/12/2013 11:34:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustomerColor](
	[CustomerColorID] [tinyint] IDENTITY(1,1) NOT NULL,
	[CustomerSubject] [nvarchar](50) NOT NULL,
	[Color] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_CustomerColor] PRIMARY KEY CLUSTERED 
(
	[CustomerColorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Customer]    Script Date: 02/12/2013 11:34:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customer](
	[CustomerID] [int] IDENTITY(10,1) NOT NULL,
	[EshterakTypeID] [tinyint] NOT NULL,
	[MahaleID] [int] NULL,
	[KnowID] [smallint] NULL,
	[CustomerColorID] [tinyint] NULL,
	[FullName] [nvarchar](50) NOT NULL,
	[Bestankary] [int] NOT NULL,
	[Etebar] [int] NOT NULL,
	[Address] [nvarchar](250) NOT NULL,
	[Telephone1] [varchar](11) NOT NULL,
	[Telephone2] [varchar](11) NULL,
	[Telephone3] [varchar](11) NULL,
	[Mobile] [varchar](11) NULL,
	[CustomerSabtDate] [datetime] NULL,
	[Describtion] [nvarchar](500) NULL,
	[GetService] [bit] NOT NULL,
	[PardakhtType] [bit] NOT NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_Customer] UNIQUE NONCLUSTERED 
(
	[FullName] ASC,
	[Telephone1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Customer] ON
INSERT [dbo].[Customer] ([CustomerID], [EshterakTypeID], [MahaleID], [KnowID], [CustomerColorID], [FullName], [Bestankary], [Etebar], [Address], [Telephone1], [Telephone2], [Telephone3], [Mobile], [CustomerSabtDate], [Describtion], [GetService], [PardakhtType]) VALUES (12, 1, 2, 3, NULL, N'اکبری', 1200, 0, N'شیراز  1




', N'8343513', N'', N'', N'09362928936', CAST(0x0000A13600000000 AS DateTime), N'توضیحات1 




', 1, 1)
INSERT [dbo].[Customer] ([CustomerID], [EshterakTypeID], [MahaleID], [KnowID], [CustomerColorID], [FullName], [Bestankary], [Etebar], [Address], [Telephone1], [Telephone2], [Telephone3], [Mobile], [CustomerSabtDate], [Describtion], [GetService], [PardakhtType]) VALUES (13, 1, 2, 3, NULL, N'توکلی', 0, 0, N'شیراز


', N'8353313', N'', N'', N'09362928936', CAST(0x0000A13600000000 AS DateTime), N'توضیحات


', 1, 1)
INSERT [dbo].[Customer] ([CustomerID], [EshterakTypeID], [MahaleID], [KnowID], [CustomerColorID], [FullName], [Bestankary], [Etebar], [Address], [Telephone1], [Telephone2], [Telephone3], [Mobile], [CustomerSabtDate], [Describtion], [GetService], [PardakhtType]) VALUES (15, 1, 2, 3, NULL, N'رحمانی', 0, 0, N'شیراز
', N'8343313', N'', N'', N'09362928936', CAST(0x0000A13400000000 AS DateTime), N'توضیحات
', 1, 1)
SET IDENTITY_INSERT [dbo].[Customer] OFF
/****** Object:  Table [dbo].[Service]    Script Date: 02/12/2013 11:34:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Service](
	[ServiceID] [bigint] IDENTITY(1,1) NOT NULL,
	[CustomerID] [int] NOT NULL,
	[DriverID] [int] NOT NULL,
	[khadamatID] [smallint] NULL,
	[EndUserName] [nvarchar](20) NOT NULL,
	[FactorEditDelID] [smallint] NULL,
	[Mabda] [nvarchar](250) NULL,
	[Maghsad] [nvarchar](250) NOT NULL,
	[SendTime] [datetime] NULL,
	[ReturnTime] [datetime] NULL,
	[Describtion] [nvarchar](500) NULL,
	[SabtDate] [datetime] NULL,
	[GetPorsant] [bit] NOT NULL,
	[DakhelShahr] [bit] NULL,
	[Gheymat] [int] NULL,
	[GetKeraye] [bit] NOT NULL,
	[Editor] [nvarchar](20) NULL,
	[EditDate] [datetime] NULL,
 CONSTRAINT [PK_Service] PRIMARY KEY CLUSTERED 
(
	[ServiceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Service] ON
INSERT [dbo].[Service] ([ServiceID], [CustomerID], [DriverID], [khadamatID], [EndUserName], [FactorEditDelID], [Mabda], [Maghsad], [SendTime], [ReturnTime], [Describtion], [SabtDate], [GetPorsant], [DakhelShahr], [Gheymat], [GetKeraye], [Editor], [EditDate]) VALUES (4, 12, 12, 2, N'SA', 1, N'شیراز


', N'شیراز2', CAST(0x0000A1600046DCA0 AS DateTime), CAST(0x0000A16000BDC9F0 AS DateTime), N'', CAST(0x0000A15B011CAD84 AS DateTime), 1, 1, 120000, 1, N'SA', CAST(0x0000A16100F09B0D AS DateTime))
INSERT [dbo].[Service] ([ServiceID], [CustomerID], [DriverID], [khadamatID], [EndUserName], [FactorEditDelID], [Mabda], [Maghsad], [SendTime], [ReturnTime], [Describtion], [SabtDate], [GetPorsant], [DakhelShahr], [Gheymat], [GetKeraye], [Editor], [EditDate]) VALUES (5, 15, 13, NULL, N'SA', 1, N'شیراز
', N'گلستان', CAST(0x0000A15C00AF3B60 AS DateTime), CAST(0x0000A16100B54640 AS DateTime), N'dsfsd', CAST(0x0000A15B011D6CD6 AS DateTime), 1, 1, 45000, 1, N'SA', CAST(0x0000A16100F17462 AS DateTime))
SET IDENTITY_INSERT [dbo].[Service] OFF
/****** Object:  ForeignKey [FK_Service_Customer]    Script Date: 02/12/2013 11:34:53 ******/
ALTER TABLE [dbo].[Service]  WITH CHECK ADD  CONSTRAINT [FK_Service_Customer] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customer] ([CustomerID])
GO
ALTER TABLE [dbo].[Service] CHECK CONSTRAINT [FK_Service_Customer]
GO
/****** Object:  ForeignKey [FK_Customer_CustomerColor]    Script Date: 02/12/2013 11:34:53 ******/
ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_CustomerColor] FOREIGN KEY([CustomerColorID])
REFERENCES [dbo].[CustomerColor] ([CustomerColorID])
GO
ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [FK_Customer_CustomerColor]
GO


/****** query  ******/

select distinct  customer.*,CustomerColor.Color,CustomerColor.CustomerSubject,b.AkharinKeraye,b.Karkard,b.Lcall
from  customer
left outer join CustomerColor on Customer.CustomerColorID=CustomerColor.CustomerColorID
,(select [Service].CustomerID,max([Service].SabtDate) as Lcall,
SUM(Service.Gheymat) as Karkard,(select s.Gheymat from [Service] as s where s.SabtDate=max([Service].SabtDate)) as AkharinKeraye
from [Service] group by [Service].CustomerID )b
order by Customer.CustomerID,Customer.FullName 

select distinct  
	customer.*
	,CustomerColor.Color
	,CustomerColor.CustomerSubject
	,b.AkharinKeraye
	,b.Karkard
	,b.Lcall
from  customer
	left outer join CustomerColor on Customer.CustomerColorID=CustomerColor.CustomerColorID
	,(select [Service].CustomerID
		,max([Service].SabtDate) as Lcall
		,SUM(Service.Gheymat) as Karkard
		,(select s.Gheymat 
			from [Service] as s 
			where s.SabtDate=max([Service].SabtDate)) as AkharinKeraye
	from [Service] 
	group by [Service].CustomerID ) b
order by Customer.CustomerID,Customer.FullName 

select distinct  
	customer.*
	,CustomerColor.Color
	,CustomerColor.CustomerSubject
	,b.AkharinKeraye
	,b.Karkard
	,b.Lcall
from  customer
	left outer join CustomerColor on Customer.CustomerColorID=CustomerColor.CustomerColorID,
	INNER JOIN (select [Service].CustomerID
			,max([Service].SabtDate) as Lcall
			,SUM(Service.Gheymat) as Karkard
			,(select s.Gheymat 
				from [Service] as s 
				where s.SabtDate=max([Service].SabtDate)) as AkharinKeraye
			from [Service] 
			group by [Service].CustomerID) b on b.CustomerID=customer.customerId
order by Customer.CustomerID,Customer.FullName 

select
*
from
table1 t1
,(select ...) t2
where
t1.col1 = t2.col1