如何使用FIFO方法在SQL中查询库存数据 [英] How to query data using the FIFO method for inventories in SQL

问题描述

我有一个表TabBill（IDBill，CreateDate，IDProduct，Number，Price，TotalMoney，Type）。包括购买和销售发票

Ex：

IDBill CreateDate IDProduct Number Price TotalMoney Type

P001 01/12/2016 001 10 10.000 100.000 In

P002 03/12/2016 001 5 12.000 60.000在

S001 05/12/2016 001 4 12.000 48.000 Out

S002 07/12 / 2016 001 10 15.000 150.000 Out

P005 07/12/2016 002 10 10.000 100.000 In



现在，我想要查询数据使用sql server中的iventories和Profit的fifo方法



感谢您的帮助！



什么我试过了：



我使用sql server 2005.你能帮我吗？

解决方案

< blockquote>你真的没有提供足够的信息来帮助你，你似乎或多或少需要一本关于SQL基础知识的教程。



然而，唯一的我可以告诉你（根据你所描述的）先进先出的方法是基于CreateDate列。所以为了做到这一点，你将在SQL中执行以下操作。

   -   第一条记录位于列表顶部 
  SELECT  *  FROM  TabBill  ORDER   BY  CreateDate 
   -   仅获得第一个 
  SELECT   TOP   1  *  FROM  TabBill < span class =code-keyword> ORDER   BY  CreateDate 

您的问题确实与查询的Order By部分有关。



如果您需要帮助以了解SQL我建议您使用google作为简单搜索SQL教程将为您提供大量结果。

创建表

 CREATE TABLE #TabBill 
（
 IDBill varchar（ 20），CreateDate date，IDProduct varchar（10），Number int，Price money，TotalMoney money，tranType varchar（10）
）; 

根据您的问题插入值

 INSERT INTO #TabBill 
 VALUES（'P001'，'2016年1月12日'，'1'，10,10,100，'In '）; 
 
 INSERT INTO #TabBill 
 VALUES（'P002'，'2016年3月12日'，'1'，5,12,60，'In'）; 
 
 INSERT INTO #TabBill 
 VALUES（'S001'，'2016年5月12日'，'1'，4,12,48，'Out'）; 
 
 INSERT INTO #TabBill 
 VALUES（'S002'，'2016年7月12日'，'1'，10,15,150，'出'）; 
 
 INSERT INTO #TabBill 
 VALUES（'P005'，'2016年7月12日'，'2'，10,10,100，'In'）; 

获取cumulative_TotalMoney和cumulative_qty

; WITH cte 
 AS （SELECT ROW_NUMBER（）OVER（ORD BY CreateDate）AS srno，
 CreateDate，
 IDProduct，
 qty = CASE 
 WHEN tranType ='out'
 THEN 0  - 编号
 ELSE编号
 END，
 TotalMoney = CASE 
 WHEN tranType ='out'
 THEN 0  -  TotalMoney 
 ELSE TotalMoney 
 END， 
价格，
 tranType 
来自#TabBill）
 SELECT *，
 SUM（TotalMoney）OVER（PARTITION BY IDProduct ORDER BY s rno）AS cumulative_TotalMoney，
 SUM（数量）OVER（PARTITION BY IDProduct ORDER BY srno）AS cumulative_qty 
 FROM cte; 

To得到摘要

; WITH cte 
 AS（SELECT ROW_NUMBER（）OVER（订购CreateDate）AS srno， 
 CreateDate，
 IDProduct，
 qty = CASE 
 WHEN tranType ='out'
 THEN 0  -  Number 
 ELSE Number 
 END，
 TotalMoney = CASE 
 WHEN tranType ='out'
 THEN 0  -  TotalMoney 
 ELSE TotalMoney 
 END，
价格，
 tranType 
 FROM #TabBill）
 SELECT IDProduct，
 SUM（TotalMoney）AS'盈利/亏损'，
 SUM（数量）AS'剩余数量'
来自cte 
 GROUP BY IDProduct; 

I have a table TabBill(IDBill, CreateDate, IDProduct, Number, Price, TotalMoney, Type). Including the purchase and sales invoices
Ex:
IDBill CreateDate IDProduct Number Price TotalMoney Type
P001 01/12/2016 001 10 10.000 100.000 In
P002 03/12/2016 001 5 12.000 60.000 In
S001 05/12/2016 001 4 12.000 48.000 Out
S002 07/12/2016 001 10 15.000 150.000 Out
P005 07/12/2016 002 10 10.000 100.000 In

Now, I want query data using the fifo method for iventories and Profit in sql server

Thank for help me!

What I have tried:

I using sql server 2005. Can you help me?

解决方案

You really haven't provided enough information to help you and it also seems you more or less need a tutorial on the basics of SQL.

However, the only way that i can tell (based on what you've described) that you can do first in first out is based off the CreateDate column. So to do this you would do the following in SQL.

--First record in is at the top of the list
SELECT * FROM TabBill ORDER BY CreateDate
--To get the first one only
SELECT TOP 1 * FROM TabBill ORDER BY CreateDate

Your question really relates to the Order By portion of the query.

If you need help with understanding that SQL I suggest you use google as a simple search of SQL Tutorials will provide you plenty of results.

Create Table

CREATE TABLE #TabBill
( 
IDBill varchar(20), CreateDate date, IDProduct varchar(10), Number int, Price money, TotalMoney money, tranType varchar(10)
);

Insert Value as per your Question

INSERT INTO #TabBill
VALUES( 'P001', 'Jan 12 2016', '1', 10, 10, 100, 'In ' );

INSERT INTO #TabBill
VALUES( 'P002', 'Mar 12 2016', '1', 5, 12, 60, 'In' );

INSERT INTO #TabBill
VALUES( 'S001', 'May 12 2016', '1', 4, 12, 48, 'Out' );

INSERT INTO #TabBill
VALUES( 'S002', 'Jul 12 2016', '1', 10, 15, 150, 'Out' );

INSERT INTO #TabBill
VALUES( 'P005', 'Jul 12 2016', '2', 10, 10, 100, 'In' );

To get cumulative_TotalMoney and cumulative_qty

;WITH cte
     AS (SELECT ROW_NUMBER() OVER(ORDER BY CreateDate) AS srno,
                CreateDate,
                IDProduct,
                qty = CASE
                          WHEN tranType = 'out'
                          THEN 0 - Number
                          ELSE Number
                      END,
                TotalMoney = CASE
                                 WHEN tranType = 'out'
                                 THEN 0 - TotalMoney
                                 ELSE TotalMoney
                             END,
                Price,
                tranType
         FROM #TabBill)
     SELECT *,
            SUM(TotalMoney) OVER(PARTITION BY IDProduct ORDER BY srno) AS cumulative_TotalMoney,
            SUM(qty) OVER(PARTITION BY IDProduct ORDER BY srno) AS cumulative_qty
     FROM cte;

To get Summary

;WITH cte
     AS (SELECT ROW_NUMBER() OVER(ORDER BY CreateDate) AS srno,
                CreateDate,
                IDProduct,
                qty = CASE
                          WHEN tranType = 'out'
                          THEN 0 - Number
                          ELSE Number
                      END,
                TotalMoney = CASE
                                 WHEN tranType = 'out'
                                 THEN 0 - TotalMoney
                                 ELSE TotalMoney
                             END,
                Price,
                tranType
         FROM #TabBill)
     SELECT IDProduct,
            SUM(TotalMoney) AS 'Profit/Loss',
            SUM(qty) AS 'Remaining Qty'
     FROM cte
     GROUP BY IDProduct;

这篇关于如何使用FIFO方法在SQL中查询库存数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！