Thanks for the reply!
Here is what I have been working with today.
@StartDate DATETIME = NULL
,@EndDate DATETIME = NULL
SET @StartDate = '2011-08-01'
SET @EndDate = '2011-08-31'
,pnl.NetRevenue - cust.plannedRevenue AS Diff
,getYtd(pnl.familycode, pnl.monthDate) AS Ytd
LEFT OUTER JOIN pnl
ON customer.Code = pnl.code AND (pnl.monthdate >= @StartDate AND pnl.monthdate <= @EndDate)
LEFT OUTER JOIN MonCustomer AS Cust
ON customer.Code = cust.code AND pnl.monthdate = cust.MonthDate
WHERE customer.custCode IN (SELECT DISTINCT custcode FROM pnl WHERE YEAR(DATEADD(YEAR,-1,pnl)) < CAST(YEAR(@StartDate) AS NVARCHAR(20)))
This what I am getting so far.
Monthdate Customer Revenue NetRev Diff Ytd
2011-08-01 Customer1 140000.00 138000.00 -2000 957000.00
NULL Customer2 0 0 0 0
For Customer2, if I change the date range to 03-01-2011 to 03-31-2011 I will get some data for that customer. I would like to see Customer2 because they had revenue in the past 6 months. There WHERE clause at the end we were trying to get it to pull all customers within a year, it does, but still no data for those particular customers.
Hope this helps. Thanks!!!