OraFAQ Forum: SQL & PL/SQL » Day book format

Home » SQL & PL/SQL » SQL & PL/SQL » Day book format (oracle 10g, windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Day book format [message #683006]

Tue, 24 November 2020 08:19

hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN

Senior Member

Dear Experts
Following is the table creation and insert statement, please help me to generate output format as attached in .png format, for your
kind reference.

Thankful,

CREATE TABLE TEMP
(
FLAG NUMBER,
JV_DATE DATE,
DESCRIPTION VARCHAR2(1052 BYTE),
ADV_AMOUNT NUMBER
)

INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
1, TO_Date( '10/25/2020 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ORDER # , ,', 100000);
INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
1, TO_Date( '11/23/2020 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ORDER # 2 ,AAMIR SB ,'
, 4600);
INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
1, TO_Date( '11/23/2020 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ORDER # 3 ,ABBAS ENGINEERING ,'
, 1000000);
INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
1, TO_Date( '11/23/2020 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ORDER # 4 ,ABDUAL GHAFAR (LHR) ,'
, 52352);
INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
1, TO_Date( '11/23/2020 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ORDER # 5 ,ABID ALI ,ORDER NO.'
, 1939);
INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
2, TO_Date( '10/25/2020 05:24:22 PM', 'MM/DD/YYYY HH:MI:SS AM'), 'NEWS PAPER,TRANSPORT ,ASDFASD'
, -1000);
INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
2, TO_Date( '11/23/2020 08:53:36 PM', 'MM/DD/YYYY HH:MI:SS AM'), 'DUSTI,BHARAI ,ABID'
, -400);
INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
2, TO_Date( '11/23/2020 08:54:37 PM', 'MM/DD/YYYY HH:MI:SS AM'), 'DUSTI,DHUHAI ,ABID'
, -600);
COMMIT;

Attachment: OUTPUT.png
(Size: 34.69KB, Downloaded 415 times)

Report message to a moderator

Re: Day book format [message #683008 is a reply to message #683006]

Tue, 24 November 2020 09:04

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

with t as (
           select  row_number() over(partition by trunc(jv_date),flag order by jv_date,description) rn,
                   t.*
             from  temp t
          )
select  t1.flag flag1,
        t1.jv_date jv_date1,
        t1.description description1,
        t1.adv_amount adv_amount1,
        t2.flag flag2,
        t2.jv_date jv_date2,
        t2.description description2,
        t2.adv_amount adv_amount2
  from  t t1,
        t t2
  where t1.flag = 1
    and t2.flag(+) = 2
    and t2.rn(+) = t1.rn
    and trunc(t2.jv_date(+)) = trunc(t1.jv_date)
  order by t1.rn,
           trunc(t1.jv_date)
/

FLAG1 JV_DATE1            DESCRIPTION1                     ADV_AMOUNT1 FLAG2 JV_DATE2            DESCRIPTION2                  ADV_AMOUNT2
----- ------------------- -------------------------------- ----------- ----- ------------------- ----------------------------- -----------
    1 10/25/2020 00:00:00 ORDER # , ,                           100000     2 10/25/2020 17:24:22 NEWS PAPER,TRANSPORT ,ASDFASD       -1000
    1 11/23/2020 00:00:00 ORDER # 2 ,AAMIR SB ,                   4600     2 11/23/2020 20:53:36 DUSTI,BHARAI ,ABID                   -400
    1 11/23/2020 00:00:00 ORDER # 3 ,ABBAS ENGINEERING ,       1000000     2 11/23/2020 20:54:37 DUSTI,DHUHAI ,ABID                   -600
    1 11/23/2020 00:00:00 ORDER # 4 ,ABDUAL GHAFAR (LHR) ,       52352
    1 11/23/2020 00:00:00 ORDER # 5 ,ABID ALI ,ORDER NO.          1939

SQL>

SY.

Report message to a moderator

Re: Day book format [message #683009 is a reply to message #683008]

Tue, 24 November 2020 09:12

hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN

Senior Member

Respectful Solomon,
Having no words to say thanks, what a great help, bundle of thanks for kind help.

really wonderful

Thankful,

best regards,

Report message to a moderator

Re: Day book format [message #683010 is a reply to message #683008]

Tue, 24 November 2020 09:36

hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN

Senior Member

Dear Respectful,
one more help needed,
Temp table having data coming from two tables
one is from flag = 1 portion
and the other is from flag = 2 portion
if we do not have any data in table of flag = 1 portion
and in flag = 2 portion having data of date 24/11/2020,
it is not appearing in the report, i think there is null values issue in table where flag = 1.

please guide us how to handle such problem?

Thankful,

Report message to a moderator

Re: Day book format [message #683011 is a reply to message #683006]

Tue, 24 November 2020 10:06

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 08 October 2020 10:49

You also forgot to format your post and to put INSIDE the post the output you want with the rules to apply to get it.
AS ALWAYS you refuse to follow the rules.

Quote:
Is it possible to generate output or i need to send again..
The later is what to do.
I repeat once more:

Michel Cadot wrote on Tue, 08 October 2019 13:08

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Report message to a moderator

Re: Day book format [message #683012 is a reply to message #683008]

Tue, 24 November 2020 10:09

hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN

Senior Member

If we insert the following rows then the given solution not working,

INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
1, TO_Date( '11/24/2020 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ORDER # 7 ,AAMIR SB ,TEST'
, 2408);
INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
1, TO_Date( '11/25/2020 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ORDER # 8 ,ABBAS ENGINEERING ,TEST ADVANCE'
, 26216);
INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
2, TO_Date( '11/24/2020 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'ELECTRICITY BILL,NIKASI ,SAEED'
, -5600);
INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
2, TO_Date( '11/24/2020 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'MAINTENANCE,PURCHASE ,SALEEM'
, -6700);
INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
2, TO_Date( '11/24/2020 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'DEISEL,BHARAI ,ABID'
, -5000);
INSERT INTO TEMP ( FLAG, JV_DATE, DESCRIPTION, ADV_AMOUNT ) VALUES (
2, TO_Date( '11/24/2020 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'DUSTI,DHUHAI ,ASDFASD'
, -4000);
COMMIT;

Thankful,

Report message to a moderator

Re: Day book format [message #683013 is a reply to message #683011]

Tue, 24 November 2020 10:16

hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN

Senior Member

Dear Michel Cadot
my apologies, i have tried a lot to work out as per your given instructions but "Instant Sql formatter" opening the following site,
http://www.dpriver.com/pp/sqlformat.htm

but "This site can't be reached" message is showing

Thanks,

Report message to a moderator

Re: Day book format [message #683014 is a reply to message #683010]

Tue, 24 November 2020 10:21

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

Use full outer join:

SQL> update temp set jv_date = sysdate where adv_amount = -600;

1 row updated.

SQL> select * from temp order by trunc(jv_date),flag;

      FLAG JV_DATE             DESCRIPTION                         ADV_AMOUNT
---------- ------------------- ----------------------------------- ----------
         1 10/25/2020 00:00:00 ORDER # , ,                             100000
         2 10/25/2020 17:24:22 NEWS PAPER,TRANSPORT ,ASDFASD            -1000
         1 11/23/2020 00:00:00 ORDER # 5 ,ABID ALI ,ORDER NO.            1939
         1 11/23/2020 00:00:00 ORDER # 3 ,ABBAS ENGINEERING ,         1000000
         1 11/23/2020 00:00:00 ORDER # 2 ,AAMIR SB ,                     4600
         1 11/23/2020 00:00:00 ORDER # 4 ,ABDUAL GHAFAR (LHR) ,         52352
         2 11/23/2020 20:53:36 DUSTI,BHARAI ,ABID                        -400
         2 11/24/2020 10:58:05 DUSTI,DHUHAI ,ABID                        -600

8 rows selected.

SQL>

Now:

with t as (
           select  row_number() over(partition by trunc(jv_date),flag order by jv_date,description) rn,
                   t.*
             from  temp t
          ),
    t1 as (
           select  *
             from  t
             where flag = 1
          ),
    t2 as (
           select  *
             from  t
             where flag = 2
          )
select  t1.flag flag1,
        t1.jv_date jv_date1,
        t1.description description1,
        t1.adv_amount adv_amount1,
        t2.flag flag2,
        t2.jv_date jv_date2,
        t2.description description2,
        t2.adv_amount adv_amount2
  from      t1
        full join
            t2
          on     t2.rn = t1.rn
             and
                 trunc(t2.jv_date) = trunc(t1.jv_date)
  order by t1.rn,
           trunc(t1.jv_date)
/

FLAG1 JV_DATE1  DESCRIPTION1                        ADV_AMOUNT1 FLAG2 JV_DATE2  DESCRIPTION2                        ADV_AMOUNT2
----- --------- ----------------------------------- ----------- ----- --------- ----------------------------------- -----------
    1 25-OCT-20 ORDER # , ,                              100000     2 25-OCT-20 NEWS PAPER,TRANSPORT ,ASDFASD             -1000
    1 23-NOV-20 ORDER # 2 ,AAMIR SB ,                      4600     2 23-NOV-20 DUSTI,BHARAI ,ABID                         -400
    1 23-NOV-20 ORDER # 3 ,ABBAS ENGINEERING ,          1000000
    1 23-NOV-20 ORDER # 4 ,ABDUAL GHAFAR (LHR) ,          52352
    1 23-NOV-20 ORDER # 5 ,ABID ALI ,ORDER NO.             1939
                                                                    2 24-NOV-20 DUSTI,DHUHAI ,ABID                         -600

6 rows selected.

SQL>

SY.

Report message to a moderator

Re: Day book format [message #683015 is a reply to message #683014]

Tue, 24 November 2020 10:39

hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN

Senior Member

Very Well done , Great Solomon Yakobson, This is what we were looking for.

Bundle of thanks,

Report message to a moderator

Re: Day book format [message #683016 is a reply to message #683013]

Tue, 24 November 2020 10:43

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

hissam78 wrote on Tue, 24 November 2020 17:16

Dear Michel Cadot
my apologies, i have tried a lot to work out as per your given instructions but "Instant Sql formatter" opening the following site,
http://www.dpriver.com/pp/sqlformat.htm

but "This site can't be reached" message is showing

Thanks,

This site is CURRENTLY unreachable and this does not prevent you from using code tags.
Do you think Solomon used the site?

Report message to a moderator

Previous Topic:	Need SQL Logic For Comparing Data Between Two Tabless
Next Topic:	One row for multiple rows based on a condition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Mar 25 08:54:40 CDT 2023