时间戳

首页 > 解决方案 > 如何按操作日期列出库存？

sql - 如何按操作日期列出库存？

问题描述

如何按操作日期列出库存？

例如，我想要这个查询结果：

StoreCode , StoreDescription, Inventory, InventoryAmount, OperationDate --1 行

1110, Store 1110,   213, 1150, 2021-08-01

--2 行

1110, Store 1110,   555, 6700, 2021-08-02

数据库：SQL Server

列：trStock.OperationDate

SELECT

 Inventory.StoreCode,
 Inventory.StoreDescription,
 Inventory      =   SUM (Inventory) ,
 InventoryAmount  =     SUM(InventoryAmount),
 OperationDate


 FROM (

 SELECT 
 
 StoreCode,
 StoreDescription       = ISNULL((SELECT CurrAccDescription FROM cdCurrAccDesc WITH(NOLOCK) WHERE cdCurrAccDesc.CurrAccTypeCode = 5 AND cdCurrAccDesc.CurrAccCode = trStock.StoreCode AND cdCurrAccDesc.LangCode = 'TR') ,SPACE(0)),                             

 Inventory      = SUM(In_Qty1-Out_Qty1),
 InventoryAmount  = SUM(In_Qty1-Out_Qty1) * PurchasePrice.Price,
 OperationDate


 FROM dbo.trStock WITH(NOLOCK)

 LEFT OUTER JOIN
    (
        SELECT prItemBasePrice.ItemCode,
               Price
        FROM prItemBasePrice WITH (NOLOCK)
        WHERE prItemBasePrice.BasePriceCode = 2
              AND prItemBasePrice.ItemTypeCode = 1
    ) PurchasePrice
    ON PurchasePrice.ItemCode = trStock.ItemCode

 WHERE

 ItemTypeCode = 1
 AND OperationDate BETWEEN '1900-01-01' AND '2021-08-07'
 AND dbo.trStock.OfficeCode <> 'M'
 


 GROUP BY StoreCode, PurchasePrice.Price, OperationDate

 ) AS Inventory

 GROUP BY  Inventory.StoreCode, Inventory.StoreDescription, OperationDate

标签： sqlsql-servertsql

解决方案

这是我重构代码的尝试

select s.StoreCode, isnull(c.CurrAccDescription, space(0)) StoreDescription,                             
       Inventory      = sum(s.In_Qty1-s.Out_Qty1),
       InventoryAmount  = sum(In_Qty1-Out_Qty1) * isnull(pr.Price, 0),
       s.OperationDate
from dbo.trStock s
     left join prItemBasePrice pr on s.ItemCode=pr.ItemCode
                                     and pr.BasePriceCode = 2
                                     and pr.ItemTypeCode = 1
     left join cdCurrAccDesc c on s.StoreCode=c.CurrAccCode
                                  and c.urrAccTypeCode = 5 
                                  and c.LangCode = 'TR'
where s.ItemTypeCode = 1
      and s.OperationDate between '1900-01-01' and '2021-08-07'
      and s.OfficeCode <> 'M'
group by  s.StoreCode, isnull(c.CurrAccDescription, space(0)), s.OperationDate;

推荐阅读