Advanced Query help

I have this query here

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 cusnum = 572 and sug <> ' ' and sug <> 0;

Open in new window


RESULTS

STORE	ITEM	MONDAY		TUESDAY		WEDNESDAY	THURSDAY	FRIDAY		SATURDAY	o1	o2	o3	o4	o5	sug
572	3754	20131007	0		0		0		20131004	0		1	0	 	 	 	1
572	7415	20131007	0		0		0		20131004	0		1	0	 	 	 	1
572	7434	20131007	0		0		0		20131004	0		1	0	 	 	 	1
572	12	20131007	0		0		0		20131004	0		1	0	 	 	 	1
572	1475	20131007	0		0		0		20131004	0		1	0	 	 	 	1
572	3750	20131007	0		0		0		20131004	0		1	0	 	 	 	1
572	1428	20131007	0		0		0		20131004	0		1	0	 	 	 	1
572	17	20131007	0		0		0		20131004	0		1	1	 	 	 	2
572	305	20131007	0		0		0		20131004	0		14	9	 	 	 	23
572	309	20131007	0		0		0		20131004	0		2	2	 	 	 	4
572	8780	20131007	0		0		0		20131004	0		1	0	 	 	 	1
572	3731	20131007	0		0		0		20131004	0		1	0	 	 	 	1
572	3752	20131007	0		0		0		20131004	0		1	0	 	 	 	1
572	6	20131007	0		0		0		20131004	0		5	3	 	 	 	8
572	307	20131007	0		0		0		20131004	0		6	4	 	 	 	10
572	306	20131007	0		0		0		20131004	0		4	2	 	 	 	6
572	14	20131007	0		0		0		20131004	0		1	0	 	 	 	1
572	7668	20131007	0		0		0		20131004	0		1	0	 	 	 	1
572	3755	20131007	0		0		0		20131004	0		1	0	 	 	 	1

Open in new window


which gives me these results



I am looking for a query, to give me these results.
Store	DATE		ITEM	QTY
572	20131007	7434	1
572	20131007	7415	1
572	20131007	3754	1
572	20131007	1475	1
572	20131007	12	1
572	20131007	1428	1
572	20131007	3750	1
572	20131007	8780	1
572	20131007	309	2
572	20131007	305	14
572	20131007	17	1
572	20131007	3731	1
572	20131007	306	4
572	20131007	307	6
572	20131007	6	5
572	20131007	3752	1
572	20131007	7668	1
572	20131007	14	1
572	20131007	3755	1
572	20131004	309	2
572	20131004	305	9
572	20131004	17	1
572	20131004	306	2
572	20131004	307	4
572	20131004	6	3

Open in new window





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
FutureDBA-Asked:
Who is Participating?
I wear a lot of hats...

"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.

AshokSr. Software EngineerCommented:
Sorry, I will post it for Oracle.
0
FutureDBA-Author Commented:
ok
0
AshokSr. Software EngineerCommented:
Try this!

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
Big Business Goals? Which KPIs Will Help You

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.

sdstuberCommented:
which columns are from which tables?
0
sdstuberCommented:
and, what are your rules for pivoting the data?

does Monday always map to o1?
does Friday always map to o2?

if not, how do you know which day to pair with with 'oX'  ?
0
sdstuberCommented:
what version of the db?  full version  like 10.2.0.1,   11.2.0.3

not just 10g  or 11g
0
sdstuberCommented:
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.
0
AshokSr. Software EngineerCommented:
if a store gets monday wednesday and saturday, o1 = monday, o2 = wednesday, o3 = saturday

if a store gets Monday Tuesday and Friday, o1 = Monday, o2 = Tuesday, o3 = Friday

Is that what you want?

Can you have 2 deliveries in a week?

Can you have 4 deliveries in a week?
0
FutureDBA-Author Commented:
@sdstuber,
o1 and o2 would represent monday and friday if there are dates in those columns,

it could also represent tuesday and thursday, or wednesday and saturday.. o1 and o2 all comes down to which order of delivery days



@ashokk111,

i can have anywhere between 1 and 4 deliveries in a week for a specific store..
0
FutureDBA-Author Commented:
ashokk111, yes that is the correct logic.
0
FutureDBA-Author Commented:
sdstuber, ashokks logic is correct

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

if a store gets Monday Tuesday and Friday, o1 = Monday, o2 = Tuesday, o3 = Friday
0
sdstuberCommented:
thanks, what about the other questions?
0
AshokSr. Software EngineerCommented:
STORE	ITEM	MONDAY		TUESDAY		WEDNESDAY	THURSDAY	FRIDAY		SATURDAY	o1	o2	o3	o4	o5	sug
572	3754	20131007	0		0		0		20131004	0		1	2	 	 	 	1
572	7415	0	        20131008	0		20131004	0		0		3	4	 	 	 	1

Open in new window


Could you explain above (and is the data correct?)?
0
FutureDBA-Author Commented:
sdstuber, 11gr2
0
FutureDBA-Author Commented:
ashokk, that data is not correct. i correct it below, and an explanation.


STORE	ITEM	MONDAY		TUESDAY		WEDNESDAY	THURSDAY	FRIDAY		SATURDAY	o1	o2	o3	o4	o5	sug
572	3754	20131007	0		0		20131004	0		0		1	2	 	 	 	1
572	7415	20131007	0		0		20131004	0		0		3	4	 	 	 	1

Open in new window


This means that store 572 will receive quanties on monday of 1, and on thursday of 2 for item 3754
On thursday they will receive 3 and 4 on item 7515
0
FutureDBA-Author Commented:
this is what data with multiple stores looks like



STORE	ITEM		MONDAY		TUESDAY		WEDNESDAY	THURSDAY	FRIDAY		SATURDAY	o1	o2	o3	o4	o5	sug
1225	1428		20131007	20131008	0		20131010	0		20131005	0	0	0	1	0	1
1225	1613		20131007	20131008	0		20131010	0		20131005	0	0	0	1	0	1
1225	209		20131007	20131008	0		20131010	0		20131005	1	1	2	0	0	4
1225	222		20131007	20131008	0		20131010	0		20131005	1	1	2	2	0	6
1225	1690		20131007	20131008	0		20131010	0		20131005	1	1	3	2	0	7
1225	1475		20131007	20131008	0		20131010	0		20131005	0	0	0	1	0	1
1225	298		20131007	20131008	0		20131010	0		20131005	0	0	0	1	0	1
1225	3731		20131007	20131008	0		20131010	0		20131005	0	0	0	1	0	1
1225	3703		20131007	20131008	0		20131010	0		20131005	0	0	0	1	0	1
1225	136		20131007	20131008	0		20131010	0		20131005	0	0	1	1	0	2
1225	137		20131007	20131008	0		20131010	0		20131005	0	0	1	1	0	2
1711	3752		20131007	20131008	20131009	0		20131004	0		0	0	0	1	0	1
1711	3751		20131007	20131008	20131009	0		20131004	0		0	0	0	1	0	1
1711	3743		20131007	20131008	20131009	0		20131004	0		0	0	0	1	0	1
1711	3747		20131007	20131008	20131009	0		20131004	0		0	0	0	1	0	1
1711	3749		20131007	20131008	20131009	0		20131004	0		0	0	0	1	0	1
1711	3753		20131007	20131008	20131009	0		20131004	0		0	0	0	1	0	1
1881	3751		20131007	20131008	0		20131010	0		20131005	0	0	0	1	0	1
1881	3749		20131007	20131008	0		20131010	0		20131005	0	0	0	1	0	1
1881	3753		20131007	20131008	0		20131010	0		20131005	0	0	0	1	0	1
1881	3755		20131007	20131008	0		20131010	0		20131005	0	0	0	1	0	1
1881	6		20131007	20131008	0		20131010	0		20131005	3	3	3	6	0	15
1881	17		20131007	20131008	0		20131010	0		20131005	1	1	1	0	0	3
1881	12		20131007	20131008	0		20131010	0		20131005	1	1	1	0	0	3
1881	14		20131007	20131008	0		20131010	0		20131005	0	0	0	1	0	1
1881	305		20131007	20131008	0		20131010	0		20131005	17	17	17	36	0	87
1881	307		20131007	20131008	0		20131010	0		20131005	6	6	6	13	0	31
3110	3752		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	0	1	1
3110	3751		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	0	1	1
3110	3743		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	0	1	1
3110	3745		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	0	1	1
3110	3746		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	0	1	1
3110	3747		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	0	1	1
3110	3749		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	1	1	2
3110	3750		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	1	1	2
3110	3753		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	1	1	2
3110	3754		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	0	1	1
3110	3755		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	0	1	1

Open in new window

0
sdstuberCommented:
sdstuber, 11gr2

that's not a full version as described above


for the corrected data...

a particular store can't receive product on different days for different items?


or, if they do, you'd still record them as if they all showed up, but simply mark some of them with 0 quantity?
0
sdstuberCommented:
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 (select
Chain,
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 Saturday
from elmdates)

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;

Open in new window

0
FutureDBA-Author Commented:
o1,o2,o3,o4,o5

are coming from elm_orders table


those are the quantities for the items
0
AshokSr. Software EngineerCommented:
To get o1 qty...

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.
0
AshokSr. Software EngineerCommented:
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

Is this correct?
0
sdstuberCommented:
your multi-store data and your results don't work together

for example store 3110
3110	3745		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	0	1	1

Open in new window


according to the description above,  this store receives item 3745 everyday but Sunday.
That's 6 days, but there are only 5 quantities.

So, which day gets left out?

also, there's no reason to have to requery the same data 5 times (or 6 times assuming you wanted to support a 6th quantity)

You can get all of the results (including a 6th quantity) in just one pass of the tables.

I'm refraining from posting a result since my code is based on assumptions that the current sample data and described results don't support.

i.e. there should be an "o6" quantity column

also, there are other pending questions above I'm still waiting on.
0
sdstuberCommented:
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?
0
sdstuberCommented:
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 > 0
ORDER BY yourdate, item

Open in new window


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.

Start your 7-day free trial
AshokSr. Software EngineerCommented:
sdstuber,

I think you have got it.  Perfect example of Decode and Case functions. I hope I have contributed here to solve his problem.

Question: Wouldn't cross-tab query work in this situation?  I am not sure if ORACLE has cross-tab query.

Thanks,
Ashok
0
FutureDBA-Author Commented:
sdstuber, as always.... thank you..

This query is to create an import file for our ERP for one of our chains.


Q1: i missed the question of full db version, full version =  11.2.0.2.0

Q2: the end result of what I am trying to get will always be ran on Mondays giving me my order results for the rest of the week.

Q3:
3110	3745		20131007	20131008	20131009	20131010	20131004	20131005	0	0	0	0	1	1

Open in new window

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;

Open in new window

0
FutureDBA-Author Commented:
I am at home now and tested the query remotely and it seemed to work.

I will verify data in the early morning.
0
sdstuberCommented:
o1 - o5 do not represent the day of the week, but delivery for that week

I know, that's why the counting is needed so you can map them to days


If you'll never have more than 5 then remove this line

                   WHEN n = 6 AND satcnt = 6 THEN o6

Open in new window


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
0
FutureDBA-Author Commented:
attached is elm_rmrvcp data
elm-rmrvcpt.sql
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.