====== pears.PopulatePayrollCalendar ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."PopulatePayrollCalendar"()
/* Application Maintained Function / Procedure - DO NOT EDIT*/
begin
  -- Clears out and then re-populates the Payroll Calendar table after any changes
  -- Clear Out Existing Records
  delete from "PayrollCalendar";
  call "sa_reset_identity"('PayrollCalendar','pears',0);
  -- Insert All Defined Dates
  insert into "PayrollCalendar"( "Year","TheDate","Week" ) 
    select "YearNumber",
      "TheDate",
      "Week"
      from(select "YearNumber",
          "date"("dateadd"("day",-6,"PayrollYear"."Week1Friday")) as "StartDate",
          "date"("dateadd"("day",-1,("dateadd"("week","PayrollYear"."NumberOfWeeks","StartDate")))) as "EndDate",
          "date"("dateadd"("day","row_num","StartDate")) as "TheDate",("row_num"/7)+1 as "Week"
          from "PayrollYear" cross join "sa_rowgenerator"(0,1000)
          where "TheDate" between "StartDate" and "EndDate") as "DefinedDates"
      order by "TheDate" asc;
  -- Remove Duplicates
  delete from
    "PayrollCalendar"
    where "PayrollCalendarID" = any(select "a"."PayrollCalendarID" from "PayrollCalendar" as "a" join "PayrollCalendar" as "b" on "a"."TheDate" = "b"."TheDate" and "a"."Year" < "b"."Year");
  -- Fill Gaps
  insert into "PayrollCalendar"( "TheDate","Year","Week" ) 
    select "Day",
      (select "max"("Year") from "PayrollCalendar" where "TheDate" < "Day") as "Year",
      (select "max"("Week") from "PayrollCalendar" where "TheDate" < "Day") as "Week"
      from "PayrollCalendar"
        right outer join(select "date"("dateadd"("day","row_num",(select "min"("TheDate") from "PayrollCalendar"))) as "Day"
          from "sa_rowgenerator"(0,(select "datediff"("day","min"("TheDate"),"max"("TheDate")) from "PayrollCalendar"))) as "AllDates"
        on "day" = "TheDate" where "TheDate" is null;
  -- Populate Other Fields
  update "PayrollCalendar" as "z"
    set "z"."Period" = ("z"."Year"*100)+"z"."Week",
    "WeekBeginning" = (select "min"("a"."TheDate") from "PayrollCalendar" as "a" where "a"."Year" = "z"."Year" and "a"."Week" = "z"."Week"),
    "WeekEnding" = (select "max"("a"."TheDate") from "PayrollCalendar" as "a" where "a"."Year" = "z"."Year" and "a"."Week" = "z"."Week");
  -- Populate PayrollCalendarWeek table 
  delete from "PayrollCalendarWeek";
  call "sa_reset_identity"('PayrollCalendarWeek','pears',0);
  insert into "PayrollCalendarWeek"( "Period","Year","Week","WeekBeginning","WeekEnding" ) 
    select distinct "Period","Year","Week","WeekBeginning","WeekEnding" from "PayrollCalendar" order by "period" asc
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."PopulatePayrollCalendar" IS 
{create procedure PopulatePayrollCalendar 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

()
BEGIN 
-- Clears out and then re-populates the Payroll Calendar table after any changes
-- Clear Out Existing Records
	delete from PayrollCalendar;
	call sa_reset_identity('PayrollCalendar','pears',0);
-- Insert All Defined Dates
	insert into PayrollCalendar(Year, TheDate, Week) 
	 select YearNumber, 
	 TheDate, 
	 Week 
	 from (
	 select 
	 YearNumber, 
	 date(dateadd(day,-6,PayrollYear.Week1Friday)) as StartDate, 
	 date(dateadd(day,-1,(dateadd(week,PayrollYear.NumberOfWeeks,StartDate))))as EndDate, 
	 date(dateadd(day,row_num,StartDate)) as TheDate, (row_num/7)+1 as Week 
	 from PayrollYear cross join sa_rowgenerator(0,1000)
	 where TheDate between StartDate and EndDate
	 ) DefinedDates
	 order by TheDate;
-- Remove Duplicates
	delete from 
	 PayrollCalendar 
	 where 
	 PayrollCalendarID in (select a.PayrollCalendarID from PayrollCalendar a join PayrollCalendar b on a.TheDate = b.TheDate and a.Year < b.Year);
-- Fill Gaps
	insert into PayrollCalendar(TheDate,Year,Week)
		 select 
			 Day, 
			(select max(Year) from PayrollCalendar where TheDate < Day) as Year, 
			(select max(Week) from PayrollCalendar where TheDate < Day) as Week 
		 from 
			PayrollCalendar 
			right outer join 
			(select 
				date(dateadd(day,row_num,(select min(TheDate) from PayrollCalendar))) Day 
				from sa_rowgenerator(0,(select datediff(day,min(TheDate),max(TheDate)) from PayrollCalendar))) as AllDates 
			on day = TheDate where TheDate is null;
-- Populate Other Fields
	update PayrollCalendar z 
	 set z.Period = (z.Year*100)+z.Week, 
	 WeekBeginning = (select min(a.TheDate) from PayrollCalendar a where a.Year=z.Year and a.Week=z.Week), 
	 WeekEnding = (select max(a.TheDate) from PayrollCalendar a where a.Year=z.Year and a.Week=z.Week);
-- Populate PayrollCalendarWeek table 
	delete from PayrollCalendarWeek;
	call sa_reset_identity('PayrollCalendarWeek','pears',0);
	insert into PayrollCalendarWeek(Period, Year, Week, WeekBeginning, WeekEnding) 
	   select distinct Period, Year, Week, WeekBeginning, WeekEnding from PayrollCalendar order by period; 
END
}
</code>