[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle query

Posted on 2014-07-18
18
Medium Priority
?
274 Views
Last Modified: 2014-07-31
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
0
Comment
Question by:anumoses
  • 11
  • 5
  • 2
18 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 668 total points
ID: 40205210
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
 
LVL 6

Author Comment

by:anumoses
ID: 40205231
Thanks for part of the answer

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

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 332 total points
ID: 40205290
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 6

Author Comment

by:anumoses
ID: 40205304
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40205364
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
 
LVL 6

Author Comment

by:anumoses
ID: 40205373
But my question was it has to show months and zeros right? It did not.
0
 
LVL 6

Author Comment

by:anumoses
ID: 40205385
See attached
data.JPG
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40205610
Mine does show months and zeroes, that means you have different data than mine.
Attach your data file or insert statements.
0
 
LVL 6

Author Comment

by:anumoses
ID: 40208999
I have data for 13th JUL (sunday) and 20th JUL ( Sunday)
table-data.txt
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40209036
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
 
LVL 6

Author Comment

by:anumoses
ID: 40209049
Yes it does. Thanks
0
 
LVL 6

Author Comment

by:anumoses
ID: 40211607
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
 
LVL 6

Author Comment

by:anumoses
ID: 40211860
Dont worry I got it.
0
 
LVL 6

Author Comment

by:anumoses
ID: 40212318
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 668 total points
ID: 40212329
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
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40212493
thanks
0
 
LVL 6

Author Comment

by:anumoses
ID: 40231883
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40232027
.... Improvements for few people are not correctly calculated.
You never gave us the formula.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month18 days, 1 hour left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question