anumoses
asked on
Oracle query
related to
https://www.experts-exchange.com/questions/28478256/oracle-query-with-max-and-group-by-clause.html?anchorAnswerId=40202661#a40202661
-------------------------- ---------- -
SELECT MAX(year) as Year,
day_of_week AS day_of_week,
MAX(month) AS month,
order_by,
MAX(CASE WHEN rep_id = 2 THEN BOOKING_SURPLUS_DEFICIT END) AS Jen,
MAX(CASE WHEN rep_id = 3 THEN BOOKING_SURPLUS_DEFICIT END) AS Karen,
MAX(CASE WHEN rep_id = 4 THEN BOOKING_SURPLUS_DEFICIT END) AS Carrie,
MAX(CASE WHEN rep_id = 5 THEN BOOKING_SURPLUS_DEFICIT END) AS Camille,
MAX(CASE WHEN rep_id = 6 THEN BOOKING_SURPLUS_DEFICIT END) AS Janet,
MAX(CASE WHEN rep_id = 7 THEN BOOKING_SURPLUS_DEFICIT END) AS Julie,
MAX(CASE WHEN rep_id = 8 THEN BOOKING_SURPLUS_DEFICIT END) AS Kelly,
MAX(CASE WHEN rep_id = 9 THEN BOOKING_SURPLUS_DEFICIT END) AS Mellisa,
MAX(CASE WHEN rep_id = 10 THEN BOOKING_SURPLUS_DEFICIT END) AS Shad,
MAX(CASE WHEN rep_id = 11 THEN BOOKING_SURPLUS_DEFICIT END) AS Sheri,
MAX(CASE WHEN rep_id = 12 THEN BOOKING_SURPLUS_DEFICIT END) AS Courtney,
MAX(CASE WHEN rep_id = 13 THEN BOOKING_SURPLUS_DEFICIT END) AS Katie,
MAX(CASE WHEN rep_id = 15 THEN BOOKING_SURPLUS_DEFICIT END) AS David,
MAX(CASE WHEN rep_id = 18 THEN BOOKING_SURPLUS_DEFICIT END) AS Amy,
MAX(CASE WHEN rep_id = 19 THEN BOOKING_SURPLUS_DEFICIT END) AS Theresa
FROM wkly_proposed_goal_improve
GROUP BY order_by,day_of_week
ORDER BY day_of_week,order_by
-------------------------- ----
Expected is attached
I also wanted the query to give me data from the present month that we are running the report. For now from JUL onwards. The table has data from JAN 2014.
Just including sample data here. Just need help to add weekly improvements.
Goal-Improvements-by-Week.xls
goal-improvements.JPG
https://www.experts-exchange.com/questions/28478256/oracle-query-with-max-and-group-by-clause.html?anchorAnswerId=40202661#a40202661
--------------------------
SELECT MAX(year) as Year,
day_of_week AS day_of_week,
MAX(month) AS month,
order_by,
MAX(CASE WHEN rep_id = 2 THEN BOOKING_SURPLUS_DEFICIT END) AS Jen,
MAX(CASE WHEN rep_id = 3 THEN BOOKING_SURPLUS_DEFICIT END) AS Karen,
MAX(CASE WHEN rep_id = 4 THEN BOOKING_SURPLUS_DEFICIT END) AS Carrie,
MAX(CASE WHEN rep_id = 5 THEN BOOKING_SURPLUS_DEFICIT END) AS Camille,
MAX(CASE WHEN rep_id = 6 THEN BOOKING_SURPLUS_DEFICIT END) AS Janet,
MAX(CASE WHEN rep_id = 7 THEN BOOKING_SURPLUS_DEFICIT END) AS Julie,
MAX(CASE WHEN rep_id = 8 THEN BOOKING_SURPLUS_DEFICIT END) AS Kelly,
MAX(CASE WHEN rep_id = 9 THEN BOOKING_SURPLUS_DEFICIT END) AS Mellisa,
MAX(CASE WHEN rep_id = 10 THEN BOOKING_SURPLUS_DEFICIT END) AS Shad,
MAX(CASE WHEN rep_id = 11 THEN BOOKING_SURPLUS_DEFICIT END) AS Sheri,
MAX(CASE WHEN rep_id = 12 THEN BOOKING_SURPLUS_DEFICIT END) AS Courtney,
MAX(CASE WHEN rep_id = 13 THEN BOOKING_SURPLUS_DEFICIT END) AS Katie,
MAX(CASE WHEN rep_id = 15 THEN BOOKING_SURPLUS_DEFICIT END) AS David,
MAX(CASE WHEN rep_id = 18 THEN BOOKING_SURPLUS_DEFICIT END) AS Amy,
MAX(CASE WHEN rep_id = 19 THEN BOOKING_SURPLUS_DEFICIT END) AS Theresa
FROM wkly_proposed_goal_improve
GROUP BY order_by,day_of_week
ORDER BY day_of_week,order_by
--------------------------
Expected is attached
I also wanted the query to give me data from the present month that we are running the report. For now from JUL onwards. The table has data from JAN 2014.
Just including sample data here. Just need help to add weekly improvements.
Goal-Improvements-by-Week.xls
goal-improvements.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tried using nvl, But not getting any data
SELECT Month
, MAX ( CASE WHEN Rep_Id = 2 THEN nvl(Wkly_Improve,0) END ) AS Jen
-- , E t c ...
, MAX ( CASE WHEN Rep_Id = 11 THEN nvl(Wkly_Improve,0) END ) AS Sheri
FROM ( SELECT W0.Month
, W0.Day_Of_Week
, W0.Day_Of_Week - 7 Dow_7
, W0.Order_By
, W0.Rep_Id
, W1.Goals
, ( nvl(W0.Booking_Surplus_Def icit,0) - nvl(W1.Booking_Surplus_Def icit,0) )
Wkly_Improve
FROM Wkly_Proposed_Goal_Improve W0
JOIN Wkly_Proposed_Goal_Improve W1
ON W1.Day_Of_Week = W0.Day_Of_Week - 7
AND W1.Month = W0.Month
AND W1.Rep_Id = W0.Rep_Id
WHERE W0.Year = EXTRACT ( YEAR FROM SYSDATE )
AND TRUNC ( W0.Day_Of_Week, 'mm' ) >= TRUNC ( SYSDATE, 'mm' ) )
GROUP BY Order_By, Month
ORDER BY Order_By
SELECT Month
, MAX ( CASE WHEN Rep_Id = 2 THEN nvl(Wkly_Improve,0) END ) AS Jen
-- , E t c ...
, MAX ( CASE WHEN Rep_Id = 11 THEN nvl(Wkly_Improve,0) END ) AS Sheri
FROM ( SELECT W0.Month
, W0.Day_Of_Week
, W0.Day_Of_Week - 7 Dow_7
, W0.Order_By
, W0.Rep_Id
, W1.Goals
, ( nvl(W0.Booking_Surplus_Def
Wkly_Improve
FROM Wkly_Proposed_Goal_Improve
JOIN Wkly_Proposed_Goal_Improve
ON W1.Day_Of_Week = W0.Day_Of_Week - 7
AND W1.Month = W0.Month
AND W1.Rep_Id = W0.Rep_Id
WHERE W0.Year = EXTRACT ( YEAR FROM SYSDATE )
AND TRUNC ( W0.Day_Of_Week, 'mm' ) >= TRUNC ( SYSDATE, 'mm' ) )
GROUP BY Order_By, Month
ORDER BY Order_By
Because you have the same data for weeks 7/7/2014 and 7/14/2014.
SQL> SELECT Month
, MAX ( CASE WHEN Rep_Id = 2 THEN Wkly_Improve END ) AS Jen
2 3 , MAX ( CASE WHEN Rep_Id = 11 THEN Wkly_Improve END ) AS Sheri
4 FROM ( SELECT W0.Month
5 , W0.Day_Of_Week
6 , W0.Day_Of_Week - 7 Dow_7
7 , W0.Order_By
8 , W0.Rep_Id
9 , W1.Goals
10 , ( W0.Booking_Surplus_Deficit - W1.Booking_Surplus_Deficit )
11 Wkly_Improve
12 FROM Wkly_Proposed_Goal_Improve W0
13 JOIN Wkly_Proposed_Goal_Improve W1
14 ON W1.Day_Of_Week = W0.Day_Of_Week - 7
15 AND W1.Month = W0.Month
16 AND W1.Rep_Id = W0.Rep_Id
17 WHERE W0.Year = EXTRACT ( YEAR FROM SYSDATE )
18 AND TRUNC ( W0.Day_Of_Week, 'mm' ) >= TRUNC ( SYSDATE, 'mm' ) )
19 GROUP BY Order_By, Month
20 ORDER BY Order_By
21 /
MONTH JEN SHERI
--------------- ---------- ----------
JAN 0 0
FEB 0 0
MAR 0 0
APR 0 0
MAY 0 0
JUN 0 0
JUL 0 0
AUG 0 0
SEP 0 0
OCT 0 0
NOV 0 0
DEC 0 0
12 rows selected.
ASKER
But my question was it has to show months and zeros right? It did not.
ASKER
See attached
data.JPG
data.JPG
Mine does show months and zeroes, that means you have different data than mine.
Attach your data file or insert statements.
Attach your data file or insert statements.
ASKER
I have data for 13th JUL (sunday) and 20th JUL ( Sunday)
table-data.txt
table-data.txt
Mine still works with your data:
SQL> SELECT Day_Of_Week AS From_Week
2 , Dow_7 To_Week
3 , Month
4 , MAX ( CASE WHEN Rep_Id = 2 THEN Wkly_Improve END ) AS Jen
5 , MAX ( CASE WHEN Rep_Id = 11 THEN Wkly_Improve END ) AS Sheri
6 FROM ( SELECT W0.Month
7 , W0.Day_Of_Week
8 , W0.Day_Of_Week - 7 Dow_7
9 , W0.Order_By
10 , W0.Rep_Id
11 , W1.Goals
12 , ( W0.Booking_Surplus_Deficit - W1.Booking_Surplus_Deficit )
13 Wkly_Improve
14 FROM Wkly_Proposed_Goal_Improve W0
15 JOIN Wkly_Proposed_Goal_Improve W1
16 ON W1.Day_Of_Week = W0.Day_Of_Week - 7
17 AND W1.Month = W0.Month
18 AND W1.Rep_Id = W0.Rep_Id
19 WHERE W0.Year = EXTRACT ( YEAR FROM SYSDATE )
20 AND TRUNC ( W0.Day_Of_Week, 'mm' ) >= TRUNC ( SYSDATE, 'mm' ) )
21 GROUP BY Order_By, Month, Day_Of_Week, Dow_7
22 ORDER BY Order_By
23 /
FROM_WEEK TO_WEEK MONTH JEN SHERI
------------------ ------------------ --------------- ---------- ----------
20-Jul-14 13-Jul-14 JAN 0 0
20-Jul-14 13-Jul-14 FEB 0 0
20-Jul-14 13-Jul-14 MAR 0 0
20-Jul-14 13-Jul-14 APR 0 0
20-Jul-14 13-Jul-14 MAY 0 0
20-Jul-14 13-Jul-14 JUN 0 0
20-Jul-14 13-Jul-14 JUL 0 0
20-Jul-14 13-Jul-14 AUG 0 -17
20-Jul-14 13-Jul-14 SEP 0 0
20-Jul-14 13-Jul-14 OCT 0 0
20-Jul-14 13-Jul-14 NOV 0 0
20-Jul-14 13-Jul-14 DEC 0 0
12 rows selected.
ASKER
Yes it does. Thanks
ASKER
I have one more request. Need a total on each line at the end.
eg
FROM_WEEK TO_WEEK MONTH JEN SHERI TOTAL
------------------ ------------------ --------------- ---------- ----------
20-Jul-14 13-Jul-14 JAN 0 0 0
20-Jul-14 13-Jul-14 FEB 0 0 0
20-Jul-14 13-Jul-14 MAR 0 0
20-Jul-14 13-Jul-14 APR 0 0
20-Jul-14 13-Jul-14 MAY 0 0
20-Jul-14 13-Jul-14 JUN 0 0
20-Jul-14 13-Jul-14 JUL 0 0
20-Jul-14 13-Jul-14 AUG 0 -17 -17
20-Jul-14 13-Jul-14 SEP 0 0
20-Jul-14 13-Jul-14 OCT 0 0
20-Jul-14 13-Jul-14 NOV 0 0
20-Jul-14 13-Jul-14 DEC 0 0
add all deficits and give a total.
eg
FROM_WEEK TO_WEEK MONTH JEN SHERI TOTAL
------------------ ------------------ --------------- ---------- ----------
20-Jul-14 13-Jul-14 JAN 0 0 0
20-Jul-14 13-Jul-14 FEB 0 0 0
20-Jul-14 13-Jul-14 MAR 0 0
20-Jul-14 13-Jul-14 APR 0 0
20-Jul-14 13-Jul-14 MAY 0 0
20-Jul-14 13-Jul-14 JUN 0 0
20-Jul-14 13-Jul-14 JUL 0 0
20-Jul-14 13-Jul-14 AUG 0 -17 -17
20-Jul-14 13-Jul-14 SEP 0 0
20-Jul-14 13-Jul-14 OCT 0 0
20-Jul-14 13-Jul-14 NOV 0 0
20-Jul-14 13-Jul-14 DEC 0 0
add all deficits and give a total.
ASKER
Dont worry I got it.
ASKER
WHERE W0.Year = EXTRACT ( YEAR FROM SYSDATE )
AND TRUNC ( W0.Day_Of_Week, 'mm' ) >= TRUNC ( SYSDATE, 'mm' ) )
Was this condition not supposed to give me from present month forward? If it is true then I am getting data from Jan to Dec
AND TRUNC ( W0.Day_Of_Week, 'mm' ) >= TRUNC ( SYSDATE, 'mm' ) )
Was this condition not supposed to give me from present month forward? If it is true then I am getting data from Jan to Dec
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
ASKER
There is a new question posted which has few issues with the query posted by @MikeOM_DBA Improvements for few people are not correctly calculated.
https://www.experts-exchange.com/questions/28487730/oracle-query.html
https://www.experts-exchange.com/questions/28487730/oracle-query.html
.... Improvements for few people are not correctly calculated.You never gave us the formula.
ASKER
Just including sample data here. Just need help to add weekly improvements.