Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

You had helped me solve this question here

http://www.experts-exchange.com/Database/Oracle/Q_28257301.html

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

http://www.experts-exchang

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;
```

Experts Exchange Solution brought to you by ConnectWise

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

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.

I need Monday to be treated as last day of the week.

actual data, rows and columns - not descriptions

looks like there are 2 tables involved. elm_dates and elm_book, please provide sample data for both, as well as the expected results for them

```
10579 20131203 1064 1
10579 20131203 1064 1
10579 20131203 12 1
10579 20131203 12 1
10579 20131203 1475 1
10579 20131203 1475 1
10579 20131203 17 1
10579 20131203 17 1
10579 20131203 305 12
10579 20131203 305 12
10579 20131203 306 4
10579 20131203 306 4
10579 20131203 307 6
10579 20131203 307 6
10579 20131203 309 2
10579 20131203 309 2
10579 20131203 384 1
10579 20131203 384 1
10579 20131203 6 2
10579 20131203 6 2
10579 20131203 7415 1
10579 20131203 7415 1
10579 20131205 1064 2
10579 20131205 1064 2
10579 20131205 12 1
10579 20131205 12 1
10579 20131205 1475 1
10579 20131205 1475 1
10579 20131205 17 1
10579 20131205 17 1
10579 20131205 305 16
10579 20131205 305 16
10579 20131205 306 6
10579 20131205 306 6
10579 20131205 307 8
10579 20131205 307 8
10579 20131205 309 3
10579 20131205 309 3
10579 20131205 384 2
10579 20131205 384 2
10579 20131205 6 3
10579 20131205 6 3
10579 20131205 7415 1
10579 20131205 7415 1
```

Thanks

```
10395 20131207 1 2
10395 20131207 13 1
10395 20131207 1690 1
10395 20131207 1692 1
10395 20131207 30 1
10395 20131207 305 6
10395 20131207 306 2
10395 20131207 307 5
10395 20131207 308 1
10395 20131207 309 1
10395 20131207 3743 1
10395 20131207 3752 1
10395 20131207 384 1
10395 20131207 6 1
10396 20131205 1 1
10396 20131205 17 1
10396 20131205 30 1
10396 20131205 305 6
10396 20131205 306 3
10396 20131205 307 4
10396 20131205 309 1
10396 20131205 384 1
10396 20131205 6 1
```

if you could provide a sql script that creates the table and has inserts like the other table that would be even better

can you provide something similar for elm_book ?

CREATE TABLE export_table

(

chain NUMBER,

cusnum NUMBER,

sunday VARCHAR2(20),

monday VARCHAR2(20),

tuesday VARCHAR2(20),

wednesday VARCHAR2(20),

thursday VARCHAR2(20),

friday VARCHAR2(20),

saturday VARCHAR2(20)

);

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

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;
```

and it produced the results in http:#39692713

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;
```

Experts Exchange Solution brought to you by ConnectWise

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 trialQuestion has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by ConnectWise

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

Can you post sample data and expected results?