27,582
社区成员




DECLARE @TA TABLE([时间] DATETIME, [规格] VARCHAR(1), [数量] INT, [入库价格] DECIMAL(18,3), [核算价格] DECIMAL(18,3))
INSERT @TA
SELECT '2009-09-01 13:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 14:20:40', 'B', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 15:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 16:20:40', 'C', 23, 134.34, 0 UNION ALL
SELECT '2009-09-02 13:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-03 13:20:40', 'A', 23, 134.34, 0
DECLARE @TB TABLE([规格] VARCHAR(1), [库存价格] DECIMAL(18,3), [库存数量] INT)
INSERT @TB
SELECT 'A', 123.456, 23 UNION ALL
SELECT 'B', 123.456, 23 UNION ALL
SELECT 'C', 123.456, 23
SELECT A.*,B.库存价格,B.库存数量 INTO # FROM @TA AS A JOIN @TB AS B ON A.规格=B.规格 ORDER BY A.规格,时间
CREATE CLUSTERED INDEX IX_SPE ON #(规格,时间)
DECLARE @规格 VARCHAR(1), @AMT DECIMAL(18,3),@QTY INT
UPDATE #
SET @AMT=CASE WHEN @规格=规格 THEN @AMT+数量*入库价格 ELSE 数量*入库价格+库存数量*库存价格 END,
@QTY=CASE WHEN @规格=规格 THEN @QTY+数量 ELSE 数量+库存数量 END,
@规格=规格,
核算价格=@AMT/@QTY
SELECT 时间,规格,数量,入库价格,核算价格
FROM #
DROP TABLE #
/*
时间 规格 数量 入库价格 核算价格
------------------------------------------------------ ---- ----------- -------------------- --------------------
2009-09-01 13:20:40.000 A 23 134.340 128.898
2009-09-01 15:20:40.000 A 23 134.340 130.712
2009-09-02 13:20:40.000 A 23 134.340 131.619
2009-09-03 13:20:40.000 A 23 134.340 132.163
2009-09-01 14:20:40.000 B 23 134.340 128.898
2009-09-01 16:20:40.000 C 23 134.340 128.898
*/
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
DATE DATETIME,
GUIGE VARCHAR(50),
QUANTITY INT,
RPRICE NUMERIC(19,6),
HPRICE NUMERIC(19,6)
)
--SELECT 5929.308000/46
INSERT INTO A
SELECT '2009-09-01 13:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 14:20:40', 'B', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 15:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 16:20:40', 'C', 23, 134.34, 0 UNION ALL
SELECT '2009-09-02 13:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-03 13:20:40', 'A', 23, 134.34, 0
CREATE TABLE B(
GUIGE VARCHAR(50),
KPRICE NUMERIC(19,6),
KQUANTITY INT,
)
INSERT INTO B
select 'A', 123.456, 23 UNION ALL
select 'B', 123.456, 23 UNION ALL
select 'C', 123.456, 23
/*
SELECT * FROM A
SELECT * FROM B
*/
SELECT A1.GUIGE,A1.DATE
,SUM(A1.QUANTITY*A1.RPRICE)+MAX(ISNULL(B.KPRICE,0)*ISNULL(B.KQUANTITY,0)) '核算价值'
,(SUM(A1.QUANTITY)+MAX(ISNULL(B.KQUANTITY,0))) '核算数量'
,(SUM(A1.QUANTITY*A1.RPRICE)+MAX(ISNULL(B.KPRICE,0)*ISNULL(B.KQUANTITY,0)))
/(SUM(A1.QUANTITY)+MAX(ISNULL(B.KQUANTITY,0))) '核算价格'
FROM A A1
INNER JOIN A A2 ON A1.GUIGE=A2.GUIGE AND A1.DATE>=A2.DATE
LEFT JOIN B ON A1.GUIGE=B.GUIGE
GROUP BY A1.GUIGE,A1.DATE
/*
A 2009-09-01 13:20:40.000 5929.308000 46 128.898000
A 2009-09-01 15:20:40.000 9019.128000 69 130.712000
A 2009-09-02 13:20:40.000 12108.948000 92 131.619000
A 2009-09-03 13:20:40.000 15198.768000 115 132.163200
B 2009-09-01 14:20:40.000 5929.308000 46 128.898000
C 2009-09-01 16:20:40.000 5929.308000 46 128.898000
*/
select a.GUIGE,a.DATE,(hssl+ISNULL(B.KQUANTITY,0)) as hssl,
(hsjg*hssl+ISNULL(B.KPRICE,0)*ISNULL(B.KQUANTITY,0))/(hssl+ISNULL(B.KQUANTITY,0)) as hsje
from (
SELECT A1.GUIGE,A1.DATE
,SUM(A2.QUANTITY*A2.RPRICE)/sum(A2.QUANTITY) as hsjg
,SUM(A2.QUANTITY) as hssl
FROM A A1
INNER JOIN A A2
ON A1.GUIGE=A2.GUIGE
AND A1.DATE>=A2.DATE
--LEFT JOIN B ON A1.GUIGE=B.GUIGE
GROUP BY A1.GUIGE,A1.DATE
) as a
left join b ON a.GUIGE=B.GUIGE
order by a.GUIGE,a.DATE
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
DATE DATETIME,
GUIGE VARCHAR(50),
QUANTITY INT,
RPRICE NUMERIC(19,6),
HPRICE NUMERIC(19,6)
)
--SELECT 23*134.34+123.456*23
INSERT INTO A
SELECT '2009-09-01 13:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 14:20:40', 'B', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 15:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 16:20:40', 'C', 23, 134.34, 0 UNION ALL
SELECT '2009-09-02 13:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-03 13:20:40', 'A', 23, 134.34, 0
CREATE TABLE B(
GUIGE VARCHAR(50),
KPRICE NUMERIC(19,6),
KQUANTITY INT,
)
INSERT INTO B
select 'A', 123.456, 23 UNION ALL
select 'B', 123.456, 23 UNION ALL
select 'C', 123.456, 23
/*
SELECT * FROM A
SELECT * FROM B
*/
SELECT A1.GUIGE,A1.DATE
,SUM(A2.QUANTITY*A2.RPRICE)+SUM(ISNULL(B.KPRICE,0)*ISNULL(B.KQUANTITY,0)) '核算价格'
FROM A A1
INNER JOIN A A2 ON A1.GUIGE=A2.GUIGE AND A1.DATE>=A2.DATE
LEFT JOIN B ON A1.GUIGE=B.GUIGE
GROUP BY A1.GUIGE,A1.DATE
/*
A 2009-09-01 13:20:40.000 5929.308000
A 2009-09-01 15:20:40.000 11858.616000
A 2009-09-02 13:20:40.000 17787.924000
A 2009-09-03 13:20:40.000 23717.232000
B 2009-09-01 14:20:40.000 5929.308000
C 2009-09-01 16:20:40.000 5929.308000
*/