Generating missing report submissions via reference table

Question

I have a SQL query i'm currently working on which i would greatly appreciate some help with.

Here is a simplified version of the view I've been given to work on:

SELECT   a.Organisation_Name
         ,a.Org_Id
      ,b.Activity_month
      ,SUM(b.Activity_Plan) 'Plan_Activity'
      ,SUM(b.Activity_Actual) 'Actual_Activity'
      ,SUM(b.Price_Actual) 'Actual_Price'
      ,SUM(b.Price_Plan) 'Plan_Price'
      ,COUNT(b.Instances) AS 'Record_Count'
      ,CASE WHEN COUNT(b.Instances) > 0 THEN 'Yes' ELSE 'No' END AS Submitted
  FROM [ExampleDatabase].[dbo].[Organisation_Reference] a

  LEFT JOIN [ExampleDatabase].[dbo].[Report_Submissions] b
  ON a.Org_Id = b.Org_Id
    AND ([Exmaple_Code] LIKE ('X') or [Example_Code] = 'X')


WHERE a.Category_Flag = 1
AND a.Example_Code in ('X','X','X','X','X') 

  GROUP BY
      a.Organisation_Name
      ,a.Org_Id
      ,b.Activity_month

--

The Activity Month field is an Integer rather than a date, currently ranging from 1-8.

The problem i am facing is that within the [Report_Submissions] table, it only contains organisations which have actually submitted the reports, whereas the
[Organisation_Reference] table lists all the organisations which should be submitting.

Where the organisations have submitted the reports, the data is perfect and gives me a run down of all the details i need for each individual month.

Obviously if an organisation hasn't submitted then this detail wouldn't be available, but i do need to have a complete list of all organisations listed from the reference table for each individual month and whether they have submitted the reports or not.

At the moment where the 'Submitted' field = 'No' it's only bringing back one record for each organisation that has never submitted (With Activity_month coming through as null) and if an organisation has only submitted once or twice then it will include those submissions but still be missing the rest of the months from the result set.

I've tried various different joins etc. but I seem to be drawing a blank for a solution. Is there a way of generating this information within the script? Any advice would be great!

Kind Regards,

Mark

Answer 1

Since you just need numbers 1-8, using a subquery in your join to cross apply(values ()) to your Organisation_Reference table works well and does not make the query much more compliCated to read.

select
    a.Organisation_Name
  , a.Org_Id
  , a.Activity_Month
  , sum(b.Activity_Plan) 'Plan_Activity'
  , sum(b.Activity_Actual) 'Actual_Activity'
  , sum(b.Price_Actual) 'Actual_Price'
  , sum(b.Price_Plan) 'Plan_Price'
  , count(b.Instances) as 'record_count'
  , case when count(b.Instances) > 0 then 'yes' else 'no' end as Submitted
from (
  select o.*, t.Activity_Month
  from [ExampleDatabase].[dbo].[Organisation_Reference] as o
    cross apply (values (1),(2),(3),(4),(5),(6),(7),(8)) t(Activity_Month)
  ) as a
  left join [ExampleDatabase].[dbo].[Report_Submissions] b
    on a.Org_Id = b.Org_Id
   and a.Activity_Month = b.Activity_Month
   and ([exmaple_Code] like ('X') or [Example_Code] = 'X')
where a.Category_Flag = 1
  and a.Example_Code in ('X','X','X','X','X') 
group by
  a.Organisation_Name
, a.Org_Id
, b.Activity_Month

You could also cross join with a numbers/tally table, or use a common table expression to generate the range of numbers you need. I would recommend either of those options as well, especially if your logic was more compliCated.

If Report_Submissions contains all of the months you want in your query, you could cross join the distinct Activity_Months from that table to your Organisation_Reference table.

select
    a.Organisation_Name
  , a.Org_Id
  , a.Activity_Month
  , sum(b.Activity_Plan) 'Plan_Activity'
  , sum(b.Activity_Actual) 'Actual_Activity'
  , sum(b.Price_Actual) 'Actual_Price'
  , sum(b.Price_Plan) 'Plan_Price'
  , count(b.Instances) as 'record_count'
  , case when count(b.Instances) > 0 then 'yes' else 'no' end as Submitted
from (
  select o.*, t.Activity_Month
  from [ExampleDatabase].[dbo].[Organisation_Reference] as o
    cross join (select distinct Activity_Month from Report_Submissions) t
  ) as a
  left join [ExampleDatabase].[dbo].[Report_Submissions] b
    on a.Org_Id = b.Org_Id
   and a.Activity_Month = b.Activity_Month
   and ([exmaple_Code] like ('X') or [Example_Code] = 'X')
where a.Category_Flag = 1
  and a.Example_Code in ('X','X','X','X','X') 
group by
  a.Organisation_Name
, a.Org_Id
, b.Activity_Month