SQL query to export invoices

[sgonzalez]

HI, How is everyone doing? Hope well

 

I was looking forward to generate a SQL query to display

 

Invoices paid from feb 01st 2017 to feb 28th 2017, with client name, address, phone, item description, ammount, tax, total

 

 

Can anyone help me? I tried several ways but none one gave me the correct results.

 

 

 

Thanks in advance!!

[brian!]

it will be something along the lines of...

 

SELECT
tblinvoices.userid,
tblinvoices.id,
tblinvoices.date,
tblinvoices.tax,
tblinvoices.subtotal,
tblinvoices.tax2,
tblinvoices.total,
tblclients.firstname,
tblclients.lastname,
tblclients.address1,
tblclients.address2,
tblclients.city,
tblclients.country,
tblclients.postcode,
tblclients.state,
tblinvoiceitems.description,
tblinvoiceitems.amount,
tblinvoiceitems.taxed
FROM tblinvoices
INNER JOIN tblclients ON tblinvoices.userid = tblclients.id
INNER JOIN tblinvoiceitems ON tblinvoices.id = tblinvoiceitems.invoiceid
WHERE tblinvoices.date BETWEEN '2017-02-01' AND '2017-02-28'

if it were me, i'd be more tempted to modify the Invoices report to query the additional tables, but the above should give you a workable SQL query, though you might have to tweak the output column order.

[sgonzalez]

Thank you so much Brian very helpfull. I tied it up with some additional fields and its working perfect! The only thing that is driving me crazy now is that I was unable to add some customfieldsvalue

 

 

tied up code

SELECT
tblinvoices.userid,
tblinvoices.id,
tblinvoices.date,
tblinvoices.subtotal,
tblinvoices.tax,
tblinvoices.total,
tblclients.firstname,
tblclients.lastname,
tblclients.address1,
tblclients.phonenumber,
tblclients.companyname,
tblclients.country,
tblinvoiceitems.description
FROM tblinvoices
INNER JOIN tblclients ON tblinvoices.userid = tblclients.id
INNER JOIN tblinvoiceitems ON tblinvoices.id = tblinvoiceitems.invoiceid
WHERE tblinvoices.date BETWEEN '2017-02-01' AND '2017-02-28'

 

 

I attach wich customfields I need to complete this.. Tried with different ways and I was unable.. my sql knowledge is so basic

 

 

 

 

Thanks again Brian for such a great help!