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

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

Thanks for part of the answer

Just including sample data here. Just need help to add weekly improvements.

SOLUTION

MikeOM_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

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_Deficit,0) - nvl(W1.Booking_Surplus_Deficit,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

MikeOM_DBA

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.

Open in new window

anumoses

ASKER

But my question was it has to show months and zeros right? It did not.

anumoses

ASKER

See attached
data.JPG

MikeOM_DBA

Mine does show months and zeroes, that means you have different data than mine.
Attach your data file or insert statements.

anumoses

ASKER

I have data for 13th JUL (sunday) and 20th JUL ( Sunday)
table-data.txt

MikeOM_DBA

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.

Open in new window

anumoses

ASKER

Yes it does. Thanks

anumoses

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.

anumoses

ASKER

Dont worry I got it.

anumoses

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

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

thanks

anumoses

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

MikeOM_DBA

.... Improvements for few people are not correctly calculated.

You never gave us the formula.