question

vanapandi avatar image
vanapandi asked

Pivot :How to remove minus (-) and null

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----------------- paymentid salesorderid FDR ASD SalesOrderAmount OutStanding 26 6 4500.00 -6000.00 44255.00 42755.00 ----------------------------------------------- -----------------Expected result---------------- paymentid salesorderid FDR ASD SalesOrderAmount OutStanding 26 6 4500.00 0 44255.00 42755.00
sql-server-2008sql-server-2005pivot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Phil Factor avatar image
Phil Factor answered
Your problem is in using the PIVOT statement when doing it the old way gives you much more control. I've got the same answer as you did but I left out any business logic I didn't understand. It's up to you to put it back in again! Select p.paymentid, salesorderid, sum (case when receivedType='Dr' then receivedAmout else 0 end ) as [FDR], sum (case when receivedType='Cr' then receivedAmout else 0 end ) as [ASD], max(SalesOrderAmount), max(SalesOrderAmount) -sum (case when receivedType='cr' then receivedAmout else 0 end ) +sum (case when receivedType='Dr' then receivedAmout else 0 end ) as Outstanding FROM paymentHistory ph join payment p on p.paymentID=ph.paymentID group by p.paymentid, salesorderid
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.