Sql-server – Get Date Limitation

There is no need to generate dates.

The following query will give you a list of SHORTCODES with no rows at all:

select SHORTCODE from shortcodes
except
select SHORTCODE from VWTBL_INDICATOR

The following query will give you the continuous ranges of MonthYear per SHORTCODE.

select      SHORTCODE
            ,min(MonthYear) as from_MonthYear
            ,max(MonthYear) as to_MonthYear
            ,count(*)       as months

from       (SELECT   SHORTCODE
                    ,MonthYear
                    ,row_number() over (partition by SHORTCODE order by MonthYear)  as rn

            From     VWTBL_INDICATOR
            ) t

group by    SHORTCODE
            ,DATEADD(month,-rn,MonthYear)   

order by    SHORTCODE
            ,from_MonthYear

If you wish you can use the following version which has an additional layer of information:

• missing_from_MonthYear + to_MonthYear: missing range in the middle
• ranges: Number of ranges per SHORTCODE (ranges>1 means you have gaps in the middle)
• range_seq: the sequential number of each SHORTCODE range
• is_first: Indication for the first range per SHORTCODE (check from_MonthYear to see if you are missing preceding dates)
• is_last: Indication for the last range per SHORTCODE (check to_MonthYear to see if you are missing following dates)

select      SHORTCODE
           ,from_MonthYear                                                                                  as exists_from_MonthYear
           ,to_MonthYear                                                                                    as exists_to_MonthYear
           ,dateadd (day,1,to_MonthYear)                                                                    as missing_from_MonthYear
           ,dateadd (day,-1,lead (from_MonthYear) over (partition by SHORTCODE order by from_MonthYear))    as missing_to_MonthYear
           ,count       (*) over (partition by SHORTCODE)                                                   as ranges
           ,row_number  ()  over (partition by SHORTCODE order by from_MonthYear)                           as range_seq
           ,case from_MonthYear when min(from_MonthYear) over (partition by SHORTCODE) then 1 end           as is_first
           ,case to_MonthYear   when max(to_MonthYear)   over (partition by SHORTCODE) then 1 end           as is_last

from       (select      SHORTCODE
                       ,min(MonthYear)  as from_MonthYear
                       ,max(MonthYear)  as to_MonthYear
                       ,count(*)        as months

            from       (SELECT      SHORTCODE
                                   ,MonthYear
                                   ,row_number() over (partition by SHORTCODE order by MonthYear)   as rn

                        From        VWTBL_INDICATOR
                        ) t

            group by    SHORTCODE
                       ,DATEADD(month,-rn,MonthYear)    
            ) t

order by    SHORTCODE
           ,from_MonthYear

Calendar month to financial month

First of all, you can do without all those nicely formatted CASEs and use simple date arithmetic instead.

Your financial year starts in May, which means the number of a month as a financial year month lags behind the number of that month as a calendar month by four. Similarly, the year value of a financial year changes to a new one four months later than that of the calendar year.

Therefore, to convert the current calendar year and month to the corresponding financial year and month, you just need to subtract four months from the current date and extract the year and the month from the result to match against Period and Year of Targets and Analysis. Your time-related filters, therefore, would look similar to this:

...
AND analysis.Period = MONTH(DATEADD(MONTH, -4, GETDATE()))
AND analysis.Year   = YEAR (DATEADD(MONTH, -4, GETDATE()))

Joining the results

Use your current queries (the monthly query, the yearly target query and the yearly actual data query) as derived tables and join them together on the employee ID (FeeEarnerRef). Do not use the Users inside the derived tables, as there is no need to join it several times when you can do that just once. In fact, you can use the Users table as the joining point for the complete query, something like this:

SELECT
  u.FullName,
  ...
FROM
  dbo.Users AS u
  INNER JOIN
  (  -- month target and analysis
    SELECT
      t.FeeEarnerRef,
      ...
    FROM
      dbo.Targets AS t
      INNER JOIN dbo.Analysis AS a ON t.FeeEarnerRef = a.FeeEarnerRef
        AND t.Period = a.Period
        AND t.Year   = a.Year
    WHERE
      t.Period = MONTH(DATEADD(MONTH, -4, GETDATE()))
      AND t.Year = YEAR(DATEADD(MONTH, -4, GETDATE()))
    GROUP BY
      t.FeeEarnerRef
  ) AS mta ON u.FeeEarnerRef = mta.FeeEarnerRef
  INNER JOIN
  (  -- year target
    SELECT
      t.FeeEarnerRef,
      ...
    FROM
      dbo.Targets AS t
    WHERE
      t.Year = YEAR(DATEADD(MONTH, -4, GETDATE()))
    GROUP BY
      t.FeeEarnerRef
  ) AS yt ON u.FeeEarnerRef = yt.FeeEarnerRef
  INNER JOIN
  (  -- year analysis
    SELECT
      a.FeeEarnerRef,
      ...
    FROM
      dbo.Analysis AS a
    WHERE
      a.Year = YEAR(DATEADD(MONTH, -4, GETDATE()))
    GROUP BY
      a.FeeEarnerRef
  ) AS ya ON u.FeeEarnerRef = ya.FeeEarnerRef
WHERE
  u.FullName NOT LIKE 'Z%'
  AND u.Department = 'IP'
;

I would also suggest that you not apply the formatting (the dbo.HoursMins function) in the derived tables. Ideally, you should not apply formatting in a SQL query at all; instead, you should delegate that job to the presentation layer. However, if you must do the formatting in the query, do that in the main SELECT only, applying it to the data returned by the derived tables.

Alternative method

The solution suggested above merely resolves the immediate issues you have faced trying to join various pieces of data in a single result set. There is a potentially better way to do the same calculations which allows the both Targets and Analysis to be scanned only once.

The idea is to aggregate both the yearly and the monthly portion of data in the same (sub)query, using a conditional when aggregating the monthly data. Basically, a query of that kind would look like this:

SELECT
  GroupingCriterion,
  YearData  = SUM(data),
  MonthData = SUM(CASE Period = @ThisPeriod THEN data END)
FROM
  YourTable
WHERE
  Year = @ThisYear
GROUP BY
  GroupingCriterion
;

You can use the above pattern to calculate separately the target data and the actual data and join them as derived tables to Users similarly to the first solution:

SELECT
  u.FullName,
  dbo.HoursMins(t.MonthTarget),
  dbo.HoursMins(t.MonthCurrent),
  dbo.HoursMins(t.MonthTarget - t.MonthCurrent),
  dbo.HoursMins(t.YearTarget),
  dbo.HoursMins(t.YearCurrent),
  dbo.HoursMins(t.YearTarget - t.YearCurrent)
FROM
  dbo.Users AS u
  INNER JOIN
  (  -- target
    SELECT
      t.FeeEarnerRef,
      YearTarget   = SUM(t.ChargeableTime),
      MonthTarget  = SUM(CASE t.Period
                           WHEN MONTH(DATEADD(MONTH, -4, GETDATE()))
                           THEN t.ChargeableTime
                         END)
    FROM
      dbo.Targets AS t
    WHERE
      t.Year = YEAR(DATEADD(MONTH, -4, GETDATE()))
    GROUP BY
      t.FeeEarnerRef
  ) AS t ON u.FeeEarnerRef = t.FeeEarnerRef
  INNER JOIN
  (  -- analysis
    SELECT
      a.FeeEarnerRef,
      YearCurrent  = SUM(a.ChargeableTime),
      MonthCurrent = SUM(CASE t.Period
                           WHEN MONTH(DATEADD(MONTH, -4, GETDATE()))
                           THEN a.ChargeableTime
                         END)
    FROM
      dbo.Analysis AS a
    WHERE
      a.Year = YEAR(DATEADD(MONTH, -4, GETDATE()))
    GROUP BY
      a.FeeEarnerRef
  ) AS ya ON u.FeeEarnerRef = ya.FeeEarnerRef
WHERE
  u.FullName NOT LIKE 'Z%'
  AND u.Department = 'IP'
;