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

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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;

VeeamÂ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Background
In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logicalâ€¦

Configuring and using Oracle Database Gateway for ODBC
Introduction
First, a brief summary of what a Database Gateway is. A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platformsâ€¦

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.

This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.