--Lists Customers with invoices between the date range specified---
--Change the dates below for the dates you want to run the query --
DECLARE @StartDate AS VARCHAR(20)
DECLARE @EndDate AS VARCHAR(20)
SET @StartDate = 'Jan 1 2019'
SET @EndDate = 'Dec 31 2019'
SELECT CU.customer_code
[Customer Code],
CU.organisationv6
[Customer Name],
C.contactname
[Contact Name],
C.email
[Email address],
C.adr1
[Address line 1],
C.adr2
[Address line 2],
C.adr3
[City],
C.state,
C.country,
C.postcode
[Post Code],
Cast((SELECT Sum(IH.invoice_amount - IH.credit_amount)
FROM tblinvhead IH
WHERE IH.customer_code = CU.customer_code
AND IH.invdate >= Cast(@StartDate AS DATETIME)
AND IH.invdate <= Cast(@EndDate AS DATETIME)) AS
DECIMAL(10, 2)) AS
[Invoice Total]
FROM tblcust CU
LEFT OUTER JOIN tbllinkcustcontact LCC
ON LCC.id = CU.ilink_contactid
LEFT OUTER JOIN tblcontact C
ON C.id = LCC.contactid
WHERE (SELECT Count(I.id)
FROM tblinvhead I
WHERE I.customer_code = CU.customer_code
AND invdate >= Cast(@StartDate AS DATETIME)
AND invdate <= Cast(@EndDate AS DATETIME)) > 0
ORDER BY CU.customer_code