SQL : wrong total returned

Question

I'm working on a SQL project where I have a stored procedure where I return the total invoices of each customer for a specific time period. I have written a stored procedure that returns the total amount for each customer however the total results are a little bit above the real amount.

Customer 1 Total: 23 (finding him with query individually)
Customer 1: Total 25 (using a stored procedure)

This is my stored procedure and I have the tables below. Thank you for your time.

CREATE PROCEDURE QUERY4 
    @p_StartDate DATE, @p_EndDate DATE
AS
BEGIN
    SELECT ct.*, SUM(iv.Total) AS Total
    FROM Customer AS ct, Invoice AS iv
    WHERE ct.CustomerId = iv.CustomerId 
      AND iv.InvoiceDate BETWEEN @p_StartDate AND @p_EndDate
    GROUP BY ct.CustomerId, ct.FirstName, ct.LastName, ct.Company, ct.Country, ct.State, ct.City, 
            ct.Address, ct.PostalCode, ct.Phone, ct.Fax, ct.Email, ct.SupportRepId
    ORDER BY Total DESC
 END;

Tables

if you want total invoice of each custumer then you just group by customerid not other columns , because customers possibly have same name or maybe in some invoices details are not entered correctly — Mohammad, Dec 21 '19 at 15:53
Post the query that you use to find the total for an individual customer. — forpas, Dec 21 '19 at 15:53
I can't delete the rest considering that I have them included in select. — sotis, Dec 21 '19 at 15:57
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Dec 21 '19 at 21:49

LukStorms · Accepted Answer · 2019-12-23T15:14:45.900

0

You could first group the invoices in a sub-query.
Then INNER JOIN that to the customers.

    SELECT ct.*, inv.Total
    FROM 
    (
       SELECT i.CustomerId, SUM(i.Total) AS Total
       FROM Invoice i
       WHERE i.InvoiceDate >= @p_StartDate
         AND i.InvoiceDate <= @p_EndDate
       GROUP BY i.CustomerId
    ) inv
    INNER JOIN Customer AS ct
      ON ct.CustomerId = inv.CustomerId 
    ORDER BY inv.Total DESC

But this should give the same result as you got before.
So maybe you could share the query you used that found 23.
Also, is InvoiceDate a DATE type or a DATETIME?

edited Dec 23 '19 at 15:14

answered Dec 21 '19 at 23:19

LukStorms

28,916
5
31
45

Thanks for sharing your solution, the results I'm now getting are the same as when I manually add the totals. – sotis Dec 23 '19 at 14:33
@sotis Glad that it solved your issue. But to me it seems that your original query should give the same result. So I'm suspecting that InvoiceDate is a datetime field. And f.e. `'2019-12-15 00:00:01'` is not smaller or equal than the date `'2019-12-15'`. But `cast('2019-12-15 00:00:01' as date) <= cast('2019-12-15' as date)` does evaluate as true. – LukStorms Dec 23 '19 at 15:22
InvoiceDate is indeed a datetime field, you are correct. – sotis Dec 24 '19 at 14:08
@sotis If that's the case, perhaps you'll find [this answer](https://stackoverflow.com/a/7691846/4003419) an interesting trick. The thing is that doing something like `where cast(datetimecol1 as date) <= @datevariable` would work, but because of the CAST an index on datetimecol1 could be ignored by the query optimizer. Thus making the query slower. Well, that's only if it's indexed though. – LukStorms Dec 24 '19 at 14:23

SQL : wrong total returned

1 Answers1