May 1, 2012 at 3:09 am
hello all.
I wrote this code with alter table and add columns.but i need to replace this code with pivot and i don't know how to do it?pleas help me and qine me script.thanks.
if @TypeOfOperation=1------------فرمت1
begin
IF OBJECT_ID('TempDB..#Out1','U') IS NOT NULL
DROP TABLE #Out1
create table #out1(ID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,COM_CdOpenDate nvarchar(10),COM_CdDescription nvarchar(3000),COM_CdCode nvarchar(15),Com_CdCondition nvarchar(700),TBL_CustomerID_fk bigint,COM_CdcID bigint)
set @sql='select COM_CdOpenDate,COM_CdDescription,COM_CdCode,Com_CdCondition,TBL_CustomerID_fk,COM_CdcID
from COM_CommercialDocument INNER JOIN dbo.COM_CommercialDocumentCustomer ON dbo.COM_Commercialdocument.COM_CdID = dbo.COM_CommercialDocumentCustomer.COM_CdID_fk INNER JOIN dbo.TBL_Customer ON dbo.COM_CommercialDocumentCustomer.TBL_CustomerID_fk = dbo.TBL_Customer.TBL_CustomerID
where dbo.COM_Commercialdocument.COM_CdID='+cast(@COM_CdID as nvarchar(20))+''
insert into #out1 exec sp_executesql @sql
set @count1=@@ROWCOUNT
set @sql='alter table #out1 add[WinCustomerName] nvarchar(1000) NOT NULL DEFAULT ((0)),
[WinCustomerPrice] nvarchar(500) NOT NULL DEFAULT ((0))'
exec sp_executesql @sql
IF OBJECT_ID('TempDB..#Out2','U') IS NOT NULL
DROP TABLE #Out2
create table #out2(ID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,COM_CdWinType tinyint,Tbl_customerid_fk bigint,WinPrice numeric(27,3))
set @sql='select COM_CdWinType,Tbl_customerid_fk ,sum(COM_CdsdPrice) as WinPrice from V_COM_Cdc
Where Com_CdID='+cast(@COM_CdID as nvarchar(20))+' and COM_CdsdWin=1 Group BY Tbl_customerid_fk,COM_CdWinType ORDER BY sum(BalanceGradePrice)'
insert into #out2 exec sp_executesql @sql
--------تک برنده ای
if(@WinType=1)
begin
select @Winprice=WinPrice from #out2 where ID=1
set @sql='update #out1
set WinCustomerName='''+@winCustomer+'''
,WinCustomerPrice='''+@Winprice+'''
where ID=1'
exec sp_executesql @sql
end
set @k=1
while (@k<=@count1)
begin
if (@k=1)
begin
set @sql='alter table #out1 add[CustomerID] int NOT NULL DEFAULT ((0)),
[CustomerName] nvarchar(2000) NOT NULL DEFAULT ((0)),
[LapseReason] nvarchar(max) NOT NULL DEFAULT ((0))'
exec sp_executesql @sql
end
IF OBJECT_ID('TempDB..#Out5','U') IS NOT NULL
DROP TABLE #Out5
create table #out5(ID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,COM_CddID bigint,COM_CddQuantity numeric(18,0),WHS_GoodsID_fk char(20),COM_ServiceID_fk int)
set @sql='select COM_CddID,COM_CddQuantity,WHS_GoodsID_fk,COM_ServiceID_fk from COM_CommercialDocumentDetail where COM_CdID_fk='+cast(@COM_CdID as nvarchar(20))+''
insert into #Out5 exec sp_executesql @sql
set @count3=@@ROWCOUNT
set @j-2=1
while (@j<=@count3)
begin
if (@k=1)
begin
set @sql='alter table #out1 add[COM_Win'+cast(@j as nvarchar(20))+'] int NOT NULL DEFAULT ((0)),
[COM_TotalPrice'+cast(@j as nvarchar(20))+'] float NOT NULL DEFAULT ((0)),
[COM_GradePrice'+cast(@j as nvarchar(20))+'] float NOT NULL DEFAULT ((0)),
[COM_BalancePrice'+cast(@j as nvarchar(20))+'] float NOT NULL DEFAULT ((0)),
[COM_UnitPrice'+cast(@j as nvarchar(20))+'] numeric(27,3) NOT NULL DEFAULT ((0)),
[GoodsName'+cast(@j as nvarchar(20))+'] nvarchar(2000) NOT NULL DEFAULT ((0)),
[UnitName'+cast(@j as nvarchar(20))+'] nvarchar(1000) NOT NULL DEFAULT ((0))'
exec sp_executesql @sql
end
select @COM_CddID=COM_CddID,@COM_CddQuantity=COM_CddQuantity,@WHS_GoodsID_fk=WHS_GoodsID_fk,@COM_ServiceID=COM_ServiceID_fk from #out5 where ID=@j
select @TBL_CustomerID_fk=TBL_CustomerID_fk,@COM_CdcID=COM_CdcID from #out1 where ID=@k
-----------نام و کد مشتري
select @TBL_CustomerID=TBL_CustomerID,@CustomerName=TBL_CustomerTitle from TBL_Customer where TBL_CustomerID=@TBL_CustomerID_fk
--select @COM_CdcLapseReason=COM_CdcLapseReason from COM_CommercialDocumentCustomer where COM_CdcID=@COM_CdcID and TBL_CustomerID_fk=@TBL_CustomerID
set @sql='update #out1
set CustomerID='+cast(@TBL_CustomerID as nvarchar(20))+'
,CustomerName='''+@CustomerName+'''
,WinCustomerName='''+@winCustomer+'''
,WinCustomerPrice='''+@Winprice+'''
where ID='+cast(@k as nvarchar(20))+''
exec sp_executesql @sql
---,LapseReason='+@COM_CdcLapseReason+'
---------بهاي کل و امتياز فني
IF OBJECT_ID('TempDB..#Out6','U') IS NOT NULL
DROP TABLE #Out6
create table #out6(ID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,COM_CdsdWin tinyint,COM_CdsdPrice numeric(27,3),COM_CdsdGrade numeric(27,3),BalanceGradePrice float)
set @sql='select COM_CdsdWin,COM_CdsdPrice,COM_CdsdGrade,BalanceGradePrice from V_COM_Cdc where COM_CddID_fk='+cast(@COM_CddID as nvarchar(20))+' and COM_CdcID_fk='+cast(@COM_CdcID as nvarchar(20))+''
insert into #out6 exec sp_executesql @sql
select @COM_CdsdWin=COM_CdsdWin,@COM_CdsdPrice=COM_CdsdPrice,@COM_CdsdGrade=COM_CdsdGrade,@BalanceGradePrice=BalanceGradePrice from #out6 where ID=1
if @@ROWCOUNT<>0
begin
if (@COM_CddQuantity=0)
begin
set @Price=0
end
else
begin
set @Price=@COM_CdsdPrice/@COM_CddQuantity
end
-----------نام کالا يا خدمات////يافتن واحد کالا يا خدمات
if @WHS_GoodsID_fk<>'0'
begin
select @WHS_GoodsName=WHS_GoodsFarsiName from WHS_Goods where WHS_GoodsID=@WHS_GoodsID_fk
select @WHS_GuName=WHS_GuName from WHS_Goods INNER JOIN dbo.WHS_GoodsUnit ON dbo.WHS_Goods.WHS_GuID_fk = dbo.WHS_GoodsUnit.WHS_GuID
where WHS_GoodsID=@WHS_GoodsID_fk
end
else if @COM_ServiceID<>0
begin
select @WHS_GoodsName=COM_ServiceSubject from COM_Service where COM_ServiceID=@COM_ServiceID
select @WHS_GuName=WHS_GuName from COM_Service INNER JOIN dbo.WHS_GoodsUnit ON dbo.WHS_GoodsUnit.WHS_GuID = dbo.COM_Service.WHS_GuID_fk
where COM_ServiceID=@COM_ServiceID
end
set @sql='update #out1
set COM_Win'+cast(@j as nvarchar(20))+'='+cast(@COM_CdsdWin as nvarchar(500))+'
,COM_TotalPrice'+CAST(@j AS NVARCHAR(20))+'='+cast(@COM_CdsdPrice as nvarchar(500))+'
,COM_GradePrice'+cast(@j as nvarchar(20))+'='+cast(@COM_CdsdGrade as nvarchar(500))+'
,COM_BalancePrice'+cast(@j as nvarchar(20))+'='+cast(@BalanceGradePrice as nvarchar(500))+'
,COM_UnitPrice'+cast(@j as nvarchar(20))+'='+cast(@Price as nvarchar(500))+'
,GoodsName'+cast(@j as nvarchar(20))+'='''+@WHS_GoodsName+'''
,UnitName'+cast(@j as nvarchar(20))+'='''+@WHS_GuName+'''
where ID='+cast(@k as nvarchar(20))+'
'
exec sp_executesql @sql
end
--------------نام کالا يا خدمات////يافتن واحد کالا يا خدمات
set @j-2=@j+1
end
set @k=@k+1
end
select * from #out1
end
May 1, 2012 at 6:30 am
Please just post the DDL of your table and some readily consumable sample data as a series of Insert Statements.
We'll provide the query for Pivot. post "only" the DDL of your table and some readily consumable sample data as a series of Insert Statements.
May 1, 2012 at 6:33 am
Hello and welcome to SSC!
I'd like to be able to help you, but it seems you've forgot to post readily consumable sample data, ddl scripts or expected results based on the sample data!
If you could read this article[/url] about the best way to post DDL and sample data, then reply to this post with readily consumable sample data, ddl scripts and expected results based on the sample data then it'd be extremely helpful to the unpaid volunteers of this site.
Thanks!
May 1, 2012 at 6:52 am
elham_azizi_62 (5/1/2012)
I wrote this code with alter table and add columns.but i need to replace this code with pivot and i don't know how to do it?pleas help me and qine me script.
Not from what you provided. I can, however, teach you how to science one out on your own. Please see the following article.
http://qa.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
May 1, 2012 at 11:57 pm
Hi, these are scripts of my tables use in procedures and view.
thanks for your replies.
May 2, 2012 at 12:53 am
elham_azizi_62 (5/1/2012)
Hi, these are scripts of my tables use in procedures and view.thanks for your replies.
Thank you very much for posting the DDL Scripts.
You would also have to post some sample data(not all the data in your tables). It would be great if you could post the Sample data in readily Consumable Format ie: as a series of Insert Statements.
The people here, who would help you, need to have some sample data in the tables so that they can provide you with a working solution.
Also tell us which table you are looking to Pivot.
If you can provide an example Result Set from the sample data of how you want the result to look like then that would be like the Icing on the cake 😛 🙂
May 2, 2012 at 1:44 am
I'm not downloading a zip file, and I'm sure many others won't either.
Instead, follow the example laid out in this article (which I've already linked you to once)[/url] and post the readily consumable sample data, DDL scripts and expected results.
May 2, 2012 at 1:55 am
Hi, please pivot V_COM_Cdc and use this data.
thanks very much 😀
May 2, 2012 at 2:13 am
Last try from me, I won't download a zip file. Please post the readily consumable sample data and ddl scripts in a post.
So until you do as requested, I'm out.
May 2, 2012 at 2:55 am
I could download this zip file.please try again.thanks
May 2, 2012 at 2:58 am
elham_azizi_62 (5/2/2012)
I could download this zip file.please try again.thanks
I didn't say I can't, I said I won't. I don't have control over your machine, so I don't know what it contained in that zip file.
With no scripts posted as requested (using IFCode shortcuts, or just as plain text), I'm out. Try reading through Jeff's articles[/url], Good luck.
May 2, 2012 at 3:02 am
elham_azizi_62 (5/2/2012)
I could download this zip file.please try again.thanks
They are not saying that they could not download it.
These are real people who take out time from their busy work schedules to help the people who post their problems here. They do not get paid for this.
They put in a lot of effort in finding solutions for the requirements you post here.
So, what they are saying is that they do not want to put in the extra effort of downloading your files. Instead, it'll be better if you post the DDLs and the sample data right here on the forum rather than adding the scripts as attachments.
How hard can it be??....CTRL+c -> CTRL+ v
May 2, 2012 at 6:12 am
these are script and data that used in my procedure and i need to pivot view v_com_cdc and result of my procedure and my favorite resulat is attached.please help me and give me script code of pivot.thanks
CREATE TABLE [dbo].[COM_CommercialDocument](
[COM_CdID] [bigint] NOT NULL,
[COM_CdParentID_fk] [bigint] NULL,
[COM_CdEvockeID_fk] [bigint] NOT NULL,
[TBL_FormID_fk] [int] NOT NULL,
[CNT_CtID_fk] [int] NOT NULL,
[TBL_ResourceID_fk] [int] NOT NULL,
[WHS_WarehouseID_fk] [int] NOT NULL,
[TBL_DepartmentID_fk] [int] NOT NULL,
[COM_CdtID_fk] [smallint] NOT NULL,
[WHS_GcID_fk] [int] NOT NULL,
[COM_ScID_fk] [int] NOT NULL,
[TBL_ExpertID_fk] [bigint] NOT NULL,
[TBL_AdviserID_fk] [bigint] NOT NULL,
[TBL_EditoreID_fk] [bigint] NOT NULL,
[COM_CdsID_fk] [smallint] NOT NULL,
[TBL_PlaceID_fk] [int] NOT NULL,
[COM_CdCode] [nvarchar](15) NOT NULL,
[COM_CdInvitationDate] [nchar](10) NULL,
[COM_CdInvitationNo] [nchar](50) NULL,
[COM_CdSurveyMembers] [nvarchar](500) NULL,
[COM_CdMasterMinuteDate] [nchar](10) NULL,
[COM_CdMasterMinuteNo] [int] NULL,
[COM_CdMasterRowNo] [nvarchar](50) NULL,
[COM_CdCommiteeMinuteNo] [int] NULL,
[COM_CdCommiteeMinuteDate] [nchar](10) NULL,
[COM_CdCommiteeRowNo] [nvarchar](50) NULL,
[COM_CdTechnicalCommiteeDate] [nchar](10) NULL,
[COM_CdAgentMinuteDate] [nchar](10) NULL,
[COM_CdTechnicalMinuteNo] [nvarchar](50) NULL,
[COM_CdAgentMinuteNo] [nvarchar](50) NULL,
[COM_CdCondition] [nvarchar](700) NULL,
[COM_CdDescription] [nvarchar](3000) NULL,
[COM_CdWinType] [tinyint] NULL,
[COM_CdEffectCoefficient] [float] NULL,
[COM_CdApprovedStartDate] [nchar](10) NULL,
[COM_CdApprovedEndDate] [nchar](10) NULL,
[COM_CdApprovedAddress1] [nvarchar](500) NULL,
[COM_CdApprovedAddress2] [nvarchar](500) NULL,
[COM_CdBuyPrice] [int] NULL,
[COM_CdAcceptTime] [nchar](100) NULL,
[COM_CdOpenTimeLetterG] [nchar](100) NULL,
[COM_CdOpenTime] [nchar](100) NULL,
[COM_CdSettleToAccountNo] [nvarchar](1000) NULL,
[COM_CdDistributedDate] [nchar](10) NULL,
[COM_CdAcceptDate] [nchar](10) NULL,
[COM_CdEditeDate] [nchar](10) NULL,
[COM_CdOpenDateLetterG] [nchar](10) NULL,
[COM_CdOpenDate] [nvarchar](10) NULL,
[COM_CdAppendixNo] [smallint] NULL,
[COM_CdEstimatedPrice] [numeric](27, 0) NULL,
[COM_CdGuaranteePrice] [numeric](27, 0) NULL,
[COM_CdFinancialControl] [tinyint] NULL,
[COM_CdMinimumMark] [numeric](18, 0) NULL,
[COM_CdTechnicalDate] [nchar](10) NULL,
[COM_CdTechnicalFinancialDate] [nchar](10) NULL,
[COM_CdApproveNo] [int] NULL,
[COM_CdRowNo] [nvarchar](50) NULL,
[COM_CdDeliveryPlaceName] [nvarchar](300) NULL,
[COM_CdExecuteTimeType] [int] NOT NULL,
[COM_CdExecuteTime] [int] NULL,
[COM_CdGuarantyCycle] [int] NULL,
[COM_CdStageNo] [smallint] NULL,
[COM_CdJustificationMeetingDate] [nchar](10) NULL,
[COM_CdReviewStartDate] [nchar](10) NULL,
[COM_CdReviewEndDate] [nchar](10) NULL,
[COM_CdDemurDamage] [nvarchar](2000) NULL,
[COM_CdOpenPlace] [nvarchar](300) NULL,
[COM_CdAdjustment] [tinyint] NOT NULL,
[COM_CdRenewMasterMinuteNo] [nvarchar](50) NOT NULL,
[COM_CdRenewMasterDate] [nvarchar](10) NOT NULL,
[COM_CdRenewMasterRowNo] [nvarchar](50) NOT NULL,
[COM_CdRenewCommitteeDate] [nvarchar](10) NOT NULL,
[COM_CdRenewCommitteeMinuteNo] [nvarchar](50) NOT NULL,
[COM_CdRenewCommitteeRowNo] [nvarchar](50) NOT NULL,
[COM_CdRenewResean] [nvarchar](500) NOT NULL,
[COM_CdNationalDataBaseCode] [nvarchar](50) NULL,
[COM_CdNote] [nvarchar](500) NULL,
[COM_CdRegisterDate] [nchar](19) NOT NULL,
[COM_CdActive] [tinyint] NOT NULL,
[COM_CdStatus] [tinyint] NULL,
[COM_CdType] [tinyint] NULL,
[COM_CdDeleteDate] [nchar](19) NULL,
[ACC_FinancialYearID] [smallint] NULL,
[TBL_UserID] [int] NOT NULL,
CONSTRAINT [PK_COM_CommercialDocument] PRIMARY KEY CLUSTERED
(
[COM_CdID] 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
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdParentID_fk_1] DEFAULT ((0)) FOR [COM_CdParentID_fk]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdEvockeID_fk] DEFAULT ((0)) FOR [COM_CdEvockeID_fk]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_TBL_ResourceID_fk_1] DEFAULT ((1)) FOR [TBL_ResourceID_fk]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_WHS_GcID_fk] DEFAULT ((1)) FOR [WHS_GcID_fk]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_TBL_PlaceID_fk] DEFAULT ((0)) FOR [TBL_PlaceID_fk]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdCommiteeMinuteNo_1] DEFAULT ((0)) FOR [COM_CdCommiteeMinuteNo]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdTechnicalCommiteeDate_1] DEFAULT (' ') FOR [COM_CdTechnicalCommiteeDate]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdAgentMinuteDate_1] DEFAULT (' ') FOR [COM_CdAgentMinuteDate]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdTechnicalMinuteNo] DEFAULT ((0)) FOR [COM_CdTechnicalMinuteNo]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdAgentMinuteNo] DEFAULT ((0)) FOR [COM_CdAgentMinuteNo]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdAppendixNo] DEFAULT ((0)) FOR [COM_CdAppendixNo]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdExecuteTimeType] DEFAULT ((0)) FOR [COM_CdExecuteTimeType]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdAdjustment] DEFAULT ((0)) FOR [COM_CdAdjustment]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdRenewMasterMinuteNo] DEFAULT ((0)) FOR [COM_CdRenewMasterMinuteNo]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdRenewMasterDate] DEFAULT ((0)) FOR [COM_CdRenewMasterDate]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdRenewMasterRowNo] DEFAULT ('0') FOR [COM_CdRenewMasterRowNo]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdRenewCommitteeDate] DEFAULT ('0') FOR [COM_CdRenewCommitteeDate]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdRenewCommitteeMinuteNo] DEFAULT ((0)) FOR [COM_CdRenewCommitteeMinuteNo]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdRenewCommitteeRowNo] DEFAULT ('0') FOR [COM_CdRenewCommitteeRowNo]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdRenewResean] DEFAULT ('0') FOR [COM_CdRenewResean]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdNationalDataBaseCode] DEFAULT ((0)) FOR [COM_CdNationalDataBaseCode]
GO
ALTER TABLE [dbo].[COM_CommercialDocument] ADD CONSTRAINT [DF_COM_CommercialDocument_COM_CdActive] DEFAULT ((1)) FOR [COM_CdActive]
GO
----------------------------------
INSERT INTO [dbo].[COM_CommercialDocument]
VALUES
(171,0,0,121,0,110,0,6210,2,0,128,84 ,0,84,0,110 ,'9007005'
,'' ,'','','',0,'',0 ,'','','','','','','' ,'???? ????(???????)60?????? ??? ?? ????? ??????'
,1,100 ,'1390/03/23','','','',0 ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,''
,0,0,0,0,0,'','',0,'','',0,0,0,0,'','','','','',0,'','','','','','','','',''
,'1390/03/23',1 ,0 ,0,'' ,1390,84)
----------------------------------------
CREATE TABLE [dbo].[COM_CommercialDocumentCustomer](
[COM_CdcID] [bigint] NOT NULL,
[TBL_CustomerID_fk] [bigint] NOT NULL,
[COM_CdID_fk] [bigint] NOT NULL,
[COM_CdcFactureNo] [nvarchar](50) NULL,
[COM_CdcLapseDate] [nchar](10) NULL,
[COM_CdcLapseReason] [nvarchar](50) NULL,
[COM_CdcWin] [tinyint] NOT NULL,
[COM_CdcFinalGrade] [numeric](18, 3) NOT NULL,
[COM_CdcContractPrice] [bigint] NOT NULL,
[COM_CdcDeliveryType] [nvarchar](100) NULL,
[COM_CdcPriceValidityTime] [nvarchar](100) NULL,
[COM_CdcDescription] [nvarchar](200) NULL,
[COM_CdcMasterMinuteDate] [nchar](10) NOT NULL,
[COM_CdcMasterMinuteNo] [nvarchar](50) NOT NULL,
[COM_CdcMasterRowNo] [nvarchar](50) NOT NULL,
[COM_CdcCommitteeDate] [nchar](10) NOT NULL,
[COM_CdcCommitteeMinuteNo] [nvarchar](50) NOT NULL,
[COM_CdcCommitteeRowNo] [nvarchar](50) NOT NULL,
[COM_CdcNote] [nvarchar](1000) NULL,
[COM_CdcType] [tinyint] NULL,
[COM_CdcRegisterDate] [nchar](19) NOT NULL,
[COM_CdcActive] [tinyint] NOT NULL,
[COM_CdcStatus] [tinyint] NULL,
[COM_CdcDeleteDate] [nchar](19) NULL,
[ACC_FinancialYearID] [smallint] NULL,
[TBL_UserID] [int] NOT NULL,
CONSTRAINT [PK_COM_CommercialDocumentCustomer] PRIMARY KEY CLUSTERED
(
[COM_CdcID] 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
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdcLapseDate] DEFAULT (' ') FOR [COM_CdcLapseDate]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdcLapseReason] DEFAULT (N'''') FOR [COM_CdcLapseReason]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdcWin_1] DEFAULT ((0)) FOR [COM_CdcWin]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdcFinalGrade] DEFAULT ((0)) FOR [COM_CdcFinalGrade]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdcContractPrice_1] DEFAULT ((0)) FOR [COM_CdcContractPrice]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdcDeliveryType] DEFAULT (' ') FOR [COM_CdcDeliveryType]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdMasterMinuteDate_1] DEFAULT (' ') FOR [COM_CdcMasterMinuteDate]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdMasterMinuteNo_1] DEFAULT ((0)) FOR [COM_CdcMasterMinuteNo]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdMasterRowNo_1] DEFAULT ('0') FOR [COM_CdcMasterRowNo]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdCommitteeDate_1] DEFAULT (' ') FOR [COM_CdcCommitteeDate]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdCommitteeMinuteNo_1] DEFAULT ((0)) FOR [COM_CdcCommitteeMinuteNo]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdCommitteeRowNo_1] DEFAULT ('0') FOR [COM_CdcCommitteeRowNo]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentCustomer] ADD CONSTRAINT [DF_COM_CommercialDocumentCustomer_COM_CdcActive] DEFAULT ((1)) FOR [COM_CdcActive]
GO
-----------------------------------------
INSERT INTO [DOCDb].[dbo].[COM_CommercialDocumentCustomer]
VALUES
(268,15571 ,171,'0','' ,'' ,0,0.000,0,'' ,'','' ,'' ,'0'
,'0','','0','0' ,'',1,'1390/04/04 11:53:12',1 ,0,'',1390 ,84)
INSERT INTO [DOCDb].[dbo].[COM_CommercialDocumentCustomer]
VALUES
(269,15574 ,171,'0','' ,'' ,0,0.000,0,'' ,'','' ,'' ,'0'
,'0','','0','0' ,'',1,'1390/04/04 11:53:40',1 ,0,'',1390 ,84)
INSERT INTO [DOCDb].[dbo].[COM_CommercialDocumentCustomer]
VALUES
(270,15572 ,171,'0','' ,'' ,0,0.000,130,'' ,'','' ,'' ,'0'
,'0','','0','0' ,'',1,'1390/04/04 11:54:00',1 ,0,'',1390 ,84)
INSERT INTO [DOCDb].[dbo].[COM_CommercialDocumentCustomer]
VALUES
(271,15573 ,171,'0','' ,'' ,0,0.000,0,'' ,'','' ,'' ,'0'
,'0','','0','0' ,'',1,'1390/04/04 11:57:23',1 ,0,'',1390 ,84)
------------------------------------------------------------------
CREATE TABLE [dbo].[COM_CommercialDocumentDetail](
[COM_CddID] [bigint] NOT NULL,
[COM_CdID_fk] [bigint] NOT NULL,
[WHS_GoodsID_fk] [char](20) NOT NULL,
[COM_ServiceID_fk] [int] NOT NULL,
[COM_CddQuantity] [numeric](18, 0) NULL,
[COM_CddPrice] [numeric](27, 0) NULL,
[COM_CddTechnicalDescription] [nvarchar](150) NULL,
[COM_CddExpertName] [nvarchar](200) NULL,
[COM_CddExpertPrice] [bigint] NULL,
[COM_CddExpertCost] [bigint] NULL,
[COM_CddNote] [nvarchar](1000) NULL,
[COM_CddRegisterDate] [nchar](19) NOT NULL,
[COM_CddActive] [tinyint] NOT NULL,
[COM_CddStatus] [tinyint] NULL,
[COM_CddType] [tinyint] NULL,
[COM_CddDeleteDate] [nchar](19) NULL,
[ACC_FinancialYearID] [smallint] NULL,
[TBL_UserID] [int] NOT NULL,
CONSTRAINT [PK_COM_CommercialDocumentDetail] PRIMARY KEY CLUSTERED
(
[COM_CddID] 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
ALTER TABLE [dbo].[COM_CommercialDocumentDetail] ADD CONSTRAINT [DF_COM_CommercialDocumentDetail_COM_CddExpertName] DEFAULT ('') FOR [COM_CddExpertName]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentDetail] ADD CONSTRAINT [DF_COM_CommercialDocumentDetail_COM_CddExpertPrice_1] DEFAULT ((0)) FOR [COM_CddExpertPrice]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentDetail] ADD CONSTRAINT [DF_COM_CommercialDocumentDetail_COM_CddExpertCost_1] DEFAULT ((0)) FOR [COM_CddExpertCost]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentDetail] ADD CONSTRAINT [DF_COM_CommercialDocumentDetail_COM_CddActive] DEFAULT ((1)) FOR [COM_CddActive]
GO
--------------------------------------
INSERT INTO [dbo].[COM_CommercialDocumentDetail]
VALUES
(207 ,171,'9900018' ,128001,1,0 ,'' ,'' ,0 ,0 ,'','1390/03/23' ,1,0,0,'900009',1390 ,84)
---------------------------------------------------------
CREATE TABLE [dbo].[COM_CommercialDocumentSDetail](
[COM_CdsdID] [bigint] NOT NULL,
[COM_CddID_fk] [bigint] NOT NULL,
[COM_CdcID_fk] [bigint] NOT NULL,
[COM_CdsdPrice] [numeric](27, 3) NULL,
[COM_CdsdGrade] [numeric](27, 3) NULL,
[COM_CdsdWin] [tinyint] NULL,
[COM_CdsdNote] [nvarchar](1000) NULL,
[COM_CdsdType] [tinyint] NULL,
[COM_CdsdRegisterDate] [nchar](19) NOT NULL,
[COM_CdsdActive] [tinyint] NOT NULL,
[COM_CdsdStatus] [tinyint] NULL,
[COM_CdsdDeleteDate] [nchar](19) NULL,
[ACC_FinancialYearID] [smallint] NULL,
[TBL_UserID] [int] NOT NULL,
CONSTRAINT [PK_COM_CommercialDocumentSubDetail] PRIMARY KEY CLUSTERED
(
[COM_CdsdID] 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
ALTER TABLE [dbo].[COM_CommercialDocumentSDetail] ADD CONSTRAINT [DF_COM_CommercialDocumentSDetail_COM_CdsdPrice] DEFAULT ((0)) FOR [COM_CdsdPrice]
GO
ALTER TABLE [dbo].[COM_CommercialDocumentSDetail] ADD CONSTRAINT [DF_COM_CommercialDocumentSDetail_COM_CdsdGrade] DEFAULT ((0)) FOR [COM_CdsdGrade]
GO
--------------------------------------
INSERT INTO [dbo].[COM_CommercialDocumentSDetail]
VALUES
(3768 ,207 ,268,150.000,0.000,2,'',0 ,'Jun 25 2011 11:34AM',1 ,0,'',1390,1)
INSERT INTO [dbo].[COM_CommercialDocumentSDetail]
VALUES
(3769 ,207 ,269,170.000,0.000,2,'',0 ,'Jun 25 2011 11:46AM',1 ,0,'',1390,1)
INSERT INTO [dbo].[COM_CommercialDocumentSDetail]
VALUES
(3770 ,207 ,270,130.000,0.000,2,'',0 ,'Jun 25 2011 11:46AM',1 ,0,'',1390,1)
INSERT INTO [dbo].[COM_CommercialDocumentSDetail]
VALUES
(3771 ,207 ,271,200.000,0.000,2,'',0 ,'Jun 25 2011 11:49AM',1 ,0,'',1390,1)
----------------------------------------------------------------
CREATE TABLE [dbo].[COM_Service](
[COM_ServiceID] [int] NOT NULL,
[COM_ServiceParentID_fk] [int] NULL,
[COM_ScID_fk] [int] NOT NULL,
[WHS_GuID_fk] [int] NOT NULL,
[COM_ServiceSubject] [nvarchar](200) NULL,
[COM_ServicePrice] [int] NULL,
[COM_ServiceNote] [nvarchar](1000) NULL,
[COM_ServiceRegisterDate] [nchar](19) NOT NULL,
[COM_ServiceType] [tinyint] NULL,
[COM_ServiceActive] [tinyint] NOT NULL,
[COM_ServiceStatus] [tinyint] NULL,
[COM_ServiceDeleteDate] [nchar](19) NULL,
[ACC_FinancialYearID] [smallint] NULL,
[TBL_UserID] [int] NOT NULL,
CONSTRAINT [PK_COM_Service] PRIMARY KEY CLUSTERED
(
[COM_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
ALTER TABLE [dbo].[COM_Service] ADD CONSTRAINT [DF_COM_Service_COM_ServiceActive] DEFAULT ((1)) FOR [COM_ServiceActive]
GO
---------------------------------
INSERT INTO [dbo].[COM_Service]
VALUES
(128001,0,128 ,0 ,'???? ???? ? ??????? ???',128001 ,'' ,'1390/04/04' ,0 ,1 ,0 ,'',1390 ,1)
-------------------------------------
CREATE TABLE [dbo].[TBL_Customer](
[TBL_CustomerID] [bigint] NOT NULL,
[TBL_CcID_fk] [int] NOT NULL,
[TBL_CustomerTitle] [nvarchar](1000) NULL,
[TBL_CustomerEconomyCode] [nvarchar](20) NULL,
[TBL_CustomerNotice] [tinyint] NULL,
[TBL_CustomerTel] [nvarchar](200) NULL,
[TBL_CustomerFax] [nvarchar](200) NULL,
[TBL_CustomerAddress] [nvarchar](1000) NULL,
[TBL_CustomerPostalCode] [nvarchar](100) NULL,
[TBL_CustomerOldNo] [bigint] NOT NULL,
[TBL_CustomerEmail] [nvarchar](100) NULL,
[TBL_CustomerWebsite] [nvarchar](100) NULL,
[TBL_CustomerBankAccount] [nvarchar](100) NULL,
[TBL_CustomerUpdateDate] [nchar](10) NULL,
[TBL_CustomerNote] [nvarchar](1000) NULL,
[TBL_CustomerType] [tinyint] NULL,
[TBL_CustomerRegisterDate] [nchar](19) NOT NULL,
[TBL_CustomerActive] [tinyint] NOT NULL,
[TBL_CustomerStatus] [tinyint] NULL,
[TBL_CustomerDeleteDate] [nchar](19) NULL,
[ACC_FinancialYearID] [smallint] NULL,
[TBL_UserID] [int] NOT NULL,
CONSTRAINT [PK_TBL_Customer] PRIMARY KEY CLUSTERED
(
[TBL_CustomerID] 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
ALTER TABLE [dbo].[TBL_Customer] ADD CONSTRAINT [DF_TBL_Customer_TBL_CcID_fk] DEFAULT ((0)) FOR [TBL_CcID_fk]
GO
ALTER TABLE [dbo].[TBL_Customer] ADD CONSTRAINT [DF_TBL_Customer_TBL_CustomerCompanyAddress] DEFAULT ('') FOR [TBL_CustomerAddress]
GO
ALTER TABLE [dbo].[TBL_Customer] ADD CONSTRAINT [DF_TBL_Customer_TBL_CustomerOldNo] DEFAULT ((0)) FOR [TBL_CustomerOldNo]
GO
ALTER TABLE [dbo].[TBL_Customer] ADD CONSTRAINT [DF_TBL_Customer_TBL_CustomerActive] DEFAULT ((1)) FOR [TBL_CustomerActive]
GO
-------------------------------------------------------
INSERT INTO [dbo].[TBL_Customer]
VALUES
(15571,11 ,'???? ??????? ????? ???? ????????' ,'',0 ,'' ,'' ,'' ,'',0 ,'' ,'' ,'' ,'' ,'',0 ,'1390/04/04 11:22:59' ,1,0,'' ,1390,36)
INSERT INTO [dbo].[TBL_Customer]
VALUES
(15572,11 ,'???? ???????? ???? ????? ?????' ,'',0 ,'' ,'' ,'' ,'',0 ,'' ,'' ,'' ,'' ,'',0 ,'1390/04/04 11:22:59' ,1,0,'' ,1390,36)
INSERT INTO [dbo].[TBL_Customer]
VALUES
(15573,11 ,'???? ???????? ???? ?????' ,'',0 ,'' ,'' ,'' ,'',0 ,'' ,'' ,'' ,'' ,'',0 ,'1390/04/04 11:22:59' ,1,0,'' ,1390,36)
INSERT INTO [dbo].[TBL_Customer]
VALUES
(15574,11 ,'???? ???? ?????? ????? ?????' ,'',0 ,'' ,'' ,'' ,'',0 ,'' ,'' ,'' ,'' ,'',0 ,'1390/04/04 11:22:59' ,1,0,'' ,1390,36)
-----------------------------------------------------
CREATE TABLE [dbo].[WHS_Goods](
[WHS_GoodsID] [char](20) NOT NULL,
[WHS_GoodsParentID_fk] [char](20) NULL,
[WHS_GuID_fk] [int] NOT NULL,
[WHS_GcID_fk] [int] NOT NULL,
[ASS_AcID_fk] [bigint] NOT NULL,
[WHS_GstID_fk] [smallint] NOT NULL,
[WHS_GoodsFarsiName] [nvarchar](200) NULL,
[WHS_GoodsEnglishName] [nvarchar](200) NULL,
[WHS_GoodsModel] [nvarchar](200) NULL,
[WHS_GoodsKeepCondition] [nvarchar](1000) NULL,
[WHS_GoodsSimilar] [nvarchar](1000) NULL,
[WHS_GoodsCurrentPrice] [numeric](18, 0) NULL,
[WHS_OldGoodsID] [char](20) NULL,
[WHS_OldGoodsFarsiName] [nvarchar](200) NULL,
[WHS_OldGoodsUnitId] [int] NOT NULL,
[WHS_GoodsNote] [nvarchar](1000) NULL,
[WHS_GoodsRegisterDate] [nchar](19) NOT NULL,
[WHS_GoodsType] [tinyint] NULL,
[WHS_GoodsActive] [tinyint] NOT NULL,
[WHS_GoodsStatus] [tinyint] NULL,
[WHS_GoodsDeleteDate] [nchar](19) NULL,
[ACC_FinancialYearID] [smallint] NULL,
[TBL_UserID] [int] NOT NULL,
CONSTRAINT [PK_Goods] PRIMARY KEY CLUSTERED
(
[WHS_GoodsID] 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
ALTER TABLE [dbo].[WHS_Goods] ADD CONSTRAINT [DF_WHS_Goods_WHS_OldGoodsUnitId] DEFAULT ((0)) FOR [WHS_OldGoodsUnitId]
GO
ALTER TABLE [dbo].[WHS_Goods] ADD CONSTRAINT [DF_WHS_Goods_WHS_GoodsActive] DEFAULT ((1)) FOR [WHS_GoodsActive]
GO
---------------------------------------
INSERT INTO [DOCDb].[dbo].[WHS_Goods]
VALUES
('9900018' ,'9300173' ,15 ,111,3959090900,3 ,'???? ???? ? ? ???? 20????? ??? '
,'','','35','250' ,0,'9900018','???? ???? ? ? ???? 20????? ??? ',0,''
,'0',1 ,1,0,'9900018' ,1388 ,1)
----------------------------------------------
CREATE TABLE [dbo].[WHS_GoodsUnit](
[WHS_GuID] [int] NOT NULL,
[WHS_GuName] [nvarchar](100) NULL,
[WHS_GuNote] [nvarchar](700) NULL,
[WHS_GuRegisterDate] [nchar](19) NOT NULL,
[WHS_GuType] [tinyint] NULL,
[WHS_GuActive] [tinyint] NOT NULL,
[WHS_GuStatus] [tinyint] NULL,
[WHS_GuDeleteDate] [nchar](19) NULL,
[ACC_FinancialYearID] [smallint] NULL,
[TBL_UserID] [int] NOT NULL,
CONSTRAINT [PK_WHS_GoodsUnit] PRIMARY KEY CLUSTERED
(
[WHS_GuID] 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
ALTER TABLE [dbo].[WHS_GoodsUnit] ADD CONSTRAINT [DF_WHS_GoodsUnit_WHS_GuActive] DEFAULT ((1)) FOR [WHS_GuActive]
GO
-------------------------------------------------------
INSERT INTO [dbo].[WHS_GoodsUnit]
VALUES
(15,'???' ,'','0',1,1,0,'',1390 ,1)
-----------------------------------------------------
CREATE VIEW [dbo].[V_COM_Cdc]
AS
SELECT TOP (100) PERCENT dbo.COM_CommercialDocumentDetail.COM_CdID_fk, dbo.COM_CommercialDocumentDetail.WHS_GoodsID_fk,
dbo.COM_CommercialDocumentDetail.COM_CddQuantity, dbo.COM_CommercialDocumentDetail.COM_ServiceID_fk,
dbo.COM_CommercialDocumentDetail.COM_CddPrice, dbo.COM_CommercialDocumentDetail.COM_CddTechnicalDescription,
dbo.COM_Commercialdocument.COM_CdCode, dbo.WHS_Goods.WHS_GoodsFarsiName, dbo.WHS_Goods.WHS_GoodsEnglishName,
dbo.WHS_Goods.WHS_GoodsModel, dbo.WHS_GoodsUnit.WHS_GuName, dbo.COM_Commercialdocument.COM_CdRowNo,
dbo.COM_Commercialdocument.COM_CdApproveNo, dbo.COM_Commercialdocument.COM_CdTechnicalFinancialDate,
dbo.COM_Commercialdocument.COM_CdTechnicalDate, dbo.COM_Commercialdocument.COM_CdMinimumMark,
dbo.COM_Commercialdocument.COM_CdFinancialControl, dbo.COM_Commercialdocument.COM_CdGuaranteePrice,
dbo.COM_Commercialdocument.COM_CdEstimatedPrice, dbo.COM_Commercialdocument.COM_CdOpenDate,
dbo.COM_Commercialdocument.COM_CdOpenDateLetterG, dbo.COM_Commercialdocument.COM_CdEditeDate,
dbo.COM_Commercialdocument.COM_CdDistributedDate, dbo.COM_Commercialdocument.COM_CdDescription, dbo.COM_Commercialdocument.COM_CdCondition,
dbo.COM_Commercialdocument.COM_CdsID_fk, dbo.COM_Commercialdocument.TBL_EditoreID_fk, dbo.COM_CommercialDocumentCustomer.COM_CdcID,
dbo.WHS_Goods.WHS_GoodsID, dbo.COM_CommercialDocumentSDetail.COM_CdsdPrice, dbo.COM_CommercialDocumentSDetail.COM_CdcID_fk,
dbo.COM_CommercialDocumentSDetail.COM_CddID_fk, dbo.TBL_Customer.TBL_CustomerTitle, dbo.TBL_Customer.TBL_CustomerTel,
dbo.TBL_Customer.TBL_CustomerFax, dbo.TBL_Customer.TBL_CustomerAddress, dbo.TBL_Customer.TBL_CustomerPostalCode, dbo.COM_Service.WHS_GuID_fk,
dbo.COM_Service.COM_ServiceSubject, dbo.COM_Service.COM_ServicePrice, dbo.COM_Service.COM_ServiceNote, dbo.COM_Service.COM_ServiceID,
dbo.COM_CommercialDocumentCustomer.COM_CdcDeliveryType, dbo.COM_CommercialDocumentCustomer.COM_CdcPriceValidityTime,
dbo.COM_CommercialDocumentCustomer.COM_CdcDescription, dbo.COM_CommercialDocumentCustomer.COM_CdcStatus,
dbo.COM_CommercialDocumentSDetail.COM_CdsdWin, dbo.COM_CommercialDocumentSDetail.COM_CdsdGrade,
dbo.COM_CommercialDocumentCustomer.COM_CdcLapseDate, dbo.COM_CommercialDocumentCustomer.COM_CdcFactureNo,
dbo.COM_CommercialDocumentCustomer.COM_CdcLapseReason, dbo.COM_Commercialdocument.WHS_GcID_fk, dbo.COM_Commercialdocument.TBL_AdviserID_fk,
dbo.COM_Commercialdocument.CNT_CtID_fk, dbo.COM_Commercialdocument.COM_ScID_fk,
dbo.COM_CommercialDocumentCustomer.TBL_CustomerID_fk, dbo.COM_Commercialdocument.COM_CdID, dbo.COM_CommercialDocumentSDetail.COM_CdsdID,
dbo.COM_Commercialdocument.COM_CdAppendixNo, dbo.COM_Commercialdocument.COM_CdApprovedStartDate,
dbo.COM_Commercialdocument.COM_CdWinType, dbo.COM_Commercialdocument.TBL_FormID_fk, dbo.COM_Commercialdocument.COM_CdBuyPrice,
dbo.COM_Commercialdocument.COM_CdtID_fk,
dbo.COM_Commercialdocument.COM_CdOpenTimeLetterG, dbo.COM_Commercialdocument.ACC_FinancialYearID,
dbo.COM_Commercialdocument.COM_CdInvitationDate, dbo.COM_Commercialdocument.COM_CdInvitationNo, dbo.COM_Commercialdocument.TBL_PlaceID_fk,
dbo.COM_CommercialDocumentCustomer.COM_CdcWin, dbo.COM_Commercialdocument.COM_CdAcceptDate, dbo.COM_Commercialdocument.COM_CdStageNo,
dbo.COM_CommercialDocumentCustomer.COM_CdcFinalGrade, dbo.COM_CommercialDocumentCustomer.COM_CdcContractPrice,
dbo.COM_CommercialDocumentCustomer.COM_CdcMasterMinuteDate, dbo.COM_CommercialDocumentCustomer.COM_CdcMasterMinuteNo,
dbo.COM_CommercialDocumentCustomer.COM_CdcMasterRowNo, dbo.COM_CommercialDocumentCustomer.COM_CdcCommitteeDate,
dbo.COM_CommercialDocumentCustomer.COM_CdcCommitteeMinuteNo, dbo.COM_CommercialDocumentCustomer.COM_CdcCommitteeRowNo,
dbo.COM_Commercialdocument.COM_CdDeliveryPlaceName, dbo.COM_Commercialdocument.COM_CdExecuteTimeType,
dbo.COM_Commercialdocument.COM_CdExecuteTime, dbo.COM_Commercialdocument.COM_CdGuarantyCycle,
dbo.COM_Commercialdocument.COM_CdApprovedEndDate, dbo.COM_CommercialDocumentSDetail.COM_CdsdNote, dbo.COM_Commercialdocument.COM_CdNote,
dbo.COM_Commercialdocument.TBL_DepartmentID_fk, dbo.COM_CommercialDocumentDetail.COM_CddID
FROM dbo.COM_Service INNER JOIN
dbo.COM_CommercialDocumentDetail INNER JOIN
dbo.COM_CommercialDocument ON dbo.COM_CommercialDocumentDetail.COM_CdID_fk = dbo.COM_Commercialdocument.COM_CdID INNER JOIN
dbo.COM_CommercialDocumentSDetail ON dbo.COM_CommercialDocumentDetail.COM_CddID = dbo.COM_CommercialDocumentSDetail.COM_CddID_fk INNER JOIN
dbo.WHS_Goods ON dbo.COM_CommercialDocumentDetail.WHS_GoodsID_fk = dbo.WHS_Goods.WHS_GoodsID INNER JOIN
dbo.WHS_GoodsUnit ON dbo.WHS_Goods.WHS_GuID_fk = dbo.WHS_GoodsUnit.WHS_GuID ON
dbo.COM_Service.COM_ServiceID = dbo.COM_CommercialDocumentDetail.COM_ServiceID_fk INNER JOIN
dbo.TBL_Customer INNER JOIN
dbo.COM_CommercialDocumentCustomer ON dbo.TBL_Customer.TBL_CustomerID = dbo.COM_CommercialDocumentCustomer.TBL_CustomerID_fk ON
dbo.COM_CommercialDocumentSDetail.COM_CdcID_fk = dbo.COM_CommercialDocumentCustomer.COM_CdcID
WHERE (dbo.COM_Commercialdocument.COM_CdID = 166) AND (dbo.COM_CommercialDocumentSDetail.COM_CdsdWin = 1)
--------------------------------------------------------
May 2, 2012 at 6:29 am
elham_azizi_62 (5/1/2012)
hello all.I wrote this code with alter table and add columns.but i need to replace this code with pivot and i don't know how to do it?pleas help me and qine me script.thanks.
...
There's probably a whole day's worth of work here, assuming that it's possible. Before encouraging anyone to commence, what benefit might you expect from replacing this script with the PIVOT alternative?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 4, 2012 at 10:18 pm
hello cadavre.i put script of tables and insert into tables.please help me that rewrite my procedure with pivot by use view v_com_cdc.thanks
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply