oracle query

This question is similar to

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

but is by months

SELECT   MAX(year) as Year,
         present_month,
         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,
         sum(booking_surplus_deficit) as totals
    FROM tab1--mthly_proposed_goal_improve
where year = EXTRACT(YEAR FROM SYSDATE)
   AND TO_DATE(year || month, 'yyyy-mm') >= TRUNC(SYSDATE, 'mm')
   and day_of_week between add_months(trunc(sysdate,'mm'),-1) and add_months(trunc(sysdate,'mm'),0)
GROUP BY order_by ,present_month,day_of_week
ORDER BY day_of_week asc ,order_by 

Open in new window



Data for the query is attached in the spread sheet.
 sample table and data attached.

I need the improvements by month

I tried a query but did not work. No data queried.

SELECT Year,Month
       , MAX ( CASE WHEN Rep_Id = 2 THEN mthly_Improve END ) AS Jen
       , MAX(CASE WHEN rep_id = 3 THEN mthly_Improve END) AS Karen
       , MAX(CASE WHEN rep_id = 4 THEN mthly_Improve END) AS Carrie
       , MAX(CASE WHEN rep_id = 5 THEN mthly_Improve END) AS Camille
       , MAX(CASE WHEN rep_id = 6 THEN mthly_Improve END) AS Janet
       , MAX(CASE WHEN rep_id = 7 THEN mthly_Improve END) AS Julie
       , MAX(CASE WHEN rep_id = 8 THEN mthly_Improve END) AS Kelly
       , MAX(CASE WHEN rep_id = 9 THEN mthly_Improve END) AS Mellisa
       , MAX(CASE WHEN rep_id = 10 THEN mthly_Improve END) AS Shad
       , MAX ( CASE WHEN Rep_Id = 11 THEN mthly_Improve END ) AS Sheri
       , MAX(CASE WHEN rep_id = 12 THEN mthly_Improve END) AS Courtney
       , MAX(CASE WHEN rep_id = 13 THEN mthly_Improve END) AS Katie
       , MAX(CASE WHEN rep_id = 15 THEN mthly_Improve END) AS David
       , MAX(CASE WHEN rep_id = 18 THEN mthly_Improve END) AS Amy
       , MAX(CASE WHEN rep_id = 19 THEN mthly_Improve END) AS Theresa
        FROM ( SELECT M0.Year,M0.Month
                    , M0.Day_Of_Week
					,add_months(trunc(M0.day_of_week,'mm'),0) - add_months(trunc(M0.day_of_week,'mm'),-1)
                    , M0.Order_By
                   , M0.Rep_Id
                   , M1.Goals
                   , ( M0.Booking_Surplus_Deficit - M1.Booking_Surplus_Deficit )
                        mthly_Improve
                FROM tab1 M0
                     JOIN tab1 M1                      
					  ON add_months(trunc(M0.day_of_week,'mm'),0) = add_months(trunc(M0.day_of_week,'mm'),-1)
					   AND M1.Month = M0.Month
                       AND M1.Rep_Id = M0.Rep_Id
   where m0.day_of_week between add_months(trunc(sysdate,'mm'),-1) and add_months(trunc(sysdate,'mm'),0))
   GROUP BY Order_By, Month,Year
   ORDER BY Order_By

Open in new window

data.xls
sample-table-and-data.txt
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:
looks to me like the first query gives you the data in your spread sheet.

if that's not what you want, what do you want it to do?

I don't need the results that don't want - I can run the query on the data and see them as is.

 I need to see the results you do want.
0
anumosesAuthor Commented:
please see attached
data.xls
0
sdstuberCommented:
SELECT MAX(year) AS year,
         present_month,
         day_of_week AS day_of_week,
         MAX(month) AS month,
         order_by,
         MAX(CASE WHEN rep_id = 2 THEN diff END) AS jen,
         MAX(CASE WHEN rep_id = 3 THEN diff END) AS karen,
         MAX(CASE WHEN rep_id = 4 THEN diff END) AS carrie,
         MAX(CASE WHEN rep_id = 5 THEN diff END) AS camille,
         MAX(CASE WHEN rep_id = 6 THEN diff END) AS janet,
         MAX(CASE WHEN rep_id = 7 THEN diff END) AS julie,
         MAX(CASE WHEN rep_id = 8 THEN diff END) AS kelly,
         MAX(CASE WHEN rep_id = 9 THEN diff END) AS mellisa,
         MAX(CASE WHEN rep_id = 10 THEN diff END) AS shad,
         MAX(CASE WHEN rep_id = 11 THEN diff END) AS sheri,
         MAX(CASE WHEN rep_id = 12 THEN diff END) AS courtney,
         MAX(CASE WHEN rep_id = 13 THEN diff END) AS katie,
         MAX(CASE WHEN rep_id = 15 THEN diff END) AS david,
         MAX(CASE WHEN rep_id = 18 THEN diff END) AS amy,
         MAX(CASE WHEN rep_id = 19 THEN diff END) AS theresa,
         SUM(diff) AS totals
    FROM (SELECT *
            FROM (SELECT t.*,
                           booking_surplus_deficit
                         - LAG(
                               booking_surplus_deficit
                           )
                           OVER(
                               PARTITION BY year, month, rep_id
                               ORDER BY TO_DATE(year || present_month, 'yyyy-mon')
                           )
                             diff,
                         ROW_NUMBER()
                         OVER(
                             PARTITION BY year, month, rep_id
                             ORDER BY TO_DATE(year || present_month, 'yyyy-mon')
                         )
                             rn
                    FROM tab1 t)
           WHERE rn > 1)
   WHERE year = EXTRACT(YEAR FROM SYSDATE)
     AND TO_DATE(year || month, 'yyyy-mm') >= TRUNC(SYSDATE, 'mm')
     AND day_of_week BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1)
                         AND ADD_MONTHS(TRUNC(SYSDATE, 'mm'), 0)
GROUP BY order_by, present_month, day_of_week
ORDER BY day_of_week ASC, 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
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.