select STORE,ITEM, MONDAY,TUESDAY,WEDNESDAY,THURSDAY, FRIDAY,SATURDAY,o1,o2,o3,o4,o5,sugfrom elm_orders,q where store = cusnum and Monday = 20131007 and cusnum = 572 and sug <> ' ' and sug <> 0;

o1 - o5 represent which order for the day of the week. if my stores get monday and friday deliverys, o1 would be the qty for monday, o2 would be the qty for friday.

if a store gets monday wednesday and saturday, o1 = monday, o2 = wednesday, o3 = saturday

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Select
STORE,
to_char(sysdate, 'yyyymmdd') As "DATE",
CASE to_number(to_char(sysdate, 'd'))
WHEN 2 Then o1
WHEN 3 Then o2
WHEN 4 Then o3
WHEN 5 Then o4
WHEN 6 Then o5
ELSE 0
END As QTY
from elm_orders where store = cusnum and cusnum = 572 and sug <> ' ' and sug <> 0;

0

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

assuming anything from 9.1.0.1 or higher
and assuming you only ever want Monday and Friday values
and assuming you want Monday with o1 and Friday with o2
then

try this...

SELECT store,
CASE WHEN n = 1 THEN monday WHEN n = 2 THEN friday END yourdate,
item,
CASE WHEN n = 1 THEN o1 WHEN n = 2 THEN o2 END qty
FROM (yourquery),
(SELECT 1 n FROM DUAL
UNION ALL
SELECT 2 n FROM DUAL)

substitute your existing query as it is now for "yourquery"

assuming at least one of my assumptions is not correct, please elaborate per all of the questions asked above.

also, you are generating the data from 2 tables or views

elm_orders
q

which columns are from which tables?

I can do like I did above and nest your query inside another query, but it might make more sense to write it directly on the tables

0

FutureDBA-Author Commented:

below is the query for "q", elm_orders is a table

with q as (selectChain,cusnum,case when sunday = 'Sunday' then to_char(next_day(to_date(20131003,'yyyymmdd'),'Sunday'), 'yyyymmdd') else '0' end as Sunday,case when monday = 'Monday' then to_char(next_day(to_date(20131003,'yyyymmdd'),'Monday'), 'yyyymmdd') else '0' end as Monday,case when tuesday = 'Tuesday' then to_char(next_day(to_date(20131003,'yyyymmdd'),'Tuesday'), 'yyyymmdd') else '0' end as Tuesday,case when wednesday = 'Wednesday' then to_char(next_day(to_date(20131003,'yyyymmdd'),'Wednesday'), 'yyyymmdd') else '0' end as Wednesday ,case when thursday = 'Thursday' then to_char(next_day(to_date(20131003,'yyyymmdd'),'Thursday'), 'yyyymmdd') else '0' end as Thursday,case when friday = 'Friday' then to_char(next_day(to_date(20131003,'yyyymmdd'),'Friday'), 'yyyymmdd') else '0' end as friday,case when saturday = 'Saturday' then to_char(next_day(to_date(20131003,'yyyymmdd'),'Saturday'), 'yyyymmdd') else '0' end as Saturdayfrom elmdates)select STORE,ITEM, MONDAY,TUESDAY,WEDNESDAY,THURSDAY, FRIDAY,SATURDAY,o1,o2,o3,o4,o5,sugfrom elm_orders,q where store = cusnum and Monday = 20131007 and sug <> ' ' and sug <> 0;

select
STORE,
MONDAY,
ITEM,
o1
from elm_orders, q where store = cusnum and Monday <> 0 and cusnum = 572 and sug <> ' ' and sug <> 0;

select
STORE,
TUESDAY,
ITEM,
o1
from elm_orders, q where store = cusnum and (Monday = 0 and Tuesday <> 0) and cusnum = 572 and sug <> ' ' and sug <> 0;

select
STORE,
WEDNESDAY,
ITEM,
o1
from elm_orders, q where store = cusnum and (Monday = 0 and and Tuesday = 0 and Wednesday <> 0) and cusnum = 572 and sug <> ' ' and sug <> 0;

select
STORE,
THURSDAY,
ITEM,
o1
from elm_orders, q where store = cusnum and (Monday = 0 and and Tuesday = 0 and Wednesday = 0 and Thursday <> 0) and cusnum = 572 and sug <> ' ' and sug <> 0;

select
STORE,
FRIDAY,
ITEM,
o1
from elm_orders, q where store = cusnum and (Monday = 0 and and Tuesday = 0 and Wednesday = 0 and Thursday = 0 and Friday <> 0) and cusnum = 572 and sug <> ' ' and sug <> 0;

Use similar logic above to change it for o2 qty. If above works, let me now if you need help.

By the way, you can use union all to combine all select into one select.

This means that store 572 will receive quanties on monday of 1, and on thursday of 2 for item 3754
and
store 572 will receive quanties on monday of 3, and on thursday of 4 for item 7515

also, based on the filtering condition and the displayed result columns,
will you always be looking for weeks beginning on Monday and continuing through Saturday?

So, will something like "Monday = xxxxxx" always be a driving criteria?

on the chance that every guess I've made is correct, then try this..

again, if any of assumptions or guesses above are incorrect PLEASE elaborate on ALL pending questions.

WITH q AS (SELECT chain, cusnum, CASE WHEN sunday = 'Sunday' THEN TO_NUMBER( TO_CHAR(NEXT_DAY(TO_DATE(20131003, 'yyyymmdd'), 'Sunday'), 'yyyymmdd') ) ELSE 0 END AS sunday, CASE WHEN monday = 'Monday' THEN TO_NUMBER( TO_CHAR(NEXT_DAY(TO_DATE(20131003, 'yyyymmdd'), 'Monday'), 'yyyymmdd') ) ELSE 0 END AS monday, CASE WHEN tuesday = 'Tuesday' THEN TO_NUMBER( TO_CHAR(NEXT_DAY(TO_DATE(20131003, 'yyyymmdd'), 'Tuesday'), 'yyyymmdd') ) ELSE 0 END AS tuesday, CASE WHEN wednesday = 'Wednesday' THEN TO_NUMBER( TO_CHAR( NEXT_DAY(TO_DATE(20131003, 'yyyymmdd'), 'Wednesday'), 'yyyymmdd' ) ) ELSE 0 END AS wednesday, CASE WHEN thursday = 'Thursday' THEN TO_NUMBER( TO_CHAR( NEXT_DAY(TO_DATE(20131003, 'yyyymmdd'), 'Thursday'), 'yyyymmdd' ) ) ELSE 0 END AS thursday, CASE WHEN friday = 'Friday' THEN TO_NUMBER( TO_CHAR(NEXT_DAY(TO_DATE(20131003, 'yyyymmdd'), 'Friday'), 'yyyymmdd') ) ELSE 0 END AS friday, CASE WHEN saturday = 'Saturday' THEN TO_NUMBER( TO_CHAR( NEXT_DAY(TO_DATE(20131003, 'yyyymmdd'), 'Saturday'), 'yyyymmdd' ) ) ELSE 0 END AS saturday FROM elmdates), x AS (SELECT store, item, monday, tuesday, wednesday, thursday, friday, saturday, o1, o2, o3, o4, o5, sug FROM elm_orders, q WHERE store = cusnum AND monday = 20131007 AND sug <> ' ' AND sug <> 0)SELECT * FROM (SELECT store, DECODE( n, 1, monday, 2, tuesday, 3, wednesday, 4, thursday, 5, friday, 6, saturday ) yourdate, 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 WHEN n = 6 AND satcnt = 6 THEN o6 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 > 0ORDER BY yourdate, item

it's a lot of code, but the bulk of it is simply the original query.
if this doesn't work, in addition to answering previous questions, please post sample data for elmdates.
I'm having a hard time envisioning what that data might look like based on the usage

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

in this event, item 3745 will get shipped quantities of 1 on the 4th and 5th delivery for that week. that was bad sample data on my end and one of the date columns should have been a 0

Q4: "according to the description above, this store receives item 3745 everyday but Sunday. That's 6 days, but there are only 5 quantities." data error on my end when creating sample for you, o1 - o5 do not represent the day of the week, but delivery for that week, none of the stores in that chain get 6 day deliveries. at most a very small set of stores get 5 deliveries in a week, it can be mon = 01, tuesday=02, thursday=o3, friday=o4, saturday = o5

q5: elmdates query =

with m as (SELECT RVCCUSCHN AS CHAIN, RVCCUSNUM AS CUSNUM, CASE WHEN substr(to_char(RVCCUSWK1),1,1) = '1' THEN RVCRTENUM ELSE 0 END AS SUN, CASE WHEN substr(to_char(RVCCUSWK1),2,1) = '1' THEN RVCRTENUM ELSE 0 END AS MON, CASE WHEN substr(to_char(RVCCUSWK1),3,1) = '1' THEN RVCRTENUM ELSE 0 END AS TUE, CASE WHEN substr(to_char(RVCCUSWK1),4,1) = '1' THEN RVCRTENUM ELSE 0 END AS WED, CASE WHEN substr(to_char(RVCCUSWK1),5,1) = '1' THEN RVCRTENUM ELSE 0 END AS THU, CASE WHEN substr(to_char(RVCCUSWK1),6,1) = '1' THEN RVCRTENUM ELSE 0 END AS FRI, CASE WHEN substr(to_char(RVCCUSWK1),7,1) = '1' THEN RVCRTENUM ELSE 0 END AS SAT FROM ELM_RMRVCP WHERE RVCCUSCHN = 9007)SELECT M.CHAIN, M.CUSNUM, case when SUM(M.SUN) > 0 then 'Sunday' else '0' end AS SUNDAY, case when SUM(M.MON) > 0 then 'Monday' else '0' end AS MONDAY, case when SUM(M.TUE) > 0 then 'Tuesday' else '0' end AS TUESDAY, case when SUM(M.WED) > 0 then 'Wednesday' else '0' end AS WEDNESDAY, case when SUM(M.THU) > 0 then 'Thursday' else '0' end AS THURSDAY, case when SUM(M.FRI) > 0 then 'Friday' else '0' end AS FRIDAY, case when SUM(M.SAT) > 0 then 'Saturday' else '0' end AS SATURDAY FROM m GROUP BY M.CHAIN, M.CUSNUM;

the rest of it can remain the same. My "x" query was wrong above because I left out the o6 column, but since it's not necessary (doesn't exist), then it's correct but only by accident.

I still think the code could be made simpler if I had elmdates data to work with or, apparently ELM_RMRVCP if elmdates isn't the real source

that is, I don't want your queries that process the data, I want the data itself.
I'll write the query to read the data

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for freeEdge Out The Competitionfor your dream job with proven skills and certifications.Get started todayStand Outas the employee with proven skills.Start learning today for freeMove Your Career Forwardwith certification training in the latest technologies.Start your trial today