Home » SQL & PL/SQL » SQL & PL/SQL » Report with Date Range (Oracle 11g, SQL, Windows XP)
Report with Date Range [message #576399] Mon, 04 February 2013 10:02 Go to next message
ninan
Messages: 116
Registered: June 2011
Location: bangalore
Senior Member
I have a cost data for effective date range as below.


MODEL_NO	TIER_COST	EFFECTIVE_START_DATE	EFFECTIVE_END_DATE

I3 3770	           265	            2/3/2013	          3/31/2013
I3 3770	           269	            4/1/2013	          5/4/2013



This data needs to be represented in a SQL report as below. The Date Range in the below i.e., Starts with FEB, by checking against the sysdate.. That is from sysdate it will display the Quarter data for 4 months as below.



MODEL NO	FEB FY13	MAR FY13	APR FY13	MAY FY13
I3 3770	          265	           265	           269	           269



Currently I am using a procedural logic to populate data into a different table in the above format.
Is there any method to do with a single SQL using PIVOT.

Below given is the table structure and Insert scripts.



CREATE TABLE ITEM_TAG(MODEL_NO VARCHAR2(50), TIER_COST NUMBER(13,4), EFFECTIVE_START_DATE DATE, EFFECTIVE_END_DATE DATE );




Insert statements


Insert into ADMIN_COSTPL_OWNER.ECAPS_ENTITLEMENT
   (MODEL_NO, TIER_COST, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
 Values
   ('I3 3770', 265, TO_DATE('02/03/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/31/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ADMIN_COSTPL_OWNER.ECAPS_ENTITLEMENT
   (MODEL_NO, TIER_COST, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
 Values
   ('I3 3770', 269, TO_DATE('04/01/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/04/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;



Kindly advise.

Thanks

Ninan.
Re: Report with Date Range [message #576400 is a reply to message #576399] Mon, 04 February 2013 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post insert statements for the table you give the DDL and post them WITHOUT any schema name.

Quote:
The Date Range in the below i.e., Starts with FEB, by checking against the sysdate.. That is from sysdate it will display the Quarter data for 4 months as below.


Explain this sentence. For instance, how start and end dates come into play? Can there be overlap?

Quote:
Currently I am using a procedural logic to populate data into a different table in the above format.
Is there any method to do with a single SQL using PIVOT.


Most likely, post the code maybe you will then understand what you want.

Regards
Michel
Re: Report with Date Range [message #576402 is a reply to message #576399] Mon, 04 February 2013 10:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1992
Registered: January 2010
Senior Member
ninan wrote on Mon, 04 February 2013 11:02
Is there any method to do with a single SQL using PIVOT.


Only if you know all effective dates upfront. Something like:

select  model_no,
        dt1_tier_cost "FEB FY13",
        dt1_tier_cost "MAR FY13",
        dt2_tier_cost "APR FY13",
        dt2_tier_cost "MAY FY13"
  from  item_tag
  pivot(
        max(tier_cost) tier_cost
        for (effective_start_date,effective_end_date) in (
                                                          (date '2013-02-03',date '2013-03-31') dt1,
                                                          (date '2013-04-01',date '2013-05-04') dt2
                                                         )
       )
/

MODEL_NO     FEB FY13   MAR FY13   APR FY13   MAY FY13
---------- ---------- ---------- ---------- ----------
I3 3770           265        265        269        269

SQL> 


SY.
Re: Report with Date Range [message #576404 is a reply to message #576402] Mon, 04 February 2013 10:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1992
Registered: January 2010
Senior Member
Actually, this can be simpler:

select  model_no,
        dt1_tier_cost "FEB FY13",
        dt1_tier_cost "MAR FY13",
        dt2_tier_cost "APR FY13",
        dt2_tier_cost "MAY FY13"
  from  item_tag
  pivot(
        max(tier_cost) tier_cost,
        max(effective_end_date) x
        for effective_start_date in (
                                     date '2013-02-03' dt1,
                                     date '2013-04-01' dt2
                                    )
       )
/

MODEL_NO     FEB FY13   MAR FY13   APR FY13   MAY FY13
---------- ---------- ---------- ---------- ----------
I3 3770           265        265        269        269

SQL> 


SY.
Re: Report with Date Range [message #576406 is a reply to message #576400] Mon, 04 February 2013 11:11 Go to previous messageGo to next message
ninan
Messages: 116
Registered: June 2011
Location: bangalore
Senior Member
Sorry for posting wrong insert statements with schema name. There will be no overlap dates.
Re: Report with Date Range [message #576407 is a reply to message #576404] Mon, 04 February 2013 11:12 Go to previous messageGo to next message
ninan
Messages: 116
Registered: June 2011
Location: bangalore
Senior Member
We may not know the dates before hand. Only check is that it should run based on the sysdate. i.e., if the query is running today it should take the first month as of Sysdate and then 3 subsequent months in the Quarter.
Re: Report with Date Range [message #576411 is a reply to message #576407] Mon, 04 February 2013 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 58840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL cannot give dynamic heading. you can simulate this using a pipelined function or a function returning a ref cursor.
What is the tool to generate the report? SQL*Plus?

Regards
Michel
Re: Report with Date Range [message #576412 is a reply to message #576407] Mon, 04 February 2013 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
and then 3 subsequent months in the Quarter


A quarter has only 3 months so you cannot display 4 months or do you mean 3 subsequent months in the year or simply 3 subsequent months?

Note that a standard pivot query can do this if you omit the column headings.

Regards
Michel
Re: Report with Date Range [message #576415 is a reply to message #576412] Mon, 04 February 2013 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance:
SQL> select model_no,
  2         sum(case when trunc(sysdate,'MONTH') 
  3                         between trunc(effective_start_date,'MONTH') 
  4                             and last_day(effective_end_date)+1-1/86400
  5                  then tier_cost
  6             end) "Month 0",
  7         sum(case when add_months(trunc(sysdate,'MONTH'),1) 
  8                         between trunc(effective_start_date,'MONTH') 
  9                             and last_day(effective_end_date)+1-1/86400
 10                  then tier_cost
 11             end) "Month 1",
 12         sum(case when add_months(trunc(sysdate,'MONTH'),2) 
 13                         between trunc(effective_start_date,'MONTH') 
 14                             and last_day(effective_end_date)+1-1/86400
 15                  then tier_cost
 16             end) "Month 2",
 17         sum(case when add_months(trunc(sysdate,'MONTH'),3)
 18                         between trunc(effective_start_date,'MONTH') 
 19                             and last_day(effective_end_date)+1-1/86400
 20                  then tier_cost
 21             end) "Month 3"
 22  from item_tag
 23  group by model_no
 24  /
MODEL_NO      Month 0    Month 1    Month 2    Month 3
---------- ---------- ---------- ---------- ----------
I3 3770           265        265        269        269

regards
Michel
Re: Report with Date Range [message #576484 is a reply to message #576412] Tue, 05 February 2013 09:16 Go to previous messageGo to next message
ninan
Messages: 116
Registered: June 2011
Location: bangalore
Senior Member
I mean the 3 subsequent months after the current month ( Month of Sysdate ). I think this query may help. Let me check and get back to you .
Re: Report with Date Range [message #576490 is a reply to message #576484] Tue, 05 February 2013 09:43 Go to previous message
Michel Cadot
Messages: 58840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you use a SQL*Plus script, you can do it in the following way:
SQL> host type q.sql
set termout off
col f0 new_value f0
col f1 new_value f1
col f2 new_value f2
col f3 new_value f3
select to_char(sysdate, 'MON "FY"YY') f0,
       to_char(add_months(sysdate,1), 'MON "FY"YY') f1,
       to_char(add_months(sysdate,2), 'MON "FY"YY') f2,
       to_char(add_months(sysdate,3), 'MON "FY"YY') f3
from dual
/
col model_no format a10
col m0 heading "&f0"
col m1 heading "&f1"
col m2 heading "&f2"
col m3 heading "&f3"
set termout on
select model_no,
       sum(case when trunc(sysdate,'MONTH')
                       between trunc(effective_start_date,'MONTH')
                           and last_day(effective_end_date)+1-1/86400
                then tier_cost
           end) m0,
       sum(case when add_months(trunc(sysdate,'MONTH'),1)
                       between trunc(effective_start_date,'MONTH')
                           and last_day(effective_end_date)+1-1/86400
                then tier_cost
           end) m1,
       sum(case when add_months(trunc(sysdate,'MONTH'),2)
                       between trunc(effective_start_date,'MONTH')
                           and last_day(effective_end_date)+1-1/86400
                then tier_cost
           end) m2,
       sum(case when add_months(trunc(sysdate,'MONTH'),3)
                       between trunc(effective_start_date,'MONTH')
                           and last_day(effective_end_date)+1-1/86400
                then tier_cost
           end) m3
from item_tag
group by model_no
/

SQL> @.\q
MODEL_NO     FEB FY13   MAR FY13   APR FY13   MAY FY13
---------- ---------- ---------- ---------- ----------
I3 3770           265        265        269        269

Regards
Michel
Previous Topic: Assigning an ID
Next Topic: Customized Query Builder Architecture
Goto Forum:
  


Current Time: Wed Aug 20 06:36:28 CDT 2014

Total time taken to generate the page: 0.06070 seconds