SQL Query to produce Monthly Turnover by Customer, inc Months with zero Invoices (for Graphing)

I am trying to create a graph in a Crystal report, for showing the Invoiced amount (turnover) per Month for a given customer.  I've found lots of scripts online that claim to work, but when I've tested them on my DB table they don't - they only ever produce results for the months that do have at least one invoice.

Please help!
LVL 13
Mark DamenERP System ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark DamenERP System ManagerAuthor Commented:
This is one of the samples that I've tried - it produces monthly totals, but only for months that have an invoice!  I need it to show all months between the date ranges, and a 0 if necessary for graphing purposes.

DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME;

SELECT @StartDate = '20140501', @EndDate = '20150529';

;WITH d(d) AS
(
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
  FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1)
    n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
    FROM sys.all_objects ORDER BY [object_id] ) AS n
)
SELECT
  [Month]    = DATENAME(MONTH, d.d),
  [Year]     = YEAR(d.d),
  InvTotal = SUM(o.InvoiceAmt)
FROM d LEFT JOIN InvcHead AS o
  ON o.InvoiceDate >= d.d
  AND o.InvoiceDate < DATEADD(MONTH, 1, d.d)
  WHERE CustNum = 233
GROUP BY d.d
ORDER BY d.d;
vastoCommented:
Try this


DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME;

SELECT @StartDate = '20140501', @EndDate = '20150529';

WITH DatesTable(CurrentDate) AS
(
    SELECT DATEADD(mm,DATEDIFF(mm,0,@StartDate),0) AS datetime
    UNION ALL
    SELECT DATEADD(MONTH, 1, CurrentDate) FROM DatesTable WHERE DATEADD(MONTH, 1, CurrentDate) < @EndDate
)

SELECT CurrentDate FROM DatesTable
mlmccCommented:
If you can't get a script to work I can probably figure out a way to have it done in a subreport.

mlmcc
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark DamenERP System ManagerAuthor Commented:
vasto - how does that link to my other table?  the problem always seems to be when linking to the other table.  Before I apply the WHERE clause in the code I posted it works - I can a listing which includes some null entries, but has the month.

mlmcc - I am intending this to go into a subreport anyway, building a customer summary sheet including account balance, turnover, current orders, rebates etc.

WHY even with a LEFT OUTER JOIN does it only show records matching in both tables?  All other instances a LEFT OUTER would show me all of the dates, and only records from the right that match!
mlmccCommented:
Are you filtering on the joined table?
If so then Crystal converts the join to INNER since a NULL in the joined table can't match the value.

mlmcc
mlmccCommented:
If you are referring to the SQL above move the filter
   CustNum = 233

into the join

DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME;

 SELECT @StartDate = '20140501', @EndDate = '20150529';

 ;WITH d(d) AS 
 (
   SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
   FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1) 
     n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
     FROM sys.all_objects ORDER BY [object_id] ) AS n
 )
 SELECT 
   [Month]    = DATENAME(MONTH, d.d), 
   [Year]     = YEAR(d.d), 
   InvTotal = SUM(o.InvoiceAmt) 
 FROM d LEFT JOIN InvcHead AS o
   ON o.InvoiceDate >= d.d
   AND o.InvoiceDate < DATEADD(MONTH, 1, d.d)
   AND o.CustNum = 233
 GROUP BY d.d
 ORDER BY d.d; 

Open in new window


mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James0628Commented:
You could move the test on CustNum into the Join, as mlmcc suggested.  Or change the Where to also allow nulls.

  WHERE CustNum = 233 OR CustNum IS NULL


 James
Mark DamenERP System ManagerAuthor Commented:
Thanks very much for all the help - after all that comparing what I posted to what mlmcc provided was only 1 line different, but that line made all the difference!  Thanks for helping me to arrive at the solution.

One other point worth noting if anybody is reading this later and trying to the same kind of thing in Crystal - my query above is sorted in date order, but I charted this in Crystal it decided to ORDER the dataset in what it calls ascending order - which is an alpha sort.  Once I changed it back to "in original order" but graph looks as intended.

I also converted the @EndDate to GetDATE() to pull current date, and changed the @StartDate to be DATEADD(month, -11, @EndDate) to make the date range dynamic.  I then created a parameter for CustNum, and that gets set on the subreport from the main report.

Many Thanks
Mark
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.