How to Determine Values for Missing Months Based on Data of Previous Months in T-Sql

How to Determine Values for Missing Months based on Data of Previous Months in T-SQL

I'd start by building a Numbers table holding sequential integers from 1 to a million or so. They come in really handy once you get the hang of it.

For example, here is how to get the 1st of every month in 2008:

select firstOfMonth = dateadd( month, n - 1, '1/1/2008')
from Numbers
where n <= 12;

Now, you can put that together using OUTER APPLY to find the most recent transaction for each date like so:

with Dates as (
    select firstOfMonth = dateadd( month, n - 1, '1/1/2008')
    from Numbers
    where n <= 12
)
select d.firstOfMonth, t.TransactionValue
from Dates d
outer apply (
    select top 1 TransactionValue
    from Transactions
    where TransactionDate <= d.firstOfMonth
    order by TransactionDate desc
) t;

This should give you what you're looking for, but you might have to Google around a little to find the best way to create the Numbers table.

Detect if a month is missing and insert them automatically with a select statement (MSSQL)

You need a recursive CTE to get all the years in the table (and the missing ones if any) and another one to get all the month numbers 1-12.

A CROSS join of these CTEs will be joined with a LEFT join to the table and finally filtered so that rows prior to the first year/month and later of the last year/month are left out:

WITH
  limits AS (
    SELECT MIN(year) min_year, -- min year in the table
           MAX(year) max_year, -- max year in the table
           MIN(DATEFROMPARTS(year, monthnum, 1)) min_date, -- min date in the table 
           MAX(DATEFROMPARTS(year, monthnum, 1)) max_date  -- max date in the table
    FROM tablename
   ),
  years(year) AS ( -- recursive CTE to get all the years of the table (and the missing ones if any)
    SELECT min_year FROM limits
    UNION ALL
    SELECT year + 1 
    FROM years
    WHERE year < (SELECT max_year FROM limits)
  ),  
  months(monthnum) AS ( -- recursive CTE to get all the month numbers 1-12
    SELECT 1 
    UNION ALL
    SELECT monthnum + 1
    FROM months
    WHERE monthnum < 12
  )
SELECT y.year, m.monthnum,
       DATENAME(MONTH, DATEFROMPARTS(y.year, m.monthnum, 1)) month,
       COALESCE(value, 0) value
FROM months m CROSS JOIN years y
LEFT JOIN tablename t 
ON t.year = y.year AND t.monthnum = m.monthnum
WHERE DATEFROMPARTS(y.year, m.monthnum, 1) 
      BETWEEN (SELECT min_date FROM limits) AND (SELECT max_date FROM limits)
ORDER BY y.year, m.monthnum 

See the demo.

SQL Server query to get the data from previous month if the column value is not present in next month

This answers the original version of the question.

Assuming that month is stored as a date with the first first day of the month, then a simple method uses recursive CTEs:

with cte as (
      select restaurant, num_items, month,
             dateadd(month, -1,
                     coalesce(lead(month) over (partition by restaurant order by month),
                              max(month) over ()
                             )
                    ) as end_month
      from t
      union all
      select restaurant, num_items, dateadd(month, 1, month), end_month
      from cte
      where month < end_month
     )
select *
from cte
order by restaurant, month;

Here is a db<>fiddle.

GROUP BY & SUM of values with missing MONTHS

You need to move condtition to ON:

-- ...
SELECT 
  [Period] = CONVERT(VARCHAR(4),YEAR(d.d)) +'-'+ CONVERT(VARCHAR(2), MONTH(d.d)),
  QtyTotal = ISNULL(SUM(o.QEXIT),0)
FROM d LEFT OUTER JOIN VE_STOCKTRANS AS o
  ON o.TRANSDATE >= d.d

  AND o.TRANSDATE < DATEADD(MONTH, 1, d.d)
  AND STOCKID = 6000 AND TRANSTYPE = 3553   -- here
GROUP BY d.d
ORDER BY d.d;

TSQL - fill out missing months with totals

All you need to do is to transform the rows from Amount so that they encompass the range in which they're valid and then join them to the calendar table.

The rows will implicitly have the start date in the YearMonth column. To get the cutoff date, you can use the LEAD window function to get the date from the next row in order. If there is no next row, then just add 1 to the current row's YearMonth so that only that row is used.

SELECT amt.Employee, amt.Reference_no, cal.YearMonth, amt.Amount
FROM (
  SELECT *, 
    LEAD(YearMonth, 1, YearMonth + 1)
      OVER (PARTITION BY Employee, Reference_no ORDER BY YearMonth) [Cutoff]
  FROM Amount
) amt
INNER JOIN Calendar cal ON cal.YearMonth >= amt.YearMonth AND cal.YearMonth < amt.Cutoff

How to fill data for missing months for more than 1 year

If you have a Projects table in your database:

select ProjectNo, Period, IsNull(wip, 0) wip , IsNull(billing, 0) billing
from Projects d1
outer apply (
    select m.*,d2.wip, d2.billing
    from Months m
    left join ProjectReportData d2 on m.Period = d2.Period and d2.ProjectNo=d1.ProjectNo
) mm
order by 1, 2 desc

If you don't have a Projects table in your database:

select ProjectNo, Period, IsNull(wip, 0) wip , IsNull(billing, 0) billing
from (
    select distinct ProjectNo
    from ProjectReportData 
) d1
outer apply (
    select m.*,d2.wip, d2.billing
    from Months m
    left join ProjectReportData d2 on m.Period = d2.Period and d2.ProjectNo=d1.ProjectNo
) mm
order by 1, 2 desc

T-SQL Running Monthly Totals Including Missing Months

DECLARE @start_date date, @end_date date
SELECT @start_date='2012-04-01',@end_date='2013-03-31'
;WITH xo AS
(
    SELECT @start_date AS cte_start_date
    UNION ALL
    SELECT DATEADD(MONTH, 1, cte_start_date)
    FROM xo
    WHERE DATEADD(MONTH, 1, cte_start_date) <= @end_date   
), x as (
    select *,row_number() over (order by cte_start_date) monthno
  from xo
  )
,  y as (
  select distinct handler from test
)
SELECT y.handler, datename(mm,x.cte_start_date), x.monthno
,(select sum(mtd) from test a where a.handler=y.handler and a.monthno<=x.monthno) mtd
FROM y
cross join x
order by 1,3

see example on SQLFiddle http://sqlfiddle.com/#!3/7d483/15

---

Related Topics