Oracle query

related to
http://www.experts-exchange.com/Database/Oracle/Q_28478256.html#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
LVL 6
anumosesAsked:
Who is Participating?
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.

sdstuberCommented:
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
 WHERE year = EXTRACT(YEAR FROM SYSDATE)
   AND TO_DATE(year || '-' || month, 'yyyy-mm') >= TRUNC(SYSDATE, 'mm')
GROUP BY order_by, day_of_week
ORDER BY day_of_week, order_by
0

Experts Exchange Solution brought to you by

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 trial
anumosesAuthor Commented:
Thanks for part of the answer

Just including sample data here. Just need help to add weekly improvements.
0
MikeOM_DBACommented:
Try this:
SELECT Month
     , MAX ( CASE WHEN Rep_Id = 2 THEN Wkly_Improve END ) AS Jen
--  , E t c  ...
     , MAX ( CASE WHEN Rep_Id = 11 THEN Wkly_Improve 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
              , ( W0.Booking_Surplus_Deficit - W1.Booking_Surplus_Deficit )
                   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
/

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

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.

anumosesAuthor Commented:
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
0
MikeOM_DBACommented:
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

0
anumosesAuthor Commented:
But my question was it has to show months and zeros right? It did not.
0
anumosesAuthor Commented:
See attached
data.JPG
0
MikeOM_DBACommented:
Mine does show months and zeroes, that means you have different data than mine.
Attach your data file or insert statements.
0
anumosesAuthor Commented:
I have data for 13th JUL (sunday) and 20th JUL ( Sunday)
table-data.txt
0
MikeOM_DBACommented:
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

0
anumosesAuthor Commented:
Yes it does. Thanks
0
anumosesAuthor Commented:
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.
0
anumosesAuthor Commented:
Dont worry I got it.
0
anumosesAuthor Commented:
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
0
sdstuberCommented:
This is what I originally posted,  does it not work?


 WHERE year = EXTRACT(YEAR FROM SYSDATE)
   AND TO_DATE(year || '-' || month, 'yyyy-mm') >= TRUNC(SYSDATE, 'mm')
0
anumosesAuthor Commented:
thanks
0
anumosesAuthor Commented:
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.

http://www.experts-exchange.com/Database/Oracle/Q_28487730.html
0
MikeOM_DBACommented:
.... Improvements for few people are not correctly calculated.
You never gave us the formula.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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