用友U8材料出库单的月出库量

USE [UFDATA_001_2018]

GO

/****** Object: StoredProcedure [dbo].[P_Quck_MonthOutRd11] Script Date: 2023-02-23 11:33:19 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--材料出库单的月出库量

ALTER PROCEDURE [dbo].[P_Quck_MonthOutRd11]

@opedate NVARCHAR(30)

AS

DECLARE @staredate1 DATETIME,

@enddate1 DATETIME,

@staredate2 DATETIME,

@enddate2 DATETIME,

@staredate3 DATETIME,

@enddate3 DATETIME,

@staredate4 DATETIME,

@enddate4 DATETIME,

@staredate5 DATETIME,

@enddate5 DATETIME,

@staredate6 DATETIME,

@enddate6 DATETIME,

@staredate7 DATETIME,

@enddate7 DATETIME,

@staredate8 DATETIME,

@enddate8 DATETIME,

@staredate9 DATETIME,

@enddate9 DATETIME,

@staredate10 DATETIME,

@enddate10 DATETIME,

@staredate11 DATETIME,

@enddate11 DATETIME,

@staredate12 DATETIME,

@enddate12 DATETIME;

SET @staredate1 = @opedate + '-01-01'; --获取一个日期月开始

SET @enddate1 = CONVERT(VARCHAR(20), DATEADD(d, -DAY(@opedate + '-01-01'), DATEADD(m, 1, @opedate + '-01-01')), 23); --获取一个日期月结束

SET @staredate2 = @opedate + '-02-01'; --获取一个日期月开始

SET @enddate2 = CONVERT(VARCHAR(20), DATEADD(d, -DAY(@opedate + '-02-01'), DATEADD(m, 1, @opedate + '-02-01')), 23); --获取一个日期月结束

SET @staredate3 = @opedate + '-03-01'; --获取一个日期月开始

SET @enddate3 = CONVERT(VARCHAR(20), DATEADD(d, -DAY(@opedate + '-03-01'), DATEADD(m, 1, @opedate + '-03-01')), 23); --获取一个日期月结束

SET @staredate4 = @opedate + '-04-01'; --获取一个日期月开始

SET @enddate4 = CONVERT(VARCHAR(20), DATEADD(d, -DAY(@opedate + '-04-01'), DATEADD(m, 1, @opedate + '-04-01')), 23); --获取一个日期月结束

SET @staredate5 = @opedate + '-05-01'; --获取一个日期月开始

SET @enddate5 = CONVERT(VARCHAR(20), DATEADD(d, -DAY(@opedate + '-05-01'), DATEADD(m, 1, @opedate + '-05-01')), 23); --获取一个日期月结束

SET @staredate6 = @opedate + '-06-01'; --获取一个日期月开始

SET @enddate6 = CONVERT(VARCHAR(20), DATEADD(d, -DAY(@opedate + '-06-01'), DATEADD(m, 1, @opedate + '-06-01')), 23); --获取一个日期月结束

SET @staredate7 = @opedate + '-07-01'; --获取一个日期月开始

SET @enddate7 = CONVERT(VARCHAR(20), DATEADD(d, -DAY(@opedate + '-07-01'), DATEADD(m, 1, @opedate + '-07-01')), 23); --获取一个日期月结束

SET @staredate8 = @opedate + '-08-01'; --获取一个日期月开始

SET @enddate8 = CONVERT(VARCHAR(20), DATEADD(d, -DAY(@opedate + '-08-01'), DATEADD(m, 1, @opedate + '-08-01')), 23); --获取一个日期月结束

SET @staredate9 = @opedate + '-09-01'; --获取一个日期月开始

SET @enddate9 = CONVERT(VARCHAR(20), DATEADD(d, -DAY(@opedate + '-09-01'), DATEADD(m, 1, @opedate + '-09-01')), 23); --获取一个日期月结束

SET @staredate10 = @opedate + '-10-01'; --获取一个日期月开始

SET @enddate10 = CONVERT(VARCHAR(20), DATEADD(d, -DAY(@opedate + '-10-01'), DATEADD(m, 1, @opedate + '-10-01')), 23); --获取一个日期月结束

SET @staredate11 = @opedate + '-11-01'; --获取一个日期月开始

SET @enddate11 = CONVERT(VARCHAR(20), DATEADD(d, -DAY(@opedate + '-11-01'), DATEADD(m, 1, @opedate + '-11-01')), 23); --获取一个日期月结束

SET @staredate12 = @opedate + '-12-01'; --获取一个日期月开始

SET @enddate12 = CONVERT(VARCHAR(20), DATEADD(d, -DAY(@opedate + '-12-01'), DATEADD(m, 1, @opedate + '-12-01')), 23); --获取一个日期月结束

SELECT DISTINCT

a.存货编码,

a.存货名称,

a.规格型号,

SUM(a.一月份) AS 一月,

SUM(a.二月份) AS 二月,

SUM(a.三月份) AS 三月,

SUM(a.四月份) 四月,

SUM(a.五月份) AS 五月,

SUM(a.六月份) AS 六月,

SUM(a.七月份) AS 七月,

SUM(a.八月份) AS 八月,

SUM(a.九月份) AS 九月,

SUM(a.十月份) AS 十月,

SUM(a.十一月份) AS 十一月,

SUM(a.十二月份) AS 十二月

FROM

(

SELECT rdrecords11.cInvCode 存货编码,

Inventory.cInvName 存货名称,

Inventory.cInvStd 规格型号,

CASE

WHEN rdrecord11.dDate >= @staredate1

AND rdrecord11.dDate <= @enddate1 THEN

SUM(ISNULL(rdrecords11.iQuantity, 0))

ELSE

0

END 一月份,

CASE

WHEN rdrecord11.dDate >= @staredate2

AND rdrecord11.dDate <= @enddate2 THEN

SUM(ISNULL(rdrecords11.iQuantity, 0))

ELSE

0

END 二月份,

CASE

WHEN rdrecord11.dDate >= @staredate3

AND rdrecord11.dDate <= @enddate3 THEN

SUM(ISNULL(rdrecords11.iQuantity, 0))

ELSE

0

END 三月份,

CASE

WHEN rdrecord11.dDate >= @staredate4

AND rdrecord11.dDate <= @enddate4 THEN

SUM(ISNULL(rdrecords11.iQuantity, 0))

ELSE

0

END 四月份,

CASE

WHEN rdrecord11.dDate >= @staredate5

AND rdrecord11.dDate <= @enddate5 THEN

SUM(ISNULL(rdrecords11.iQuantity, 0))

ELSE

0

END 五月份,

CASE

WHEN rdrecord11.dDate >= @staredate6

AND rdrecord11.dDate <= @enddate6 THEN

SUM(ISNULL(rdrecords11.iQuantity, 0))

ELSE

0

END 六月份,

CASE

WHEN rdrecord11.dDate >= @staredate7

AND rdrecord11.dDate <= @enddate7 THEN

SUM(ISNULL(rdrecords11.iQuantity, 0))

ELSE

0

END 七月份,

CASE

WHEN rdrecord11.dDate >= @staredate8

AND rdrecord11.dDate <= @enddate8 THEN

SUM(ISNULL(rdrecords11.iQuantity, 0))

ELSE

0

END 八月份,

CASE

WHEN rdrecord11.dDate >= @staredate9

AND rdrecord11.dDate <= @enddate9 THEN

SUM(ISNULL(rdrecords11.iQuantity, 0))

ELSE

0

END 九月份,

CASE

WHEN rdrecord11.dDate >= @staredate10

AND rdrecord11.dDate <= @enddate10 THEN

SUM(ISNULL(rdrecords11.iQuantity, 0))

ELSE

0

END 十月份,

CASE

WHEN rdrecord11.dDate >= @staredate11

AND rdrecord11.dDate <= @enddate11 THEN

SUM(ISNULL(rdrecords11.iQuantity, 0))

ELSE

0

END 十一月份,

CASE

WHEN rdrecord11.dDate >= @staredate12

AND rdrecord11.dDate <= @enddate12 THEN

SUM(ISNULL(rdrecords11.iQuantity, 0))

ELSE

0

END 十二月份

FROM rdrecord11

JOIN rdrecords11

ON rdrecord11.ID = rdrecords11.ID

JOIN Inventory

ON Inventory.cInvCode = rdrecords11.cInvCode

WHERE ISNULL(rdrecords11.iQuantity, 0) <> 0

AND rdrecord11.dDate >= @staredate1

GROUP BY rdrecords11.cInvCode,

Inventory.cInvName,

Inventory.cInvStd,

rdrecord11.dDate

) a

GROUP BY a.存货编码,

a.存货名称,

a.规格型号;