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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #651031 is a reply to message #651027] Tue, 10 May 2016 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think you should write your SQL on one line, it'd more readable then.

Re: help in query [message #651032 is a reply to message #651031] Tue, 10 May 2016 06:06 Go to previous messageGo to next message
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 #651037 is a reply to message #651032] Tue, 10 May 2016 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.dpriver.com/pp/sqlformat.htm

Re: help in query [message #651062 is a reply to message #651032] Tue, 10 May 2016 11:55 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: sql
Next Topic: Query Analytics
Goto Forum:
  


Current Time: Fri Mar 29 10:36:03 CDT 2024