Блог

Что я хочу сделать, так это дублировать предыдущую строку, если в ней отсутствуют данные за месяц для этого местоположения. При добавлении столбца, который добавлял бы дату за этот месяц, которая отсутствовала. Например, если бы в ней отсутствовал сентябрь, в новом столбце было бы указано 9/1/2018.

Итак, небольшая справка Я просматриваю информацию о коллекциях для тонны разных местоположений, которые по большей части собираются за каждый месяц. Но иногда за определенный месяц нет коллекций, и в этом случае мы хотим дублировать данные за отсутствующий месяц из предыдущего месяца. Я думал, что мог бы найти месяцы, в которых не было сбора, создав Month Diff столбец. Тогда мне просто нужно вставить одну строку в месяц diff. Поэтому, если разница составляет 3 месяца, я бы вставил три новые строки и добавил дополнительный столбец, в котором была бы указана дата за этот месяц.

Вот код, который у меня есть на данный момент, но я застрял на добавлении строк, и я не уверен, возможно ли это вообще.

 Select  
  Location_ID, 
  Convert(Date,CONVERT(varchar(10),Collect_Month_Key,101)) as Collect_Date, 
  Calc_Gross_Totals, 
  Loc_Country, 
  CONVERT(varchar(8),Collect_Month_Key) '-' Location_ID as [Unique Key],
  MONTH(Convert(Date,CONVERT(varchar(10),Collect_Month_Key,101))) as MONTH, 
  YEAR(Convert(Date,CONVERT(varchar(10),Collect_Month_Key,101))) as 'YEAR',
  ROW_NUMBER() OVER(PARTITION BY Location_ID '-' left(Collect_Month_Key,4) ORDER BY Collect_Month_Key ASC)  as 'INDEX',
Cast(
    Case 
        when MONTH(Convert(Date,CONVERT(varchar(10),Collect_Month_Key,101))) > ROW_NUMBER() OVER(PARTITION BY Location_ID '-' left(Collect_Month_Key,4) ORDER BY Collect_Month_Key ASC) 
        Then MONTH(Convert(Date,CONVERT(varchar(10),Collect_Month_Key,101))) - ROW_NUMBER() OVER(PARTITION BY Location_ID '-' left(Collect_Month_Key,4) ORDER BY Collect_Month_Key ASC) 
        Else 0 
    End as bit) as 'Month Diff'
From FT_GPM_NPM_CYCLES AS cyc
INNER JOIN LU_Location AS loc ON cyc.Lu_Loc_Key = loc.LU_Loc_Key
INNER JOIN LU_Loc_Country AS cty ON loc.LU_Loc_Country_Key = cty.LU_Loc_Country_Key
Where 
  Collect_Month_Key <> -1 and 
  Convert(Date,CONVERT(varchar(10),Collect_Month_Key,101)) >= '2016-1-1'
Order By 
  Location_ID, 
Collect_Date;`
  

Прилагается мой вывод в виде изображения, чтобы дать представление о том, как это должно выглядеть.

Ну, прежде всего, подсказка о получении полного списка месяцев — для этого вы можете использовать рекурсивный CTE, см. Начало примера. дата 2018 — это первый месяц, 2020-01-01 — это первый месяц, который вы не хотите видеть на своем графике.

Второй CTE — фиктивный «фактические данные отчета, только те месяцы, в которых они существуют». Пропустите это пока.

Как только у вас будет полный список месяцев, используйте условие datediff, аналогичное приведенному ниже примеру, чтобы помочь вам с внешним присоединением этого измерения к вашей таблице данных. На этом этапе вам нужна только дата отчета, а не столбцы данных.

Теперь вы можете использовать функцию window, чтобы выяснить, в каком месяце будут данные для пропусков внешнего соединения (см. MAX ... OVER ... Предложение ниже). Обратите внимание, что при этом используется поведение этой оконной функции по умолчанию — МАКСИМУМ по сравнению с предыдущими строками.

Далее вам просто нужно вернуться к исходным данным, включая столбцы данных.

Пример:

 WITH all_months(monthStart) AS (
    SELECT CAST('2018-01-01' AS date)
     UNION ALL
     SELECT DATEADD(month, 1, monthStart)
     FROM all_months
     WHERE DATEADD(month, 1, monthStart) < '2020-01-01'
    )
, cte_data as (
    select /* LocationID, */ DATEADD(day, 3, monthStart) as reportDate, 'zum-zum_data_' CAST(monthStart as varchar) as actual_data
from all_months
WHERE datediff(month, SYSDATETIME(), monthStart) %3 =0 
)
, cte_data_join as (
select /* LocationID, */ monthStart, reportDate 
from all_months
    LEFT JOIN cte_data ON (datediff(month, cte_data.reportDate, all_months.monthStart )= 0) 
)

, cte_month_source as (
select *, max(report_date) 
            over (/* PARTITION BY LocationID*/ order by monthStart) as source_date
from cte_data_join
)

select /* LocationID, */ cte_month_source.monthStart as reporting_month, source_date as report_data_date, actual_data
from cte_month_source 
join cte_data ON (cte_month_source.source_date = cte_data.reportDate) 
ORDER BY monthStart
  

Я упростил ваш пример, чтобы сосредоточиться на дате сбора данных и годе / месяце аудита. Сведения о дате сбора — это то, что дублируется, когда год / месяц аудита не существует.

 begin
    -- simplified table
    create table    #collect    (
                                    Coll_Dt     date
                                    ,Val        int
                                    ,Aud_Yr     int
                                    ,Aud_Mth    int
                                )

    -- adding data
    insert into #collect
    values       ('2018-01-01',1,2018,1)
                ,('2018-02-01',2,2018,2)
                ,('2018-03-01',3,2018,3)
                ,('2018-05-01',5,2018,5)
                ,('2018-06-01',6,2018,6)
                ,('2018-07-01',7,2018,7)
                ,('2018-08-01',8,2018,8)
                ,('2018-12-01',12,2018,12)


    -- adding row number to determine where listing starts
    select      row_number() over (order by aud_yr,aud_mth) pid
                ,*
    into        #wrk
    from        #collect

end


declare @i      int = 1
        ,@i2    int
        ,@max   int = (select max(pid) from #wrk)
        ,@diff  int
        ,@rows  int

while @i <= @max
begin

    -- if @i = 1 then it is the first record and there's nothing to compare to
    if @i > 1
    begin
        -- determining the difference between current and prior collections
        select      @diff = datediff(month,b.coll_dt,a.coll_dt)
        from        #wrk    a
        outer apply (
                        select      top 1
                                    *
                        from        #wrk    b
                        where       b.Coll_Dt < a.Coll_Dt
                        order by    b.Coll_Dt desc
                    )       b
        where       a.pid = @i

        if @diff > 1
        begin
            -- number of rows to be added
            set @rows = @diff - 1

            -- resetting incrementor
            set @i2 = 1

            -- adding new rows
            while @i2 <= @rows
            begin
                insert into #collect
                select      Coll_Dt
                            ,Val
                            ,year(dateadd(month,@i2 * -1,coll_dt))
                            ,month(dateadd(month,@i2 * -1,coll_dt))
                from        #wrk
                where       pid = @i


                -- incrementing to exit loop and add additional rows, if more than 1 row is needed
                set @i2 = @i2   1
            end

        end

    end

    -- incrementing loop
    set @i = @i  1
end

select * from #collect order by aud_yr, aud_Mth


-- cleaning db
drop table  #collect
            ,#wrk