pl-sql 本周，上周，上上周，上上上周？求修改优化

Question

以下是代码及需求   只有款号，颜色，累计销售qty，  库存qty为数据库字段，其他要通过计算得出作为字段。比如占比，上周qty等。 SELECT plu,                                                            --plu
         tstyle,                                                          --款号
         tcolor,                                                          --颜色
         SUM (-leijiqty) + SUM (kucunqty) shqty,                       --收货qty
         SUM (-leijiqty),                                            --累计销售qty
         SUM (kucunqty),                                               --库存qty
         --库存占比
         --周转（库存件数/本周选择日期的件数）         --to_char(round(decode((SUM(-leijiqty)+SUM(kucunqty)),0,null,sum(-leijiqty)/(SUM(-leijiqty)+SUM(kucunqty))*100),2),'fm9999999990.00')||'%' sth,
         SUM (qty),                                                    --本周qty
         --SUM (col),           --上周占比
         SUM (shangshangzhou),                                        --上上周qty
         SUM (shangshangszhou)                                         ----前四周
    FROM (SELECT    '''' || xf_plu plu,
                   '''' || xf_style tstyle,
                   '''' || xf_color tcolor,
                   XF_QTY || XF_QOH shqty,
                   -- sth
                   -- SUM (-leijiqty) + SUM (kucunqty) shqty,
                   XF_QTY leijiqty,
                   XF_QOH kucunqty,
                   -- sum(-leijiqty)/(SUM (-leijiqty) + SUM (kucunqty))   sth,
                   case
                      when XF_TXDATE between (  TRUNC ( SYSDATE - 7, 'DD')
                                              - TO_CHAR ( SYSDATE - 7, 'D')
                                              + 2)
                                         and (  TRUNC ( SYSDATE - 7, 'DD')
                                              - TO_CHAR ( SYSDATE - 7, 'D')
                                              + 8)
                      then
                         SUM (XF_QOH)                                    ---上周
                      else
                         0
                   end
                      as qty,
                   case
                      when     XF_TXDATE >=
                                  TRUNC (NEXT_DAY ( SYSDATE - 8, 1) + 1)
                           and XF_TXDATE <=
                                  TRUNC (NEXT_DAY ( SYSDATE - 8, 1) + 7) + 1 --本週
                      then
                         SUM (XF_QOH)
                   end
                      as col,
                   case
                      when XF_TXDATE between TRUNC (SYSDATE) - 14
                                         and TRUNC (SYSDATE) - 8       --上上周
                      then
                         SUM (XF_QOH)
                      else
                         0
                   end
                      as shangshangzhou,
                   case
                      when XF_TXDATE between TRUNC (SYSDATE)-21     
                                         and TRUNC (SYSDATE)-15         --上上上周
                      then
                         SUM (XF_QOH)
                      else
                         0
                   end
                      as shangshangszhou
              FROM (SELECT MS.XF_TXDATE,
                           ms.xf_plu,
                           ms.xf_style,
                           ms.xf_color,
                           MS.XF_QTY,
                           vw.XF_QOH
                      FROM xf_itemdm ms, BOS_QOH_VW vw         --,XF_STORE sto
                     WHERE     MS.XF_STORECODE = VW.XF_STORECODE
                           -- STO.XF_GROUP2
                           and ms.XF_TXTYPE in ('SA', 'SR')
                           AND vw.XF_STORECODE <> '5AW500'
                           AND MS.XF_GROUP4 = 'SS15'                     --新字季
                           and MS.XF_TXDATE between TO_DATE ('2015-01-01', 'yyyy-MM-dd')
                                                and TO_DATE ('2015-01-02', 'yyyy-MM-dd'))
          GROUP BY  xf_plu,
                   xf_style,
                   xf_color,
                   XF_QTY,
                   XF_QOH,
                   XF_TXDATE)
GROUP BY  plu,
         tstyle,
         tcolor,
         shqty
-- sth
order by shqty desc; --compute sum() by;
以上根据sysdate当前时间计算捞出来数据是错误的。假如我在页面上选择的日期是3月份，四月份，如用sysdate作为计算则为现在日期即五月的这样就错了。要的是我页面选择的日期如果为上个月某个星期内不是当前的则以我选择的那个日期为准，再往前推。eg.2015-03-12（本周）..这样往前推上周日期则为03-04---03-10.上上周（-03-27----03-04-03）

Answer 1

最近四周，可以将where条件限制在上上上周的周一以后
case when 日期 ... then sum() end
这个不对,应该是
sum(case when 日期条件 then 数量 else 0 end)本周的周一是TRUNC(SYSDATE-1,'D')+1 
上周的周一是TRUNC(SYSDATE-8,'D')+1
以此类推

Answer 2

按你这么写好像也不行吧！就是假如在页面我查的是3月份或四月份的，那么对应的日期都应是3月份四月份你选的日期之前的那几周。此处还是用TRUNC(SYSDATE-1,'D')+1 这样子查出来数据是当月（sysdate）的吧！

Answer 3

将system时间改为参数啊  这样你需要查询那一天的就传那一天的就可以啦