CODEWITHSUNDEEP
plsqlgroup-bypivot-table
teteh May
teteh May

Reputation: 455

Month To Date Report PLSQL

I am using PLSQL and I am trying to create month to date and year to date report from the following table (TAX):

Date            District         City          Amount
2020-01-02       West            Auckland      1000
2020-01-03       East            Auckland      1200
2020-01-04       North           Auckland      1300
2020-02-02       West            Auckland       500
2020-02-03       East            Auckland       300
2020-02-04       North           Auckland       200
2020-01-02       West            Wellington    2000
2020-01-03       East            Wellington     500
2020-01-04       North           Wellington    2500
2020-02-02       West            Wellington     300
2020-02-03       East            Wellington    1000
2020-02-04       North           Wellington     500

What I want to get is a group by City for latest day, month to date and year to date (sort of summary) as below:

City             2020-02-04   Month_To_Date    Year_To_Date
Auckland           200         1000             3000
Wellington         500         2800             7800

Is there any way to do this using Oracle PLSQL?

Upvotes: 0

Views: 67

Answers (1)

Belayer
Belayer

Reputation: 14886

The basics of what you want is a relatively simple query

select city      
      , <current_date>  
      , sum(amount) amt 
   from tax
   cross join for_date
   where tax_date = <current_date>
   group by city, <current_date>

However, this needs slight modification for each subset of totals (amt above), as the
tax_date = <current_date>, needs adjusting for the differing date periods. Further because these different conditions you cannot do this in a single select. The solution is to build a CTE for each date range then join then together.
(Note: I change the variable/column name to tax_date as date is data type with set definition and a reserved word. Using it as a variable/column name is very bad practice. So tip: never use a data type as a object name.)

with tax   as
  ( select date '2020-01-02' tax_date
         , 'West' district,'Auckland' city
         , 1000 amount 
      from dual union all                                   
    select date '2020-01-03','East','Auckland',1200    from dual union all 
    select date '2020-01-04','North','Auckland',1300   from dual union all
    select date '2020-02-02','West','Auckland',500     from dual union all
    select date '2020-02-03','East','Auckland',300     from dual union all
    select date '2020-02-04','North','Auckland',200    from dual union all
    select date '2020-01-02','West','Wellington',2000  from dual union all
    select date '2020-01-03','East','Wellington',500   from dual union all
    select date '2020-01-04','North','Wellington',2500 from dual union all
    select date '2020-02-02','West','Wellington',300   from dual union all
    select date '2020-02-03','East','Wellington',1000  from dual union all
    select date '2020-02-04','North','Wellington',500  from dual 
   )
   , for_date as (select date '&current_date' dt from dual)
   , dly as 
          ( select city      
                 , dt   
                 , sum(amount) amt 
              from tax
              cross join for_date
             where tax_date = dt 
             group by city, dt              
          )
   , mtd as 
          ( select city      
                 , dt   
                 , sum(amount) amt
              from tax                 
             cross join for_date             
             where trunc(tax_date,'mm') = trunc(dt,'mm') 
               and tax_date <= dt
             group by city, dt
          )
   , ytd as 
          ( select city      
                 , dt   
                 , sum(amount) amt 
              from tax
              cross join for_date             
             where trunc(tax_date,'yyyy') = (select trunc(dt,'yyyy') from for_date)
               and tax_date <= dt 
             group by city, dt
          )
select  
       dly.city       "City"
     , dly.dt         "Tax Date"
     , dly.amt        "Daily Amount"
     , mtd.amt        "Month to Date"
     , ytd.amt        "Year To Date"
  from dly
  join mtd on (dly.city = mtd.city)
  join ytd on (dly.city = ytd.city)
 order by dly.city;

Difference: The result adds the current date as a column instead trying to do it as a column name. For SQL this is major hoop jumping (if it's even doable). But this transformation should very simple for your presentation layer.

BTW: check your math it's off. Example: Month to Date for Wellington is 1800 (500+1000+300) not 2800 and indicated in excepted results.

Upvotes: 1

Related Questions