Solved

Oracle query for dates

Posted on 2014-07-22
4
272 Views
Last Modified: 2014-07-22
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,
         sum(booking_surplus_deficit) as totals
    FROM wkly_proposed_goal_improve
GROUP BY day_of_week,order_by
ORDER BY day_of_week,order_by


-------------------------

My requirement

Right now I have data for 2 weeks

1.    7/13/2014
2.    7/20/2014
Next week I will have for 7/27/2014

But the user wants the query to pull for the latest 2 weeks

Right now I am ok. but next week it has to give the output for 7/20/2014 and 7/27/2014
Goal-Improvements-by-Week.xls
0
Comment
Question by:anumoses
  • 2
4 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 40212286
Can never seem to get you to provide sample data and expected results which makes it so much easier for experts to help. Without seeing any data, I can only assume that there's a date field and the desired result will likely be some kind of analytical query such as row_number(partition by year, week order by datefield desc) rn where rn <= 2. Hope this helps.
0
 
LVL 6

Author Comment

by:anumoses
ID: 40212300
sample data enclosed
sample-data.txt
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 40212305
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,
         sum(booking_surplus_deficit) as totals
    FROM wkly_proposed_goal_improve
where day_of_week >= trunc(sysdate,'dy') - 14
GROUP BY day_of_week,order_by
ORDER BY day_of_week,order_by
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40212484
thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now