how to remove null and minus symbol in PIVOT

Question

Post reply

how to remove null and minus symbol in PIVOT

vanapandi

SSC Enthusiast

Points: 120
More actions
February 15, 2013 at 3:41 am

#270968

here i produced all DDL and Query with result.
i want to remove minus values and to replace minus value by 0 or nill.
same way i want to remove null values into 0 or Nil.
GO
/****** Object: Table [dbo].[SalesOrder] Script Date: 02/15/2013 16:03:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SalesOrder](
[SalesOrderID] [int] NOT NULL,
[dcr_id] [int] NOT NULL,
[leadID] [int] NOT NULL,
[Qtnhead_id] [int] NOT NULL,
[Revisedqtnno] [int] NOT NULL,
[OrderDate] [datetime] NULL,
[RequiredDate] [datetime] NULL,
[ShippedDate] [datetime] NULL,
[CustomerPONumber] [nvarchar](25) NULL,
[SalesOrderStatus] [varchar](10) NULL,
[CustomerName] [nvarchar](200) NULL,
[ExecutiveName] [nvarchar](100) NULL,
[Billto_Name] [varchar](300) NULL,
[BillToAddress] [varchar](max) NULL,
[Bill_Addressid] [int] NULL,
[Billto_ContactName] [varchar](300) NULL,
[Billto_line1] [nvarchar](max) NULL,
[Billto_line2] [nvarchar](max) NULL,
[Billto_line3] [varchar](max) NULL,
[Billto_City] [varchar](300) NULL,
[Billlto_Region] [varchar](300) NULL,
[Billto_state] [varchar](300) NULL,
[Billto_country] [nvarchar](25) NULL,
[Billto_Phone1] [varchar](100) NULL,
[Billto_Phone2] [varchar](100) NULL,
[Billto_email] [varchar](100) NULL,
[Billto_fax] [varchar](100) NULL,
[Billto_mobile] [varchar](100) NULL,
[ShipToAddress] [varchar](max) NULL,
[Shipto_Name] [nvarchar](100) NULL,
[Shipto_Addressid] [int] NULL,
[Shipto_contactName] [varchar](100) NULL,
[Shipto_line1] [nvarchar](max) NULL,
[Shipto_line2] [nvarchar](max) NULL,
[shipto_line3] [nvarchar](max) NULL,
[Shipto_City] [varchar](100) NULL,
[Shipto_Region] [nvarchar](25) NULL,
[Shipto_State] [nvarchar](25) NULL,
[Shipto_Country] [nvarchar](25) NULL,
[ShipVia] [nvarchar](15) NULL,
[Shipto_Phone1] [varchar](100) NULL,
[Shipto_Phone2] [varchar](100) NULL,
[Shipto_email] [varchar](100) NULL,
[shipto_fax] [varchar](100) NULL,
[Shipto_mobile] [varchar](100) NULL,
[TotalDiscountAmount] [money] NULL,
[DiscountPercentage] [money] NULL,
[TotalTax] [money] NULL,
[FreightAmount] [money] NULL,
[TotalLineItemAmount] [money] NULL,
[TotalLineItemDiscountAmount] [money] NULL,
[OrderCondtion1] [varchar](max) NULL,
[OrderCondition2] [varchar](max) NULL,
[PaymentCondition1] [varchar](max) NULL,
[PaymentCondition2] [varchar](max) NULL,
[PaymentCondition3] [varchar](max) NULL,
[OrderNote1] [varchar](max) NULL,
[OrderNote2] [varchar](max) NULL,
[Modifiedon] [datetime] NULL,
[Modifiedby] [nvarchar](50) NULL,
[Createdon] [datetime] NULL,
[Createdby] [nvarchar](50) NULL,
CONSTRAINT [pk_SalesOrder_qtnhead_qtnrevised] PRIMARY KEY CLUSTERED
(
[SalesOrderID] ASC,
[Qtnhead_id] ASC,
[Revisedqtnno] 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
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (1, 2147, 1, 1487, 1, CAST(0x0000A0BF00000000 AS DateTime), CAST(0x0000A0C300000000 AS DateTime), NULL, N'1/2012-13', N'A', N'SRF EBIZ LTD', N'VENKATARAMAN', N'SRF EBIZ LTD', N'AMAR SUDAR 3RD FLOOR NO,43, PANTHEION ROAD, EGMORE, CHENNAI - 008', NULL, N'MR. K. CHANDRA SEKARAN', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044 2841 2652', N'044 2841 2652', N'kchandra@srfebiz.com', N'044 4214 7180', N'98412 76500', N'AMAR SUDAR 3RD FLOOR NO,43, PANTHEION ROAD, EGMORE, CHENNAI - 008', N'SRF EBIZ LTD', NULL, N'MR. K. CHANDRA SEKARAN', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044 2841 2652', N'044 2841 2652', N'kchandra@srfebiz.com', N'', N'98412 76500', 0.0000, 0.0000, 0.0000, 0.0000, 23516.6500, 2663.3500, N'', N'', N'', N'', N'', N'', N' ', CAST(0x0000A0C200000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A0C200B8D850 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (2, 2753, 1, 1525, 2, CAST(0x0000A0DA00000000 AS DateTime), CAST(0x0000A0E200000000 AS DateTime), NULL, N'1525/2012/09/13', N'A', N'Evanjaline', N'JAYARAMAN', N'Evanjaline', N'lakshmi nagar', NULL, N'vanapandi', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04467899020', N'', N'eva@gmail.com', N'', N'8790456382', N'lakshmi nagar', N'Evanjaline', NULL, N'vanapandi', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04467899020', N'', N'eva@gmail.com', N'', N'8790456382', 0.0000, 0.0000, 0.0000, 0.0000, 22470.0000, 0.0000, N'', N'', N'', N'', N'', N'', N' ', CAST(0x0000A0CB00000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A0CB010E1374 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (3, 1548, 1, 1505, 0, CAST(0x0000A0CA00000000 AS DateTime), CAST(0x0000A0DB00000000 AS DateTime), NULL, N'1505/201213', N'A', N'VEL TECH UNIVERCITY', N'VIJAY', N'VEL TECH UNIVERCITY', N'VELTECH NAGAR AVADI', NULL, N'MR.SURESH', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044 26841348', N'NILL', N'VELSPURCHASE@GMAIL.COM', N'044 26841348', N'9789869418', N'VELTECH NAGAR AVADI', N'VEL TECH UNIVERCITY', NULL, N'MR.SURESH', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044 26841348', N'NILL', N'VELSPURCHASE@GMAIL.COM', N'', N'9789869418', 0.0000, 0.0000, 0.0000, 0.0000, 14950.0000, 0.0000, N'', N'3. vat 14.5% will ne added from the above rates', N'50% advance along with the purchase order', N'50% against delivery.', N'', N'', N' ', CAST(0x0000A0CD00000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A0CD00CEF7FC AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (5, 1205, 1, 1476, 0, CAST(0x0000A0C200000000 AS DateTime), CAST(0x0000A0DB00000000 AS DateTime), NULL, N'1476/201213', N'A', N'HANIL AUTOMTIVE INDIA PVT LTD, PLANT I', N'VENKATARAMAN', N'HANIL AUTOMTIVE INDIA PVT LTD, PLANT I', N'NO, A-B, SIPCOT INDUSTRIAL PARK INRRUNGATTUKOTTAI SRIPERUMPUDHUR TK KANCHEEPURAM DIST, TAMIL NADU 602 105', NULL, N'MR. NANDHA KUMAR', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044 371712653', N'NILL', N'nandhu@hanilauto.in', N'044 2715 6366', N'98413 37626', N'NO, A-B, SIPCOT INDUSTRIAL PARK INRRUNGATTUKOTTAI SRIPERUMPUDHUR TK KANCHEEPURAM DIST, TAMIL NADU 602 105', N'HANIL AUTOMTIVE INDIA PVT LTD, PLANT I', NULL, N'MR. NANDHA KUMAR', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044 371712653', N'NILL', N'nandhu@hanilauto.in', N'', N'98413 37626', 0.0000, 0.0000, 0.0000, 0.0000, 57050.0000, 0.0000, N'30 days', N'3. Vat @ 14.5% will be charged extra.', N'50% Advance payment along with purchase order', N'Balance against delivery.', N'', N'', N' ', CAST(0x0000A0CD00000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A0CD00EF81AC AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (6, 1315, 1, 1490, 0, CAST(0x0000A0D100000000 AS DateTime), CAST(0x0000A0DA00000000 AS DateTime), NULL, N'123/12-13', N'A', N'JAY USHIN LIMITED', N'VENKATARAMAN', N'JAY USHIN LIMITED', N'D-1/2,SIPCOT INDUSTRIAL PARK IRUNGULAM VILLAGE SRIPERUMPUDHUR TALUK 602 106', NULL, N'K.V. PRABU DOSS', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044 37105658', N'0443710 2020', N'jaushinchennai@gmail>com', N'NILL', N'9894330512', N'D-1/2,SIPCOT INDUSTRIAL PARK IRUNGULAM VILLAGE SRIPERUMPUDHUR TALUK 602 106', N'JAY USHIN LIMITED', NULL, N'K.V. PRABU DOSS', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044 37105658', N'0443710 2020', N'jaushinchennai@gmail>com', N'', N'9894330512', 0.0000, 0.0000, 0.0000, 0.0000, 44255.0000, 0.0000, N'', N'3. Vat @ 14.5% will be charged extra', N'75% advance payment', N'Balance against delivary', N'', N'', N' ', CAST(0x0000A0D000000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A0D000B2EE40 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (7, 920, 1, 1526, 0, CAST(0x0000A0D200000000 AS DateTime), CAST(0x0000A0DB00000000 AS DateTime), NULL, N'920/1213', N'P', N'Bright metal finisher', N'ACC0UNTS', N'Bright metal finisher', N'C19,Sipcot Industrial Park,Sriperumpudur Talk, Kanchipuram, dist,602 105', NULL, N'mr.balamurugan (manager)', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044-22756029', N'nil', N'jai@brightmetalfinisher.com', N'nil', N'9841098133', N'C19,Sipcot Industrial Park,Sriperumpudur Talk, Kanchipuram, dist,602 105', N'Bright metal finisher', NULL, N'mr.balamurugan (manager)', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044-22756029', N'nil', N'jai@brightmetalfinisher.com', N'', N'9841098133', 0.0000, 0.0000, 0.0000, 0.0000, 8000.0000, 0.0000, N'', N'', N'', N'', N'', N'', N' ', NULL, NULL, CAST(0x0000A0D000BBCE84 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (8, 920, 1, 1526, 0, CAST(0x0000A0D200000000 AS DateTime), CAST(0x0000A0DB00000000 AS DateTime), NULL, N'920/1213', N'P', N'Bright metal finisher', N'ACC0UNTS', N'Bright metal finisher', N'C19,Sipcot Industrial Park,Sriperumpudur Talk, Kanchipuram, dist,602 105', NULL, N'mr.balamurugan (manager)', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044-22756029', N'', N'jai@brightmetalfinisher.com', N'', N'9841098133', N'C19,Sipcot Industrial Park,Sriperumpudur Talk, Kanchipuram, dist,602 105', N'Bright metal finisher', NULL, N'mr.balamurugan (manager)', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044-22756029', N'', N'jai@brightmetalfinisher.com', N'', N'9841098133', 0.0000, 0.0000, 0.0000, 0.0000, 8000.0000, 0.0000, N'', N'', N'', N'', N'', N'', N' ', NULL, NULL, CAST(0x0000A0D000BBDEEC AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (9, 920, 1, 1526, 0, CAST(0x0000A0D200000000 AS DateTime), CAST(0x0000A0DB00000000 AS DateTime), NULL, N'920/1213', N'P', N'Bright metal finisher', N'ACC0UNTS', N'Bright metal finisher', N'C19,Sipcot Industrial Park,Sriperumpudur Talk, Kanchipuram, dist,602 105', NULL, N'mr.balamurugan (manager)', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04422756029', N'', N'jai@brightmetalfinisher.com', N'', N'9841098133', N'C19,Sipcot Industrial Park,Sriperumpudur Talk, Kanchipuram, dist,602 105', N'Bright metal finisher', NULL, N'mr.balamurugan (manager)', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04422756029', N'', N'jai@brightmetalfinisher.com', N'', N'9841098133', 0.0000, 0.0000, 0.0000, 0.0000, 8000.0000, 0.0000, N'', N'', N'', N'', N'', N'', N' ', NULL, NULL, CAST(0x0000A0D000BBF2D8 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (10, 920, 1, 1526, 0, CAST(0x0000A0D200000000 AS DateTime), CAST(0x0000A0DB00000000 AS DateTime), NULL, N'920/1213', N'A', N'Bright metal finisher', N'ACC0UNTS', N'Bright metal finisher', N'C19,Sipcot Industrial Park,Sriperumpudur Talk, Kanchipuram, dist,602 105', NULL, N'mr.balamurugan ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04422756029', N'', N'jai@brightmetalfinisher.com', N'', N'9841098133', N'C19,Sipcot Industrial Park,Sriperumpudur Talk, Kanchipuram, dist,602 105', N'Bright metal finisher', NULL, N'mr.balamurugan', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04422756029', N'', N'jai@brightmetalfinisher.com', N'', N'9841098133', 0.0000, 0.0000, 0.0000, 0.0000, 8000.0000, 0.0000, N'', N'', N'', N'', N'', N'', N' ', CAST(0x0000A0D000000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A0D000BC1D08 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (11, 1886, 1, 1475, 0, CAST(0x0000A0C500000000 AS DateTime), CAST(0x0000A0D400000000 AS DateTime), NULL, N'1886/1/2012', N'A', N'SYSTEMATIC CONSCOM LTD', N'VIJAY', N'SYSTEMATIC CONSCOM LTD', N'NO 4/5 BHARATHIYAR ST ,IRUMBULLYUR TAMBARAM, CHENNAI-45', NULL, N'MR.MARIMURUGAN', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044 2226 4380', N'044 3250 5446', N'aelchennai@conscom.in', N'NIL', N'9600087372', N'NO 4/5 BHARATHIYAR ST ,IRUMBULLYUR TAMBARAM, CHENNAI-45', N'SYSTEMATIC CONSCOM LTD', NULL, N'MR.MARIMURUGAN', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044 2226 4380', N'044 3250 5446', N'aelchennai@conscom.in', N'', N'9600087372', 0.0000, 0.0000, 0.0000, 0.0000, 223050.0000, 0.0000, N'', N'3. Vat 2 14.5% will be chrged extra', N'50% advance payment', N'Balance against delivary', N'', N'', N'The partician glass will be removed and kept aside nearer to the area of the partition. shifting is not included. ', CAST(0x0000A0D000000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A0D000CA5ABC AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (12, 2685, 1, 1479, 0, CAST(0x0000A0CA00000000 AS DateTime), CAST(0x0000A0DA00000000 AS DateTime), NULL, N'2685/1/2012', N'A', N'Dhanalakshmi Srinivasan Medical College & Hospital', N'VIJAY', N'Dhanalakshmi Srinivasan Medical College & Hospital', N'Siruvachur, Perambalur– 621 113.', NULL, N'Mrs.KALYANI', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04328327999', N'', N'kalyani-rose15354@yahoo.co.in', N'04328220075', N'9487249982', N'Siruvachur, Perambalur– 621 113.', N'Dhanalakshmi Srinivasan Medical College & Hospital', NULL, N'Mrs.KALYANI', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04328327999', N'', N'kalyani-rose15354@yahoo.co.in', N'', N'9487249982', 0.0000, 0.0000, 0.0000, 0.0000, 1610.0000, 0.0000, N'', N'3. Vat @ 14.5% will be charged extra', N'30% advance along with the purchase order & colour confirmanation.', N'Balance against delivary', N'', N'', N' ', CAST(0x0000A0D000000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A0D000CB1768 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (13, 256, 1, 1256, 0, CAST(0x0000A0C800000000 AS DateTime), CAST(0x0000A0D900000000 AS DateTime), NULL, N'256/1/2012', N'A', N'department of mechancial engineering', N'VIJAY', N'department of mechancial engineering', N'iit, gundy', NULL, N'p.kumar', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04422574650', N'NIL', N'meoffice@iitm.ac.in', N'22574652', N'nil', N'iit, gundy', N'department of mechancial engineering', NULL, N'p.kumar', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04422574650', N'NIL', N'meoffice@iitm.ac.in', N'', N'nil', 0.0000, 0.0000, 0.0000, 0.0000, 50000.0000, 0.0000, N'90 Days', N'3. Inclusive of all taxes', N'As per your Norms', N'', N'', N'', N' ', CAST(0x0000A0D000000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A0D000D88F10 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (14, 2691, 1, 1062, 0, CAST(0x0000A0CA00000000 AS DateTime), CAST(0x0000A0D600000000 AS DateTime), NULL, N'123/Jeeva/12-13', N'A', N'JEEVITHA DECORATOR', N'SAAGAR_VIJAY', N'JEEVITHA DECORATOR', N'315/10.NEAR SARASVATHY THEATRE 7TH ST EXTN, RATHINAPURI, COMBATORE, 641 027', NULL, N'MR.DAMU', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'0422 254 1464', N'NILL', N'damu.guna@gmail.com', N'NILL', N'98434 49464', N'315/10.NEAR SARASVATHY THEATRE 7TH ST EXTN, RATHINAPURI, COMBATORE, 641 027', N'JEEVITHA DECORATOR', NULL, N'MR.DAMU', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'0422 254 1464', N'NILL', N'damu.guna@gmail.com', N'', N'98434 49464', 0.0000, 0.0000, 0.0000, 0.0000, 4896.0000, 864.0000, N'', N'3. Vat 5% will be added from the above rates.', N'50% advance along with the purchase order.', N'50% against delivery', N'', N'', N'1. For special metalic colours like GOLD / SILVER Rs. 250 will be charged extra. ', CAST(0x0000A0D200000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A0D200E4FB88 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (15, 2747, 1, 1518, 1, CAST(0x0000A11100000000 AS DateTime), CAST(0x0000A11A00000000 AS DateTime), NULL, N'123456', N'A', N'HSKINDIA Reddy PVT LIMITED', N'JAYARAMAN', N'HSKINDIA Reddy PVT LIMITED', N'Addrs', NULL, N'Reddy Roa', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04443549876', N'04443549876', N'email@hskindia.co.in', N'04443549876', N'9550078952', N'Addrs', N'HSKINDIA Reddy PVT LIMITED', NULL, N'Reddy Roa', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04443549876', N'04443549876', N'email@hskindia.co.in', N'', N'9550078952', 0.0000, 0.0000, 0.0000, 0.0000, 132500.0000, 0.0000, N'30 day', N'', N'', N'', N'', N'', N'1. 2.', CAST(0x0000A10F00000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A10F00FF47F4 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (16, 2737, 1, 1527, 0, CAST(0x0000A0FB00000000 AS DateTime), CAST(0x0000A11200000000 AS DateTime), NULL, N'223344', N'A', N'MAHA BEAUTY PARLOUR & BEAUTY TRAINING ACADEMY', N'ACC0UNTS', N'MAHA BEAUTY PARLOUR & BEAUTY TRAINING ACADEMY', N'NO 4: C BLOCK, VENKATESA AGRAHARAM STREET
MYLAPORE
CHENNAI 600 004', NULL, N'SELVI', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'NIL', N'NIL', N'mahakannan@yahoo.com', N'NIL', N'+91 9840327770', N'NO 4: C BLOCK, VENKATESA AGRAHARAM STREET
MYLAPORE
CHENNAI 600 004', N'MAHA BEAUTY PARLOUR & BEAUTY TRAINING ACADEMY', NULL, N'SELVI', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'NIL', N'NIL', N'mahakannan@yahoo.com', N'', N'+91 9840327770', 0.0000, 0.0000, 0.0000, 0.0000, 65000.0000, 0.0000, N'', N'', N'', N'', N'', N'', N' ', CAST(0x0000A10F00000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A10F0101DF3C AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (17, 2744, 1, 1511, 0, CAST(0x0000A10300000000 AS DateTime), CAST(0x0000A11300000000 AS DateTime), NULL, N'123451', N'A', N'PANDIAN22', N'JAYARAMAN', N'PANDIAN22', N'poochikadu', NULL, N'VANAPANDIAN', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'03443443434', N'98934848343', N'vanapandi@f.in', N'7867766767', N'9551368080', N'poochikadu', N'PANDIAN22', NULL, N'VANAPANDIAN', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'03443443434', N'98934848343', N'vanapandi@f.in', N'', N'9551368080', 0.0000, 0.0000, 0.0000, 0.0000, 32500.0000, 0.0000, N'', N'', N'', N'', N'', N'', N' ', CAST(0x0000A11000000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A110013034F4 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (18, 2743, 1, 1509, 1, CAST(0x0000A10300000000 AS DateTime), CAST(0x0000A11300000000 AS DateTime), NULL, N'12345000', N'A', N'Abhishek Industries Pvt Ltd', N'JAYARAMAN', N'Abhishek Industries Pvt Ltd', N'Abhishek Industry Address 1', NULL, N'Shankar', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04412345678', N'04412345678', N'shankar@abhishekindustry.com', N'04412345678', N'9500012345', N'Abhishek Industry Address 1', N'Abhishek Industries Pvt Ltd', NULL, N'Shankar', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'04412345678', N'04412345678', N'shankar@abhishekindustry.com', N'', N'9500012345', 0.0000, 0.0000, 0.0000, 0.0000, 193641.2500, 838.7500, N'', N'', N'', N'', N'', N'', N' ', CAST(0x0000A11100000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A11100CCC6A8 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (19, 2661, 1, 1504, 0, CAST(0x0000A10400000000 AS DateTime), CAST(0x0000A11900000000 AS DateTime), NULL, N'54321', N'A', N'CALSONIC KANSEI MOTHERSON AUTO PRODUCTS LIMITED', N'JAYARAMAN', N'CALSONIC KANSEI MOTHERSON AUTO PRODUCTS LIMITED', N'PLOT NO A 4/SIPCOT INDUSTRIAL GROWTH CENTRE; ORAGADAM:', NULL, N'rahkumar', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'+91 9176682040', N'NIL', N'rajkumar@ck.motherson.com', N'NIL', N'+91 91766628035', N'PLOT NO A 4/SIPCOT INDUSTRIAL GROWTH CENTRE; ORAGADAM:', N'CALSONIC KANSEI MOTHERSON AUTO PRODUCTS LIMITED', NULL, N'rahkumar', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'+91 9176682040', N'NIL', N'rajkumar@ck.motherson.com', N'', N'+91 91766628035', 0.0000, 0.0000, 0.0000, 0.0000, 8700.0000, 0.0000, N'', N'3. Vat @ 14.5% will be charged extra', N'100% completion of work', N'', N'', N'', N' ', CAST(0x0000A11600000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A116011937CC AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (20, 2739, 1, 1502, 0, CAST(0x0000A10300000000 AS DateTime), CAST(0x0000A11300000000 AS DateTime), NULL, N'121212', N'A', N'FRITZMEIER MOTHERSON CABIN ENGINEERING LIMITED', N'VENKATARAMAN', N'FRITZMEIER MOTHERSON CABIN ENGINEERING LIMITED', N'NH 4 TO ATHIVAKKAM ROAD
112 SINGADI VAKKAM VILLAGE,
ATTUPUTHUR POST - 631561
KANCHIPURAM DIST & TALUK
TAMIL NADU', NULL, N'Mr. K. RAMASUBBU', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'+91 44 27294396', N'+91 44 27294592', N'k.ramasubbu@fritzmeier.motherson.com', N'NIL', N'+91 9790987852', N'NH 4 TO ATHIVAKKAM ROAD
112 SINGADI VAKKAM VILLAGE,
ATTUPUTHUR POST - 631561
KANCHIPURAM DIST & TALUK
TAMIL NADU', N'FRITZMEIER MOTHERSON CABIN ENGINEERING LIMITED', NULL, N'Mr. K. RAMASUBBU', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'+91 44 27294396', N'+91 44 27294592', N'k.ramasubbu@fritzmeier.motherson.com', N'', N'+91 9790987852', 0.0000, 0.0000, 0.0000, 0.0000, 500.0000, 0.0000, N'', N'3. Vat @ 14.5% will be charged extra', N'100% against completion of work', N'', N'', N'', N' ', CAST(0x0000A11600000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A1160123DB3C AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (21, 2730, 1, 1474, 1, CAST(0x0000A11F00000000 AS DateTime), CAST(0x0000A13600000000 AS DateTime), NULL, N'123456', N'P', N'NIKKI INDIA FUEL SYSTEMS PVT LTD', N'VENKATARAMAN', N'NIKKI INDIA FUEL SYSTEMS PVT LTD', N'32 VENKATANARAYANA ROAD
T. NAGAR
CHENNAI 600 017', NULL, N'Mr. P.TAMIL VANAN & Mr. GENSAKU KONAGAI (MD)', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'99', N'', N'tamiru15@gmail.com', N'+91 8939539315', N'+918516969760', N'32 VENKATANARAYANA ROAD
T. NAGAR
CHENNAI 600 017', N'NIKKI INDIA FUEL SYSTEMS PVT LTD', NULL, N'Mr. P.TAMIL VANAN & Mr. GENSAKU KONAGAI (MD)', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'98939539315', N'', N'tamiru15@gmail.com', N'', N'91851696976', 0.0000, 0.0000, 0.0000, 0.0000, 58176.0000, 2424.0000, N'', N'3. Vat @ 14.5% will be charged extra', N'50% advance payment', N'Balance against delivary', N'', N'', N'For KALPANASAGAR FURN SOLUTION PVT. LTD. ', NULL, NULL, CAST(0x0000A13300C42804 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (22, 2730, 1, 1474, 1, CAST(0x0000A11F00000000 AS DateTime), CAST(0x0000A13600000000 AS DateTime), NULL, N'123456', N'A', N'NIKKI INDIA FUEL SYSTEMS PVT LTD', N'VENKATARAMAN', N'NIKKI INDIA FUEL SYSTEMS PVT LTD', N'32 VENKATANARAYANA ROAD
T. NAGAR
CHENNAI 600 017', NULL, N'Mr. P.TAMIL VANAN ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'99', N'', N'tamiru15@gmail.com', N'+98939539315', N'+918516969760', N'32 VENKATANARAYANA ROAD
T. NAGAR
CHENNAI 600 017', N'NIKKI INDIA FUEL SYSTEMS PVT LTD', NULL, N'Mr. P.TAMIL VANAN', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'98939539315', N'', N'tamiru15@gmail.com', N'', N'91851696976', 0.0000, 0.0000, 0.0000, 0.0000, 58176.0000, 2424.0000, N'', N'3. Vat @ 14.5% will be charged extra', N'50% advance payment', N'Balance against delivary', N'', N'', N'For KALPANASAGAR FURN SOLUTION PVT. LTD. ', CAST(0x0000A13300000000 AS DateTime), N'ACC0UNTS-ACCOUNTS', CAST(0x0000A13300C44B2C AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (23, 1485, 1, 1499, 0, CAST(0x0000A11900000000 AS DateTime), CAST(0x0000A13500000000 AS DateTime), NULL, N'2323', N'P', N'Sharadha motors pvt ltd_1485', N'VENKATARAMAN', N'Sharadha motors pvt ltd_1485', N'G-20,Sipcot Industrial Estate,Irrungattukottai,Sriperumbudur Tk,Kanchipuram Dt,TN-602 117', NULL, N'Mr.sudhakar', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044-37176529', N'044-37176555', N'materialch@sharadamotors.com', N'044-37176555', N'95000 16704', N'G-20,Sipcot Industrial Estate,Irrungattukottai,Sriperumbudur Tk,Kanchipuram Dt,TN-602 117', N'Sharadha motors pvt ltd_1485', NULL, N'Mr.sudhakar', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044-37176529', N'044-37176555', N'materialch@sharadamotors.com', N'', N'95000 16704', 0.0000, 0.0000, 0.0000, 0.0000, 8650.0000, 0.0000, N'', N'3. Vat @ 14.5% will be charged extra', N'50 % advance payment with confirmed purchase order', N'Balance against delivary', N'', N'', N' ', NULL, NULL, CAST(0x0000A13800B79D14 AS DateTime), N'ACC0UNTS-ACCOUNTS')
INSERT [dbo].[SalesOrder] ([SalesOrderID], [dcr_id], [leadID], [Qtnhead_id], [Revisedqtnno], [OrderDate], [RequiredDate], [ShippedDate], [CustomerPONumber], [SalesOrderStatus], [CustomerName], [ExecutiveName], [Billto_Name], [BillToAddress], [Bill_Addressid], [Billto_ContactName], [Billto_line1], [Billto_line2], [Billto_line3], [Billto_City], [Billlto_Region], [Billto_state], [Billto_country], [Billto_Phone1], [Billto_Phone2], [Billto_email], [Billto_fax], [Billto_mobile], [ShipToAddress], [Shipto_Name], [Shipto_Addressid], [Shipto_contactName], [Shipto_line1], [Shipto_line2], [shipto_line3], [Shipto_City], [Shipto_Region], [Shipto_State], [Shipto_Country], [ShipVia], [Shipto_Phone1], [Shipto_Phone2], [Shipto_email], [shipto_fax], [Shipto_mobile], [TotalDiscountAmount], [DiscountPercentage], [TotalTax], [FreightAmount], [TotalLineItemAmount], [TotalLineItemDiscountAmount], [OrderCondtion1], [OrderCondition2], [PaymentCondition1], [PaymentCondition2], [PaymentCondition3], [OrderNote1], [OrderNote2], [Modifiedon], [Modifiedby], [Createdon], [Createdby]) VALUES (24, 1888, 1, 1470, 0, CAST(0x0000A13C00000000 AS DateTime), CAST(0x0000A15200000000 AS DateTime), NULL, N'1211', N'P', N'GRT HOTELS & RESORTS', N'VIJAY', N'GRT HOTELS & RESORTS', N'NO 21,COATS ROAD, T NAGAR CHENNAI-17', NULL, N'MR.RAJA RATHINAM', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044 665 00075', N'NIL', N'purchase@grthotels.com', N'044 66500065', N'98413 93859', N'NO 21,COATS ROAD, T NAGAR CHENNAI-17', N'GRT HOTELS & RESORTS', NULL, N'MR.RAJA RATHINAM', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'044 665 00075', N'NIL', N'purchase@grthotels.com', N'', N'98413 93859', 0.0000, 0.0000, 0.0000, 0.0000, 23750.0000, 0.0000, N'30 days', N'3. Vat @ 14.5% will be charged extra.', N'50% Advance payment along with purchase order', N'Balance against delivery', N'', N'', N'1.PACKING AND FORWARDING CHARGES WILL BE EXTRA. RS.250/- PER TABLE 2. TRANSPORT CHARGES WILL BE EXTRA. RS.3,000/-', NULL, NULL, CAST(0x0000A15601221B94 AS DateTime), N'ACC0UNTS-ACCOUNTS')
/****** Object: Table [dbo].[paymentHistory] Script Date: 02/15/2013 16:03:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[paymentHistory](
[PaymentHistoryID] [int] IDENTITY(1,1) NOT NULL,
[paymentID] [int] NULL,
[receivedAmout] [money] NULL,
[receivedDate] [datetime] NULL,
[receivedType] [varchar](30) NULL,
[BankName] [varchar](100) NULL,
[Reason] [varchar](100) NULL,
[cheque_DD_no] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[paymentHistory] ON
INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (32, 26, 6000.0000, CAST(0x0000A16600000000 AS DateTime), N'Cr', N'', N'ASD', N'')
INSERT [dbo].[paymentHistory] ([PaymentHistoryID], [paymentID], [receivedAmout], [receivedDate], [receivedType], [BankName], [Reason], [cheque_DD_no]) VALUES (33, 26, 4500.0000, CAST(0x0000A16600000000 AS DateTime), N'Dr', N'', N' FDR', N'')
SET IDENTITY_INSERT [dbo].[paymentHistory] OFF
/****** Object: Table [dbo].[payment] Script Date: 02/15/2013 16:03:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[payment](
[paymentID] [int] IDENTITY(1,1) NOT NULL,
[salesOrderID] [int] NULL,
[QuotationID] [int] NULL,
[Quotation_revisedID] [int] NULL,
[receivedAmount] [money] NULL,
[SalesOrderAmount] [money] NULL,
[InvoiceID] [int] NULL,
[inv_TotalAmount] [money] NULL,
[GroupName] [varchar](10) NULL,
CONSTRAINT [PK__payment__A0D9EFA629971E47] PRIMARY KEY CLUSTERED
(
[paymentID] 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].[payment] ON
INSERT [dbo].[payment] ([paymentID], [salesOrderID], [QuotationID], [Quotation_revisedID], [receivedAmount], [SalesOrderAmount], [InvoiceID], [inv_TotalAmount], [GroupName]) VALUES (26, 6, NULL, NULL, 6000.0000, 44255.0000, 0, 0.0000, N'Pvt')
SET IDENTITY_INSERT [dbo].[payment] OFF
-----------------Query------------------------
declare @col_Str varchar(max) ,
@SQl_Query varchar(max);
WITH substitue as (Select Distinct Reason from paymentHistory )
--select @col_Str=isnull(@col_Str+',','')+reason from substitue;
select @col_Str=isnull(@col_Str+'],[','')+Reason from substitue
--select @col_Str=isnull(@col_Str+'],[','')+Reason from substitue
select @col_Str='['+@col_Str+']'
--select p.salesOrderID,p.SalesOrderAmount , p.salesOrderID from payment p
set @SQl_Query='SELECT paymentid,salesorderid,'+@col_Str+',SalesOrderAmount,OutStanding
FROM (SELECT ph.paymentid,p.salesorderid , CASE receivedType WHEN ''Dr'' THEN receivedAmout else -1 * receivedAmout END receivedAmout ,
reason,p.SalesOrderAmount,sum(p.SalesOrderAmount)-SUM( p.receivedAmount)+isNull((select SUM(receivedAmout) from paymentHistory where paymentID=ph.paymentID and receivedType=''Dr''),''0'') as OutStanding,total=(select SUM(receivedAmout) from paymentHistory where paymentID=ph.paymentID and receivedType=''Dr'')
FROM paymentHistory ph join payment p on p.paymentID=ph.paymentID group by ph.paymentid,reason,p.salesorderid,p.SalesOrderAmount,receivedType,receivedAmout) as Sourcetbl
PIVOT(sum(receivedAmout) for reason IN('+@col_Str+')) as PivotTabl'
exec (@SQl_Query);
-------------------output-----------------
paymentidsalesorderid FDRASDSalesOrderAmountOutStanding
26 6 4500.00-6000.00 44255.00 42755.00
-----------------------------------------------
-----------------Expected result----------------
paymentidsalesorderid FDRASDSalesOrderAmountOutStanding
26 6 4500.00 0 44255.00 42755.00
:hehe::hehe::hehe::hehe:

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

raym85 SSC Eights! Points: 938 More actions · Answer 1

I think this is what you're looking for.

Cheers.

declare @col_Str varchar(max) ,

@SQl_Query varchar(max);

WITH substitue as (Select Distinct Reason from paymentHistory )

--select @col_Str=isnull(@col_Str+',','')+reason from substitue;

select @col_Str=isnull(@col_Str+'],[','')+Reason from substitue

--select @col_Str=isnull(@col_Str+'],[','')+Reason from substitue

select @col_Str='['+@col_Str+']'

--select p.salesOrderID,p.SalesOrderAmount , p.salesOrderID from payment p

set @SQl_Query=

'

SELECT paymentid,salesorderid,'+@col_Str+',SalesOrderAmount,OutStanding

FROM (SELECT paymentid,

salesorderid,

CASE

WHEN receivedAmout < 0 THEN 0 ELSE receivedAmout

END AS receivedAmout,

reason,

SalesOrderAmount,

Outstanding,

total

FROM(

SELECT ph.paymentid,

p.salesorderid ,

CASE receivedType WHEN ''Dr'' THEN receivedAmout else -1 * receivedAmout END receivedAmout ,

reason,

p.SalesOrderAmount,

sum(p.SalesOrderAmount)-SUM( p.receivedAmount)+isNull((select SUM(receivedAmout) from paymentHistory where paymentID=ph.paymentID and receivedType=''Dr''),''0'') as OutStanding,total=(select SUM(receivedAmout) from paymentHistory where paymentID=ph.paymentID and receivedType=''Dr'')

FROM paymentHistory ph join payment p on p.paymentID=ph.paymentID

group by ph.paymentid,reason,p.salesorderid,p.SalesOrderAmount,receivedType,receivedAmout)H) as Sourcetbl

PIVOT(sum(receivedAmout) for reason IN('+@col_Str+')) as PivotTabl'

exec (@SQl_Query);