• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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
0
anumoses
Asked:
anumoses
  • 2
  • 2
1 Solution
 
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
 
anumosesAuthor Commented:
thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now