CODEWITHSUNDEEP
sqloracle-database
mrdiu
mrdiu

Reputation: 49

Can I pivot date rows into columns without having to specify the dates in the pivot? Oracle SQL

Maybe pivot isn't the right function but it's how I can currently get the data to show the way I want with manually inputting the pivot dates, which I'm trying to avoid, since I already have my date range between sysdate and sysdate +5

The purpose of the query is to find how many pallets are to be picked in the next 5 days for each customer.

Sample table data

COMP_CODE | CUST_CODE | ORD_NUM | SHIP_DATE | PALLETS
W2          100001       100       02-09-21    5
W2          100001       101       02-10-21    10
W2          100002       102       02-11-21    7
W2          100003       103       02-12-21    3

My code current code

with pallets as 
(
SELECT    a.comp_code,
          a.cust_code,
          a.ord_num, 
          to_char(a.ord_to_ship_date,'mm-dd-yy')ship_date,
          sum(b.ord_ship_qty)/(c.item_qty_bkd_qty) pallets
FROM      e_ord_h a
LEFT JOIN e_ord_d5 b
ON        a.comp_code=b.comp_code and a.ord_num=b.ord_num
LEFT JOIN m_item_d1 c
ON        b.comp_code=c.comp_code and b.cust_code=c.cust_code and b.ord_lev1=c.item_code
WHERE     a.comp_code='W2'
AND       c.item_qty_bkd_lev_num=1
AND       a.flow_pros_code!='COOR'
AND       trunc(a.ord_to_ship_date) between sysdate and sysdate +5
GROUP BY  a.comp_code, a.cust_code, a.ord_num, a.ord_to_ship_date, c.item_qty_bkd_qty
ORDER BY  ship_date
)
SELECT    * from 
(
SELECT    comp_code, cust_code, ship_date, sum(ceil(pallets)) pallets
FROM      pallets
GROUP BY  comp_code, cust_code, ship_date
)
PIVOT     (sum(pallets) for ship_date in ('02-09-21','02-09-21','02-10-21','02-11-21','02-13-21'))

Output I'm trying to achieve without using pivots to manually enter the pivot dates.

COMP_CODE | CUST_CODE | 02-09-21 | 02-10-21 | 02-11-21 | 02-12-21
W2          100001        5            
W2          100001                    10
W2          100002                              7
W2          100003                                         3

Thank you in advance for the help and have a great day!

Upvotes: 1

Views: 969

Answers (1)

user5683823
user5683823

Reputation:

In the pallets subquery you select a string representation of dates (next five or six days). Instead of that, you should select integers, as follows:

select .... , trunc(a.ord_to_ship_date) - trunc(sysdate) as days_ahead, ....

And then, in the outer select,

pivot .... for days_ahead in (1 as today_plus_1, 2 as today_plus_2, 3 as ....)

Note that in pallets you have trunc(a.ord_to_ship_date) between sysdate and sysdate + 5. This means that if the query is run exactly at midnight, you will get ship dates for today, tomorrow, ..., today + 5 (SIX days total). If it's at any time other than midnight, you will get only five days - EXCLUDING today. Not sure what your actual requirement is, but you may want to compare to TRUNC(sysdate) instead of sysdate, and see exactly what you need in the query.

Upvotes: 2

Related Questions