SQL Reports

[ejmerkel]

First off, I am not a SQL programmer but I understand the basics. I have been trying to create a report which shows payments by a Client Groups by a date range and payment type. For example show me all of the credit card payments yesterday made by Client Group ID #.

I have been playing with Modules Garden Report Builder but it is limited to joining two tables (invoices and customer) and I think I need more (transactions) to make this work. Here is what I've come up with so far and it kind of works.

SELECT tblclients.firstname AS `First Name`, tblclients.lastname AS `Last Name`, tblinvoices.total AS `Total Paid` FROM tblinvoices LEFT JOIN tblclients ON tblinvoices.userid = tblclients.id WHERE tblclients.groupid = 12 AND tblinvoices.datepaid BETWEEN DATE(:dateStart) AND DATE(:dateStop)

It is not limited payment type like credit card and some payments show 0 like when a check was applied as a credit on an invoice but it does show payments by this client group.  Any advice on how to this to show these transactions correctly?

Also, since I am not the greatest at this, does anyone have advice on a query builder for MySQL that might make this easier?

[web2008]

Can this help?

 

SELECT
	tblclients.firstname AS `First Name`,
	tblclients.lastname AS `Last Name`,
	tblinvoices.total AS `Total Paid`,
	tblinvoices.paymentmethod AS `Payment Method` 
FROM
	tblinvoices
	LEFT JOIN tblclients ON tblinvoices.userid = tblclients.id 
WHERE
	tblclients.groupid = 11 
	AND tblinvoices.paymentmethod = 0
	AND (
		tblinvoices.datepaid BETWEEN '2022-01-29 06:00:00' 
	AND '2022-03-25 10:15:55' 
	)

 

[ejmerkel]

Thank you that gets me closer. For some reason that includes charge attempts that failed so I just added  "tblinvoices.total > 0" and that gets what I want. A couple things if you would be so kind to answer. 

1) I would like the date period to be yesterday. I tried added this but it didn't seem to be the correct way to do this: "tblinvoices.datepaid BETWEEN (SUBDATE(NOW(),1) and NOW())"

2) Once I get all that work, would there be a way to change it so that it show all client groups for yesterday and their totals by groupid?

Thanks again for your help!

 

Best regards,

Eric

[web2008]

This should give you yesterday:

SELECT
	tblclients.firstname AS `First Name`,
	tblclients.lastname AS `Last Name`,
	tblinvoices.datepaid AS `Date Paid`,
	tblinvoices.total AS `Total Paid`,
	tblinvoices.paymentmethod AS `Payment Method` 
FROM
	tblinvoices
	LEFT JOIN tblclients ON tblinvoices.userid = tblclients.id 
WHERE
	tblclients.groupid = 0 
	AND tblinvoices.paymentmethod = 0 
	AND tblinvoices.datepaid BETWEEN ( CURDATE()- INTERVAL 1 DAY ) 
	AND ( CURDATE()- INTERVAL 0 DAY );