Get the net sales

Transact-SQL
1

sir, good day to you

May i ask ur help to get my net sales using sql

My basic formula
Net sales= gross sales - sales return

Sales table
Salesordercode
Itemcode
Qty
Transdate

Return table
Salesordercode
Itemcode
Qty
Transdate

I want to get the net sales daily sir. Return qty will be deducted to sales if they are equal to salesordercode and transdate.

Thank you and looking forward to your help sir

2

Please post table scripts, sample data and expected output. See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidance.

3

Thanks a lot for the immediate response sir,

Here are my tables and sample data

/*
Navicat SQL Server Data Transfer

Source Server : MSSQL
Source Server Version : 80000
Source Host : JAM:1433
Source Database : SP_ERP
Source Schema : dbo

Target Server Type : SQL Server
Target Server Version : 80000
File Encoding : 65001

Date: 2016-07-30 09:19:30
*/

-- Table structure for [dbo].[trn_SalesOrder_Detail]

DROP TABLE [dbo].[trn_SalesOrder_Detail]
GO
CREATE TABLE [dbo].[trn_SalesOrder_Detail] (
[SalesOrderCode] nvarchar(10) NOT NULL ,
[TransTypeCode] bigint NULL ,
[ItemCode] bigint NULL ,
[UnitCode] bigint NULL ,
[Qty] decimal(18) NULL ,
[UnitPrice] decimal(18,8) NULL ,
[Amount] decimal(18,8) NULL ,
[LotNumber] nvarchar(20) NULL ,
[Discount] decimal(18,8) NULL
)

GO

-- Records of trn_SalesOrder_Detail

INSERT INTO [dbo].[trn_SalesOrder_Detail] VALUES (N'SO-0000001', N'1', N'160802548', N'1', N'10', N'1124.00000000', N'11240.00000000', N'NA', N'.00000000');
GO
INSERT INTO [dbo].[trn_SalesOrder_Detail] VALUES (N'SO-0000001', N'1', N'160802551', N'1', N'10', N'1079.00000000', N'10790.00000000', N'NA', N'.00000000');
GO

-- Table structure for [dbo].[trn_SalesOrder_Header]

DROP TABLE [dbo].[trn_SalesOrder_Header]
GO
CREATE TABLE [dbo].[trn_SalesOrder_Header] (
[SalesOrderCode] nvarchar(10) NOT NULL ,
[DocTypeCode] bigint NULL ,
[CustomerCode] nvarchar(12) NULL ,
[PaymentTypeCode] bigint NULL ,
[CreditTermCode] bigint NULL ,
[TransDate] datetime NULL ,
[DeliveryDate] datetime NULL ,
[DueDate] datetime NULL ,
[CustRef] nvarchar(20) NULL ,
[Remarks] nvarchar(100) NULL ,
[StatusCode] nvarchar(2) NULL ,
[EncodedBy] nvarchar(50) NULL ,
[DateEncoded] datetime NULL ,
[ModifiedBy] nvarchar(50) NULL ,
[LastDateModified] datetime NULL ,
[LoadingCode] nvarchar(10) NULL ,
[PaymentStatusCode] nvarchar(2) NULL ,
[ShippingCode] nvarchar(5) NULL ,
[AutoCode] int NOT NULL
)

GO

-- Records of trn_SalesOrder_Header

INSERT INTO [dbo].[trn_SalesOrder_Header] VALUES (N'SO-0000001', N'100', N'1305-C0001', N'2', N'5', N'2016-07-30 00:00:00.000', N'2016-07-30 00:00:00.000', N'2016-08-29 00:00:00.000', N'12345', N'', N'PS', N'admin', N'2016-07-30 09:17:40.000', null, null, null, N'NP', N'', N'17');
GO

-- Table structure for [dbo].[trn_SalesReturn_Detail]

DROP TABLE [dbo].[trn_SalesReturn_Detail]
GO
CREATE TABLE [dbo].[trn_SalesReturn_Detail] (
[SalesReturnCode] nvarchar(10) NULL ,
[ItemCode] bigint NULL ,
[UnitCode] bigint NULL ,
[Qty] decimal(18,8) NULL ,
[TransTypeCode] bigint NULL ,
[UnitPrice] decimal(18,8) NULL ,
[Amount] decimal(18,8) NULL ,
[LotNumber] nvarchar(30) NULL ,
[Discount] decimal(18,8) NULL
)

GO

-- Records of trn_SalesReturn_Detail

INSERT INTO [dbo].[trn_SalesReturn_Detail] VALUES (N'SR-0000001', N'160802548', N'1', N'5.00000000', N'1', N'1124.00000000', N'5620.00000000', N'NA', N'.00000000');
GO
INSERT INTO [dbo].[trn_SalesReturn_Detail] VALUES (N'SR-0000001', N'160802551', N'1', N'5.00000000', N'1', N'1079.00000000', N'5395.00000000', N'NA', N'.00000000');
GO

-- Table structure for [dbo].[trn_SalesReturn_Header]

DROP TABLE [dbo].[trn_SalesReturn_Header]
GO
CREATE TABLE [dbo].[trn_SalesReturn_Header] (
[SalesReturnCode] nvarchar(10) NOT NULL ,
[DocTypeCode] bigint NULL ,
[CustomerCode] nvarchar(12) NULL ,
[TransDate] datetime NULL ,
[DeliveryDate] datetime NULL ,
[ReferenceNo] nvarchar(20) NULL ,
[Remarks] nvarchar(100) NULL ,
[StatusCode] nvarchar(2) NULL ,
[EncodedBy] nvarchar(50) NULL ,
[DateEncoded] datetime NULL ,
[ModifiedBy] nvarchar(50) NULL ,
[LastDateModified] datetime NULL ,
[Reason] nvarchar(255) NULL ,
[AutoCode] int NOT NULL
)

GO

-- Records of trn_SalesReturn_Header

INSERT INTO [dbo].[trn_SalesReturn_Header] VALUES (N'SR-0000001', N'102', N'1305-C0001', N'2016-07-30 09:18:28.000', N'2016-07-30 00:00:00.000', N'SO-0000001', N'12345', N'PS', N'admin', N'2016-07-30 09:18:28.000', N'admin', N'2016-07-30 09:18:45.000', null, N'1');
GO

-- Indexes structure for table trn_SalesOrder_Detail

CREATE INDEX [_WA_Sys_SalesOrderCode_0D7A028] ON [dbo].[trn_SalesOrder_Detail]
([SalesOrderCode] ASC)
GO
CREATE INDEX [_WA_Sys_TransTypeCode_0D7A0286] ON [dbo].[trn_SalesOrder_Detail]
([TransTypeCode] ASC)
GO
CREATE INDEX [_WA_Sys_ItemCode_0D7A0286] ON [dbo].[trn_SalesOrder_Detail]
([ItemCode] ASC)
GO
CREATE INDEX [_WA_Sys_UnitCode_0D7A0286] ON [dbo].[trn_SalesOrder_Detail]
([UnitCode] ASC)
GO
CREATE INDEX [_WA_Sys_LotNumber_0D7A0286] ON [dbo].[trn_SalesOrder_Detail]
([LotNumber] ASC)
GO
CREATE INDEX [_WA_Sys_Qty_0D7A0286] ON [dbo].[trn_SalesOrder_Detail]
([Qty] ASC)
GO

-- Indexes structure for table trn_SalesOrder_Header

CREATE INDEX [_WA_Sys_StatusCode_0B91BA14] ON [dbo].[trn_SalesOrder_Header]
([StatusCode] ASC)
GO
CREATE INDEX [_WA_Sys_DeliveryDate_0B91BA14] ON [dbo].[trn_SalesOrder_Header]
([DeliveryDate] ASC)
GO
CREATE INDEX [_WA_Sys_DocTypeCode_0B91BA14] ON [dbo].[trn_SalesOrder_Header]
([DocTypeCode] ASC)
GO
CREATE INDEX [_WA_Sys_CustomerCode_0B91BA14] ON [dbo].[trn_SalesOrder_Header]
([CustomerCode] ASC)
GO
CREATE INDEX [_WA_Sys_PaymentTypeCode_0B91BA] ON [dbo].[trn_SalesOrder_Header]
([PaymentTypeCode] ASC)
GO
CREATE INDEX [_WA_Sys_TransDate_0B91BA14] ON [dbo].[trn_SalesOrder_Header]
([TransDate] ASC)
GO
CREATE INDEX [_WA_Sys_CreditTermCode_0B91BA1] ON [dbo].[trn_SalesOrder_Header]
([CreditTermCode] ASC)
GO
CREATE INDEX [_WA_Sys_LoadingCode_0B91BA14] ON [dbo].[trn_SalesOrder_Header]
([LoadingCode] ASC)
GO
CREATE INDEX [_WA_Sys_CustRef_0B91BA14] ON [dbo].[trn_SalesOrder_Header]
([CustRef] ASC)
GO
CREATE INDEX [_WA_Sys_PaymentStatusCode_0B91BA14] ON [dbo].[trn_SalesOrder_Header]
([PaymentStatusCode] ASC)
GO

-- Primary Key structure for table [dbo].[trn_SalesOrder_Header]

ALTER TABLE [dbo].[trn_SalesOrder_Header] ADD PRIMARY KEY ([SalesOrderCode])
GO

-- Indexes structure for table trn_SalesReturn_Detail

CREATE INDEX [_WA_Sys_SalesOrderCode_0D7A028] ON [dbo].[trn_SalesReturn_Detail]
([SalesReturnCode] ASC)
GO
CREATE INDEX [_WA_Sys_TransTypeCode_151B244E] ON [dbo].[trn_SalesReturn_Detail]
([TransTypeCode] ASC)
GO
CREATE INDEX [_WA_Sys_ItemCode_0D7A0286] ON [dbo].[trn_SalesReturn_Detail]
([ItemCode] ASC)
GO
CREATE INDEX [_WA_Sys_UnitCode_0D7A0286] ON [dbo].[trn_SalesReturn_Detail]
([UnitCode] ASC)
GO

-- Indexes structure for table trn_SalesReturn_Header

CREATE INDEX [_WA_Sys_DocTypeCode_1332DBDC] ON [dbo].[trn_SalesReturn_Header]
([DocTypeCode] ASC)
GO
CREATE INDEX [_WA_Sys_CustomerCode_1332DBDC] ON [dbo].[trn_SalesReturn_Header]
([CustomerCode] ASC)
GO
CREATE INDEX [_WA_Sys_StatusCode_1332DBDC] ON [dbo].[trn_SalesReturn_Header]
([StatusCode] ASC)
GO
CREATE INDEX [_WA_Sys_TransDate_1332DBDC] ON [dbo].[trn_SalesReturn_Header]
([TransDate] ASC)
GO
CREATE INDEX [_WA_Sys_DeliveryDate_1332DBDC] ON [dbo].[trn_SalesReturn_Header]
([DeliveryDate] ASC)
GO

-- Primary Key structure for table [dbo].[trn_SalesReturn_Header]

ALTER TABLE [dbo].[trn_SalesReturn_Header] ADD PRIMARY KEY ([SalesReturnCode])
GO

            NET

CUSTOMER CODE CUSTOMER NAME ITEM CODE DESCRIPTION QUANTITY SALES RETURN NET
1305-C0001 JOMAR FARM CENTER 160802548 PIGROW PRE-STARTER 5 10 5 5
1305-C0001 JOMAR FARM CENTER 160802551 PIGROW MATERNA 1 5 10 5 5

4

Missing information to display customer name and item description.

But this might get you started:

select a.customercode
      ,b.itemcode
      ,b.qty as sales_qty
      ,isnull(d.qty,0) as return_qty
      ,b.qty-isnull(d.qty,0) as net_qty
      ,b.amount as sales_amount
      ,isnull(d.amount,0) as return_amount
      ,isnull(d.discount,0) as discount
      ,b.amount-isnull(d.amount,0)-isnull(d.discount,0) as net_amount
  from #trn_salesorder_header as a
       inner join #trn_salesorder_detail as b
               on b.salesordercode=a.salesordercode
       left outer join #trn_salesreturn_header as c
                    on c.customercode=a.customercode
                   and c.referenceno=a.salesordercode
       left outer join #trn_salesreturn_detail as d
                    on d.salesreturncode=c.salesreturncode
                   and d.itemcode=b.itemcode
                   and d.lotnumber=b.lotnumber
;
5

Thanks a lot to all sqlteam dev