Home » SQL & PL/SQL » SQL & PL/SQL » Day book format (oracle 10g, windows 7)
Day book format [message #683006] Tue, 24 November 2020 08:19 Go to next message
hissam78
Messages: 141
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 67 times)
Re: Day book format [message #683008 is a reply to message #683006] Tue, 24 November 2020 09:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3020
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.
Re: Day book format [message #683009 is a reply to message #683008] Tue, 24 November 2020 09:12 Go to previous messageGo to next message
hissam78
Messages: 141
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,
Re: Day book format [message #683010 is a reply to message #683008] Tue, 24 November 2020 09:36 Go to previous messageGo to next message
hissam78
Messages: 141
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,
Re: Day book format [message #683011 is a reply to message #683006] Tue, 24 November 2020 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 67648
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.

Re: Day book format [message #683012 is a reply to message #683008] Tue, 24 November 2020 10:09 Go to previous messageGo to next message
hissam78
Messages: 141
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,
Re: Day book format [message #683013 is a reply to message #683011] Tue, 24 November 2020 10:16 Go to previous messageGo to next message
hissam78
Messages: 141
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,
Re: Day book format [message #683014 is a reply to message #683010] Tue, 24 November 2020 10:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3020
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.
Re: Day book format [message #683015 is a reply to message #683014] Tue, 24 November 2020 10:39 Go to previous messageGo to next message
hissam78
Messages: 141
Registered: August 2011
Location: PAKISTAN
Senior Member
Very Well done , Great Solomon Yakobson, This is what we were looking for.

Bundle of thanks,
Re: Day book format [message #683016 is a reply to message #683013] Tue, 24 November 2020 10:43 Go to previous message
Michel Cadot
Messages: 67648
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?

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


Current Time: Fri Jan 22 09:20:34 CST 2021