Forum Stats

  • 3,770,131 Users
  • 2,253,073 Discussions
  • 7,875,336 Comments

Discussions

result set with summary in the beginning

AQH
AQH Member Posts: 298 Blue Ribbon
edited May 1, 2019 5:17AM in SQL & PL/SQL

i have a table ml with following script; and i require following result-set ; it is that if where date is DT > TO_DATE('07/31/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS')  then result set with o/b (opening balance)  auto populate sum of dr/cr with DT <= TO_DATE('07/31/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS') for each ACID, can any one assist

pastedImage_3.png

tabledata

ml table

CREATE TABLE ML

(

  ID       NUMBER,

  DT       DATE,

  DR       NUMBER,

  CR       NUMBER,

  REMARKS  VARCHAR2(10 BYTE),

  ACID     NUMBER,

  JRNL     NUMBER

)

INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

VALUES (1,to_date('31-JUL-2018', 'DD-MON-YYYY'),0,541698,'cb',473,16);

INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

VALUES (2,to_date('31-JUL-2018','DD-MON-YYYY') ,541698,0,'cb',341,16);

INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

VALUES (3,TO_DATE('25-AUG-2018','DD-MON-YYYY'),197550,0,'tr',473,22);

INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

VALUES (4,TO_DATE('25-AUG-2018','DD-MON-YYYY'),0,8890,'tr',468,22);

INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

VALUES (5,TO_DATE('25-AUG-2018','DD-MON-YYYY'),0,188660,'tr',30,22);

INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

VALUES (6,TO_DATE('31-AUG-2018','DD-MON-YYYY'),6100,0,'tr',341,2);

INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

VALUES (7,TO_DATE('31-AUG-2018','DD-MON-YYYY'),0,6100,'tr',473,2);

Message was edited by: AQH

AQHBluShadowFrank Kulash

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Apr 29, 2019 3:50PM

    Hi,

    AQH wrote:i have a table ml with following script; and i require following result-set ; it is that if where date is DT > TO_DATE('07/31/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS') then result set with o/b (opening balance) auto populate sum of dr/cr with DT <= TO_DATE('07/31/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS') for each ACID, can any one assist
    pastedImage_3.png

    table
    data

    ml table

    CREATE TABLE ML

    (

    ID NUMBER,

    DT DATE,

    DR NUMBER,

    CR NUMBER,

    REMARKS VARCHAR2(10 BYTE),

    ACID NUMBER,

    JRNL NUMBER

    )

    INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

    VALUES (1,'31-JULY-2018',0,541698,'cb',473,16);

    INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

    VALUES (2,'31-JULY-2018',541698,0,'cb',341,16);

    INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

    VALUES (3,'25-AUG-2018',197550,0,'tr',473,22);

    INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

    VALUES (4,'25-AUG-2018',0,8890,'tr',468,22);

    INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

    VALUES (5,'25-AUG-2018',0,188660,'tr',30,22);

    INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

    VALUES (6,'31-AUG-2018',6100,0,'tr',341,2);

    INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)

    VALUES (7,'31-AUG-2018',0,6100,'tr',473,2);

    Thanks for posting the sample data, but don't try to insert VARCHAR2 values (such as '31-JULY-2018') into DATE columns (such as dt.).

    Could you explain a little more how you get the results?  For example, do you always want the DR values to appear in the CR column, and vice-versa?  Are you computing a cumulative total in orer by dt, even though the display is in order by acid?  It would help if you showed how to compute all the output for the first couple of dts (or acids).

    When do you want '-' in the dr and cr columns, and when do you want NULL (or ' ')?

    Are there 4 columns in the result set, or 5?

    Always say which version of Oracle you're using (for example, 12.2.0.1.0).

    See the Forum FAQ:

    Do you really want to display different things (such as the NUMBER acid and the DATE dt) in the same column?  If so, you'll need to convert them to strings before displaying.

    You can use GROUP BY ROLLUP (or GROUPBY GROUPING SETS) to get totals at different levels; e.g., a total for each value of acid, and then a grand total.

    AQH
  • AQH
    AQH Member Posts: 298 Blue Ribbon
    edited Apr 30, 2019 1:39AM

    values in dt column are date values. in all above columns, values will remains same as per insert statements. computing order can be order by acid. '-' is require when dr or cr =0 or null. there are 7columns are in result set dt, jrnl, remarks, dr, cr and acid. and the 7 column will be o/b (sum of dr or cr)

    acid and dt shows in above result set in one column are just for formatting.  actually result set is in group by acid.

    thanks for reply  @Frank Kulash

  • BluShadow
    BluShadow Member, Moderator Posts: 41,493 Red Diamond
    edited Apr 30, 2019 3:15AM
    AQH wrote:values in dt column are date values. in all above columns, values will remains same as per insert statements.

    No.  The values in your insert statements are strings, not dates.  Your insert statements are dependent on your own databases NLS settings, so those statement may work for some other people who have similar settings, but may not work for other people with different settings.

    When dealing with dates, always explicitly turn the strings to dates using the correct format mask to ensure it will work in any environment.

    See the community document: to get a better understanding of the DATE datatype (and why you shouldn't treat it as a string)

    AQHFrank Kulash
  • AQH
    AQH Member Posts: 298 Blue Ribbon
    edited Apr 30, 2019 4:00AM

    insert statements has been update with to_date function in original post

    BluShadow
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Apr 30, 2019 4:53PM

    Maybe (something to start with)

    with

    ml as

    (select 1 id,to_date('31-JUL-2018','dd-MON-yyyy') dt,0 cr,541698 dr,'cb' remarks,473 acid,16 jrnl from dual union all

    select 2,to_date('31-JUL-2018','dd-MON-yyyy'),541698,0,'cb',341,16 from dual union all

    select 3,to_date('25-AUG-2018','dd-MON-yyyy'),197550,0,'tr',473,22 from dual union all

    select 4,to_date('25-AUG-2018','dd-MON-yyyy'),0,8890,'tr',468,22 from dual union all

    select 5,to_date('25-AUG-2018','dd-MON-yyyy'),0,188660,'tr',30,22 from dual union all

    select 6,to_date('31-AUG-2018','dd-MON-yyyy'),6100,0,'tr',341,2 from dual union all

    select 7,to_date('31-AUG-2018','dd-MON-yyyy'),0,6100,'tr',473,2 from dual

    )

    select case when acid = lag(acid) over (order by rn) then null else acid end acid,case when remarks != 'cb' then dt end dt,jrnl,remarks,dr,cr

      from (select acid,dt,jrnl,remarks,dr,cr,row_number() over (order by acid,decode(remarks,null,0,'cb',1,'tr',2,3)) rn

              from (select acid,dt,nullif(jrnl,16) jrnl,case grouping_id(acid,dt,jrnl) when 3 then 'total:' when 7 then 'page total:' else max(remarks) end remarks,nullif(sum(cr),0) dr,nullif(sum(dr),0) cr

                      from ml

                     group by rollup(acid,dt,jrnl)

                     having grouping_id(acid,dt,jrnl) != 1

                    union all

                    select distinct acid,null,null,null,null,null

                      from ml

                    union all

                    select distinct acid,to_date(null),null,'cb',null,null

                      from ml x

                     where not exists(select null from ml where acid = x.acid and remarks = 'cb')

                   )

          )

    ACIDDTJRNLREMARKSDRCR
    30-----
    ---cb--
    -25-AUG-2018 00:00:0022tr-188660
    ---total:-188660
    341-----
    ---cb541698-
    -31-AUG-2018 00:00:002tr6100-
    ---total:547798-
    468-----
    ---cb--
    -25-AUG-2018 00:00:0022tr-8890
    ---total:-8890
    473-----
    ---cb-541698
    -25-AUG-2018 00:00:0022tr197550-
    -31-AUG-2018 00:00:002tr-6100
    ---total:197550547798
    ---page total:745348745348

    Regards

    Etbin

    AQH
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited May 1, 2019 5:17AM

    next version

    with

    ml as

    (select 1 id,to_date('31-JUL-2018','dd-MON-yyyy') dt,0 cr,541698 dr,'cb' remarks,473 acid,16 jrnl from dual union all

    select 2,to_date('31-JUL-2018','dd-MON-yyyy'),541698,0,'cb',341,16 from dual union all

    select 3,to_date('25-AUG-2018','dd-MON-yyyy'),197550,0,'tr',473,22 from dual union all

    select 4,to_date('25-AUG-2018','dd-MON-yyyy'),0,8890,'tr',468,22 from dual union all

    select 5,to_date('25-AUG-2018','dd-MON-yyyy'),0,188660,'tr',30,22 from dual union all

    select 6,to_date('31-AUG-2018','dd-MON-yyyy'),6100,0,'tr',341,2 from dual union all

    select 7,to_date('31-AUG-2018','dd-MON-yyyy'),0,6100,'tr',473,2 from dual

    )

    select case when remarks is null

                then 'ACID'

                else ' '

           end ".",

           case when remarks is null and acid != lag(acid,1,0) over (order by rn)

                then to_char(acid)

                when remarks != 'cb'

                then nvl(to_char(dt,'dd/mm/yyyy'),' ')

                else ' '

           end dt,

           nvl(to_char(jrnl),' ') jrnl,

           nvl(remarks,' ') remarks,

           nvl2(remarks,dr,' ') dr,

           nvl2(remarks,cr,' ') cr

      from (select acid,

                   dt,

                   jrnl,

                   remarks,dr,cr,row_number() over (order by acid,decode(remarks,null,0,'cb',1,'tr',2,3)) rn

              from (select acid,

                           dt,

                           nullif(jrnl,16) jrnl,

                           case grouping_id(acid,dt,jrnl) when 3 then 'total:'

                                                          when 7 then 'page total:'

                                                          else max(remarks)

                           end remarks,

                           to_char(nullif(sum(cr),0),'999G999G999') dr,

                           to_char(nullif(sum(dr),0),'999G999G999') cr

                      from ml

                     group by rollup(acid,dt,jrnl)

                     having grouping_id(acid,dt,jrnl) != 1

                    union all

                    select distinct acid,null,null,null,null,null

                      from ml

                    union all

                    select distinct acid,to_date(null),null,'cb',null,null

                      from ml x

                     where not exists(select null from ml where acid = x.acid and remarks = 'cb')

                   )

          )

    .DTJRNLREMARKSDRCR
    ACID30
    cb--
    25/08/201822tr-188,660
    total:-188,660
    ACID341
    cb541,698-
    31/08/20182tr6,100-
    total:547,798-
    ACID468
    cb--
    25/08/201822tr-8,890
    total:-8,890
    ACID473
    cb-541,698
    25/08/201822tr197,550-
    31/08/20182tr-6,100
    total:197,550547,798
    page total:745,348745,348

    AQH