CREATE TABLE [VIEW_DS_MOTime]([ITEM_CODE] varchar(20) , [DES] nvarchar(10) , [TO_BE_FLLW] int , [DATE_START] DATETIME , [SO_NUMBER] VARCHAR(20) null ,[HDREMARK] VARCHAR(20) null) --插入测试数据 INSERT INTO [VIEW_DS_MOTime] ([ITEM_CODE],[DES],[TO_BE_FLLW],[DATE_START],[SO_NUMBER],[HDREMARK]) SELECT '21-00001-00010',N'塑胶A',60,'2010-09-07','SO1012440',null UNION SELECT '21-00001-00010',N'塑胶A',40,'2010-09-28','SO1012441',null UNION SELECT '21-00001-00010',N'塑胶A',10,'2010-11-05','SO1012472',null UNION SELECT '21-00002-00030',N'塑胶C',50,'2010-09-11','SO1012458',null UNION SELECT '21-00002-00030',N'塑胶C',80,'2010-11-11',null,'#98-01015' UNION SELECT '21-00002-00030',N'塑胶C',30,'2010-11-12','SO1012458',null UNION SELECT '21-00002-00030',N'塑胶C',40,'2010-11-17','SO1012458',null 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 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.* , t2.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, ( select m.[ITEM_CODE],m.[DES],m.[TO_BE_FLLW],m.[DATE_START],n.[CODE] from [VIEW_DS_MOTime] m,[SO_LN1] n where (m.[SO_NUMBER] = n.[INVOICE] or m.[HDREMARK] = n.[CODE]) ) t2 where t1.ITEM_CODE = t2.ITEM_CODE 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.* , t2.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, ( select m.[ITEM_CODE],m.[DES],m.[TO_BE_FLLW],m.[DATE_START],n.[CODE] from [VIEW_DS_MOTime] m,[SO_LN1] n where (m.[SO_NUMBER] = n.[INVOICE] or m.[HDREMARK] = n.[CODE]) ) t2 where t1.ITEM_CODE = t2.ITEM_CODE 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楼主看看能不能满足你的要求吧。。
CREATE TABLE [VIEW_DS_MOTime]([ITEM_CODE] varchar(20) , [DES] nvarchar(10) , [TO_BE_FLLW] int , [DATE_START] DATETIME , [SO_NUMBER] VARCHAR(20) null ,[HDREMARK] VARCHAR(20) null)
--插入测试数据
INSERT INTO [VIEW_DS_MOTime] ([ITEM_CODE],[DES],[TO_BE_FLLW],[DATE_START],[SO_NUMBER],[HDREMARK])
SELECT '21-00001-00010',N'塑胶A',60,'2010-09-07','SO1012440',null UNION
SELECT '21-00001-00010',N'塑胶A',40,'2010-09-28','SO1012441',null UNION
SELECT '21-00001-00010',N'塑胶A',10,'2010-11-05','SO1012472',null UNION
SELECT '21-00002-00030',N'塑胶C',50,'2010-09-11','SO1012458',null UNION
SELECT '21-00002-00030',N'塑胶C',80,'2010-11-11',null,'#98-01015' UNION
SELECT '21-00002-00030',N'塑胶C',30,'2010-11-12','SO1012458',null UNION
SELECT '21-00002-00030',N'塑胶C',40,'2010-11-17','SO1012458',null
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
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.* , t2.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,
(
select m.[ITEM_CODE],m.[DES],m.[TO_BE_FLLW],m.[DATE_START],n.[CODE] from [VIEW_DS_MOTime] m,[SO_LN1] n where (m.[SO_NUMBER] = n.[INVOICE] or m.[HDREMARK] = n.[CODE])
) t2
where t1.ITEM_CODE = t2.ITEM_CODE
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.* , t2.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,
(
select m.[ITEM_CODE],m.[DES],m.[TO_BE_FLLW],m.[DATE_START],n.[CODE] from [VIEW_DS_MOTime] m,[SO_LN1] n where (m.[SO_NUMBER] = n.[INVOICE] or m.[HDREMARK] = n.[CODE])
) t2
where t1.ITEM_CODE = t2.ITEM_CODE
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楼主看看能不能满足你的要求吧。。