result set with summary in the beginning

AQH

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

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,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

Frank Kulash

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

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

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

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)

AQH

insert statements has been update with to_date function in original post

Etbin

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')

               )

      )

ACID	DT	JRNL	REMARKS	DR	CR
30	-	-	-	-	-
-	-	-	cb	-	-
-	25-AUG-2018 00:00:00	22	tr	-	188660
-	-	-	total:	-	188660
341	-	-	-	-	-
-	-	-	cb	541698	-
-	31-AUG-2018 00:00:00	2	tr	6100	-
-	-	-	total:	547798	-
468	-	-	-	-	-
-	-	-	cb	-	-
-	25-AUG-2018 00:00:00	22	tr	-	8890
-	-	-	total:	-	8890
473	-	-	-	-	-
-	-	-	cb	-	541698
-	25-AUG-2018 00:00:00	22	tr	197550	-
-	31-AUG-2018 00:00:00	2	tr	-	6100
-	-	-	total:	197550	547798
-	-	-	page total:	745348	745348

Regards

Etbin

Etbin

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')

               )

      )

.	DT	JRNL	REMARKS	DR	CR
ACID	30
			cb	-	-
	25/08/2018	22	tr	-	188,660
			total:	-	188,660
ACID	341
			cb	541,698	-
	31/08/2018	2	tr	6,100	-
			total:	547,798	-
ACID	468
			cb	-	-
	25/08/2018	22	tr	-	8,890
			total:	-	8,890
ACID	473
			cb	-	541,698
	25/08/2018	22	tr	197,550	-
	31/08/2018	2	tr	-	6,100
			total:	197,550	547,798
			page total:	745,348	745,348