用友U8采购入库单的月入库量

USE [UFDATA_001_2018]

GO

/****** Object: StoredProcedure [dbo].[P_Quck_MonthOutRd01] Script Date: 2023-02-23 11:32:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--采购入库单的月入库量

ALTER PROCEDURE [dbo].[P_Quck_MonthOutRd01] @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 rdrecords01.cInvCode 存货编码,

Inventory.cInvName 存货名称,

Inventory.cInvStd 规格型号,

CASE

WHEN RdRecord01.dDate >= @staredate1

AND RdRecord01.dDate <= @enddate1 THEN

SUM(ISNULL(rdrecords01.iQuantity, 0))

ELSE

0

END 一月份,

CASE

WHEN RdRecord01.dDate >= @staredate2

AND RdRecord01.dDate <= @enddate2 THEN

SUM(ISNULL(rdrecords01.iQuantity, 0))

ELSE

0

END 二月份,

CASE

WHEN RdRecord01.dDate >= @staredate3

AND RdRecord01.dDate <= @enddate3 THEN

SUM(ISNULL(rdrecords01.iQuantity, 0))

ELSE

0

END 三月份,

CASE

WHEN RdRecord01.dDate >= @staredate4

AND RdRecord01.dDate <= @enddate4 THEN

SUM(ISNULL(rdrecords01.iQuantity, 0))

ELSE

0

END 四月份,

CASE

WHEN RdRecord01.dDate >= @staredate5

AND RdRecord01.dDate <= @enddate5 THEN

SUM(ISNULL(rdrecords01.iQuantity, 0))

ELSE

0

END 五月份,

CASE

WHEN RdRecord01.dDate >= @staredate6

AND RdRecord01.dDate <= @enddate6 THEN

SUM(ISNULL(rdrecords01.iQuantity, 0))

ELSE

0

END 六月份,

CASE

WHEN RdRecord01.dDate >= @staredate7

AND RdRecord01.dDate <= @enddate7 THEN

SUM(ISNULL(rdrecords01.iQuantity, 0))

ELSE

0

END 七月份,

CASE

WHEN RdRecord01.dDate >= @staredate8

AND RdRecord01.dDate <= @enddate8 THEN

SUM(ISNULL(rdrecords01.iQuantity, 0))

ELSE

0

END 八月份,

CASE

WHEN RdRecord01.dDate >= @staredate9

AND RdRecord01.dDate <= @enddate9 THEN

SUM(ISNULL(rdrecords01.iQuantity, 0))

ELSE

0

END 九月份,

CASE

WHEN RdRecord01.dDate >= @staredate10

AND RdRecord01.dDate <= @enddate10 THEN

SUM(ISNULL(rdrecords01.iQuantity, 0))

ELSE

0

END 十月份,

CASE

WHEN RdRecord01.dDate >= @staredate11

AND RdRecord01.dDate <= @enddate11 THEN

SUM(ISNULL(rdrecords01.iQuantity, 0))

ELSE

0

END 十一月份,

CASE

WHEN RdRecord01.dDate >= @staredate12

AND RdRecord01.dDate <= @enddate12 THEN

SUM(ISNULL(rdrecords01.iQuantity, 0))

ELSE

0

END 十二月份

FROM RdRecord01

JOIN rdrecords01

ON RdRecord01.ID = rdrecords01.ID

JOIN Inventory

ON Inventory.cInvCode = rdrecords01.cInvCode

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

AND RdRecord01.dDate >= @staredate1

GROUP BY rdrecords01.cInvCode,

Inventory.cInvName,

Inventory.cInvStd,

RdRecord01.dDate

) a

GROUP BY a.存货编码,

a.存货名称,

a.规格型号;