Solved

# Reorder code to make monday last day of week

Posted on 2013-12-01
Medium Priority
344 Views
You had helped me solve this question here

http://www.experts-exchange.com/Database/Oracle/Q_28257301.html

I need to rewrite it so that the first order to be treated as tuesday, and last order to be treated as following monday if it has a  monday order.

Ideally for it to work with Tuesday as start of week, monday as end of week.

this is what you wrote before. I've tried figuring it out on my own, but can't figure it out

``````  WITH q AS
&#9;&#9;&#9;(SELECT chain,
cusnum,

CASE WHEN monday  &#9;= 'Monday' &#9;&#9;AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Monday'),'yyyymmdd'))  < to_number(to_char(sysdate,'yyyymmdd'))
THEN TO_NUMBER(to_char(next_day((sysdate), 'Monday'),'yyyymmdd'))
ELSE 0 END AS monday,

CASE WHEN tuesday &#9;= 'Tuesday'&#9;&#9;AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Tuesday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
THEN TO_NUMBER(to_char(next_day((sysdate), 'Tuesday'),'yyyymmdd'))
ELSE 0 END AS tuesday,

CASE WHEN wednesday = 'Wednesday'&#9;AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Wednesday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
THEN TO_NUMBER(to_char(next_day((sysdate), 'Wednesday'),'yyyymmdd'))  ELSE 0 END AS wednesday,

CASE WHEN thursday &#9;= 'Thursday' &#9;AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Thursday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
THEN TO_NUMBER(to_char(next_day((sysdate), 'Thursday'),'yyyymmdd'))
ELSE 0 END AS thursday,

CASE WHEN friday &#9;  = 'Friday' &#9;&#9;AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Friday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
THEN TO_NUMBER(to_char(next_day((sysdate), 'Friday'),'yyyymmdd'))&#9;
ELSE 0 END AS friday,

CASE WHEN saturday  = 'Saturday'   AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Saturday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
THEN TO_NUMBER(to_char(next_day((sysdate), 'Saturday'),'yyyymmdd'))  ELSE 0 END AS saturday
&#9;&#9;&#9;FROM elm_dates),
x AS
&#9;&#9;&#9;(SELECT store,
item,
monday,
tuesday,
wednesday,
thursday,
friday,
saturday,
o1,
o2,
o3,
o4,
o5,
sug
FROM elm_book, q
&#9;&#9;   WHERE store = cusnum and sug <> ' ' AND sug <> '0')
SELECT "STORE","XDATE","ITEM","QTY"
FROM (SELECT store,
DECODE(
n,
1, monday,
2, tuesday,
3, wednesday,
4, thursday,
5, friday,
6, saturday
)
xdate,
item,
CASE
WHEN n = 1 AND moncnt = 1 THEN o1
WHEN n = 2 AND tuecnt = 1 THEN o1
WHEN n = 3 AND wedcnt = 1 THEN o1
WHEN n = 4 AND thucnt = 1 THEN o1
WHEN n = 5 AND fricnt = 1 THEN o1
WHEN n = 6 AND satcnt = 1 THEN o1

WHEN n = 2 AND tuecnt = 2 THEN o2
WHEN n = 3 AND wedcnt = 2 THEN o2
WHEN n = 4 AND thucnt = 2 THEN o2
WHEN n = 5 AND fricnt = 2 THEN o2
WHEN n = 6 AND satcnt = 2 THEN o2

WHEN n = 3 AND wedcnt = 3 THEN o3
WHEN n = 4 AND thucnt = 3 THEN o3
WHEN n = 5 AND fricnt = 3 THEN o3
WHEN n = 6 AND satcnt = 3 THEN o3

WHEN n = 4 AND thucnt = 4 THEN o4
WHEN n = 5 AND fricnt = 4 THEN o4
WHEN n = 6 AND satcnt = 4 THEN o4

WHEN n = 5 AND fricnt = 5 THEN o5
WHEN n = 6 AND satcnt = 5 THEN o5
END
qty
FROM (SELECT x.*,
n,
SIGN(monday) moncnt,
SIGN(monday) + SIGN(tuesday) tuecnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) wedcnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) + SIGN(thursday) thucnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) + SIGN(thursday) + SIGN(friday) fricnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) + SIGN(thursday) + SIGN(friday) + SIGN(saturday) satcnt
FROM x,
(SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 6)
WHERE n = 1 AND monday != 0
OR n = 2 AND tuesday != 0
OR n = 3 AND wednesday != 0
OR n = 4 AND thursday != 0
OR n = 5 AND friday != 0
OR n = 6 AND saturday != 0))
WHERE qty > '0'
and store = '10397'
ORDER BY store,xdate,item;
``````
0
Question by:FutureDBA-
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 10
• 6

LVL 74

Expert Comment

ID: 39689962
I'm not sure what you're asking.

Can you post sample data and expected results?
0

Author Comment

ID: 39689988
in the query above which you wrote.  Monday is treated as the first day of the week.

I need Monday to be treated as last day of the week.
0

Author Comment

ID: 39690000
I trade changing the decode portion and the connect by level and giving monday a ranking of 6, but it doesnt seem to work correctly
0

LVL 74

Expert Comment

ID: 39690083
Can you post sample data and expected results?

actual data, rows and columns - not descriptions

looks like there are 2 tables involved.  elm_dates and elm_book, please provide sample data for both, as well as the expected results for them
0

Author Comment

ID: 39690209
0

Author Comment

ID: 39690219
I am looking for the data in this format below, with tuesday being treated as first day of the week, and monday being treated as last day of the week.

``````10579	20131203	1064	1
10579	20131203	1064	1
10579	20131203	12	1
10579	20131203	12	1
10579	20131203	1475	1
10579	20131203	1475	1
10579	20131203	17	1
10579	20131203	17	1
10579	20131203	305	12
10579	20131203	305	12
10579	20131203	306	4
10579	20131203	306	4
10579	20131203	307	6
10579	20131203	307	6
10579	20131203	309	2
10579	20131203	309	2
10579	20131203	384	1
10579	20131203	384	1
10579	20131203	6	2
10579	20131203	6	2
10579	20131203	7415	1
10579	20131203	7415	1
10579	20131205	1064	2
10579	20131205	1064	2
10579	20131205	12	1
10579	20131205	12	1
10579	20131205	1475	1
10579	20131205	1475	1
10579	20131205	17	1
10579	20131205	17	1
10579	20131205	305	16
10579	20131205	305	16
10579	20131205	306	6
10579	20131205	306	6
10579	20131205	307	8
10579	20131205	307	8
10579	20131205	309	3
10579	20131205	309	3
10579	20131205	384	2
10579	20131205	384	2
10579	20131205	6	3
10579	20131205	6	3
10579	20131205	7415	1
10579	20131205	7415	1
``````
0

Author Comment

ID: 39690261
note, the data above is not correct because there are duplicate item numbers
0

Author Comment

ID: 39692713
This is the correct data, for customer 10395, please let me know if you need anything else from me.

Thanks

``````10395	20131207	1	2
10395	20131207	13	1
10395	20131207	1690	1
10395	20131207	1692	1
10395	20131207	30	1
10395	20131207	305	6
10395	20131207	306	2
10395	20131207	307	5
10395	20131207	308	1
10395	20131207	309	1
10395	20131207	3743	1
10395	20131207	3752	1
10395	20131207	384	1
10395	20131207	6	1
10396	20131205	1	1
10396	20131205	17	1
10396	20131205	30	1
10396	20131205	305	6
10396	20131205	306	3
10396	20131205	307	4
10396	20131205	309	1
10396	20131205	384	1
10396	20131205	6	1
``````
0

Author Comment

ID: 39698723
sdstuber, please let me know if you require any additional data or information from me.

Thank you
0

LVL 74

Expert Comment

ID: 39699165
what is the table definition for the elm-book.csv  data?

if you could provide a sql script that creates the table and has inserts like the other table that would be even better
0

LVL 74

Expert Comment

ID: 39699171
this is what I guessed for the export_table

can you provide something similar for elm_book ?

CREATE TABLE export_table
(
chain     NUMBER,
cusnum    NUMBER,
sunday    VARCHAR2(20),
monday    VARCHAR2(20),
tuesday   VARCHAR2(20),
wednesday VARCHAR2(20),
thursday  VARCHAR2(20),
friday    VARCHAR2(20),
saturday  VARCHAR2(20)
);
0

Author Comment

ID: 39700025
sdstuber, attached file has create table and inserts.

the reason some of these are varchar vs number is because when i get the data dump from our customer, they send whitespaces. i have to clean the file after i have in oracle.
elm-book.csv
0

Author Comment

ID: 39701629
please let me know if this is what you are looking for. if not, i will retry to get you the correct data over.
0

Author Comment

ID: 39717411
is there anything else i can provide you with ?
0

LVL 74

Expert Comment

ID: 39758482
oops, sorry I missed the notifications on this question and forgot about it.  I'll take a look tomorrow.
0

LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 39764347
using the tables and data above  I ran this query...
I used  2013-12-03 as the date because that was the date when you posted the expected results

``````WITH q
AS (SELECT chain,
cusnum,
CASE
WHEN monday = 'Monday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03') - 7), 'Monday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR((date '2013-12-03'), 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03')), 'Monday'), 'yyyymmdd'))
ELSE
0
END
AS monday,
CASE
WHEN tuesday = 'Tuesday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03') - 7), 'Tuesday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR((date '2013-12-03'), 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03')), 'Tuesday'), 'yyyymmdd'))
ELSE
0
END
AS tuesday,
CASE
WHEN wednesday = 'Wednesday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03') - 7), 'Wednesday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR((date '2013-12-03'), 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03')), 'Wednesday'), 'yyyymmdd'))
ELSE
0
END
AS wednesday,
CASE
WHEN thursday = 'Thursday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03') - 7), 'Thursday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR((date '2013-12-03'), 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03')), 'Thursday'), 'yyyymmdd'))
ELSE
0
END
AS thursday,
CASE
WHEN friday = 'Friday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03') - 7), 'Friday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR((date '2013-12-03'), 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03')), 'Friday'), 'yyyymmdd'))
ELSE
0
END
AS friday,
CASE
WHEN saturday = 'Saturday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03') - 7), 'Saturday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR((date '2013-12-03'), 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03')), 'Saturday'), 'yyyymmdd'))
ELSE
0
END
AS saturday
FROM export_table),
x
AS (SELECT store,
item,
monday,
tuesday,
wednesday,
thursday,
friday,
saturday,
o1,
o2,
o3,
o4,
o5,
sug
FROM elm_book, q
WHERE store = cusnum AND sug <> ' ' AND sug <> '0')
SELECT "STORE",
"XDATE",
"ITEM",
"QTY"
FROM (SELECT store,
DECODE(
n,
1, monday,
2, tuesday,
3, wednesday,
4, thursday,
5, friday,
6, saturday
)
xdate,
item,
CASE
WHEN n = 1 AND moncnt = 1 THEN o1
WHEN n = 2 AND tuecnt = 1 THEN o1
WHEN n = 3 AND wedcnt = 1 THEN o1
WHEN n = 4 AND thucnt = 1 THEN o1
WHEN n = 5 AND fricnt = 1 THEN o1
WHEN n = 6 AND satcnt = 1 THEN o1
WHEN n = 2 AND tuecnt = 2 THEN o2
WHEN n = 3 AND wedcnt = 2 THEN o2
WHEN n = 4 AND thucnt = 2 THEN o2
WHEN n = 5 AND fricnt = 2 THEN o2
WHEN n = 6 AND satcnt = 2 THEN o2
WHEN n = 3 AND wedcnt = 3 THEN o3
WHEN n = 4 AND thucnt = 3 THEN o3
WHEN n = 5 AND fricnt = 3 THEN o3
WHEN n = 6 AND satcnt = 3 THEN o3
WHEN n = 4 AND thucnt = 4 THEN o4
WHEN n = 5 AND fricnt = 4 THEN o4
WHEN n = 6 AND satcnt = 4 THEN o4
WHEN n = 5 AND fricnt = 5 THEN o5
WHEN n = 6 AND satcnt = 5 THEN o5
END
qty
FROM (SELECT x.*,
n,
SIGN(monday) moncnt,
SIGN(monday) + SIGN(tuesday) tuecnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) wedcnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) + SIGN(thursday) thucnt,
SIGN(monday)
+ SIGN(tuesday)
+ SIGN(wednesday)
+ SIGN(thursday)
+ SIGN(friday)
fricnt,
SIGN(monday)
+ SIGN(tuesday)
+ SIGN(wednesday)
+ SIGN(thursday)
+ SIGN(friday)
+ SIGN(saturday)
satcnt
FROM x,
(SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 6)
WHERE n = 1 AND monday != 0
OR n = 2 AND tuesday != 0
OR n = 3 AND wednesday != 0
OR n = 4 AND thursday != 0
OR n = 5 AND friday != 0
OR n = 6 AND saturday != 0))
WHERE qty > '0' AND store in ( '10395','10396')
ORDER BY store, xdate, item;
``````

and it produced the results in http:#39692713

Using current date you simply substitute sysdate for (date '2013-12-03')

``````WITH q
AS (SELECT chain,
cusnum,
CASE
WHEN monday = 'Monday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE - 7), 'Monday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE), 'Monday'), 'yyyymmdd'))
ELSE
0
END
AS monday,
CASE
WHEN tuesday = 'Tuesday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE - 7), 'Tuesday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE), 'Tuesday'), 'yyyymmdd'))
ELSE
0
END
AS tuesday,
CASE
WHEN wednesday = 'Wednesday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE - 7), 'Wednesday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE), 'Wednesday'), 'yyyymmdd'))
ELSE
0
END
AS wednesday,
CASE
WHEN thursday = 'Thursday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE - 7), 'Thursday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE), 'Thursday'), 'yyyymmdd'))
ELSE
0
END
AS thursday,
CASE
WHEN friday = 'Friday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE - 7), 'Friday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE), 'Friday'), 'yyyymmdd'))
ELSE
0
END
AS friday,
CASE
WHEN saturday = 'Saturday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE - 7), 'Saturday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE), 'Saturday'), 'yyyymmdd'))
ELSE
0
END
AS saturday
FROM export_table),
x
AS (SELECT store,
item,
monday,
tuesday,
wednesday,
thursday,
friday,
saturday,
o1,
o2,
o3,
o4,
o5,
sug
FROM elm_book, q
WHERE store = cusnum AND sug <> ' ' AND sug <> '0')
SELECT "STORE",
"XDATE",
"ITEM",
"QTY"
FROM (SELECT store,
DECODE(
n,
1, monday,
2, tuesday,
3, wednesday,
4, thursday,
5, friday,
6, saturday
)
xdate,
item,
CASE
WHEN n = 1 AND moncnt = 1 THEN o1
WHEN n = 2 AND tuecnt = 1 THEN o1
WHEN n = 3 AND wedcnt = 1 THEN o1
WHEN n = 4 AND thucnt = 1 THEN o1
WHEN n = 5 AND fricnt = 1 THEN o1
WHEN n = 6 AND satcnt = 1 THEN o1
WHEN n = 2 AND tuecnt = 2 THEN o2
WHEN n = 3 AND wedcnt = 2 THEN o2
WHEN n = 4 AND thucnt = 2 THEN o2
WHEN n = 5 AND fricnt = 2 THEN o2
WHEN n = 6 AND satcnt = 2 THEN o2
WHEN n = 3 AND wedcnt = 3 THEN o3
WHEN n = 4 AND thucnt = 3 THEN o3
WHEN n = 5 AND fricnt = 3 THEN o3
WHEN n = 6 AND satcnt = 3 THEN o3
WHEN n = 4 AND thucnt = 4 THEN o4
WHEN n = 5 AND fricnt = 4 THEN o4
WHEN n = 6 AND satcnt = 4 THEN o4
WHEN n = 5 AND fricnt = 5 THEN o5
WHEN n = 6 AND satcnt = 5 THEN o5
END
qty
FROM (SELECT x.*,
n,
SIGN(monday) moncnt,
SIGN(monday) + SIGN(tuesday) tuecnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) wedcnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) + SIGN(thursday) thucnt,
SIGN(monday)
+ SIGN(tuesday)
+ SIGN(wednesday)
+ SIGN(thursday)
+ SIGN(friday)
fricnt,
SIGN(monday)
+ SIGN(tuesday)
+ SIGN(wednesday)
+ SIGN(thursday)
+ SIGN(friday)
+ SIGN(saturday)
satcnt
FROM x,
(SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 6)
WHERE n = 1 AND monday != 0
OR n = 2 AND tuesday != 0
OR n = 3 AND wednesday != 0
OR n = 4 AND thursday != 0
OR n = 5 AND friday != 0
OR n = 6 AND saturday != 0))
WHERE qty > '0' AND store IN ('10395', '10396')
ORDER BY store, xdate, item;
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-orâ€¦
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I Â will demonstrate that undo for DMLâ€™s is stored both in undo tablespace and online redo logs. Then, we will analyze the reasoâ€¦
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode aâ€¦
###### Suggested Courses
Course of the Month10 days, 10 hours left to enroll