SQL Query returns no data but should have approx 25 rows

Transact-SQL
1

I have an sql query that compiles with no errors but my issue is that it returns no data when it should return approx. 25 rows. I apologize as I am new at doing queries but am not seeing where this query shouldn't work. Is there anything that stands out to anyone that might show why no data would be produced? Thanks!

SELECT     CASE WHEN COUNT(h.[Main Case ID]) IS NULL THEN 0 ELSE COUNT(h.[Main Case ID]) END AS Completed, CASE WHEN AVG(h.[Total Work Days]) 
                      IS NULL THEN 0 ELSE AVG(h.[Total Work Days]) END AS ProcessTime, CASE WHEN SUM(h.[Total Work Days]) 
                      IS NULL THEN 0 ELSE SUM(h.[Total Work Days]) END AS TotalProcessTime, CASE WHEN SUM(h.[Met Standard]) IS NULL 
                      THEN 0 ELSE SUM(h.[Met Standard]) END AS NumberMet, J.MonthYear AS Month, J.FYmonth, J.MonthStart
FROM         (SELECT     a.TicketNumber AS [Main Case ID], A.CustomerIdName AS Customer, 
                                              b.ELRLCaseID, e.Catagory, f.[Functional Area], CASE WHEN b.Station IS NULL THEN '' ELSE b.Station END AS Station, a.CreatedOn, 
                                              b.Owner, CASE WHEN a.[Urgency] = 1 THEN 'Normal' ELSE 'High' END AS [Closed Urgency], 
                                              CASE WHEN a.StateCode = 0 THEN 'Open' WHEN a.StateCode = 1 THEN 'Closed' ELSE 'Cancelled' END AS Status, 
                                              CASE WHEN b.AccountNumber IS NULL THEN '' ELSE b.AccountNumber END AS [Account Number], b.MouStart, b.MouEnd, 
					      CASE WHEN dbo.fx_TotalWorkdays(b.MouStart, b.MouEnd) = '0' THEN 1 
					      ELSE dbo.fx_TotalWorkdays(b.MouStart, b.MouEnd) END AS [Total Work Days], CASE WHEN dbo.fx_TotalWorkdays(b.MouStart, b.MouEnd) 
                                              > 5 THEN 0 ELSE 1 END AS [Met Standard], { fn MONTHNAME(b.MouEnd) } + ' ' + STR(YEAR(b.MouEnd), 4) AS Month, YEAR(b.MouEnd) 
                                              AS Year, CASE WHEN Month(b.MouEnd) = 10 THEN Year(b.MouEnd) + 1 WHEN Month(b.MouEnd) = 11 THEN Year(b.MouEnd) 
                                              + 1 WHEN Month(b.MouEnd) = 12 THEN Year(b.MouEnd) + 1 ELSE Year(b.MouEnd) END AS FY, CASE WHEN Month(b.MouEnd) 
                                              = 10 THEN 1 WHEN Month(b.MouEnd) = 11 THEN 2 WHEN Month(b.MouEnd) = 12 THEN 3 ELSE Month(b.MouEnd) + 3 END AS FM
FROM          (SELECT     AA.IncidentId, AA.TicketNumber, AA.CreatedBy, BB.Custom_BusinessLine, BB.Custom_Regarding, AA.CaseTypeCode, 
                          AA.OwnerID, DD.FullName, CC.Name, AA.CreatedOn, AA.StateCode, AA.SeverityCode AS Urgency, AA.ModifiedOn, AA.CustomerIdName
FROM          IncidentBase AS AA INNER JOIN
                          IncidentExtensionBase AS BB ON AA.IncidentId = BB.IncidentId LEFT OUTER JOIN
                          AccountBase AS CC ON AA.CustomerId = CC.AccountId LEFT OUTER JOIN
                          ContactBase AS DD ON AA.OwnerID = DD.ContactId
WHERE      (BB.Custom_BusinessLine = 1)) AS a LEFT OUTER JOIN
              (SELECT     EE.Custom_employeelaborrelationsId AS ELRLCaseID, EE.Custom_CaseNumber, EE.Custom_EmployeeId, 
                          HH.AccountNumber, FF.statecode, EE.Custom_FacilityId, HH.Name AS Station, GG.FullName AS Owner, FF.statuscode, 
                          CASE WHEN dbo.fx_GetTaskStartDate(EE.Custom_employeelaborrelationsId, 'Sent Final Letter to POC') IS NULL 
                          THEN dbo.fx_GetTaskStartDate(EE.Custom_employeelaborrelationsId, 'Issued Final Letter to Employee') 
                          ELSE dbo.fx_GetTaskStartDate(EE.Custom_employeelaborrelationsId, 'Sent Final Letter to POC') 
                          END AS MouStart, DATEADD(hh, - 6, FF.ModifiedOn) AS [Last Modified], 
               					CASE WHEN dbo.fx_GetTaskEndDate(EE.Custom_employeelaborrelationsId, 'Sent Final Letter to POC') IS NULL 
                                                THEN dbo.fx_GetTaskEndDate(EE.Custom_employeelaborrelationsId, 'Issued Final Letter to Employee') 
                                              --ELSE CASE WHEN dbo.fx_GetTaskEndDate(EE.Custom_employeelaborrelationsId, 'Issued Final Letter to Employee') IS NOT NULL 
                                              --THEN CASE WHEN dbo.fx_GetTaskEndDate(EE.Custom_employeelaborrelationsId, 'Sent Final Letter to POC') 
                                              -->= dbo.fx_GetTaskEndDate(EE.Custom_employeelaborrelationsId, 'Issued Final Letter to Employee') 
                                              --THEN dbo.fx_GetTaskEndDate(EE.Custom_employeelaborrelationsId, 'Sent Final Letter to POC') 
                                                ELSE dbo.fx_GetTaskEndDate(EE.Custom_employeelaborrelationsId, 'Sent Final Letter to POC') 
                                              --END ELSE dbo.fx_GetTaskEndDate(EE.Custom_employeelaborrelationsId, 'Issued Final Letter to Employee') 
                                                END AS MouEnd
FROM          Custom_employeelaborrelationsExtensionBase AS EE INNER JOIN
                         Custom_employeelaborrelationsBase AS FF ON 
                         EE.Custom_employeelaborrelationsId = FF.Custom_employeelaborrelationsId INNER JOIN
                         SystemUserBase AS GG ON FF.OwnerId = GG.SystemUserId LEFT OUTER JOIN
                         AccountBase AS HH ON EE.Custom_FacilityId = HH.AccountId
                                                    WHERE      (HH.AccountNumber IN (@Stations))) AS b ON 
                                              a.TicketNumber = b.Custom_CaseNumber LEFT OUTER JOIN
                                                  (SELECT     JJ.TicketNumber, LL.Value AS Catagory
                                                    FROM          IncidentBase AS JJ INNER JOIN
                                                                           IncidentExtensionBase AS KK ON JJ.IncidentId = KK.IncidentId INNER JOIN
                                                                           StringMap AS LL ON KK.Custom_Regarding = LL.AttributeValue
                                                    WHERE      (KK.Custom_BusinessLine = 1) AND (LL.AttributeName = N'custom_regarding')) AS e ON 
                                              a.TicketNumber = e.TicketNumber LEFT OUTER JOIN
                                                  (SELECT     MM.TicketNumber, NN.Value AS [Functional Area]
                                                    FROM          IncidentBase AS MM INNER JOIN
                                                                           StringMap AS NN ON MM.CaseTypeCode = NN.AttributeValue
                                                    WHERE      (NN.AttributeName = N'casetypecode')) AS f ON a.TicketNumber = f.TicketNumber
WHERE      (f.[Functional Area] = 'ER / LR') AND (e.Catagory = 'Case') AND (b.MouEnd >= @StartDate) AND (b.MouEnd < DATEADD(d, 1, @EndDate))) 
                      AS h RIGHT OUTER JOIN
                      dbo.fn_GetMonthList(@StartDate, @EndDate) AS J ON h.FM = J.FYmonth
GROUP BY J.MonthYear, J.FYmonth, J.MonthStart
ORDER BY J.MonthStart
2

That's a rough query. Haven't seen Right join in a long time. That might have something to do with it. Do you have any sample data to look at.

3

From an initial quick look, in the WHERE clause:

WHERE (f.[Functional Area] = 'ER / LR') AND (e.Catagory = 'Case') AND (b.MouEnd >= @StartDate) AND (b.MouEnd < DATEADD(d, 1, @EndDate)))

At least f. and b. columns (not sure about the e. column) come from LEFT joins, meaning they might be NULL, so you should either: (1) check them in the original query that produces the f and b results OR (2) also check for NULL in the WHERE, something like this:

WHERE ((f.[Functional Area] IS NULL OR f.[Functional Area] = 'ER / LR')) AND (e.Catagory = 'Case') AND (b.MouEnd IS NULL OR ((b.MouEnd >= @StartDate) AND (b.MouEnd < DATEADD(d, 1, @EndDate)))))

4

I did try that and unfortunately still don't receive any results but I appreciate the response.