1 (edited by agusecc 2022-09-08 16:23:15)

Topic: tablegrid data

hello friends, can you help me to create 2 views on a tablegrid with data from different months in one table? For example, I display balances from August and September in parallel. 
that is my project

https://drive.google.com/file/d/1-coDmO … d0Qij/view

thanks you

Post's attachments

Attachment icon question.JPG 50.49 kb, 84 downloads since 2022-09-08 

2 (edited by agusecc 2022-09-10 01:45:18)

Re: tablegrid data

hello Anyone can help me?? bacuse i dont know to do it..
.thanks you

Re: tablegrid data

Hello, here is an example found on the forum and there are others.

Post's attachments

Attachment icon many2many.zip 337.73 kb, 183 downloads since 2022-09-10 

Destiny

4 (edited by sparrow 2022-09-10 08:39:33)

Re: tablegrid data

Hi,


select distinct
akun.kodeakun as Kode,
akun.namaakun as nama_akun,  
((select sum(debet) from jurnal 
  where jurnal.id_akun = akun.id                             
  and jurnal.tanggal >= DATE({DateTimePicker2}, 'start of month')  
  and  jurnal.tanggal <= DATE({DateTimePicker2}, 'start of month', '+1 month', '-1 day') ) 
  - 
 (select sum(kredit) from jurnal 
  where jurnal.id_akun = akun.id 
  and jurnal.tanggal >= DATE({DateTimePicker2}, 'start of month') 
  and  jurnal.tanggal <= DATE({DateTimePicker2}, 'start of month', '+1 month', '-1 day')) )  as September,
((select sum(debet) from jurnal  
  where jurnal.id_akun = akun.id 
  and jurnal.tanggal >= DATE({DateTimePicker1}, 'start of month')  
  and  jurnal.tanggal <= DATE({DateTimePicker1}, 'start of month', '+1 month', '-1 day') ) 
  - 
  (select sum(kredit) from jurnal 
  where jurnal.id_akun = akun.id 
  and jurnal.tanggal >= DATE({DateTimePicker1}, 'start of month') 
  and  jurnal.tanggal <= DATE({DateTimePicker1}, 'start of month', '+1 month', '-1 day')) )  as Agustus
                                                                                                                                                                                                                                                                                                         
from akun                                                                                                                                                                                                                                                                                                   

left outer join jurnal on jurnal.id_akun = akun.id                                                                                                                                                                                                                                                             
where  jurnal.tanggal >= {DateTimePicker1} and 
    jurnal.tanggal <= {DateTimePicker2} and  akun.NRCLR = 'NRC'
order by akun.id    

Counting from the 1st day of the month to the last day of the month.
If needed from the selected date to the selected date:


select distinct
akun.kodeakun as Kode,
akun.namaakun as nama_akun,  
((select sum(debet) from jurnal 
  where jurnal.id_akun = akun.id                             
  and jurnal.tanggal >= DATE({DateTimePicker2}, 'start of month')  
  and  jurnal.tanggal <= DATE({DateTimePicker2}) ) 
  - 
 (select sum(kredit) from jurnal 
  where jurnal.id_akun = akun.id 
  and jurnal.tanggal >= DATE({DateTimePicker2}, 'start of month') 
  and  jurnal.tanggal <= DATE({DateTimePicker2})) )  as September,
((select sum(debet) from jurnal  
  where jurnal.id_akun = akun.id 
  and jurnal.tanggal >= DATE({DateTimePicker1})  
  and  jurnal.tanggal <= DATE({DateTimePicker1}, 'start of month', '+1 month', '-1 day') ) 
  - 
  (select sum(kredit) from jurnal 
  where jurnal.id_akun = akun.id 
  and jurnal.tanggal >= DATE({DateTimePicker1}) 
  and  jurnal.tanggal <= DATE({DateTimePicker1}, 'start of month', '+1 month', '-1 day')) )  as Agustus
                                                                                                                                                                                                                                                                                                         
from akun                                                                                                                                                                                                                                                                                                   

left outer join jurnal on jurnal.id_akun = akun.id                                                                                                                                                                                                                                                             
where  jurnal.tanggal >= {DateTimePicker1} and 
    jurnal.tanggal <= {DateTimePicker2} and  akun.NRCLR = 'NRC'
order by akun.id 

5 (edited by agusecc 2022-09-10 09:13:07)

Re: tablegrid data

Destiny thank for your example.
Sparrow, I want value in Septemer and Agustus defferent,, for your script value still same in september  and Agustus
I want have value like in  the picture.
Sparrow, can you help me again?

Post's attachments

Attachment icon keuangan.JPG 22.55 kb, 85 downloads since 2022-09-10 

6 (edited by sparrow 2022-09-10 09:19:53)

Re: tablegrid data

You did something wrong Here is my result with these queries


Or choose the dates correctly

Post's attachments

Attachment icon 2022-09-10_121627.jpg 29.88 kb, 77 downloads since 2022-09-10 

Re: tablegrid data

fixed

Post's attachments

Attachment icon akuntansinonlaba-fix.zip 360.84 kb, 181 downloads since 2022-09-10 

Re: tablegrid data

Sparrow, Thank you
I want user choose datetimeepicker1 and datetimepicker2  for one month, but outcome value for the mont for today (september) and last month (agustus)

thanks you

Re: tablegrid data

Then like this.
Such a request will be correct if both dates are in September !!!
additionally corrected in the query if the results are NULL. Allows you to get a negative result.

Post's attachments

Attachment icon akuntansinonlaba-fix1.zip 360.5 kb, 182 downloads since 2022-09-10 

Re: tablegrid data

Sparrow, I am very grateful for helping.
If in the future there is a problem, I will ask again.
thank you very much

11 (edited by agusecc 2022-09-17 01:02:44)

Re: tablegrid data

Sparrow, can help me again?
can I write My Variable in report Sql Script from component in form?

select distinct      
akun.kodeakun as Kode,
akun.namaakun as nama_akun,  
((select sum(debet) from jurnal 
  where jurnal.id_akun = akun.id                             
  and jurnal.tanggal >= DATE({DateTimePicker2}, 'start of month')  
  and  jurnal.tanggal <= DATE({DateTimePicker2}) ) 
  - 
 (select sum(kredit) from jurnal 
  where jurnal.id_akun = akun.id 
  and jurnal.tanggal >= DATE({DateTimePicker2}, 'start of month') 
  and  jurnal.tanggal <= DATE({DateTimePicker2})) )  as The_Month,
((select sum(debet) from jurnal                                      
  where jurnal.id_akun = akun.id 
  and jurnal.tanggal >= DATE({DateTimePicker1})                                          
  and  jurnal.tanggal <= DATE({DateTimePicker1}, 'start of month', '+1 month', '-1 day') ) 
  - 
  (select sum(kredit) from jurnal 
  where jurnal.id_akun = akun.id 
  and jurnal.tanggal >= DATE({DateTimePicker1})      
  and  jurnal.tanggal <= DATE({DateTimePicker1}, 'start of month', '+1 month', '-1 day')) )  as Last_Month
                                                                                                                                                                                                                                                                                                         
from akun                                                                                                                                                                                                                                                                                                   
                                                                                                   
left outer join jurnal on jurnal.id_akun = akun.id                                                                                                                                                                                                                                                             
where  jurnal.tanggal >= {DateTimePicker1} and 
    jurnal.tanggal <= {DateTimePicker2} and  akun.NRCLR = 'NRC'                                         
    
/* can I write myvariable? example 
{EditTheMonth};
{EditLastMonth};  
*/

order by akun.id  

and how to make footer Sum from Curency LastMonth or The Month  in tablegrid??

Post's attachments

Attachment icon akuntansinonlaba-fix1.zip 360.5 kb, 188 downloads since 2022-09-17 

Re: tablegrid data

1. Create components on the form with these names and you can use their values in your request.
2. Here is a link to an example of how to make the amount in the footer.
http://myvisualdatabase.com/forum/viewt … 989#p17989

Re: tablegrid data

Sparrow, thanks you for your information