CODEWITHSUNDEEP
sqloracle-database
author4
author4

Reputation: 135

Get the Data for Each Month (till that month) for 12 months

I have tried using the query to get the item counts(In + Out) for container, File-folder using the Detailed table.

Eg.: Now the current month is July 2019. As per the snap shot table we will get the data as July 2019(<=July 2019) and for June (<= June 2019)..,

But in detailed table, now our query gives counts only for July 2019, but we need the data till July 2019 (I,e.. <=July 2019).

Required: In the same manner we need the data for each month July 2019,June 2019.. to July 2018(12 months Trend).

SELECT CASE
       WHEN gdw.dim_strg_itm.rfid_stat_id = 1
          AND gdw.dim_strg_itm_tp.strg_itm_ctgry_dsc = 'CONTAINER'
          AND strg_itm_nw_stat_key IN (16004, 16000) THEN
        COUNT(strg_itm_nw_stat_key)
     END "CNT",
     CASE
       WHEN gdw.dim_strg_itm.rfid_stat_id = 2
          AND gdw.dim_strg_itm_tp.strg_itm_ctgry_dsc = 'CONTAINER'
          AND strg_itm_nw_stat_key IN (16004, 16000) THEN
        COUNT(strg_itm_nw_stat_key)
     END "NCNT",
     gdw.dim_dt.mo_yr_cd
FROM   gdw.dim_dt dim_dt_fclty_srvc,
     gdw.dim_cust_acnt,
     gdw.dim_cust_dept,
     gdw.dim_strg_itm,
     gdw.dim_strg_itm_stat,
     gdw.dim_dt,
     gdw.fact_rim_itm_actvty_dtl,
     gdw.dim_strg_itm_tp
WHERE  gdw.fact_rim_itm_actvty_dtl.actvty_dt_key || fact_rim_itm_actvty_dtl.dim_actvty_key || gdw.fact_rim_itm_actvty_dtl.actvty_tm_key =
     (SELECT MAX(actvty_dt_key || dim_actvty_key || actvty_tm_key)
      FROM   gdw.fact_rim_itm_actvty_dtl a1
      INNER  JOIN gdw.dim_dt dt
      ON     a1.actvty_dt_key = dt.dim_dt_key
      WHERE  gdw.fact_rim_itm_actvty_dtl.dim_strg_itm_key = a1.dim_strg_itm_key
      AND    gdw.fact_rim_itm_actvty_dtl.dim_cust_acnt_key = a1.dim_cust_acnt_key
      AND    gdw.fact_rim_itm_actvty_dtl.dim_cust_dept_key = a1.dim_cust_dept_key
      AND    a1.strg_itm_nw_stat_key <> -1
      AND    dt.clndr_dt <= '02-FEB-2019')
AND    (gdw.fact_rim_itm_actvty_dtl.actvty_dt_key = dim_dt_fclty_srvc.dim_dt_key)
AND    (gdw.fact_rim_itm_actvty_dtl.dim_strg_itm_key = gdw.dim_strg_itm.dim_strg_itm_key)
AND    (gdw.dim_cust_acnt.dim_cust_key = gdw.fact_rim_itm_actvty_dtl.dim_cust_acnt_key)
AND    (gdw.fact_rim_itm_actvty_dtl.dim_cust_dept_key = gdw.dim_cust_dept.dim_cust_dept_key)
AND    (gdw.fact_rim_itm_actvty_dtl.strg_itm_nw_stat_key = gdw.dim_strg_itm_stat.dim_strg_itm_stat_key)
AND    (gdw.fact_rim_itm_actvty_dtl.actvty_dt_key = gdw.dim_dt.dim_dt_key)
AND    (gdw.dim_strg_itm.strg_itm_tp_cd = gdw.dim_strg_itm_tp.strg_itm_tp_cd)
AND    (gdw.dim_cust_acnt.cust_id IN '7y897451'
    --@Prompt('Account:','A','Customer\Customer Account\Level 1 Account Code',Multi,Free,Persistent,,User:0)
    AND (gdw.dim_cust_acnt.src_sys_cd = 'RSSQL' AND substr(cust_id, 1, 2) IN ('ON', 'RP', 'RE', 'RL', 'PR', 'PL', 'LC', 'LE', 'PT', 'RR')) AND
    gdw.dim_cust_acnt.src_sys_cd IN ('RSSQL'))
GROUP  BY gdw.dim_strg_itm.rfid_stat_id,
        gdw.dim_strg_itm_tp.strg_itm_ctgry_dsc,
        strg_itm_nw_stat_key,
        gdw.dim_dt.mo_yr_cd
ORDER  BY to_date(gdw.dim_dt.mo_yr_cd, 'Mon YYYY') DESC

For Example: january 2019 data (sum of all months data less than or equal to January 2019 ), similarly, for Feb 2019 (sum of data <=Feb 2019). like this for 12 months.

Upvotes: 0

Views: 122

Answers (1)

cdub
cdub

Reputation: 2297

It looks to me like the goal is to create a running total by month. Below is an example of how this might be accomplished in Oracle using manufactured sample data. 

WITH fact_data (trx_date, qty)
AS
(
  SELECT DATE '2019-05-01', 10 FROM dual UNION ALL
  SELECT DATE '2019-05-15', 5 FROM dual UNION ALL
  SELECT DATE '2019-05-17', 10 FROM dual UNION ALL
  SELECT DATE '2019-06-04', 20 FROM dual UNION ALL
  SELECT DATE '2019-06-15', 5 FROM dual UNION ALL
  SELECT DATE '2019-06-30', 10 FROM dual UNION ALL
  SELECT DATE '2019-07-01', 5 FROM dual UNION ALL
  SELECT DATE '2019-07-15', 5 FROM dual UNION ALL
  SELECT DATE '2019-07-20', 5 FROM dual
)
SELECT TO_CHAR(TRUNC(trx_date, 'MONTH'), 'fmMonth YYYY') month,
       SUM(SUM(qty)) OVER (ORDER BY TRUNC(trx_date, 'MONTH') ) qty_to_date
  FROM fact_data
  GROUP BY TRUNC(trx_date, 'MONTH') 
  ORDER BY TRUNC(trx_date, 'MONTH');

 MONTH        QTY_TO_DATE
------------ -----------
May 2019              25
June 2019             60
July 2019             75

Upvotes: 1

Related Questions