Jump to content

SQL Reports


ejmerkel

Recommended Posts

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?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 4 weeks later...

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

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated