Display Month Gaps for Each location

Question

I have the following query which takes in the opps and calculates the duration, and revenue for each month. However, for some locations, where there is no data, it is missing some months. Essentially, I would like all months to appear for each of the location and record type. I tried a left outer join on the calendar but that didn't seem to work either.

Here is the query:

;With DateSequence( [Date] ) as
(
    Select CAST(@fromdate as DATE) as [Date]
        union all
    Select CAST(dateadd(day, 1, [Date]) as Date)
        from DateSequence
        where Date < @todate
)


INSERT INTO CalendarTemp (Date, Day, DayOfWeek, DayOfYear, WeekOfYear, Month, MonthName, Year) 

Select
    [Date] as [Date],
    DATEPART(DAY,[Date]) as [Day],
    DATENAME(dw, [Date]) as [DayOfWeek],
    DATEPART(DAYOFYEAR,[Date]) as [DayOfYear],
    DATEPART(WEEK,[Date]) as [WeekOfYear],
    DATEPART(MONTH,[Date]) as [Month],
    DATENAME(MONTH,[Date]) as [MonthName],
    DATEPART(YEAR,[Date]) as [Year]

from DateSequence option (MaxRecursion 10000)
;

DELETE FROM CalendarTemp WHERE DayOfWeek IN ('Saturday', 'Sunday');


SELECT 
AccountId
,AccountName
,Office
,Stage = (CASE WHEN StageName = 'Closed Won' THEN 'Closed Won'
  ELSE 'Open'
  END)  
,Id
,Name
,RecordType= (CASE 
                         WHEN recordtypeid = 'LAS1' THEN 'S'
                          END)
,Start_Date
,End_Date
,Probability
,Estimated_Revenue_Won = ISNULL(Amount, 0)
,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name) AS Row
--,Revenue_Per_Day = CAST(ISNULL(Amount/NULLIF(dbo.CalculateNumberOFWorkDays(Start_Date, End_Date),0),0) as money)
,YEAR(c.Date) as year
,MONTH(c.Date) as Month
,c.MonthName
--, ISNULL(CAST(Sum((Amount)/NULLIF(dbo.CalculateNumberOFWorkDays(Start_Date, End_Date),0)) as money),0) As RevenuePerMonth


FROM SF_Extracted_Opps o
LEFT OUTER JOIN CalendarTemp c on o.Start_Date <= c.Date AND o.End_Date >= c.Date


WHERE 
Start_Date <= @todate AND End_Date >= @fromdate
AND Office IN (@Location)
AND recordtypeid IN ('LAS1')



GROUP BY
AccountId
,AccountName
,Office
,(CASE WHEN StageName = 'Closed Won' THEN 'Closed Won'
  ELSE 'Open'
  END)  
,Id
,Name
,(CASE 
        WHEN recordtypeid = 'LAS1' THEN 'S'

END)
,Amount
--, CAST(ISNULL(Amount/NULLIF(dbo.CalculateNumberOFWorkDays(Start_Date, End_Date),0),0) as money)
,Start_Date
,End_Date
,Probability
,YEAR(c.Date)
,Month(c.Date)
,c.MonthName
,dbo.CalculateNumberOFWorkDays(Start_Date, End_Date)


ORDER BY Office
, (CASE 
        WHEN recordtypeid = 'LAS1' THEN 'S'

END)
,(CASE WHEN StageName = 'Closed Won' THEN 'Closed Won'
  ELSE 'Open'
  END)  
, [Start_Date], Month(c.Date), AccountName, Row;

I tried adding another left outer join to this and using this a sub query and the join essentially on the calendar based on the year and month, but that did not seem to work either. Suggestions would be extremely appreciated.

--Date Calendar for each location: 
;With DateSequence( [Date], Locatio) as
(
    Select CAST(@fromdate as DATE) as [Date], oo.Office as location
        union all
    Select CAST(dateadd(day, 1, [Date]) as Date), oo.Office as location
        from DateSequence dts
        join Opportunity_offices oo on 1 = 1
        where Date < @todate
)

--select result


INSERT INTO CalendarTemp (Location,Date, Day, DayOfWeek, DayOfYear, WeekOfYear, Month, MonthName, Year) 

Select
    location,
    [Date] as [Date],
    DATEPART(DAY,[Date]) as [Day],
    DATENAME(dw, [Date]) as [DayOfWeek],
    DATEPART(DAYOFYEAR,[Date]) as [DayOfYear],
    DATEPART(WEEK,[Date]) as [WeekOfYear],
    DATEPART(MONTH,[Date]) as [Month],
    DATENAME(MONTH,[Date]) as [MonthName],
    DATEPART(YEAR,[Date]) as [Year]

from DateSequence option (MaxRecursion 10000)
;

LEFT JOIN return all data from left collection try use RIGHT JOIN or swap data source CalendarTemp c LEFT OUTER JOIN SF_Extracted_Opps o — steryd, Sep 19 '16 at 19:01

Matt · Accepted Answer · 2016-09-20T14:35:56.723

you have your LEFT JOIN backwards if you want all records from CalendarTemp and only those that match from SF_Extracted_Opps then you the CalendarTemp should be the table on the LEFT. You can however switch LEFT JOIN to RIGHT JOIN and it should be fixed. The other issue will be your WHERE statement is using columns from your SF_Extracted_Opps table which will just make that an INNER JOIN again.

here is one way to fix.

SELECT
.....

FROM
    CalendarTemp c
    LEFT JOIN SF_Extracted_Opps o
    ON o.Start_Date <= c.Date AND o.End_Date >= c.Date
    AND o.Start_Date <= @todate AND End_Date >= @fromdate
    AND o.Office IN (@Location)
    AND o.recordtypeid IN ('LAS1')

The other issue you might run into is because you remove weekends from your CalendarTemp Table not all dates are represented I would test with the weekends still in and out and see if you get different results.

this line:

AND o.Start_Date <= @todate AND End_Date >= @fromdate

should not be needed either because you are already limiting the dates from the line before and values in your CalendarTempTable

A note about your CalendarDate table you don't have to go back and delete those records simply add the day of week as a WHERE statement on the select that populates that table.

Edit for All Offices you can use a cross join of your offices table with your CalendarTemp table to do this do it in your final query not the cte that builds the calendar. The problem with doing it in the CTE calendar definition is that it is recursive so you would have to do it in both the anchor and the recursive member definition.

SELECT
.....

FROM
    CalendarTemp c
    CROSS JOIN Opportunity_offices oo
    LEFT JOIN SF_Extracted_Opps o
    ON o.Start_Date <= c.Date AND o.End_Date >= c.Date
    AND o.Start_Date <= @todate AND End_Date >= @fromdate
    AND oo.office = o.Office
    AND o.recordtypeid IN ('LAS1')

One last question: When I run this for all locations. How would I edit this so that it creates the missing months for each of the location. When I run this for all locations at the moment, it will get the missing months based on the entire dataset. Essentially I need it so that It would perform the left join and take each location into account. — a415, Sep 20 '16 at 13:48
So you want to show all locations and all dates regardless of whether a record exists? You would have to build a cte of the locations .e.g select distinct from then cross join that to calendartemp and modify the left join to be related to the locations table — Matt, Sep 20 '16 at 14:15
Please see the added detail in the questions Is that what you mean. It seems that I cannot add a subquery within the CTE — a415, Sep 20 '16 at 14:28
see my edit for more detail you are just trying to join it in the wrong location is all, you could do it in the cte definition if you do it in both spots but it would get messy on the joins etc but if you do it after wards in the query it is an easy CROSS JOIN statement — Matt, Sep 20 '16 at 14:36

Display Month Gaps for Each location

1 Answers1