title: SSRS budget table joined to cases of demo data
body: <p>This is probalby a simple question, but I don't fully understand what is going wrong..with the method I am using..SoI've gone back to the original two tables that need to be used. So if you can suggest how to tackle this from the outset, would be greatly appreciated. 
  One table is cases, that holds individual case details, with dates that can be used to calculate, how long a hire went on for and the rate applied. It has a Source ID, that it has in common with a budget table. Every case has a Source ID. The other table is budget by Source ID for each calendar month. Need to compare the actual income from the dates, and rates in cases and add them and compare, the actual against budget.</p>
  
  <p>The dates used to calculate the duration is the actual start date to the actual end date, but to further complicate matters, only the days of hire in the month of interest are required to be returned. So if a hire started earlier , than the month in question, just those dates are to be compared against the budget for that month.
  The only other requirement is to set the rate dependent on the name  of the company, either contract rate or hire daily rate. But i tried to keep it simple as possible.</p>
  
  <p>It's all demo data...</p>
  
  <pre><code>USE [AutoHires]
  GO
  /****** Object:  Table [dbo].[tblBudget]    Script Date: 22/01/2018 09:57:21 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[tblBudget](
      [Ref] [int] IDENTITY(1,1) NOT NULL,
      [SourceID] [int] NOT NULL,
      [BudgetRevenue] [money] NULL,
      [BudgetHires] [int] NULL,
      [BudgetSold] [int] NULL,
      [BudgetInstructions] [int] NULL,
      [SourceGroup] [int] NULL,
      [MonthFrom] [datetime] NULL,
      [MonthTo] [datetime] NULL,
      [Name] [nvarchar](50) NULL,
   CONSTRAINT [PK_Budget] PRIMARY KEY CLUSTERED 
  (
      [Ref] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  SET IDENTITY_INSERT [dbo].[tblBudget] ON 
  
  INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (1, 1, 300.0000, 12, 45, 80, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEA')
  INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (2, 2, 400.0000, 15, 50, 90, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEB')
  INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (3, 3, 700.0000, 4, 6, 50, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEH')
  INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (4, 4, 323.0000, 6, 18, 50, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEG')
  INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (5, 5, 567.0000, 6, 45, 56, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEF')
  INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (6, 6, 566.0000, 12, 56, 67, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEE')
  INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (7, 7, 566.0000, 23, 12, 44, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCED')
  INSERT [dbo].[tblBudget] ([Ref], [SourceID], [BudgetRevenue], [BudgetHires], [BudgetSold], [BudgetInstructions], [SourceGroup], [MonthFrom], [MonthTo], [Name]) VALUES (8, 8, 668.0000, 3, 44, 23, NULL, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-31 00:00:00.000' AS DateTime), N'SOURCEC')
  SET IDENTITY_INSERT [dbo].[tblBudget] OFF
  </code></pre>
  
  <p>Second table of cases.</p>
  
  <pre><code> GO
  SET ANSI_PADDING ON
  GO
  CREATE TABLE [dbo].[tblCase](
      [PK_RefNo] [int] IDENTITY(1,1) NOT NULL,
      [SourceID] [int] NOT NULL,
      [DateInstructed] [datetime] NOT NULL,
      [DateClosed] [datetime] NULL,
      [Insurer_tpi] [varchar](50) NULL,
      [HireDailyRate] [money] NULL,
      [ActualStartDate] [datetime] NULL,
      [ActualFinishDate] [datetime] NULL,
      [HireContractRate] [money] NULL,
      [DailyRate] [money] NULL,
      [AdditionalCosts] [money] NULL,
   CONSTRAINT [PK_tblCase] PRIMARY KEY CLUSTERED 
  (
      [PK_RefNo] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  SET ANSI_PADDING OFF
  GO
  SET IDENTITY_INSERT [dbo].[tblCase] ON 
  
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (1, 1, CAST(N'2017-10-09 00:00:00.000' AS DateTime), CAST(N'2017-12-13 00:00:00.000' AS DateTime), N'avg', 2.0000, CAST(N'2017-11-14 00:00:00.000' AS DateTime), CAST(N'2017-12-19 00:00:00.000' AS DateTime), 2.0000, 1.0000, 3.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (2, 1, CAST(N'2017-01-10 00:00:00.000' AS DateTime), CAST(N'2018-01-16 00:00:00.000' AS DateTime), N'Bull', 4.0000, CAST(N'2017-01-03 00:00:00.000' AS DateTime), NULL, 4.0000, 3.0000, 1.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (3, 3, CAST(N'2017-12-13 00:00:00.000' AS DateTime), CAST(N'2018-01-01 00:00:00.000' AS DateTime), N'bit', 3.0000, CAST(N'2017-12-13 00:00:00.000' AS DateTime), CAST(N'2017-12-27 00:00:00.000' AS DateTime), 5.0000, 3.0000, 1.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (4, 4, CAST(N'2017-12-02 00:00:00.000' AS DateTime), CAST(N'2018-01-01 00:00:00.000' AS DateTime), N'Avast', 5.0000, CAST(N'2017-12-15 00:00:00.000' AS DateTime), CAST(N'2017-12-27 00:00:00.000' AS DateTime), 5.0000, 4.0000, 2.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (5, 5, CAST(N'2017-12-08 00:00:00.000' AS DateTime), NULL, N'Kasp', 6.0000, CAST(N'2017-12-22 00:00:00.000' AS DateTime), CAST(N'2018-01-26 00:00:00.000' AS DateTime), 6.0000, 4.0000, 2.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (6, 1, CAST(N'2017-12-06 00:00:00.000' AS DateTime), CAST(N'2018-01-01 00:00:00.000' AS DateTime), N'avg', 5.0000, CAST(N'2017-10-10 00:00:00.000' AS DateTime), CAST(N'2017-12-27 00:00:00.000' AS DateTime), 6.0000, 4.0000, 2.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (7, 1, CAST(N'2017-09-01 00:00:00.000' AS DateTime), CAST(N'2018-01-01 00:00:00.000' AS DateTime), N'avg', 5.0000, CAST(N'2017-11-01 00:00:00.000' AS DateTime), CAST(N'2017-12-08 00:00:00.000' AS DateTime), 5.0000, 4.0000, 1.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (8, 4, CAST(N'2017-12-05 00:00:00.000' AS DateTime), CAST(N'2018-01-11 00:00:00.000' AS DateTime), N'Avast', 6.0000, CAST(N'2017-11-30 00:00:00.000' AS DateTime), CAST(N'2018-01-03 00:00:00.000' AS DateTime), 4.0000, 3.0000, 1.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (9, 4, CAST(N'2017-12-05 00:00:00.000' AS DateTime), CAST(N'2018-01-10 00:00:00.000' AS DateTime), N'Panda', 6.0000, CAST(N'2017-12-04 00:00:00.000' AS DateTime), CAST(N'2017-12-30 00:00:00.000' AS DateTime), 6.0000, 4.0000, 2.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (10, 5, CAST(N'2017-01-10 00:00:00.000' AS DateTime), CAST(N'2018-01-01 00:00:00.000' AS DateTime), N'Avast', 7.0000, CAST(N'2017-12-13 00:00:00.000' AS DateTime), CAST(N'2018-01-24 00:00:00.000' AS DateTime), 6.0000, 4.0000, 1.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (11, 8, CAST(N'2017-05-17 00:00:00.000' AS DateTime), CAST(N'2018-09-07 00:00:00.000' AS DateTime), N'Bull', 3.0000, CAST(N'2017-12-04 00:00:00.000' AS DateTime), CAST(N'2017-12-26 00:00:00.000' AS DateTime), 5.0000, 3.0000, 2.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (12, 2, CAST(N'2017-01-16 00:00:00.000' AS DateTime), CAST(N'2017-12-20 00:00:00.000' AS DateTime), N'bit', 5.0000, CAST(N'2017-12-16 00:00:00.000' AS DateTime), CAST(N'2017-12-20 00:00:00.000' AS DateTime), 6.0000, 5.0000, 2.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (13, 8, CAST(N'2017-12-05 00:00:00.000' AS DateTime), CAST(N'2017-12-20 00:00:00.000' AS DateTime), N'Avast', 6.0000, CAST(N'2001-03-01 00:00:00.000' AS DateTime), CAST(N'2017-12-27 00:00:00.000' AS DateTime), 6.0000, 1.0000, 2.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (14, 8, CAST(N'2017-12-12 00:00:00.000' AS DateTime), CAST(N'2017-12-19 00:00:00.000' AS DateTime), N'Kasp', 5.0000, CAST(N'2017-11-30 00:00:00.000' AS DateTime), CAST(N'2018-01-02 00:00:00.000' AS DateTime), 6.0000, 3.0000, 1.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (15, 8, CAST(N'2017-12-04 00:00:00.000' AS DateTime), CAST(N'2017-12-28 00:00:00.000' AS DateTime), N'Panda', 6.0000, CAST(N'2017-11-30 00:00:00.000' AS DateTime), CAST(N'2018-01-03 00:00:00.000' AS DateTime), 5.0000, 3.0000, 2.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (16, 2, CAST(N'2017-12-05 00:00:00.000' AS DateTime), CAST(N'2017-12-20 00:00:00.000' AS DateTime), N'Avast', 6.0000, CAST(N'2017-12-01 00:00:00.000' AS DateTime), CAST(N'2017-12-19 00:00:00.000' AS DateTime), 5.0000, 1.0000, 2.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (17, 6, CAST(N'2017-12-05 00:00:00.000' AS DateTime), CAST(N'2018-01-10 00:00:00.000' AS DateTime), N'Avast', 6.0000, CAST(N'2017-11-30 00:00:00.000' AS DateTime), CAST(N'2017-12-30 00:00:00.000' AS DateTime), 5.0000, 1.0000, 3.0000)
  INSERT [dbo].[tblCase] ([PK_RefNo], [SourceID], [DateInstructed], [DateClosed], [Insurer_tpi], [HireDailyRate], [ActualStartDate], [ActualFinishDate], [HireContractRate], [DailyRate], [AdditionalCosts]) VALUES (18, 5, CAST(N'2017-12-05 00:00:00.000' AS DateTime), CAST(N'2018-01-10 00:00:00.000' AS DateTime), N'Avast', 6.0000, CAST(N'2017-11-30 00:00:00.000' AS DateTime), CAST(N'2018-01-03 00:00:00.000' AS DateTime), 6.0000, 3.0000, 1.0000)
  SET IDENTITY_INSERT [dbo].[tblCase] OFF
  </code></pre>
  
  <p>If hope you understand the question.</p>