Home » SQL & PL/SQL » SQL & PL/SQL » help in query (10g, win8.1)
() 1 Vote
help in query [message #651000] |
Mon, 09 May 2016 08:16 |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
Here are the following TWO views.
select to_char(productiondate,'FMMon YYYY') as the_month,sum(sqft) as sqft_production
from production_view_sqft
group by to_char(productiondate,'FMMon YYYY')
order by to_char(productiondate,'FMMon YYYY')
/
THE_MONT SQFT_PRODUCTION
-------- ---------------
Apr 2016 172400
Mar 2016 14880
May 2016 29100
select to_char(orderdate,'FMMon YYYY') as the_month,sum(sqft) as sqft_sale
from orders_view
group by to_char(orderdate,'FMMon YYYY')
order by to_char(productiondate,'FMMon YYYY')
/
THE_MONT SQFT_SALE
-------- ----------
Apr 2016 42402
Mar 2016 2040
May 2016 25800
I am creating tables of the same structure to simplify implementation for test case.
create table monthly_production(
the_month varchar2(8),
sqft_production number(10)
)
/
insert into monthly_production values ('Apr 2016',172400);
insert into monthly_production values ('Mar 2016',14840);
insert into monthly_production values ('May 2016',29100);
create table monthly_sale(
the_month varchar2(8),
sqft_sale number(10)
)
/
insert into monthly_sale values ('Apr 2016',42402);
insert into monthly_sale values ('Mar 2016',2040);
insert into monthly_sale values ('May 2016',25800);
and here is the query I am requesting to be helped in.
select p.the_month,p.sqft_production,s.sqft_sale,nvl(p.sqft_production,0)-nvl(s.sqft_sale,0) as bal
from monthly_production p,
monthly_sale s
where p.the_month like s.the_month
order by to_date(substr(p.the_month,1,3),'fMMM')
/
THE_MONT SQFT_PRODUCTION SQFT_SALE BAL
-------- --------------- ---------- ----------
Mar 2016 14840 2040 12800
Apr 2016 172400 42402 129998
May 2016 29100 25800 3300
How If I require a column after THE_MONTH col in the above query which shows the BAL from previous month?
like
THE_MONT OPENING SQFT_PRODUCTION SQFT_SALE BAL
-------- ------- -------- ---------- ----------
Mar 2016 1 14840 2040 12801
Apr 2016 12801 172400 42402 142799
May 2016 142799 29100 25800 146099
And also how can I bring the balance of previous month ie Feb 2016 instead of 1 in Mar 2016?
Would be thankful for your assistance.
|
|
|
Re: help in query [message #651015 is a reply to message #651000] |
Mon, 09 May 2016 17:30 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> -- sample data your provided with February production of 1 added:
SCOTT@orcl_12.1.0.2.0> select * from monthly_production
2 /
THE_MONT SQFT_PRODUCTION
-------- ---------------
Feb 2016 1
Apr 2016 172400
Mar 2016 14840
May 2016 29100
4 rows selected.
SCOTT@orcl_12.1.0.2.0> select * from monthly_sale
2 /
THE_MONT SQFT_SALE
-------- ----------
Apr 2016 42402
Mar 2016 2040
May 2016 25800
3 rows selected.
SCOTT@orcl_12.1.0.2.0> -- query to get the desired results:
SCOTT@orcl_12.1.0.2.0> select *
2 from (select the_month,
3 nvl (lag (bal) over (order by to_date (the_month, 'Mon yyyy')), 0) opening,
4 sqft_production, sqft_sale, bal
5 from (select p.the_month, nvl (p.sqft_production, 0) sqft_production, nvl (s.sqft_sale, 0) sqft_sale,
6 sum (nvl (p.sqft_production, 0) - nvl (s.sqft_sale, 0)) over
7 (order by to_date (p.the_month, 'Mon yyyy')) as bal
8 from monthly_production p, monthly_sale s
9 where p.the_month = s.the_month(+)))
10 where to_date (the_month, 'Mon yyyy') between to_date ('Mar 2016', 'Mon yyyy') and to_date ('May 2016', 'Mon yyyy')
11 order by to_date (the_month, 'Mon yyyy')
12 /
THE_MONT OPENING SQFT_PRODUCTION SQFT_SALE BAL
-------- ---------- --------------- ---------- ----------
Mar 2016 1 14840 2040 12801
Apr 2016 12801 172400 42402 142799
May 2016 142799 29100 25800 146099
3 rows selected.
[Updated on: Mon, 09 May 2016 17:36] Report message to a moderator
|
|
|
Re: help in query [message #651020 is a reply to message #651015] |
Tue, 10 May 2016 01:44 |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
Thanks a lot. That works fine. But
Quote: sample data your provided with February production of 1 added:
Now, the point where I need your kind guidance is how I bring the February Balance instead of 1.
I have two approaches in my mind.
1. Create a temporary table and insert the data there, Then add the February opening balance.
2. some how create another query and get February balance from it (But I cant figure it out)
I can work around approach No.1 butam stuck towards approach 2.
Can you guide me towards approach No.2 please?
Same data from the VIEWS is as under.
select v1.the_month,
lag(sqft_production-sqft_sale,1,0) over(order by to_date(substr(v1.the_month,1,3),'fMMM')) as o
sqft_production,sqft_sale,(sqft_production-sqft_sale) as bal
from v1,v2
where v1.the_month like v2.the_month(+)
order by to_date(substr(v1.the_month,1,3),'fMMM')
SAM@orcl:>/
THE_MONT OPENING SQFT_PRODUCTION SQFT_SALE BAL
-------- ---------- --------------- ---------- ----------
Mar 2016 0 14880 2040 12840
Apr 2016 12840 172400 42402 129998
May 2016 129998 29100 25800 3300
Regards
Regards
RzKhan
[Updated on: Tue, 10 May 2016 03:15] Report message to a moderator
|
|
|
Re: help in query [message #651025 is a reply to message #651020] |
Tue, 10 May 2016 04:49 |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
I have tried and near to solve the problem as below.
I should select all the rows in v1, v2 (views). Then I should restrict the number of rows selected.
ie if above is the complete result set. Then I will select first/last n rows of my choice.
[Updated on: Tue, 10 May 2016 04:50] Report message to a moderator
|
|
|
Re: help in query [message #651027 is a reply to message #651025] |
Tue, 10 May 2016 05:01 |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
1 select v1.the_month,
2 lag(nvl(sqft_production,0)-nvl(sqft_sale,0),1,0)
3 over(order by to_date(substr(v1.the_month,1,3),'fMMM')) as opening,
4 nvl(sqft_production,0) as production,nvl(sqft_sale,0) as sale,
5 (nvl((lag(sqft_production-sqft_sale,1,0) over(order by to_date(substr(v1.the_month,1,3),'fMMM')
6 from monthly_sqft_prod v1, monthly_sqft_sale v2
7 where v1.the_month like v2.the_month(+)
8* order by to_date(substr(v1.the_month,1,3),'fMMM')
SAM@orcl:>/
THE_MONT OPENING PRODUCTION SALE BAL
-------- ---------- ---------- ---------- ----------
Jan 2016 0 6120 2040 4080
Mar 2016 4080 14880 2040 16920
Apr 2016 12840 172400 42402 142838
May 2016 129998 29100 25800 133298
SAM@orcl:>
|
|
|
|
Re: help in query [message #651032 is a reply to message #651031] |
Tue, 10 May 2016 06:06 |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
Still there is small bug. If anyone can help me.
select v1.the_month,
lag(nvl(sqft_production,0)-nvl(sqft_sale,0),1,0)
over(order by substr(v1.the_month,-4,4), to_date(substr(v1.the_month,1,3),'fMMM')) as opening,
nvl(v1.sqft_production,0) as production,
nvl(v2.sqft_sale,0) as sale,
(lag(nvl(sqft_production,0)-nvl(sqft_sale,0),1,0)
over(order by substr(v1.the_month,-4,4), to_date(substr(v1.the_month,1,3),'fMMM')) +
nvl(v1.sqft_production,0) -
nvl(v2.sqft_sale,0)) as bal
from monthly_sqft_prod v1, monthly_sqft_sale v2
where v1.the_month like v2.the_month(+)
/
THE_MONT OPENING PRODUCTION SALE BAL
-------- ---------- ---------- ---------- ----------
Aug 2014 0 16631 0 16631
Sep 2014 16631 599414 228212 387833
Oct 2014 371202 493581 95638 769145
Nov 2014 397943 641245 291143 748045
Dec 2014 350102 653926 0 1004028
Jan 2015 653926 588466 0 1242392
Feb 2015 588466 595535 0 1184001
Mar 2015 595535 635810 0 1231345
Apr 2015 635810 567526 0 1203336
May 2015 567526 620458 0 1187984
Jun 2015 620458 603648.5 0 1224106.5
Jul 2015 603648.5 510146.5 5748 1108047
Aug 2015 504398.5 600099.5 0 1104498
Sep 2015 600099.5 465817.5 11322 1054595
Oct 2015 454495.5 644629 0 1099124.5
Nov 2015 644629 621752 432 1265949
Dec 2015 621320 639118 0 1260438
Mar 2016 639118 652746.5 0 1291864.5
Apr 2016 652746.5 627581.5 713843.5 566484.5
May 2016 -86262 168139 91072 -9195
20 rows selected.
I want
(OPENING+PRODUCTION)-SALE=BAL
First two records are correct, But the value 387833 is not selected as opening in Oct 2014.
Any help will be appreciated.
[Updated on: Tue, 10 May 2016 06:21] Report message to a moderator
|
|
|
|
Re: help in query [message #651062 is a reply to message #651032] |
Tue, 10 May 2016 11:55 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Did you actually run the whole query that I provided? It should have given you exactly what you asked for, without making any modifications to your data. I used 1 for the initial balance because that was what you used in your sample data. The other data in your table would determine what that actual starting balance would be. The query obtains that within an inner subquery, then uses an outer query to limit the rows. Note that the rows must be restricted from an outer query, not within the same level of query. Just copy and paste the full code below and use it, instead of just modifying your existing code with pieces of it.
select *
from (select the_month,
nvl (lag (bal) over (order by to_date (the_month, 'Mon yyyy')), 0) opening,
sqft_production, sqft_sale, bal
from (select p.the_month, nvl (p.sqft_production, 0) sqft_production, nvl (s.sqft_sale, 0) sqft_sale,
sum (nvl (p.sqft_production, 0) - nvl (s.sqft_sale, 0)) over
(order by to_date (p.the_month, 'Mon yyyy')) as bal
from monthly_production p, monthly_sale s
where p.the_month = s.the_month(+)))
where to_date (the_month, 'Mon yyyy') between to_date ('Mar 2016', 'Mon yyyy') and to_date ('May 2016', 'Mon yyyy')
order by to_date (the_month, 'Mon yyyy')
/
Here is a demonstration of the breakdown of inner query and outer query:
SCOTT@orcl_12.1.0.2.0> -- sample data your provided with February production of 1 added;
SCOTT@orcl_12.1.0.2.0> -- I added 1 because that was what you used in your example';
SCOTT@orcl_12.1.0.2.0> -- it could be any value depending on the data in your table:
SCOTT@orcl_12.1.0.2.0> select * from monthly_production
2 /
THE_MONT SQFT_PRODUCTION
-------- ---------------
Feb 2016 1
Apr 2016 172400
Mar 2016 14840
May 2016 29100
4 rows selected.
SCOTT@orcl_12.1.0.2.0> select * from monthly_sale
2 /
THE_MONT SQFT_SALE
-------- ----------
Apr 2016 42402
Mar 2016 2040
May 2016 25800
3 rows selected.
SCOTT@orcl_12.1.0.2.0> -- query of all the rows in the tables, that produces the ongoing balance:
SCOTT@orcl_12.1.0.2.0> select the_month,
2 nvl (lag (bal) over (order by to_date (the_month, 'Mon yyyy')), 0) opening,
3 sqft_production, sqft_sale, bal
4 from (select p.the_month, nvl (p.sqft_production, 0) sqft_production, nvl (s.sqft_sale, 0) sqft_sale,
5 sum (nvl (p.sqft_production, 0) - nvl (s.sqft_sale, 0)) over
6 (order by to_date (p.the_month, 'Mon yyyy')) as bal
7 from monthly_production p, monthly_sale s
8 where p.the_month = s.the_month(+))
9 /
THE_MONT OPENING SQFT_PRODUCTION SQFT_SALE BAL
-------- ---------- --------------- ---------- ----------
Feb 2016 0 1 0 1
Mar 2016 1 14840 2040 12801
Apr 2016 12801 172400 42402 142799
May 2016 142799 29100 25800 146099
4 rows selected.
SCOTT@orcl_12.1.0.2.0> -- query to get the desired results, restricting the rows that you want:
SCOTT@orcl_12.1.0.2.0> select *
2 from (select the_month,
3 nvl (lag (bal) over (order by to_date (the_month, 'Mon yyyy')), 0) opening,
4 sqft_production, sqft_sale, bal
5 from (select p.the_month, nvl (p.sqft_production, 0) sqft_production, nvl (s.sqft_sale, 0) sqft_sale,
6 sum (nvl (p.sqft_production, 0) - nvl (s.sqft_sale, 0)) over
7 (order by to_date (p.the_month, 'Mon yyyy')) as bal
8 from monthly_production p, monthly_sale s
9 where p.the_month = s.the_month(+)))
10 where to_date (the_month, 'Mon yyyy') between to_date ('Mar 2016', 'Mon yyyy') and to_date ('May 2016', 'Mon yyyy')
11 order by to_date (the_month, 'Mon yyyy')
12 /
THE_MONT OPENING SQFT_PRODUCTION SQFT_SALE BAL
-------- ---------- --------------- ---------- ----------
Mar 2016 1 14840 2040 12801
Apr 2016 12801 172400 42402 142799
May 2016 142799 29100 25800 146099
3 rows selected.
|
|
|
Re: help in query [message #651085 is a reply to message #651062] |
Tue, 10 May 2016 23:00 |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
Yes. Thanks I tried it yesterday. But could not post the result ...
1 select *
2 from (select the_month,
3 nvl (lag (bal) over (order by to_date (the_month, 'Mon yyyy')), 0) opening,
4 sqft_production, sqft_sale, bal
5 from (select p.the_month, nvl (p.sqft_production, 0) sqft_production,
6 nvl (s.sqft_sale, 0) sqft_sale,
7 sum (nvl (p.sqft_production, 0) - nvl (s.sqft_sale, 0)) over
8 (order by to_date (p.the_month, 'Mon yyyy')) as bal
9 from monthly_sqft_prod p, monthly_sqft_sale s
10 where p.the_month = s.the_month(+)))
11* order by to_date (the_month, 'Mon yyyy')
SAM@orcl:>/
THE_MONT OPENING SQFT_PRODUCTION SQFT_SALE BAL
-------- ---------- --------------- ---------- ----------
Aug 2014 0 16631 0 16631
Sep 2014 16631 599414 228212 387833
Oct 2014 387833 493581 95638 785776
Nov 2014 785776 641245 291143 1135878
Dec 2014 1135878 653926 0 1789804
Jan 2015 1789804 588466 0 2378270
Feb 2015 2378270 595535 0 2973805
Mar 2015 2973805 635810 0 3609615
Apr 2015 3609615 567526 0 4177141
May 2015 4177141 620458 0 4797599
Jun 2015 4797599 603648.5 0 5401247.5
Jul 2015 5401247.5 510146.5 5748 5905646
Aug 2015 5905646 600099.5 0 6505745.5
Sep 2015 6505745.5 465817.5 11322 6960241
Oct 2015 6960241 644629 0 7604870
Nov 2015 7604870 621752 432 8226190
Dec 2015 8226190 639118 0 8865308
Mar 2016 8865308 652746.5 0 9518054.5
Apr 2016 9518054.5 627581.5 713843.5 9431792.5
May 2016 9431792.5 168139 91072 9508859.5
20 rows selected.
SAM@orcl:>
hats off
|
|
|
Goto Forum:
Current Time: Fri Mar 29 10:36:03 CDT 2024
|