Advanced Query help

Sorry, I will post it for Oracle.

ok

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;

which columns are from which tables?

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'  ?

what version of the db?  full version  like 10.2.0.1,   11.2.0.3

not just 10g  or 11g

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.

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?

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

ashokk111, yes that is the correct logic.

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

thanks, what about the other questions?

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

Select allOpen in new window

Could you explain above (and is the data correct?)?

sdstuber, 11gr2

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

Select allOpen 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

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

Select allOpen in new window

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?

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

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;

Select allOpen in new window

o1,o2,o3,o4,o5

are coming from elm_orders table


those are the quantities for the items

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.

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?

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

Select allOpen 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.

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?

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

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

Select allOpen 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;

Select allOpen in new window

I am at home now and tested the query remotely and it seemed to work.

I will verify data in the early morning.

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

Select allOpen 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

attached is elm_rmrvcp data
elm-rmrvcpt.sql