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;

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

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

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;

Working with Network Access Control Lists in Oracle 11g (part 2)
Part 1: http://www.e-e.com/A_8429.html
Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access. For manyâ€¦

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux.
Script may need to be modified depending on OS-installation.
Please deploy and verify the script in a test environment.