title: Insert Month into data
body: <p>I have a large section of code that I declare an insert date of at the beginning:</p>
  
  <pre><code>DECLARE @lcid INT 
  DECLARE @startdate DATETIME=@10
  DECLARE @enddate DATETIME=@11
  DECLARE @PitchJobTypeId INT
  DECLARE @ClientInvestmentJobTypeId INT
  DECLARE @AdministrationJobTypeId INT
  
  IF OBJECT_ID('tempdb..#data') IS NOT NULL
      DROP TABLE #data
  
  
  DECLARE @Billablejob TABLE(
  id INT IDENTITY(1,1),
  Jobid INT)
  
  
  DECLARE @Department table(
  Id INT IDENTITY(1,1),
  DepartmentId INT)
  
  IF @10 &gt; @11
  BEGIN
      SELECT 'Error in date selection' AS 'Error';
      GOTO ExitSp;
  END
  
  
  
  INSERT INTO @Billablejob(Jobid)
  SELECT distinct s.Element FROM dbo.Split(@30,',') AS s
  
  Insert into @Department(DepartmentId)
  SELECT distinct s.Element FROM dbo.Split(@5,',') AS s
  IF EXISTS(SELECT '' FROM @Department AS d WHERE d.DepartmentId=0) OR NOT EXISTS(SELECT '' FROM @Department AS d) BEGIN
      DELETE FROM @Department
      INSERT INTO @Department(DepartmentId)
      SELECT rsa.AfdID FROM RessSelAfd AS rsa --INNER JOIN @comp c ON c.compid=rsa.SelID  
  END
  
  IF @20 IS NULL SET @20=0
  
  
  
  CREATE TABLE #data(
  ID INT IDENTITY(1,1),
  [Name]  nvarchar(MAX),
  [Employee Type] nvarchar(MAX),
  [Basic Time] money,
  [Used Time] MONEY,
  [Holiday] MONEY,
  [Recovery] MONEY,
  [Illness] MONEY,
  [Billable Time] MONEY,
  [Non Billable Time] MONEY,
  [Pitch Time] MONEY,
  [Internal Time] MONEY,
  [Used Time2] MONEY,
  [Max Billable Hours] MONEY,
  [Available Hours] MONEY,
  [Office Hours %] INT,
  [Billable %] INT,
  [Non Billable %] INT,
  [Pitch %] INT,
  [Internal %] INT,
  [Billable Target %] INT,
  [Variance to Target %] INT,
  [Department] NVARCHAR(max),
  [Job Role] NVARCHAR(max),
  [Market] NVARCHAR(max),
  EmpId INT,
  DepartmentId INT,
  JobRoleId INT,
  MarketId INT,
  IsActive BIT,
  Hiredate DATETIME,
  Expirydate DATETIME,
  EmployeeType INT
  )
  
  IF @10&gt;@11 BEGIN
  SELECT 'Date selection error' Error
  GOTO ExitSp
  END
  
  SELECT @PitchJobTypeId = sj.JobTypeID FROM SysJobtype AS sj WHERE sj.JobtypeNavn='Pitch'
  
  IF @PitchJobTypeId IS NULL BEGIN
  SELECT 'Jobtype ''Pitch'' not found' Error
  GOTO ExitSp
  END
  
  SELECT @ClientInvestmentJobTypeId = sj.JobTypeID FROM SysJobtype AS sj WHERE sj.JobtypeNavn='Client Investment'
  
  IF @ClientInvestmentJobTypeId IS NULL BEGIN
  SELECT 'Jobtype ''Client Investment'' not found' Error
  GOTO ExitSp
  END
  
  SELECT @AdministrationJobTypeId = sj.JobTypeID FROM SysJobtype AS sj WHERE sj.JobtypeNavn='Administration'
  
  IF @AdministrationJobTypeId IS NULL BEGIN
  SELECT 'Jobtype ''Administration'' not found' Error
  GOTO ExitSp
  END
  
  
  
  
  SELECT @lcid=InterfaceLcid FROM ress WHERE ressid=@UserId
  
  INSERT INTO #data( empid,[Name],[Billable Target %],[JobRoleId],[Job Role],DepartmentId,MarketId,IsActive,Hiredate,Expirydate,EmployeeType,[Employee Type])
  SELECT emp.empid,EmpName,(SELECT TOP 1 ehp.ProfitTarget*100 FROM EmpHrsPrice AS ehp WHERE ehp.EmpId = emp.empid AND ehp.ValidFrom &lt;= @enddate ORDER BY ehp.ValidFrom desc),emppost.PostId,
  
  (SELECT ISNULL(nullif(ActLang.ActTxt,''), Act.ActTxt)
      FROM    Act 
          LEFT JOIN ActLang 
              ON Act.AID = ActLang.Aid
              AND ActLang.LCID=@LCID
      WHERE   Act.AID=emp.aid),emp.DepartmentId,emp.CompId,emp.IsActive,emp.Hiredate,emp.Expirydate,emp.EmployTypeId,CASE WHEN et.enumval=1 OR et.enumval=2 THEN 'Permanent' ELSE 'Freelance' END FROM emp 
  INNER JOIN ress ON ress.RessID=emp.EmpId 
  INNER JOIN @Department AS d ON d.DepartmentId = Emp.DepartmentId
  LEFT JOIN emppost ON emp.PostId=emppost.PostId
  LEFT JOIN EnumTable(467) AS et ON et.EnumVal=emp.EmployTypeId
  WHERE (IsActive=1 OR (Expirydate&gt;=@startdate and Expirydate&lt;=@enddate)) AND (CASE WHEN emp.EmployTypeId=2 THEN 1 ELSE emp.EmployTypeId END=@20 OR @20=0) AND Ress.UserAcountType&lt;&gt;5
  
  UPDATE d SET d.Department=rsa.AfdNavn FROM #data d INNER JOIN RessSelAfd AS rsa ON d.DepartmentId=rsa.AfdID
  
  UPDATE d SET d.Market=comp.CompName FROM #data d INNER JOIN comp ON d.MarketId=comp.compid
  
  UPDATE d SET d.[Basic Time]=data.HrsNorm FROM #data d INNER JOIN
  (SELECT cap.ResId,Sum(ISNULL(Cap.HrsNorm,0)+ISNULL(Cap.HrsHoli,0)) AS HrsNorm FROM cap INNER JOIN #data d ON d.empid=cap.ResId AND CAST(DayDate AS DATE) BETWEEN @startdate AND @enddate
  GROUP BY cap.ResId) data ON d.EmpId=data.ResId
  
  UPDATE d SET d.[Used Time]=data.UsedHours FROM #data d INNER JOIN
  (SELECT tr.EmpId,SUM(tr.RegHrs) AS UsedHours FROM TimeReg AS tr WHERE CAST(RegDate AS DATE) BETWEEN @startdate AND @enddate
  and reghrs IS NOT null
  GROUP BY tr.EmpId ) data ON d.EmpId=data.EmpId
  
  UPDATE d SET [Basic Time]=[Used Time],d.[Billable Target %]=100 FROM #data d WHERE EmployeeType=3
  
  UPDATE d SET [Basic Time]=ISNULL([Basic Time],0),[Used Time]=ISNULL([Used Time],0) FROM #data d 
  
  UPDATE d
  SET d.Holiday = data.UsedHours
  FROM #data d
  INNER JOIN
  (
      SELECT tr.EmpId,SUM(tr.RegHrs) AS UsedHours
      FROM TimeReg AS tr
      INNER JOIN RessFerie AS rf ON rf.RecId=tr.EmpHoliRecId
      INNER JOIN AbsenceCode AS ac ON ac.Id=rf.AbsenceID
      INNER JOIN job ON Job.JobID = tr.JobId
      INNER JOIN RessProjekter AS rp ON rp.ProjektID = Job.ProjektID
      INNER JOIN CUST ON cust.CustId=rp.RessID    
      WHERE CAST(RegDate AS DATE) BETWEEN @startdate AND @enddate AND RegHrs IS NOT NULL AND cust.CustTypeId=5 AND job.JobTypeID&lt;&gt;@PitchJobTypeId AND ac.Descr&lt;&gt;'Recovery Day'
      GROUP BY tr.EmpId
  ) data ON d.EmpId = data.EmpId;
  
  UPDATE d
  SET d.Recovery = data.UsedHours
  FROM #data d
  INNER JOIN
  (
      SELECT tr.EmpId,SUM(tr.RegHrs) AS UsedHours
      FROM TimeReg AS tr
      INNER JOIN RessFerie AS rf ON rf.RecId=tr.EmpHoliRecId
      INNER JOIN AbsenceCode AS ac ON ac.Id=rf.AbsenceID
      INNER JOIN job ON Job.JobID = tr.JobId
      INNER JOIN RessProjekter AS rp ON rp.ProjektID = Job.ProjektID
      INNER JOIN CUST ON cust.CustId=rp.RessID    
      WHERE CAST(RegDate AS DATE) BETWEEN @startdate AND @enddate AND RegHrs IS NOT NULL AND cust.CustTypeId=5 AND job.JobTypeID&lt;&gt;@PitchJobTypeId AND ac.Descr='Recovery Day'
      GROUP BY tr.EmpId
  ) data ON d.EmpId = data.EmpId;
  
  UPDATE d
  SET d.Illness = data.UsedHours
  FROM #data d
  INNER JOIN
  (
      SELECT tr.EmpId,SUM(tr.RegHrs) AS UsedHours
      FROM TimeReg AS tr
      INNER JOIN job ON Job.JobID = tr.JobId
      INNER JOIN RessProjekter AS rp ON rp.ProjektID = Job.ProjektID
      INNER JOIN CUST ON cust.CustId=rp.RessID
      WHERE CAST(RegDate AS DATE) BETWEEN @startdate AND @enddate AND RegHrs IS NOT NULL AND cust.CustTypeId=6 AND job.JobTypeID&lt;&gt;@PitchJobTypeId
      GROUP BY tr.EmpId
  ) data ON d.EmpId = data.EmpId;
  
  UPDATE d
  SET d.[Billable Time] = data.UsedHours
  FROM #data d
  INNER JOIN
  (
      SELECT tr.EmpId,SUM(tr.RegHrs) AS UsedHours
      FROM TimeReg AS tr
      INNER JOIN job ON Job.JobID = tr.JobId
      INNER JOIN RessProjekter AS rp ON rp.ProjektID = Job.ProjektID
      INNER JOIN CUST ON cust.CustId=rp.RessID
      LEFT JOIN @Billablejob AS bj ON job.JobID=bj.Jobid
      WHERE CAST(RegDate AS DATE) BETWEEN @startdate AND @enddate AND RegHrs IS NOT NULL AND (job.DebitFlg=1 OR bj.Jobid IS NOT NULL) AND (cust.CustTypeId=1 OR cust.CustTypeId=2) AND job.JobTypeID&lt;&gt;@PitchJobTypeId AND job.JobTypeID&lt;&gt;@ClientInvestmentJobTypeId AND job.JobTypeID&lt;&gt;@AdministrationJobTypeId
      GROUP BY tr.EmpId
  ) data ON d.EmpId = data.EmpId;
  
  UPDATE d
  SET d.[Non Billable Time] = data.UsedHours
  FROM #data d
  INNER JOIN
  (
      SELECT tr.EmpId,SUM(tr.RegHrs) AS UsedHours
      FROM TimeReg AS tr
      INNER JOIN job ON Job.JobID = tr.JobId
      INNER JOIN RessProjekter AS rp ON rp.ProjektID = Job.ProjektID
      INNER JOIN CUST ON cust.CustId=rp.RessID
      LEFT JOIN @Billablejob AS bj ON job.JobID=bj.Jobid
      WHERE CAST(RegDate AS DATE) BETWEEN @startdate AND @enddate AND RegHrs IS NOT NULL AND ((bj.Jobid IS NULL and job.DebitFlg=0 AND (cust.CustTypeId=1 OR cust.CustTypeId=2) AND job.JobTypeID&lt;&gt;@PitchJobTypeId AND job.JobTypeID&lt;&gt;@AdministrationJobTypeId)  OR job.JobTypeID=@ClientInvestmentJobTypeId)
      GROUP BY tr.EmpId
  ) data ON d.EmpId = data.EmpId;
  
  UPDATE d
  SET d.[Pitch Time] = data.UsedHours
  FROM #data d
  INNER JOIN
  (
      SELECT tr.EmpId,SUM(tr.RegHrs) AS UsedHours
      FROM TimeReg AS tr
      INNER JOIN job ON Job.JobID = tr.JobId
      INNER JOIN RessProjekter AS rp ON rp.ProjektID = Job.ProjektID
      INNER JOIN CUST ON cust.CustId=rp.RessID
      WHERE CAST(RegDate AS DATE) BETWEEN @startdate AND @enddate AND RegHrs IS NOT NULL AND tr.EmpHoliRecId IS NULL AND job.JobTypeID=@PitchJobTypeId
      GROUP BY tr.EmpId
  ) data ON d.EmpId = data.EmpId;
  
  UPDATE d
  SET d.[Internal Time] = data.UsedHours
  FROM #data d
  INNER JOIN
  (
      SELECT tr.EmpId,SUM(tr.RegHrs) AS UsedHours
      FROM TimeReg AS tr
      INNER JOIN job ON Job.JobID = tr.JobId
      INNER JOIN RessProjekter AS rp ON rp.ProjektID = Job.ProjektID
      INNER JOIN CUST ON cust.CustId=rp.RessID
      WHERE CAST(RegDate AS DATE) BETWEEN @startdate AND @enddate AND RegHrs IS NOT NULL AND tr.EmpHoliRecId IS NULL AND (((cust.CustTypeId=3 OR cust.CustTypeId=4) AND job.JobTypeID&lt;&gt;@PitchJobTypeId AND job.JobTypeID&lt;&gt;@ClientInvestmentJobTypeId) OR job.JobTypeID=@AdministrationJobTypeId)
      GROUP BY tr.EmpId
  ) data ON d.EmpId = data.EmpId;
  
  UPDATE d SET d.[Used Time2]=isnull(d.[Used Time],0)-isnull(d.Recovery,0) FROM #data d
  
  UPDATE d SET d.[Max Billable Hours]=ISNULL(d.[Used Time2],0)-ISNULL(d.Holiday,0)-ISNULL(d.Illness,0) FROM #data d
  
  UPDATE d SET d.[Available Hours]=ISNULL(d.[Basic Time],0)-ISNULL(d.Holiday,0)-ISNULL(d.Illness,0) FROM #data d
  
  UPDATE d SET d.[Office Hours %]=CAST(CASE WHEN ISNULL(d.[Available Hours],0)=0 THEN 0 ELSE d.[Max Billable Hours]/d.[Available Hours]*100 END AS INT) FROM #data d
  
  UPDATE d SET d.[Billable %]=CAST(CASE WHEN ISNULL(d.[Available Hours],0)=0 THEN CASE WHEN ISNULL(d.[Billable Time],0)=0 THEN 100 ELSE 0 END ELSE d.[Billable Time]/d.[Available Hours]*100 END AS INT) FROM #data d
  
  UPDATE d SET d.[Non Billable %]=CAST(CASE WHEN ISNULL(d.[Available Hours],0)=0 THEN 0 ELSE d.[Non Billable Time]/d.[Available Hours]*100 END AS INT) FROM #data d
  
  UPDATE d SET d.[Pitch %]=CAST(CASE WHEN ISNULL(d.[Available Hours],0)=0 THEN 0 ELSE d.[Pitch Time]/d.[Available Hours]*100 END AS INT) FROM #data d
  
  UPDATE d SET d.[Internal %]=CAST(CASE WHEN ISNULL(d.[Available Hours],0)=0 THEN 0 ELSE d.[Internal Time]/d.[Available Hours]*100 END AS INT) FROM #data d
  
  UPDATE d SET d.[Variance to Target %]=ISNULL(d.[Billable %],0)-ISNULL(d.[Billable Target %],0) FROM #data d
  
  
  SELECT d.Name,
         d.[Employee Type],
         d.[Basic Time],
         d.[Used Time] AS [Total Time posted],
         isnull(d.Holiday,0)+isnull(d.Illness,0) [Absence],
         d.Recovery,
         d.[Billable Time],
         d.[Non Billable Time],
         d.[Pitch Time],
         d.[Internal Time],
         isnull(d.[Used Time2],0) AS [Used Time],
         d.[Max Billable Hours],
         d.[Available Hours],
         CAST(d.[Office Hours %] AS NVARCHAR(50))+'%' AS [Office Hours %],
         CAST(d.[Billable %] AS NVARCHAR(50))+'%' AS [Billable %],
         CAST(d.[Non Billable %] AS NVARCHAR(50))+'%' AS [Non Billable %],
         CAST(d.[Pitch %] AS NVARCHAR(50))+'%' AS [Pitch %],
         CAST(d.[Internal %] AS NVARCHAR(50))+'%' AS [Internal %],
         CAST(d.[Billable Target %] AS NVARCHAR(50))+'%' AS [Billable Target %],
         CAST(d.[Variance to Target %] AS NVARCHAR(50))+'%' AS [Variance to Target %],
         d.Department,
         d.[Job Role],
         d.Market
         FROM #data d
  
  
  
  ExitSp:
  </code></pre>
  
  <p>This forces the user to pick a date range before pulling back the data.</p>
  
  <p>An example of the data received back is:</p>
  
  <p><a href="https://i.stack.imgur.com/9r1nQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9r1nQ.png" alt="enter image description here"></a></p>
  
  <p>What is the best way to then mark this data with the Month of the range the user picks?</p>
  
  <p>For example if they choose <strong>startdate 01/01/2019</strong> and <strong>enddate 16/01/2019</strong> all records would be marked with an extra column showing Jan 2019 or something similar.</p>
  
  <p>Additionally if there was a selection that spanned multiple months eg. <strong>01/01/2019 to 20/02/2019</strong> this would mark those relating to Jan and those relating to Feb in a new column?</p>
  
  <p>Example of desired result:</p>
  
  <p><a href="https://i.stack.imgur.com/GsQiZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GsQiZ.png" alt="enter image description here"></a></p>
  
  <p>Many thanks for any advice</p>