一周的范围而不是定期日期

Question

我的查询低于查询，而不是像'22 -oct-2012和28-oct-2012'这样的一周范围，我woulk woulk放置了诸如Currentweek -2或Currentweek之类的代码-1，这将避免每周需要编辑我需要运行的查询。你知道如何制作吗？

谢谢LD

    SELECT WO.USER_6 AS STYLE
 ,SUM (CASE WHEN (OPERATION.STATUS ='C' AND OPERATION.CLOSE_DATE BETWEEN '22-OCT-2012' AND '28-OCT-2012') THEN OPERATION.RUN_HRS ELSE 0 END) WEEK43
 ,SUM (CASE WHEN (OPERATION.STATUS ='C' AND OPERATION.CLOSE_DATE BETWEEN '29-OCT-2012' AND '04-NOV-2012') THEN OPERATION.RUN_HRS ELSE 0 END) WEEK44
FROM WORK_ORDER WO, OPERATION
WHERE  WO.BASE_ID = OPERATION.WORKORDER_BASE_ID
AND WO.Lot_ID = Operation.Workorder_Lot_ID
        AND WO.Sub_ID = Operation.Workorder_Sub_ID
        AND WO.Split_ID = Operation.Workorder_Split_ID
AND WO.TYPE ='W'
AND WO.WAREHOUSE_ID ='MEX-04'
AND OPERATION.CLOSE_DATE BETWEEN '22-OCT-2012'  AND '04-NOV-2012'
AND OPERATION.RESOURCE_ID IN ('171-4','171-ADD','171-3' ,'BAMEX-SEWCONC','BAMEX-SEWPATC')
AND OPERATION.RUN > 0
GROUP BY
WO.USER_6

Answer 1

如果我理解您的问题，那么您可以使用它来通过当前的工作日

SUM (CASE WHEN (OPERATION.STATUS ='C' AND OPERATION.CLOSE_DATE BETWEEN to_char(trunc(sysdate),'DD-MON-YYYY')  and to_char(trunc(sysdate)-6,'DD-MON-YYYY')) THEN OPERATION.RUN_HRS ELSE 0 END) WEEK43

希望这有所帮助

Answer 2

在这种情况下，我将使用trunc函数：

currentweek will be trunc(sysdate,'D') 
current_week - 1 will be trunc(sysdate,'D') - 7
current week - 2 will be trunc(sysdate,'D') - 2 * 7

注意这将给出周日的第一天。如果您想星期一，有一天应该：

current week - 2 will be trunc(sysdate,'D') - 2 * 7 + 1

更新：弗兰克是对的，一周中的第一天的行为取决于NLS_Teritory

alter session set NLS_TERRITORY ='UNITED KINGDOM';
select trunc(sysdate,'D') from dual;
05-11-2012
alter session set NLS_TERRITORY ='AMERICA';
select trunc(sysdate,'D') from dual;
04-11-2012