Home » SQL & PL/SQL » SQL & PL/SQL » Sql for previous day/ day before previous day (Oracle, 8.1.7.0.0, Windows Server 2007.)
Sql for previous day/ day before previous day [message #574793] Tue, 15 January 2013 21:10 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

The main condition in SQL is like this.
SELECT   TO_DATE (TO_CHAR (doc_date, 'MON-YY'), 'MON-YY') "INV_MTH",
         SUM (inv_amt) INV_TOTAL
  FROM   table_x
 WHERE   doc_date BETWEEN TRUNC (SYSDATE, 'YYYY')
                      AND  LAST_DAY (
                              ADD_MONTHS (TRUNC (SYSDATE, 'YYYY'), 11)
                           );

My Output from if run in JAN as of now 16 Jan.

INV_MTH INV_TOTAL
Jan-13 6260830.42

I want an sql until previous day of that month for example 15 Jan and
another sql until day before previous day of that month for example 14 Jan.

Any help on this from you experts?
Re: Sql for previous day/ day before previous day [message #574794 is a reply to message #574793] Tue, 15 January 2013 21:21 Go to previous messageGo to next message
BlackSwan
Messages: 23150
Registered: January 2009
Senior Member
>(Oracle, 8.1.7.0.0, Windows Server 2007.)
why are you willing to upgrade OS version, but not Oracle version.
V8.1.7 was obsoleted & unsupported before Windows 2007 was ever released.

since we don't have your tables or data we can't run, test, or improve your SQL.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Sql for previous day/ day before previous day [message #574796 is a reply to message #574794] Tue, 15 January 2013 21:42 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Table Script and records
CREATE TABLE CSS_INV
(
  INV_MTH    DATE,
  INV_TOTAL  NUMBER
)
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;


SET DEFINE OFF;
Insert into CSS_INV
   (INV_MTH, INV_TOTAL)
 Values
   (TO_DATE('01/02/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 841544.85);
Insert into CSS_INV
   (INV_MTH, INV_TOTAL)
 Values
   (TO_DATE('01/03/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 876273.75);
Insert into CSS_INV
   (INV_MTH, INV_TOTAL)
 Values
   (TO_DATE('01/04/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 325170);
Insert into CSS_INV
   (INV_MTH, INV_TOTAL)
 Values
   (TO_DATE('01/07/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1584394);
Insert into CSS_INV
   (INV_MTH, INV_TOTAL)
 Values
   (TO_DATE('01/08/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 57000);
Insert into CSS_INV
   (INV_MTH, INV_TOTAL)
 Values
   (TO_DATE('01/09/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 514650);
Insert into CSS_INV
   (INV_MTH, INV_TOTAL)
 Values
   (TO_DATE('01/10/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 52713);
Insert into CSS_INV
   (INV_MTH, INV_TOTAL)
 Values
   (TO_DATE('01/11/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 515810.5);
Insert into CSS_INV
   (INV_MTH, INV_TOTAL)
 Values
   (TO_DATE('01/14/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1184664.32);
Insert into CSS_INV
   (INV_MTH, INV_TOTAL)
 Values
   (TO_DATE('01/15/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 155805);
Insert into CSS_INV
   (INV_MTH, INV_TOTAL)
 Values
   (TO_DATE('01/16/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 152805);
COMMIT;
Re: Sql for previous day/ day before previous day [message #574807 is a reply to message #574796] Wed, 16 January 2013 00:15 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
what is the input parameters of the query and expected output?

Also show us what you tried to achieve that output.

regards,
Delna
Re: Sql for previous day/ day before previous day [message #574809 is a reply to message #574793] Wed, 16 January 2013 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want an sql until previous day of that month for example 15 Jan and
another sql until day before previous day of that month for example 14 Jan.


And starting from which date?
Or better from which date and to which date?
Your sentence is not clear.

What should be the result for the data you gave?

Regards
Michel


Re: Sql for previous day/ day before previous day [message #574811 is a reply to message #574807] Wed, 16 January 2013 00:25 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
The Input is it will take from the table the total sales till date sum it up and out put will be

Jan-13 4749589

like wise if I run in Feb it will be

Jan-13 696997797 (total sales for the month)
Feb-13 4634737 (until the date I run)

Now I want the output to be until yesterday should not take the day I run the report. Same wise I also want until day before yesterday.

This is for comparison of sales between day before yesterday and yesterday.

Whether increase in sales or decrease in sales?
Re: Sql for previous day/ day before previous day [message #574815 is a reply to message #574811] Wed, 16 January 2013 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sill not understand the requirement.
If you enter 16-JAN-2013, which sums do you want? From which date(s) to which date(s)?
And what should then be the results for the data you gave?
The question are clear, I hope a clear answer.
It should be only 2 lines:
- result 1: from date A to date B -> value N
- result 2: from date C to date D -> value M
replace A, B, C, D, N and M by the wanted values. It is simple.

Regards
Michel
Re: Sql for previous day/ day before previous day [message #574817 is a reply to message #574809] Wed, 16 January 2013 00:35 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Currently my sql is if I run today it takes from Jan 1 to Jan 16.

My first request is if I run it should take from Jan 1 to Jan 15.

Secondly may be another sql if I run it should take from Jan 1 to Jan 14.

Re: Sql for previous day/ day before previous day [message #574819 is a reply to message #574817] Wed, 16 January 2013 00:40 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Did you try TRUNC with SYSDATE-1 and SYSDATE-2?

regards,
Delna
Re: Sql for previous day/ day before previous day [message #574821 is a reply to message #574817] Wed, 16 January 2013 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you really unable to CORRECTLY answer my questions and replace A, B, C...?
If so, I advice you to check a better job for you.

SQL> def input='01/16/2013'
SQL> select sum(inv_total) from css_inv 
  2  where inv_mth between trunc(to_date('&input','MM/DD/YYYY'),'YEAR') 
  3                    and to_date('&input','MM/DD/YYYY')-1
  4  /
SUM(INV_TOTAL)
--------------
    6108025.42

1 row selected.

SQL> select sum(inv_total) from css_inv 
  2  where inv_mth between trunc(to_date('&input','MM/DD/YYYY'),'YEAR') 
  3                    and to_date('&input','MM/DD/YYYY')-2
  4  /
SUM(INV_TOTAL)
--------------
    5952220.42

1 row selected.

Regards
Michel
Re: Sql for previous day/ day before previous day [message #574822 is a reply to message #574819] Wed, 16 January 2013 00:49 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
With my current condition I tried like this

doc_date BETWEEN trunc(sysdate, 'YYYY')
AND last_day(add_months(trunc(sysdate-1,'YYYY'),11))

no it is not coming.

Re: Sql for previous day/ day before previous day [message #574825 is a reply to message #574822] Wed, 16 January 2013 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you understand your query?
What does it mean
Explain!

Regards
Michel

[Updated on: Wed, 16 January 2013 00:52]

Report message to a moderator

Re: Sql for previous day/ day before previous day [message #574830 is a reply to message #574821] Wed, 16 January 2013 01:03 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Mr.Michel,

I will be glad if you give a job. I think in my way of English I explained maybe you can be expert in language.

I told you from my current script which I have if I run now it takes today's sales also which I don't want I need only upto yesterday.

And my request for another script is that if I run now it should take sales upto day before yesterday.

May I can input the date as 15 or 14 but is there any way without inputting the date to get my request.
Re: Sql for previous day/ day before previous day [message #574833 is a reply to message #574821] Wed, 16 January 2013 01:08 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Mr.Michel,

With your script if I run next month in Feb I won't get the total sales Jan?
Re: Sql for previous day/ day before previous day [message #574834 is a reply to message #574830] Wed, 16 January 2013 01:10 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
select trunc(sysdate-1) yesterday,
  2  trunc(sysdate-2) day_before_yesterday
  3  from dual;

YESTERDAY DAY_BEFOR
--------- ---------
15-JAN-13 14-JAN-13


regards,
Delna
Re: Sql for previous day/ day before previous day [message #574842 is a reply to message #574833] Wed, 16 January 2013 01:27 Go to previous message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
kumarvk wrote on Wed, 16 January 2013 08:08
Mr.Michel,

With your script if I run next month in Feb I won't get the total sales Jan?


Your requirement was not clear.
And you test case has NO value for February so it is hard to test any query.

Just a small change:
SQL> def input='01/16/2013'
SQL> select to_char(inv_mth, 'YYYY/MM') "MONTH",  sum(inv_total) "TOTAL"
  2  from css_inv 
  3  where inv_mth between trunc(to_date('&input','MM/DD/YYYY'),'YEAR') 
  4                    and to_date('&input','MM/DD/YYYY')-1
  5  group by to_char(inv_mth, 'YYYY/MM') 
  6  order by 1
  7  /
MONTH        TOTAL
------- ----------
2013/01 6108025.42

SQL> select to_char(inv_mth, 'YYYY/MM') "MONTH",  sum(inv_total) "TOTAL"
  2  from css_inv 
  3  where inv_mth between trunc(to_date('&input','MM/DD/YYYY'),'YEAR') 
  4                    and to_date('&input','MM/DD/YYYY')-2
  5  group by to_char(inv_mth, 'YYYY/MM') 
  6  order by 1
  7  /
MONTH        TOTAL
------- ----------
2013/01 5952220.42

Regards
Michel



Previous Topic: Problem with performance
Next Topic: Query to find relevant Table name
Goto Forum:
  


Current Time: Fri Dec 19 20:30:45 CST 2014

Total time taken to generate the page: 0.09191 seconds