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  |
 |
ninan
Messages: 108
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 #576402 is a reply to message #576399] |
Mon, 04 February 2013 10:36  |
Solomon Yakobson
Messages: 1398
Registered: January 2010
|
Senior Member |
|
|
ninan wrote on Mon, 04 February 2013 11:02Is 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 |
Solomon Yakobson
Messages: 1398
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 #576415 is a reply to message #576412] |
Mon, 04 February 2013 12:49 |
 |
Michel Cadot
Messages: 54155
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 #576490 is a reply to message #576484] |
Tue, 05 February 2013 09:43 |
|
Michel Cadot
Messages: 54155
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
|
|
|
|
Goto Forum:
Current Time: Tue May 21 05:56:21 CDT 2013
Total time taken to generate the page: 0.12465 seconds
|
] 
Members
Search
Help
Register
Login
Home
