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 			(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 elm_dates), 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 = '10397'ORDER BY store,xdate,item;

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
(CODE)
and it produced the results in http:#39692713

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

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

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;

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;

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

How to Unravel a Tricky Query
Introduction
If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them. â€¦

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.

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â€¦

This video shows how to Export data from an Oracle database using the Datapump Export Utility. The corresponding Datapump Import utility is also discussed and demonstrated.