从 SQL Server 表计算销售额、退款和破损 [英] calculating sales, refund and breakages from SQL Server Tables

问题描述

我有 4 个表，分别是 Products、SalesLog、Breakages、SalesReturn.

I have 4 tables namely Products, SalesLog, Breakages, SalesReturn.

产品表

CREATE TABLE [dbo].[Products](
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pSize] [int] NULL,
[pPrice] [decimal](10, 2) NULL,
[pPackQty] [int] NULL,
[pGroup] [int] NULL,
[pCode] [int] NULL,
[OpenStock] [int] NULL,
[CloseStock] [int] NULL,
[YrlyOpenStock] [int] NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
[ProductId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

销售日志表

CREATE TABLE [dbo].[SalesLog](
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[MemoNo] [int] NULL,
[ProductCode] [int] NULL,
[Quantity] [int] NULL,
[Price] [decimal](10, 2) NULL,
[pGroup] [int] NULL,
[pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pSize] [int] NULL,
[BillDate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SalesLog] PRIMARY KEY CLUSTERED 
(
[SalesID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

销售退货表

CREATE TABLE [dbo].[SalesReturn](
[srID] [int] IDENTITY(1,1) NOT NULL,
[ProductCode] [int] NULL,
[Quantity] [int] NULL,
[pGroup] [int] NULL,
[MemoNo] [int] NULL,
[SalesmanID] [int] NULL,
[Price] [decimal](10, 2) NULL,
[JobShift] [int] NULL,
[BillDate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddedOn] [datetime] NULL,
 CONSTRAINT [PK_SalesReturn] PRIMARY KEY CLUSTERED 
(
[srID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

破损表

CREATE TABLE [dbo].[Breakages](
[breakId] [int] IDENTITY(1,1) NOT NULL,
[MemoNo] [int] NULL,
[SalesmanID] [int] NULL,
[ProductCode] [int] NULL,
[pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Quantity] [int] NULL,
[Price] [decimal](10, 2) NULL,
[pGroup] [int] NULL,
[JobShift] [int] NULL,
[BillDate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddedOn] [datetime] NULL,
CONSTRAINT [PK_Breakages_1] PRIMARY KEY CLUSTERED 
(
[breakId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

我必须生成一个报告来显示特定日期的 SalesLOg、SalesReturn 和 Breakages(根据 SalesLog、Breakages 和 SalesReturn 的 BillDate 列)，格式如下.

I have to generate a single report to show SalesLOg, SalesReturn and Breakages of particular date (according to BillDate column of SalesLog, Breakages and SalesReturn) in following pattern.

想要的输出

Code      ItemName     Price     SalesQty     BreakagesQty    SalesReturnQty
 1           A          $10         50              2               2
 1           A          $12         150             1               10
 15          X          $5          56              0               2
 20          Z          $8          121             0               0

为了阅读，减少了输出列

如您所见，由于该产品的价格变化，产品A"已被列出两次.为此，我编写了一个查询:

as you can see that Product "A" has been listed twice because of price change of that product. to achieve this i have written a query:

SELECT     SalesLog.pName, SalesLog.ProductCode, MIN(ItemGroup.gName) AS GroupName, 
           SalesLog.Price, SUM    (SalesLog.Quantity) AS SalesQty, 
           SUM(SalesLog.Quantity * SalesLog.Price) AS SalesValue, 
     ISNULL ((SELECT     SUM(Quantity) AS Expr1 FROM Breakages
        WHERE (ProductCode = SalesLog.ProductCode) AND (Price = SalesLog.Price) AND 
          (BillDate = '07/01/2010') AND (pGroup <> 15) AND (pGroup <> 16)), 0) AS BreakQty, 
     ISNULL ((SELECT     SUM(Quantity * Price) AS Expr1
 FROM         Breakages
        WHERE     (ProductCode = SalesLog.ProductCode) AND (Price = SalesLog.Price) AND 
          (BillDate = '07/01/2010') AND (pGroup <> 15) AND (pGroup <> 16)), 0) AS BreakValue, 
     ISNULL ((SELECT     SUM(Quantity) AS Expr1
 FROM         SalesReturn
       WHERE     (ProductCode = SalesLog.ProductCode) AND (Price = SalesLog.Price) AND 
          (BillDate = '07/01/2010') AND (pGroup <> 15) AND (pGroup <> 16)), 0) AS ReturnQty, 
     ISNULL ((SELECT     SUM(Quantity * Price) AS Expr1
 FROM         SalesReturn
       WHERE     (ProductCode = SalesLog.ProductCode) AND (Price = SalesLog.Price) AND 
          (BillDate = '07/01/2010') AND (pGroup <> 15) AND (pGroup <> 16)), 0) AS ReturnValue, 
     ISNULL ((SELECT     SUM(Quantity) AS Expr1
 FROM         SalesLog
       WHERE     (ProductCode = Products.pCode)), 0) AS CummSales
 FROM         SalesLog 
          INNER JOIN
              ItemGroup ON ItemGroup.gCode = SalesLog.pGroup AND SalesLog.pGroup = ItemGroup.gCode 
          INNER JOIN
              Products ON Products.pCode = SalesLog.ProductCode
       WHERE  (SalesLog.BillDate = '07/01/2010') AND (SalesLog.pGroup <> 15) AND 
              (SalesLog.pGroup <> 16)
GROUP BY SalesLog.pName, SalesLog.ProductCode, SalesLog.Price, Products.pCode, Products.pPrice
ORDER BY SalesLog.ProductCode, SalesLog.pName

我对这个查询的问题是，它无法显示没有销售但有销售退货的项目条目.这个条件是我的客户给的.你能帮我解决这个问题吗?实现此条件的任何 t-sql 查询或逻辑都将有助于...

My problem with this query is, It is unable to show entries of items where there is no sales but there is sales return. this condition is give by my client. Can you help me on this. any t-sql query or logic to implement this condition will help...

谢谢你包容我........

thank you for bearing me.........

推荐答案

试试这个:

SELECT  MIN(Products.pName) AS pName,
        Products.pCode AS ProductCode,
        MIN(Products.pGroup) AS GroupName, 
        Sales_Trans.Price,
        SUM(Sales_Trans.Sales_Qty) AS SalesQty, 
        SUM(Sales_Trans.Sales_Value) AS SalesValue, 
        SUM(Sales_Trans.Break_Qty) AS BreakQty, 
        SUM(Sales_Trans.Break_Value) AS BreakValue, 
        SUM(Sales_Trans.Return_Qty) AS ReturnQty, 
        SUM(Sales_Trans.Return_Value) AS ReturnValue, 
FROM Products
JOIN        
(SELECT ProductCode, Price, Quantity Sales_Qty, Price * Quantity Sales_Value, 0 Break_Qty, 0 Break_Value, 0 Return_Qty, 0 Return_Value FROM SalesLog
 WHERE  (BillDate = '07/01/2010') AND (pGroup <> 15) AND (pGroup <> 16)
 UNION ALL
 SELECT ProductCode, Price, 0 Sales_Qty, 0 Sales_Value, 0 Break_Qty, 0 Break_Value, Quantity Return_Qty, Price * Quantity Return_Value FROM SalesReturn
 WHERE  (BillDate = '07/01/2010') AND (pGroup <> 15) AND (pGroup <> 16)
 UNION ALL
 SELECT ProductCode, Price, 0 Sales_Qty, 0 Sales_Value, Quantity Break_Qty, Price * Quantity Break_Value, 0 Return_Qty, 0 Return_Value FROM Breakages
 WHERE  (BillDate = '07/01/2010') AND (pGroup <> 15) AND (pGroup <> 16)
) Sales_Trans
ON (Products.pCode = Sales_Trans.ProductCode) and (Products.pPrice = Sales_Trans.Price)
GROUP BY Products.pCode, Sales_Trans.Price
ORDER BY 2,1

(假设在 Products 表中存储了完整的价格变化历史 - 如果仅最新价格存储在 Products 上，则需要删除 Products.pPrice = Sales_Trans.Price 连接条件.)

(This assumes a full history of price changes is stored in the Products table - if only the latest prices are stored on Products, then the Products.pPrice = Sales_Trans.Price join condition will need to be removed.)

这篇关于从 SQL Server 表计算销售额、退款和破损的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！