修改一下存储过程

Question

没办法，上级在我做出来后才加功能。
原帖：http://topic.csdn.net/u/20101106/15/0a2ea7e9-210c-4fc6-8d35-e68c7bbd546c.html，方法见11楼。
在原来1,2点的要求上多了第3点要求。
表1：VIEW_DS_MOTime，Mo需求数量表(物料编码)   （物料名称）    (MO需求数量)       (需求日期)       (销售订单号)    (外发成品名称)
  ITEM_CODE         DES        TO_BE_FLLW        DATE_START       SO_NUMBER       HDREMARK
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-05                        #98-01111
21-00002-00030      塑胶C        30              2010-11-06       SO1012458
21-00002-00030      塑胶C        40              2010-11-11       SO1012458                 
.....表2：SO_LN1，销售单号与成品名称关系表(销售订单号)  (成品名称)
 INVOICE        CODE
SO1012440    #98-01012
SO1012440    #98-01013
SO1012441    #98-01018
SO1012472    #98-01014
SO1012458    #98-01015
SO1012458    #98-01016
SO1012458    #98-01017
......表VIEW_DS_MOTime中的SO_NUMBER与表SO_LN1中的INVOICE是同一类型的，2表靠它关联
我想要的效果如下：
1.获取当日所在周为1wk，本周以前的所有总计作为0wk,下周为2wk,类推每周从星期日开始(星期日没有记录，实际从星期一开始)，星期六结束，计算每周的MO需求数量和。假如今日是2010-11-06。只统计12周。
2.将每周内需求的销售订单号INVOICE的相关成品名称CODE分别列出来，并能满足1要求合并在一个表格
3.当销售订单号没有时会有外发成品，2者只会有其一，如果SO_NUMBER为空，则直接关联外发成品名称。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      110     #98-01111      40     #98-01015     ......   0
                                #98-01016              #98-01015             #98-01016
                                #98-01017              #98-01016             #98-01017
                                                       #98-01017                       数据库环境： sql server 2000

Answer 1

原来的方法：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_strdrop 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 行）
*/

Answer 2

原来的方法的测试数据跟原来的有点改变，多了一个字段HDREMARK

Answer 3

太长，大致看了下，
把 条件中 SO_Number=INVOICE 都换成 （SO_Number=INVOICE or HDREMARK=CODE）应该就可以了吧

Answer 4

如果SO_NUMBER为空 就在原来的基础上加上HDREMARK这个成品，HDREMARK与CODE并没有对等关系哦

Answer 5

是不是说so_number是空的，也要抓出来？
那不就是（SO_Number=INVOICE or SO_Number is null）

Answer 6

CREATE TABLE [VIEW_DS_MOTime]([ITEM_CODE] varchar(20) , [DES]  nvarchar(10)   ,    [TO_BE_FLLW]  int ,    [DATE_START]  DATETIME   ,    [SO_NUMBER]  VARCHAR(20),HDREMARK varchar(20) )
--插入测试数据
INSERT INTO [VIEW_DS_MOTime] 
    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-05',''         ,'#98-01111' UNION
    SELECT '21-00002-00030',N'塑胶C',30,'2010-11-06','SO1012458','' UNION
    SELECT '21-00002-00030',N'塑胶C',40,'2010-11-11','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' union 
    SELECT 'SO1012458','#98-01111'  -- 这条数据是我加的测试数据。
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.* , substring(t3.CODE,charindex('-',t3.code) + 1 , len(t3.code)) 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.HDREMARK = '' and t2.SO_NUMBER = t3.INVOICE) or (t2.HDREMARK <> '' and t2.HDREMARK = t3.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.* , substring(t3.CODE,charindex('-',t3.code) + 1 , len(t3.code)) 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.HDREMARK = '' and t2.SO_NUMBER = t3.INVOICE) or (t2.HDREMARK <> '' and t2.HDREMARK = t3.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,desdrop 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          01012,01013,01018                                                                                                                                                                                                                                                10           01014                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
21-00002-00030       塑胶C        50           01015,01016,01017,01111                                                                                                                                                                                                                                          110          01015,01016,01017,01111                                                                                                                                                                                                                                          40           01015,01016,01017,01111                                                                                                                                                                                                                                                       （所影响的行数为 2 行）
*/

Answer 7

谢谢啊，请大乌龟发此贴到该处：http://topic.csdn.net/u/20101109/14/7cea184e-3341-48eb-a75f-5c04ae41f8dd.html，我拿到项目上测试再回帖

Answer 8

不对哦，乌龟，SELECT 'SO1012458','#98-01111'  -- 这条数据是我加的测试数据。这个数据在那个表里是不出现的哦

Answer 9

只在VIEW_DS_MOTime这个表出现，SO_LN1表里是没有的