Home » SQL & PL/SQL » SQL & PL/SQL » Grouping for varying weeks (Oracle 11.2.0.3)
Grouping for varying weeks [message #661668] Mon, 27 March 2017 05:02 Go to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi,

I have two tables.

CREATE TABLE SCOTT.T_INVENTORY
(
  SPP_ID       NUMBER,
  INV_ST_DATE  DATE,
  INV_END_DT   DATE,
  QUANTITY     NUMBER,
  SALES_UNITS  NUMBER
);

CREATE TABLE SCOTT.T_SPP_WEEK
(
  SPP_ID   NUMBER,
  FROM_DT  VARCHAR2(20 BYTE),
  TO_DT    VARCHAR2(20 BYTE)
);

SET DEFINE OFF;
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (100, TO_DATE('03/07/2017 16:17:02', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/07/2017 16:17:02', 'MM/DD/YYYY HH24:MI:SS'), 10);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (100, TO_DATE('03/08/2017 16:17:16', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/08/2017 16:17:16', 'MM/DD/YYYY HH24:MI:SS'), 9);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (100, TO_DATE('03/09/2017 16:17:23', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/09/2017 16:17:23', 'MM/DD/YYYY HH24:MI:SS'), 8);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (100, TO_DATE('03/10/2017 16:17:33', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/10/2017 16:17:33', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (100, TO_DATE('03/11/2017 16:17:40', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/11/2017 16:17:40', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (100, TO_DATE('03/12/2017 16:17:48', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/12/2017 16:17:48', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (100, TO_DATE('03/13/2017 16:17:56', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/13/2017 16:17:56', 'MM/DD/YYYY HH24:MI:SS'), 4);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (100, TO_DATE('03/14/2017 16:18:05', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/14/2017 16:18:05', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (100, TO_DATE('03/15/2017 16:18:12', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/15/2017 16:18:12', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (100, TO_DATE('03/16/2017 16:18:20', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/16/2017 16:18:20', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (200, TO_DATE('03/07/2017 16:17:02', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/07/2017 16:17:02', 'MM/DD/YYYY HH24:MI:SS'), 10);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (200, TO_DATE('03/08/2017 16:17:16', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/08/2017 16:17:16', 'MM/DD/YYYY HH24:MI:SS'), 9);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (200, TO_DATE('03/09/2017 16:17:23', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/09/2017 16:17:23', 'MM/DD/YYYY HH24:MI:SS'), 8);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (200, TO_DATE('03/10/2017 16:17:33', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/10/2017 16:17:33', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (200, TO_DATE('03/11/2017 16:17:40', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/11/2017 16:17:40', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (200, TO_DATE('03/12/2017 16:17:48', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/12/2017 16:17:48', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (200, TO_DATE('03/13/2017 16:17:56', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/13/2017 16:17:56', 'MM/DD/YYYY HH24:MI:SS'), 4);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (200, TO_DATE('03/14/2017 16:18:05', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/14/2017 16:18:05', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (200, TO_DATE('03/15/2017 16:18:12', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/15/2017 16:18:12', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (200, TO_DATE('03/16/2017 16:18:20', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/16/2017 16:18:20', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/07/2017 16:17:02', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/07/2017 16:17:02', 'MM/DD/YYYY HH24:MI:SS'), 10);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/08/2017 16:17:16', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/08/2017 16:17:16', 'MM/DD/YYYY HH24:MI:SS'), 9);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/09/2017 16:17:23', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/09/2017 16:17:23', 'MM/DD/YYYY HH24:MI:SS'), 8);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/10/2017 16:17:33', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/10/2017 16:17:33', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/11/2017 16:17:40', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/11/2017 16:17:40', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/12/2017 16:17:48', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/12/2017 16:17:48', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/13/2017 16:17:56', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/13/2017 16:17:56', 'MM/DD/YYYY HH24:MI:SS'), 4);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/14/2017 16:18:05', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/14/2017 16:18:05', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/15/2017 16:18:12', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/15/2017 16:18:12', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/16/2017 16:18:20', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/16/2017 16:18:20', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/07/2017 16:17:02', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/07/2017 16:17:02', 'MM/DD/YYYY HH24:MI:SS'), 10);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/08/2017 16:17:16', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/08/2017 16:17:16', 'MM/DD/YYYY HH24:MI:SS'), 9);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/09/2017 16:17:23', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/09/2017 16:17:23', 'MM/DD/YYYY HH24:MI:SS'), 8);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/10/2017 16:17:33', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/10/2017 16:17:33', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/11/2017 16:17:40', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/11/2017 16:17:40', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/12/2017 16:17:48', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/12/2017 16:17:48', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/13/2017 16:17:56', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/13/2017 16:17:56', 'MM/DD/YYYY HH24:MI:SS'), 4);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/14/2017 16:18:05', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/14/2017 16:18:05', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/15/2017 16:18:12', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/15/2017 16:18:12', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into SCOTT.T_INVENTORY
   (spp_id, inv_st_date, inv_end_dt, quantity)
 Values
   (300, TO_DATE('03/16/2017 16:18:20', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/16/2017 16:18:20', 'MM/DD/YYYY HH24:MI:SS'), 1);
--
Insert into SCOTT.T_SPP_WEEK(spp_id, from_dt, to_dt) Values  (100, 'TUE', 'SAT');
Insert into SCOTT.T_SPP_WEEK(spp_id, from_dt, to_dt) Values  (200, 'MON', 'SUN');
Insert into SCOTT.T_SPP_WEEK(spp_id, from_dt, to_dt) Values  (300, 'MON', 'FRI');
Insert into SCOTT.T_SPP_WEEK(spp_id, from_dt, to_dt) Values   (400, 'WED', 'TUE');
COMMIT;
--
I would like to calculate SUM of quantity for each SPP_ID for each week and week varies for each SPP_ID based on T_SPP_WEEK table.
For e.g. for SPP_ID=100 week is from TUE to SAT. So all the quantities for SPP_ID=100 under TUE to SAT is summed.

I have written something like below.

Could you please share any other alternatives of doing it.

Thank you in advance.

SELECT spp_id,
         inv_st_date,
         quantity,
         bbbb,
         quan,
         sales,
         ROUND(quan / sales,2)
    FROM (SELECT spp_id,
                 inv_st_date,
                 quantity,
                 bbbb,
                 CASE
                    WHEN bbbb is null then NULL
                    ELSE SUM (quantity) OVER (PARTITION BY spp_id, bbbb)
                 END
                    quan,
                 CASE
                    WHEN bbbb is null then NULL
                    ELSE SUM (sales_units) OVER (PARTITION BY spp_id, bbbb)
                 END
                    sales
            FROM (SELECT i.spp_id,
                         i.inv_st_date,
                         i.quantity,
                         i.sales_units,
                         CASE
                            WHEN     i.spp_id = 100
                                 AND TO_CHAR (i.inv_st_date, 'D') BETWEEN 3
                                                                      AND 7
                            THEN
                               previous_day(i.inv_st_date,'TUE')
                            WHEN     i.spp_id = 200
                                 AND (   TO_CHAR (i.inv_st_date, 'D') BETWEEN 1
                                                                          AND 7
                                      )
                            THEN
                               previous_day(i.inv_st_date,'MON')
                            WHEN     i.spp_id = 300
                                 AND TO_CHAR (i.inv_st_date, 'D') BETWEEN 2
                                                                      AND 6
                            THEN
                               previous_day(i.inv_st_date,'MON')
                            WHEN     i.spp_id = 400
                                 AND TO_CHAR (i.inv_st_date, 'D') BETWEEN 1
                                                                      AND 7
                            THEN
                               previous_day(i.inv_st_date,'WED')
                            ELSE
                               NULL
                         END
                            bbbb
                    FROM t_inventory i, t_spp_week s
                   WHERE i.spp_id = s.spp_id))
ORDER BY 1, 2;


and the PREVIOUS_DAY function used in the above SELECT query is defined as below -
CREATE OR REPLACE FUNCTION previous_day (p_date IN DATE, p_day IN VARCHAR2)
   RETURN DATE
AS
   l_date   DATE;
BEGIN
       SELECT MAX (
                 CASE
                    WHEN TO_CHAR (TRUNC (p_date) - LEVEL + 1, 'DY') = p_day
                    THEN
                       TRUNC (p_date) - LEVEL + 1
                    ELSE
                       NULL
                 END)
         INTO l_date
         FROM DUAL
   CONNECT BY LEVEL <= 7;

   RETURN l_date;
END;
/
Regards,
Pointers



Re: Grouping for varying weeks [message #661669 is a reply to message #661668] Mon, 27 March 2017 07:53 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
One thing I don't like is:
                                 AND TO_CHAR (i.inv_st_date, 'D') BETWEEN 3
                                                                      AND 7
Re: Grouping for varying weeks [message #661671 is a reply to message #661669] Mon, 27 March 2017 08:21 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can replace the function with:
next_day(<date> -7, <day>)
Re: Grouping for varying weeks [message #661672 is a reply to message #661671] Mon, 27 March 2017 08:28 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's not at all obvious what the output should be.
What happens if the inv_st_date - inv_end_dt range crosses multiple weeks? or falls outside a week?
Your sample data has those 2 columns the same for all rows, but presumably the real data doesn't look like that or why have two columns?
Re: Grouping for varying weeks [message #661678 is a reply to message #661671] Mon, 27 March 2017 13:05 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Quote:
You can replace the function with:
next_day(<date> -7, <day>)
I could not because I would like to return the possible start day of the week based on SPP_ID.
for e.g. if the week for spp_id is MONDAY to THURSDAY, I would like to group all the days (MON, TUE, WED and THUR) in the group to MONDAY

next_day(<date>, -7, <day>) may map to previous week if date and day falls on same day for e.g. if date and day are MON and the output of next_day is previous monday but not current monday.


Quote:
It's not at all obvious what the output should be.
What happens if the inv_st_date - inv_end_dt range crosses multiple weeks? or falls outside a week?
Your sample data has those 2 columns the same for all rows, but presumably the real data doesn't look like that or why have two columns?
Both inv_st_date and inv_end_dt are same always, it is just to represt "A DAY", however our calculation is totally based on INV_ST_DATE column.

Regards,
Pointers
Re: Grouping for varying weeks [message #661679 is a reply to message #661668] Mon, 27 March 2017 21:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> SELECT spp_id, inv_st_date, quantity, bbbb, quan, sales, ROUND(quan/sales,2)
  2  FROM   (SELECT spp_id, inv_st_date, quantity, bbbb,
  3  		    CASE WHEN bbbb IS NULL THEN NULL ELSE SUM(quantity) OVER (PARTITION BY spp_id,bbbb) END quan,
  4  		    CASE WHEN bbbb IS NULL THEN NULL ELSE SUM(sales_units) OVER (PARTITION BY spp_id,bbbb) END sales
  5  	     FROM   (SELECT spp_id, inv_st_date, quantity, sales_units,
  6  			    CASE WHEN TRUNC(inv_st_date) BETWEEN from_dt AND to_dt THEN from_dt ELSE NULL END bbbb
  7  		     FROM   (SELECT i.spp_id, i.inv_st_date, i.inv_end_dt, i.quantity, i.sales_units,
  8  				    NEXT_DAY(TRUNC(i.inv_st_date)-7,s.from_dt) from_dt,
  9  				    NEXT_DAY(NEXT_DAY(TRUNC(i.inv_st_date)-7,s.from_dt),s.to_dt) to_dt
 10  			     FROM   t_inventory i, t_spp_week s
 11  			     WHERE  i.spp_id = s.spp_id)))
 12  ORDER  BY 1, 2
 13  /

    SPP_ID INV_ST_DATE           QUANTITY BBBB                      QUAN      SALES ROUND(QUAN/SALES,2)
---------- ------------------- ---------- ------------------- ---------- ---------- -------------------
       100 03/07/2017 16:17:02         10 03/07/2017 00:00:00         40
       100 03/08/2017 16:17:16          9 03/07/2017 00:00:00         40
       100 03/09/2017 16:17:23          8 03/07/2017 00:00:00         40
       100 03/10/2017 16:17:33          7 03/07/2017 00:00:00         40
       100 03/11/2017 16:17:40          6 03/07/2017 00:00:00         40
       100 03/12/2017 16:17:48          5
       100 03/13/2017 16:17:56          4
       100 03/14/2017 16:18:05          3 03/14/2017 00:00:00          6
       100 03/15/2017 16:18:12          2 03/14/2017 00:00:00          6
       100 03/16/2017 16:18:20          1 03/14/2017 00:00:00          6
       200 03/07/2017 16:17:02         10 03/06/2017 00:00:00         45
       200 03/08/2017 16:17:16          9 03/06/2017 00:00:00         45
       200 03/09/2017 16:17:23          8 03/06/2017 00:00:00         45
       200 03/10/2017 16:17:33          7 03/06/2017 00:00:00         45
       200 03/11/2017 16:17:40          6 03/06/2017 00:00:00         45
       200 03/12/2017 16:17:48          5 03/06/2017 00:00:00         45
       200 03/13/2017 16:17:56          4 03/13/2017 00:00:00         10
       200 03/14/2017 16:18:05          3 03/13/2017 00:00:00         10
       200 03/15/2017 16:18:12          2 03/13/2017 00:00:00         10
       200 03/16/2017 16:18:20          1 03/13/2017 00:00:00         10
       300 03/07/2017 16:17:02         10 03/06/2017 00:00:00         68
       300 03/07/2017 16:17:02         10 03/06/2017 00:00:00         68
       300 03/08/2017 16:17:16          9 03/06/2017 00:00:00         68
       300 03/08/2017 16:17:16          9 03/06/2017 00:00:00         68
       300 03/09/2017 16:17:23          8 03/06/2017 00:00:00         68
       300 03/09/2017 16:17:23          8 03/06/2017 00:00:00         68
       300 03/10/2017 16:17:33          7 03/06/2017 00:00:00         68
       300 03/10/2017 16:17:33          7 03/06/2017 00:00:00         68
       300 03/11/2017 16:17:40          6
       300 03/11/2017 16:17:40          6
       300 03/12/2017 16:17:48          5
       300 03/12/2017 16:17:48          5
       300 03/13/2017 16:17:56          4 03/13/2017 00:00:00         20
       300 03/13/2017 16:17:56          4 03/13/2017 00:00:00         20
       300 03/14/2017 16:18:05          3 03/13/2017 00:00:00         20
       300 03/14/2017 16:18:05          3 03/13/2017 00:00:00         20
       300 03/15/2017 16:18:12          2 03/13/2017 00:00:00         20
       300 03/15/2017 16:18:12          2 03/13/2017 00:00:00         20
       300 03/16/2017 16:18:20          1 03/13/2017 00:00:00         20
       300 03/16/2017 16:18:20          1 03/13/2017 00:00:00         20

40 rows selected.
Re: Grouping for varying weeks [message #661688 is a reply to message #661678] Tue, 28 March 2017 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
[quote title=pointers wrote on Mon, 27 March 2017 19:05]Quote:

next_day(<date>, -7, <day>) may map to previous week if date and day falls on same day for e.g. if date and day are MON and the output of next_day is previous monday but not current monday.

Did you test it? Cause it doesn't do that for me:
SQL> select next_day(sysdate -7, 'TUE'), sysdate from dual;

NEXT_DAY(SYSDAT SYSDATE
--------------- ---------------
20170328 105214 20170328 105214

Of course it's Tuesday right now.
Re: Grouping for varying weeks [message #661689 is a reply to message #661679] Tue, 28 March 2017 04:13 Go to previous message
pointers
Messages: 451
Registered: May 2008
Senior Member
@Barbara Boehmer: You made my day, although my query returns same results, I was worried about my query as it was full of hard coding (requires code changes in case of new SPP_ID)

You not only solved the problem but also showed me a new way doing it using NEXT_DAY(NEXT_DAY approach.

Thank you very much.

@cookiemontster: Yes you are right. I overlooked it.

Regards,
Pointers
Previous Topic: Inserting data from multiple table into 1 table
Next Topic: Avoid UNION ALL
Goto Forum:
  


Current Time: Thu Mar 28 11:09:14 CDT 2024