使用SQl 计算期初

zouyong3868 2017-03-14 10:38:58

从启用日期开始算，

...全文

801 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

zouyong3868 2017-03-15

打赏
举报

引用 15 楼 ch21st 的回复:


---2012 +
WITH A(Item,StockID,Qty)AS(
    SELECT '010101',5,1000 UNION ALL
    SELECT '010102',5,2000
),B(Item,StockID,InQty,OutQty,TransDate)AS(
    SELECT '010101',5,100,50,CONVERT(DATE,'2016-01-08') UNION ALL
    SELECT '010102',5,100,50,'2016-01-08' UNION ALL
    SELECT '010101',5,50,5,'2016-01-09'
)
SELECT SUM(b.InQty-b.OutQty)OVER( PARTITION BY b.Item,b.StockID ORDER BY b.TransDate)-(b.InQty-b.OutQty)+ISNULL(a.Qty,0) AS FromQty
       ,b.Item,b.StockID,b.InQty,b.OutQty,b.TransDate,SUM(b.InQty-b.OutQty)OVER( PARTITION BY b.Item,b.StockID ORDER BY b.TransDate)+ISNULL(a.Qty,0) AS ToQty
FROM b LEFT JOIN A ON b.Item=a.Item AND b.StockID=a.StockID

--under 2012
WITH A(Item,StockID,Qty)AS(
    SELECT '010101',5,1000 UNION ALL
    SELECT '010102',5,2000
),B(Item,StockID,InQty,OutQty,TransDate)AS(
    SELECT '010101',5,100,50,CONVERT(DATE,'2016-01-08') UNION ALL
    SELECT '010102',5,100,50,'2016-01-08' UNION ALL
    SELECT '010101',5,50,5,'2016-01-09'
)
SELECT  p.p_in_qty-(b.InQty-b.OutQty)+ISNULL(a.Qty,0) AS FromQty
       ,b.Item,b.StockID,b.InQty,b.OutQty,b.TransDate,p.p_in_qty+ISNULL(a.Qty,0) AS ToQty
FROM b 
OUTER APPLY(SELECT SUM(InQty-OutQty) AS p_in_qty FROM b AS b1 WHERE b1.Item=b.Item AND b1.StockID=b.StockID AND  DATEDIFF(d,b1.TransDate,b.TransDate)>=0) AS p
LEFT JOIN A ON b.Item=a.Item AND b.StockID=a.StockID


FromQty     Item   StockID     InQty       OutQty      TransDate  ToQty
----------- ------ ----------- ----------- ----------- ---------- -----------
1000        010101 5           100         50          2016-01-08 1050
2000        010102 5           100         50          2016-01-08 2050
1050        010101 5           50          5           2016-01-09 1095

谢谢，我的sql是2005的不知道可以不

道素 2017-03-15

打赏
举报

引用 16 楼 zouyong3868 的回复:

[quote=引用 15 楼 ch21st 的回复:]


---2012 +
WITH A(Item,StockID,Qty)AS(
    SELECT '010101',5,1000 UNION ALL
    SELECT '010102',5,2000
),B(Item,StockID,InQty,OutQty,TransDate)AS(
    SELECT '010101',5,100,50,CONVERT(DATE,'2016-01-08') UNION ALL
    SELECT '010102',5,100,50,'2016-01-08' UNION ALL
    SELECT '010101',5,50,5,'2016-01-09'
)
SELECT SUM(b.InQty-b.OutQty)OVER( PARTITION BY b.Item,b.StockID ORDER BY b.TransDate)-(b.InQty-b.OutQty)+ISNULL(a.Qty,0) AS FromQty
       ,b.Item,b.StockID,b.InQty,b.OutQty,b.TransDate,SUM(b.InQty-b.OutQty)OVER( PARTITION BY b.Item,b.StockID ORDER BY b.TransDate)+ISNULL(a.Qty,0) AS ToQty
FROM b LEFT JOIN A ON b.Item=a.Item AND b.StockID=a.StockID

--under 2012
WITH A(Item,StockID,Qty)AS(
    SELECT '010101',5,1000 UNION ALL
    SELECT '010102',5,2000
),B(Item,StockID,InQty,OutQty,TransDate)AS(
    SELECT '010101',5,100,50,CONVERT(DATE,'2016-01-08') UNION ALL
    SELECT '010102',5,100,50,'2016-01-08' UNION ALL
    SELECT '010101',5,50,5,'2016-01-09'
)
SELECT  p.p_in_qty-(b.InQty-b.OutQty)+ISNULL(a.Qty,0) AS FromQty
       ,b.Item,b.StockID,b.InQty,b.OutQty,b.TransDate,p.p_in_qty+ISNULL(a.Qty,0) AS ToQty
FROM b 
OUTER APPLY(SELECT SUM(InQty-OutQty) AS p_in_qty FROM b AS b1 WHERE b1.Item=b.Item AND b1.StockID=b.StockID AND  DATEDIFF(d,b1.TransDate,b.TransDate)>=0) AS p
LEFT JOIN A ON b.Item=a.Item AND b.StockID=a.StockID


FromQty     Item   StockID     InQty       OutQty      TransDate  ToQty
----------- ------ ----------- ----------- ----------- ---------- -----------
1000        010101 5           100         50          2016-01-08 1050
2000        010102 5           100         50          2016-01-08 2050
1050        010101 5           50          5           2016-01-09 1095

谢谢，我的sql是2005的不知道可以不[/quote] 你用后面 under 2012的方法

道素 2017-03-15

打赏
举报


---2012 +
WITH A(Item,StockID,Qty)AS(
    SELECT '010101',5,1000 UNION ALL
    SELECT '010102',5,2000
),B(Item,StockID,InQty,OutQty,TransDate)AS(
    SELECT '010101',5,100,50,CONVERT(DATE,'2016-01-08') UNION ALL
    SELECT '010102',5,100,50,'2016-01-08' UNION ALL
    SELECT '010101',5,50,5,'2016-01-09'
)
SELECT SUM(b.InQty-b.OutQty)OVER( PARTITION BY b.Item,b.StockID ORDER BY b.TransDate)-(b.InQty-b.OutQty)+ISNULL(a.Qty,0) AS FromQty
       ,b.Item,b.StockID,b.InQty,b.OutQty,b.TransDate,SUM(b.InQty-b.OutQty)OVER( PARTITION BY b.Item,b.StockID ORDER BY b.TransDate)+ISNULL(a.Qty,0) AS ToQty
FROM b LEFT JOIN A ON b.Item=a.Item AND b.StockID=a.StockID

--under 2012
WITH A(Item,StockID,Qty)AS(
    SELECT '010101',5,1000 UNION ALL
    SELECT '010102',5,2000
),B(Item,StockID,InQty,OutQty,TransDate)AS(
    SELECT '010101',5,100,50,CONVERT(DATE,'2016-01-08') UNION ALL
    SELECT '010102',5,100,50,'2016-01-08' UNION ALL
    SELECT '010101',5,50,5,'2016-01-09'
)
SELECT  p.p_in_qty-(b.InQty-b.OutQty)+ISNULL(a.Qty,0) AS FromQty
       ,b.Item,b.StockID,b.InQty,b.OutQty,b.TransDate,p.p_in_qty+ISNULL(a.Qty,0) AS ToQty
FROM b 
OUTER APPLY(SELECT SUM(InQty-OutQty) AS p_in_qty FROM b AS b1 WHERE b1.Item=b.Item AND b1.StockID=b.StockID AND  DATEDIFF(d,b1.TransDate,b.TransDate)>=0) AS p
LEFT JOIN A ON b.Item=a.Item AND b.StockID=a.StockID


FromQty     Item   StockID     InQty       OutQty      TransDate  ToQty
----------- ------ ----------- ----------- ----------- ---------- -----------
1000        010101 5           100         50          2016-01-08 1050
2000        010102 5           100         50          2016-01-08 2050
1050        010101 5           50          5           2016-01-09 1095

道素 2017-03-15

打赏
举报

如果是SQL Server 2012 +


DECLARE @InitQty INT =100,@StartDate DATE='2016-08-01'
;WITH tb(in_Date,in_Qty)AS(
    select CONVERT(DATE,'2016-10-12'),50 union all
    select '2016-10-13',100 union ALL
    select '2016-10-14',100 union ALL
    select '2016-10-15',100
)
SELECT *,SUM(IN_qty)OVER(ORDER BY tb.in_Date)-tb.in_Qty+@InitQty AS FromQty
        ,SUM(IN_qty)OVER(ORDER BY tb.in_Date)+@InitQty AS ToQty 
FROM tb
WHERE DATEDIFF(d,@StartDate,in_Date)>0

--如果是低于2012
SELECT *,p.p_in_qty-tb.in_qty AS FromQty,p.p_in_qty+@InitQty AS ToQty
--,SUM(IN_qty)OVER(ORDER BY tb.in_Date)-tb.in_Qty+@InitQty AS FromQty
 --,SUM(IN_qty)OVER(ORDER BY tb.in_Date)+@InitQty AS ToQty 
FROM tb
OUTER APPLY(SELECT SUM(in_qty) AS p_in_qty FROM TB AS t1 WHERE DATEDIFF(d,t1.in_Date,tb.in_Date)>=0) AS p
WHERE DATEDIFF(d,@StartDate,in_Date)>0


in_Date    in_Qty      FromQty     ToQty
---------- ----------- ----------- -----------
2016-10-12 50          100         150
2016-10-13 100         150         250
2016-10-14 100         250         350
2016-10-15 100         350         450

zouyong3868 2017-03-14

打赏
举报

引用 5 楼 sinat_28984567 的回复:

[quote=引用 4 楼 zouyong3868 的回复:] [quote=引用 3 楼 sinat_28984567 的回复:] [quote=引用 2 楼 zouyong3868 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 额。可以把问题再描述清楚一点，或者画出来你想要的结果。

就是每行数据自动计算期初数量[/quote] select *,(期末数量-入库数量) as 期初数量 from 表 [/quote] 要让这个数量自动变成下一行的期初。[/quote] 是想让这条的期末作为下一条的期初？[/quote] 是的

二月十六 2017-03-14

打赏
举报

引用 4 楼 zouyong3868 的回复:

[quote=引用 3 楼 sinat_28984567 的回复:] [quote=引用 2 楼 zouyong3868 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 额。可以把问题再描述清楚一点，或者画出来你想要的结果。

就是每行数据自动计算期初数量[/quote] select *,(期末数量-入库数量) as 期初数量 from 表 [/quote] 要让这个数量自动变成下一行的期初。[/quote] 是想让这条的期末作为下一条的期初？

zouyong3868 2017-03-14

打赏
举报

引用 3 楼 sinat_28984567 的回复:

[quote=引用 2 楼 zouyong3868 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 额。可以把问题再描述清楚一点，或者画出来你想要的结果。

就是每行数据自动计算期初数量[/quote] select *,(期末数量-入库数量) as 期初数量 from 表 [/quote] 要让这个数量自动变成下一行的期初。

二月十六 2017-03-14

打赏
举报

引用 2 楼 zouyong3868 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:] 额。可以把问题再描述清楚一点，或者画出来你想要的结果。

就是每行数据自动计算期初数量[/quote] select *,(期末数量-入库数量) as 期初数量 from 表

zouyong3868 2017-03-14

打赏
举报

引用 1 楼 sinat_28984567 的回复:

额。可以把问题再描述清楚一点，或者画出来你想要的结果。

就是每行数据自动计算期初数量

二月十六 2017-03-14

打赏
举报

额。可以把问题再描述清楚一点，或者画出来你想要的结果。

zouyong3868 2017-03-14

打赏
举报

引用 12 楼 sinat_28984567 的回复:

[quote=引用 11 楼 zouyong3868 的回复:] 你看下这样行不

看不出来。你肯定是想通过现有的数据算出来一列数据吧？现有的有那几个？数量，日期，入库和期末？还是日期、期初、入库和期末？[/quote] 第一个表的数据全部都有第二个表里面除了期初和结存全部都有

二月十六 2017-03-14

打赏
举报

引用 11 楼 zouyong3868 的回复:

你看下这样行不

看不出来。你肯定是想通过现有的数据算出来一列数据吧？现有的有那几个？数量，日期，入库和期末？还是日期、期初、入库和期末？

zouyong3868 2017-03-14

打赏
举报

你看下这样行不

二月十六 2017-03-14

打赏
举报

如果还不对，提供下测试数据。

if not object_id(N'Tempdb..#T') is null

    drop table #T

Go

Create table #T(日期 DATE,入库数量 INT,期末数量 INT)

Insert #T

select '2016-10-12',50,150 union all

select '2016-10-13',100,250 union ALL

select '2016-10-14',100,350 union ALL

select '2016-10-15',100,450 

Go

--测试数据结束

;WITH    temptab

          AS ( SELECT   * ,

                        ROW_NUMBER() OVER ( ORDER BY #T.日期 ) AS num

               FROM     #T

             )

    SELECT  b.日期 ,

            ( CASE WHEN b.num = 1 THEN ( b.期末数量 - b.入库数量 )

                   ELSE a.期末数量

              END ) AS 期初数量 ,

            b.入库数量 ,

            b.期末数量

    FROM    temptab b

            LEFT JOIN temptab a ON b.num - 1 = a.num

结果：

RINK_1 2017-03-14

打赏
举报

WITH CTE AS (SELECT *,ROW_NUMBER() OVER (ORDER BY 日期 ASC) AS RN FROM TABLE) SELECT A.日期, CASE WHEN A.RN=1 THEN A.期末数量-A.入库数量 ELSE B.期末数量 END AS 期初数量, A.入库数量, A.期末数量 FROM CTE A LEFT JOIN CTE B ON A.RN=B.RN+1 ORDER BY A.日期

zouyong3868 2017-03-14

打赏
举报

引用 7 楼 sinat_28984567 的回复:

语句：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T(日期 DATE,入库数量 INT,期末数量 INT)
Insert #T
select '2016-10-12',50,150 union all
select '2016-10-13',100,250 union ALL
select '2016-10-14',100,350 union ALL
select '2016-10-15',100,450 
Go
--测试数据结束
;WITH temptab AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY #T.日期) AS num FROM #T
)
SELECT  b.日期 ,
        a.期末数量 AS 期初数量 ,
        b.入库数量 ,
        b.期末数量
FROM    temptab b
        LEFT JOIN temptab a ON b.num - 1 = a.num

结果：

第一行的期初也是有数据的是从使用那天开始其他表中获取到的数量应该如何处理？

二月十六 2017-03-14

打赏
举报

语句：

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T(日期 DATE,入库数量 INT,期末数量 INT)

Insert #T

select '2016-10-12',50,150 union all

select '2016-10-13',100,250 union ALL

select '2016-10-14',100,350 union ALL

select '2016-10-15',100,450 

Go

--测试数据结束

;WITH temptab AS (

SELECT *,ROW_NUMBER()OVER(ORDER BY #T.日期) AS num FROM #T

)

SELECT  b.日期 ,

        a.期末数量 AS 期初数量 ,

        b.入库数量 ,

        b.期末数量

FROM    temptab b

        LEFT JOIN temptab a ON b.num - 1 = a.num