22,210
社区成员
发帖
与我相关
我的任务
分享
(物料编码) (物料名称) (MO需求数量) (需求日期) (销售订单号)
ITEM_CODE DES TO_BE_FLLW DATE_START SO_NUMBER
21-00001-00010 塑胶A 60 2010-09-07 SO1012440
21-00001-00010 塑胶A 40 2010-09-28 SO1012441
21-00001-00010 塑胶A 10 2010-11-05 SO1012472
21-00002-00030 塑胶C 50 2010-09-11 SO1012458
21-00002-00030 塑胶C 80 2010-11-11 SO1012458
21-00002-00030 塑胶C 30 2010-11-12 SO1012458
21-00002-00030 塑胶C 40 2010-11-17 SO1012458
.....
(销售订单号) (成品名称)
INVOICE CODE
SO1012440 #98-01012
SO1012440 #98-01013
SO1012441 #98-01018
SO1012472 #98-01014
SO1012458 #98-01015
SO1012458 #98-01016
SO1012458 #98-01017
......
ITEM_CODE DES 0wk 0wk需求成品 1wk 1wk需求成品 2wk 2wk需求成品 ...... 12wk
21-00001-00010 塑胶A 100 #98-01012 10 #98-01014 0 ...... 0
#98-01013
#98-01018
21-00002-00030 塑胶C 50 #98-01015 0 110 #98-01015 ...... 0
#98-01016 #98-01016
#98-01017 #98-01017
CREATE TABLE [VIEW_DS_MOTime]([ITEM_CODE] varchar(20) , [DES] nvarchar(10) , [TO_BE_FLLW] int , [DATE_START] DATETIME , [SO_NUMBER] VARCHAR(20) )
--插入测试数据
INSERT INTO [VIEW_DS_MOTime] ([ITEM_CODE],[DES],[TO_BE_FLLW],[DATE_START],[SO_NUMBER])
SELECT '21-00001-00010',N'塑胶A',60,'2010-09-07','SO1012440' UNION
SELECT '21-00001-00010',N'塑胶A',40,'2010-09-28','SO1012441' UNION
SELECT '21-00001-00010',N'塑胶A',10,'2010-11-05','SO1012472' UNION
SELECT '21-00002-00030',N'塑胶C',50,'2010-09-11','SO1012458' UNION
SELECT '21-00002-00030',N'塑胶C',80,'2010-11-11','SO1012458' UNION
SELECT '21-00002-00030',N'塑胶C',30,'2010-11-12','SO1012458' UNION
SELECT '21-00002-00030',N'塑胶C',40,'2010-11-17','SO1012458'
CREATE TABLE [SO_LN1]( [INVOICE] VARCHAR(20) , [CODE] VARCHAR(20) )
--插入测试数据
INSERT INTO [SO_LN1] ([INVOICE],[CODE])
SELECT 'SO1012440','#98-01012' UNION
SELECT 'SO1012440','#98-01013' UNION
SELECT 'SO1012441','#98-01018' UNION
SELECT 'SO1012472','#98-01014' UNION
SELECT 'SO1012458','#98-01015' UNION
SELECT 'SO1012458','#98-01016' UNION
SELECT 'SO1012458','#98-01017'
GO
--创建函数实现code的合并
create function dbo.f_str(@ITEM_CODE varchar(20),@des varchar(20) ,@date_num int, @dt datetime) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ',' , '') + cast(code as varchar) from
(
select distinct t1.* , t3.CODE from
(
SELECT m.ITEM_CODE,m.DES,sum(m.TO_BE_FLLW) TO_BE_FLLW ,case when datediff(week,@dt,m.date_start) + 1 <= 0 then 0 else datediff(week,@dt,m.date_start) + 1 end date_num FROM [VIEW_DS_MOTime] m group by m.ITEM_CODE,m.DES,case when datediff(week,@dt,m.date_start) + 1 <= 0 then 0 else datediff(week,@dt,m.date_start) + 1 end
) t1,VIEW_DS_MOTime t2, SO_LN1 t3
where t1.ITEM_CODE = t2.ITEM_CODE and t2.SO_NUMBER = t3.INVOICE
and (case when datediff(week,@dt,t2.date_start) + 1 <= 0 then 0 else datediff(week,@dt,t2.date_start) + 1 end) = t1.date_num
) o
where ITEM_CODE = @ITEM_CODE and des = @des and date_num = @date_num
return @str
end
go
--定义日期以决定当周
declare @dt as datetime
set @dt = '2010-11-06'
declare @sql varchar(8000)
set @sql = 'select ITEM_CODE,des '
select @sql = @sql + ' , max(case date_num when ''' + cast(date_num as varchar) + ''' then ltrim(to_be_fllw) else '''' end) [' + cast(date_num as varchar) + 'wk]'
+ ' , max(case date_num when ''' + cast(date_num as varchar) + ''' then code else '''' end) [' + cast(date_num as varchar) + 'wk需求成品]'
from (select distinct date_num from
(
SELECT case when datediff(week,@dt,date_start) + 1 <= 0 then 0 else datediff(week,@dt,date_start) + 1 end date_num FROM [VIEW_DS_MOTime]
) m) as a
set @sql = @sql + ' from
(
select ITEM_CODE , des , date_num , max(to_be_fllw) to_be_fllw, code = dbo.f_str(ITEM_CODE , des , date_num,''' +convert(varchar(10),@dt,120) + ''') from
(
select distinct t1.* , t3.CODE from
(
SELECT m.ITEM_CODE,m.DES,sum(m.TO_BE_FLLW) TO_BE_FLLW ,case when datediff(week,''' +convert(varchar(10),@dt,120) + ''',m.date_start) + 1 <= 0 then 0 else datediff(week,''' +convert(varchar(10),@dt,120) + ''',m.date_start) + 1 end date_num FROM [VIEW_DS_MOTime] m group by m.ITEM_CODE,m.DES,case when datediff(week,''' +convert(varchar(10),@dt,120) + ''',m.date_start) + 1 <= 0 then 0 else datediff(week,''' +convert(varchar(10),@dt,120) + ''',m.date_start) + 1 end
) t1,VIEW_DS_MOTime t2, SO_LN1 t3
where t1.ITEM_CODE = t2.ITEM_CODE and t2.SO_NUMBER = t3.INVOICE
and (case when datediff(week,''' +convert(varchar(10),@dt,120) + ''',t2.date_start) + 1 <= 0 then 0 else datediff(week,''' +convert(varchar(10),@dt,120) + ''',t2.date_start) + 1 end) = t1.date_num
) o
group by ITEM_CODE , des , date_num
) m
group by ITEM_CODE,des'
exec(@sql)
drop function dbo.f_str
drop table VIEW_DS_MOTime , SO_LN1
/*
ITEM_CODE des 0wk 0wk需求成品 1wk 1wk需求成品 2wk 2wk需求成品 3wk 3wk需求成品
-------------------- ---------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
21-00001-00010 塑胶A 100 #98-01012,#98-01013,#98-01018 10 #98-01014
21-00002-00030 塑胶C 50 #98-01015,#98-01016,#98-01017 110 #98-01015,#98-01016,#98-01017 40 #98-01015,#98-01016,#98-01017
*/
CREATE TABLE [VIEW_DS_MOTime]([ITEM_CODE] varchar(20) , [DES] nvarchar(10) , [TO_BE_FLLW] int , [DATE_START] DATETIME , [SO_NUMBER] VARCHAR(20) )
--插入测试数据
INSERT INTO [VIEW_DS_MOTime] ([ITEM_CODE],[DES],[TO_BE_FLLW],[DATE_START],[SO_NUMBER])
SELECT '21-00001-00010',N'塑胶A',60,'2010-09-07','SO1012440' UNION
SELECT '21-00001-00010',N'塑胶A',40,'2010-09-28','SO1012441' UNION
SELECT '21-00001-00010',N'塑胶A',10,'2010-11-05','SO1012472' UNION
SELECT '21-00002-00030',N'塑胶C',50,'2010-09-11','SO1012458' UNION
SELECT '21-00002-00030',N'塑胶C',80,'2010-11-11','SO1012458' UNION
SELECT '21-00002-00030',N'塑胶C',30,'2010-11-12','SO1012458' UNION
SELECT '21-00002-00030',N'塑胶C',40,'2010-11-17','SO1012458'
CREATE TABLE [SO_LN1]( [INVOICE] VARCHAR(20) , [CODE] VARCHAR(20) )
--插入测试数据
INSERT INTO [SO_LN1] ([INVOICE],[CODE])
SELECT 'SO1012440','#98-01012' UNION
SELECT 'SO1012440','#98-01013' UNION
SELECT 'SO1012441','#98-01018' UNION
SELECT 'SO1012472','#98-01014' UNION
SELECT 'SO1012458','#98-01015' UNION
SELECT 'SO1012458','#98-01016' UNION
SELECT 'SO1012458','#98-01017'
GO
--创建函数实现code的合并
create function dbo.f_str(@ITEM_CODE varchar(20),@des varchar(20) ,@date_num int, @dt datetime) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ',' , '') + cast(code as varchar) from
(
select distinct t1.* , t3.CODE from
(
SELECT m.ITEM_CODE,m.DES,sum(m.TO_BE_FLLW) TO_BE_FLLW ,case when datediff(week,@dt,m.date_start) + 1 <= 0 then 0 else datediff(week,@dt,m.date_start) + 1 end date_num FROM [VIEW_DS_MOTime] m group by m.ITEM_CODE,m.DES,case when datediff(week,@dt,m.date_start) + 1 <= 0 then 0 else datediff(week,@dt,m.date_start) + 1 end
) t1,VIEW_DS_MOTime t2, SO_LN1 t3
where t1.ITEM_CODE = t2.ITEM_CODE and t2.SO_NUMBER = t3.INVOICE
and (case when datediff(week,@dt,t2.date_start) + 1 <= 0 then 0 else datediff(week,@dt,t2.date_start) + 1 end) = t1.date_num
) o
where ITEM_CODE = @ITEM_CODE and des = @des and date_num = @date_num
return @str
end
go
--定义日期以决定当周
declare @dt as datetime
set @dt = '2010-11-06'
select ITEM_CODE,des,
max(case date_num when 0 then ltrim(to_be_fllw) else '' end) [0wk],
max(case date_num when 0 then code else '' end) [0wk需求成品],
max(case date_num when 1 then ltrim(to_be_fllw) else '' end) [1wk],
max(case date_num when 1 then code else '' end) [1wk需求成品],
max(case date_num when 2 then ltrim(to_be_fllw) else '' end) [2wk],
max(case date_num when 2 then code else '' end) [2wk需求成品],
max(case date_num when 3 then ltrim(to_be_fllw) else '' end) [3wk],
max(case date_num when 3 then code else '' end) [3wk需求成品]
from
(
select ITEM_CODE , des , date_num , max(to_be_fllw) to_be_fllw, code = dbo.f_str(ITEM_CODE , des , date_num,@dt) from
(
select distinct t1.* , t3.CODE from
(
SELECT m.ITEM_CODE,m.DES,sum(m.TO_BE_FLLW) TO_BE_FLLW ,case when datediff(week,@dt,m.date_start) + 1 <= 0 then 0 else datediff(week,@dt,m.date_start) + 1 end date_num FROM [VIEW_DS_MOTime] m group by m.ITEM_CODE,m.DES,case when datediff(week,@dt,m.date_start) + 1 <= 0 then 0 else datediff(week,@dt,m.date_start) + 1 end
) t1,VIEW_DS_MOTime t2, SO_LN1 t3
where t1.ITEM_CODE = t2.ITEM_CODE and t2.SO_NUMBER = t3.INVOICE
and (case when datediff(week,@dt,t2.date_start) + 1 <= 0 then 0 else datediff(week,@dt,t2.date_start) + 1 end) = t1.date_num
) o
group by ITEM_CODE , des , date_num
) k
group by ITEM_CODE,des
drop function dbo.f_str
drop table VIEW_DS_MOTime , SO_LN1
/*
ITEM_CODE des 0wk 0wk需求成品 1wk 1wk需求成品 2wk 2wk需求成品 3wk 3wk需求成品
-------------------- ---------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
21-00001-00010 塑胶A 100 #98-01012,#98-01013,#98-01018 10 #98-01014
21-00002-00030 塑胶C 50 #98-01015,#98-01016,#98-01017 110 #98-01015,#98-01016,#98-01017 40 #98-01015,#98-01016,#98-01017
(所影响的行数为 2 行)
*/
-- 创建合并函数
if object_id('fn_Limpire') is not null drop function fn_Limpire
go
CREATE FUNCTION fn_Limpire
(
@ITEM_CODE varchar(14),
@DES varchar(8),
@date datetime, -- 传入本周第一日
@index int -- 0 ~ 12
)
RETURNS nvarchar(4000) AS
BEGIN
declare @d1 datetime, @d2 datetime, @codes nvarchar(4000)
if @index = 0
select @d1 = '1753', @d2 = @date
else
select @d1 = @date, @d2 = dateadd(day, 7*@index, @date)
select @codes = isnull(@codes+char(13)+char(10), '') + b.CODE
from VIEW_DS_MOTime a inner join SO_LN1 b on a.SO_NUMBER=b.INVOICE
where a.ITEM_CODE=@ITEM_CODE and a.DES=@DES and a.DATE_START>=@d1 and a.DATE_START<@d2
group by b.CODE
return isnull(@codes, '')
END
GO
-- 本周第一日
declare @date datetime
set @date = convert(varchar, (getdate() - (@@datefirst - 1 + datepart(weekday, getdate())) % 7), 112)
-- 查询
select ITEM_CODE, DES,
sum(case when DATE_START<@date then TO_BE_FLLW else 0 end) [0wk],
max(case when DATE_START<@date then dbo.fn_Limpire(ITEM_CODE,DES,@date,0) else '' end) [0code],
sum(case datediff(day,@date,DATE_START)/7 when 0 then TO_BE_FLLW else 0 end) [1wk],
max(case datediff(day,@date,DATE_START)/7 when 0 then dbo.fn_Limpire(ITEM_CODE,DES,@date,1) else '' end) [1code],
sum(case datediff(day,@date,DATE_START)/7 when 1 then TO_BE_FLLW else 0 end) [2wk],
max(case datediff(day,@date,DATE_START)/7 when 1 then dbo.fn_Limpire(ITEM_CODE,DES,@date,2) else '' end) [2code],
sum(case datediff(day,@date,DATE_START)/7 when 2 then TO_BE_FLLW else 0 end) [3wk],
max(case datediff(day,@date,DATE_START)/7 when 2 then dbo.fn_Limpire(ITEM_CODE,DES,@date,3) else '' end) [3code],
sum(case datediff(day,@date,DATE_START)/7 when 3 then TO_BE_FLLW else 0 end) [4wk],
max(case datediff(day,@date,DATE_START)/7 when 3 then dbo.fn_Limpire(ITEM_CODE,DES,@date,4) else '' end) [4code],
sum(case datediff(day,@date,DATE_START)/7 when 4 then TO_BE_FLLW else 0 end) [5wk],
max(case datediff(day,@date,DATE_START)/7 when 4 then dbo.fn_Limpire(ITEM_CODE,DES,@date,5) else '' end) [5code],
sum(case datediff(day,@date,DATE_START)/7 when 5 then TO_BE_FLLW else 0 end) [6wk],
max(case datediff(day,@date,DATE_START)/7 when 5 then dbo.fn_Limpire(ITEM_CODE,DES,@date,6) else '' end) [6code],
sum(case datediff(day,@date,DATE_START)/7 when 6 then TO_BE_FLLW else 0 end) [7wk],
max(case datediff(day,@date,DATE_START)/7 when 6 then dbo.fn_Limpire(ITEM_CODE,DES,@date,7) else '' end) [7code],
sum(case datediff(day,@date,DATE_START)/7 when 7 then TO_BE_FLLW else 0 end) [8wk],
max(case datediff(day,@date,DATE_START)/7 when 7 then dbo.fn_Limpire(ITEM_CODE,DES,@date,8) else '' end) [8code],
sum(case datediff(day,@date,DATE_START)/7 when 8 then TO_BE_FLLW else 0 end) [9wk],
max(case datediff(day,@date,DATE_START)/7 when 8 then dbo.fn_Limpire(ITEM_CODE,DES,@date,9) else '' end) [9code],
sum(case datediff(day,@date,DATE_START)/7 when 9 then TO_BE_FLLW else 0 end) [10wk],
max(case datediff(day,@date,DATE_START)/7 when 9 then dbo.fn_Limpire(ITEM_CODE,DES,@date,10) else '' end) [10code],
sum(case datediff(day,@date,DATE_START)/7 when 10 then TO_BE_FLLW else 0 end) [11wk],
max(case datediff(day,@date,DATE_START)/7 when 10 then dbo.fn_Limpire(ITEM_CODE,DES,@date,11) else '' end) [11code],
sum(case datediff(day,@date,DATE_START)/7 when 11 then TO_BE_FLLW else 0 end) [12wk],
max(case datediff(day,@date,DATE_START)/7 when 11 then dbo.fn_Limpire(ITEM_CODE,DES,@date,12) else '' end) [12code]
from VIEW_DS_MOTime group by ITEM_CODE, DES
CREATE TABLE [#VIEW_DS_MOTime]
(
[ITEM_CODE] varchar(20) ,
[DES] nvarchar(10) ,
[TO_BE_FLLW] VARCHAR(10) ,
[DATE_START] DATETIME ,
[SO_NUMBER] VARCHAR(20)
)
GO
--插入测试数据
INSERT INTO [#VIEW_DS_MOTime] ([ITEM_CODE],[DES],[TO_BE_FLLW],[DATE_START],[SO_NUMBER])
SELECT '21-00001-00010',N'塑胶A','60','2010-09-07','SO1012440' UNION
SELECT '21-00001-00010',N'塑胶A','40','2010-09-28','SO1012441' UNION
SELECT '21-00001-00010',N'塑胶A','10','2010-11-05','SO1012472' UNION
SELECT '21-00002-00030',N'塑胶C','50','2010-09-11','SO1012458' UNION
SELECT '21-00002-00030',N'塑胶C','80','2010-11-11','SO1012458' UNION
SELECT '21-00002-00030',N'塑胶C','30','2010-11-12','SO1012458' UNION
SELECT '21-00002-00030',N'塑胶C','40','2010-11-17','SO1012458'
GO
CREATE TABLE [#SO_LN1]
(
[INVOICE] VARCHAR(20) ,
[CODE] VARCHAR(20)
)
GO
--插入测试数据
INSERT INTO [#SO_LN1] ([INVOICE],[CODE])
SELECT 'SO1012440','#98-01012' UNION
SELECT 'SO1012440','#98-01013' UNION
SELECT 'SO1012441','#98-01018' UNION
SELECT 'SO1012472','#98-01014' UNION
SELECT 'SO1012458','#98-01015' UNION
SELECT 'SO1012458','#98-01016' UNION
SELECT 'SO1012458','#98-01017'
GO
SELECT * FROM [#VIEW_DS_MOTime]
SELECT * FROM [#SO_LN1]
21-00001-00010 塑胶A 100 #98-01012 10 #98-01014 0 ...... 0
#98-01013
#98-01018