[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Oracle SQL Query

Hello,

I am attaching a file that shows how the sample data is stored in a table and what the expected results are from that table.

Basically, I need to be able to write a query that shows last 6 weeks trend for certain Action's. I need the First day of the week to be Sunday. For a certain Table name, if there are more data inserted within one week then it should get the max RUN_DT for that week and its related counts.

Could someone please help?
Thank you
SAMPLE-DATA.xls
0
angel7170
Asked:
angel7170
1 Solution
 
sdstuberCommented:
you can't have dynamic columns in sql.
But, we can still have the data run for the most recent 6 weeks


  SELECT table_name,
         action_item,
         MAX(curr_week) week_ago_0,
         MAX(week_ago_1) week_ago_1,
         MAX(week_ago_2) week_ago_2,
         MAX(week_ago_3) week_ago_3,
         MAX(week_ago_4) week_ago_4,
         MAX(week_ago_5) week_ago_5
    FROM (SELECT run_dt,
                 table_name,
                 action_item,
                 val,
                 (NEXT_DAY(TRUNC(SYSDATE - 7), 'Sunday') - NEXT_DAY(TRUNC(run_dt - 7), 'Sunday')) / 7
                     run_wk
            FROM yourtable UNPIVOT (val
                           FOR action_item
                           IN (source_count,
                              target_count,
                              s_minus_t_count,
                              t_minus_s_count,
                              bwd_count))
           WHERE run_dt > NEXT_DAY(TRUNC(SYSDATE - 7), 'Sunday') - 35) PIVOT (MAX(val)
                                                                           FOR run_wk
                                                                           IN (0 curr_week,
                                                                              1 week_ago_1,
                                                                              2 week_ago_2,
                                                                              3 week_ago_3,
                                                                              4 week_ago_4,
                                                                              5 week_ago_5))
GROUP BY table_name, action_item
ORDER BY table_name, action_item;
0
 
angel7170Author Commented:
This works perfect as expected. Thank you very much
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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