#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    1
    Rep Power
    0

    Grouping by Time Range


    I am trying to break down the balance_date to display the following groupings:

    7:00-17:30 CDT
    18:00-4:30 CDT

    I currently have the query setup to display by day instead of these time ranges. I would like the output to read

    19 May Day
    19 May Night
    20 May Day
    20 May Night

    I am fairly new to this, but how would I go about making this change? Any help would be appeciated.



    SELECT
    TO_CHAR(TRUNC(balance_date,'D') + 4,'YYYY') || '-' ||
    TO_CHAR(TRUNC(balance_date,'D') + 4,'IW') as year_wk,
    TO_CHAR(TRUNC(balance_date,'D') + 4,'IW')as wk,

    balance_date,
    main_process_name,
    core_process_name,
    line_item_name,
    actual_volume,
    (actual_time_seconds/3600) as actual_hours,
    (actual_volume / (actual_time_seconds/3600)) as uph

    FROM D_DAILY_PPR_AGGREGATES

    WHERE WAREHOUSE_ID = 'ABE2'
    AND balance_date >= TO_DATE('2012/12/01' ,'YYYY/MM/DD')
    AND balance_date<= TO_DATE('2013/12/01' ,'YYYY/MM/DD')

    AND line_item_name = 'Receive'
    AND actual_time_seconds > 0
    AND actual_volume > 0
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    834
    Rep Power
    387

    Cool


    Originally Posted by Preth
    I am trying to break down the balance_date to display the following groupings:

    7:00-17:30 CDT
    18:00-4:30 CDT
    . . . E t c . . .
    TRY this:
    Code:
      SELECT CASE
               WHEN TRUNC ( balance_date, 'HH24MI') BETWEEN '0700' AND '1730'
               THEN TO_CHAR ( balance_date, 'DD Mon') || ' Day'
               WHEN TRUNC ( balance_date, 'HH24MI') BETWEEN '1800' AND '1159'
                 OR TRUNC ( balance_date, 'HH24MI') BETWEEN '0000' AND '0430'
               THEN TO_CHAR ( balance_date, 'DD Mon') || ' Night'
               ELSE TO_CHAR ( balance_date, 'DD Mon') || ' Other'
             END AS period
           , balance_date
           , main_process_name
           , core_process_name
           , line_item_name
           , actual_volume
           , (actual_time_seconds / 3600) AS actual_hours
           , (actual_volume / (actual_time_seconds / 3600)) AS uph
        FROM d_daily_ppr_aggregates
       WHERE warehouse_id = 'ABE2'
         AND balance_date >= TO_DATE ( '2012/12/01', 'YYYY/MM/DD')
         AND balance_date <= TO_DATE ( '2013/12/01', 'YYYY/MM/DD')
         AND line_item_name = 'Receive'
         AND actual_time_seconds > 0
         AND actual_volume > 0
    ORDER BY balance_date;

IMN logo majestic logo threadwatch logo seochat tools logo