Convert Sql Server stored procedure to Java

Question

I'm migrating old reporting systems to a modern Java code base and stumbled upon Microsoft Sql Server stored procedure that generated table with dates (Year, Period, WeekStarting, WeekEnding). I need to migrate this code into Java and make it dynamic, as opposed to generate it a table and take up space in the DB.

Seeking a help from a Sql Server expert to help me understand how those dates are derived, and especially numbers in the Period column

USE [Reporting]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GenDataforPeriodsTable]
    @enddate VARCHAR(10)
AS
    DECLARE @startdate VARCHAR(10)
BEGIN
    SET NOCOUNT ON; 

    SELECT @startdate = DATEADD(DAY, 1, MAX(WeekEnding)) FROM Periods;

    WITH CTE_DatesTable
    AS
    (
      SELECT CAST(@startdate as date) AS tempdate
      UNION ALL
      SELECT DATEADD(dd, 1, tempdate)
      FROM CTE_DatesTable
      WHERE DATEADD(dd, 1, tempdate) <= @enddate
    )
    INSERT INTO Periods (YEAR, Period, WeekStarting, WeekEnding)
    SELECT YEAR(tempdate) as Year, MONTH(DATEADD(DAY, -3, tempdate)) as Period, 
            DATEADD(DAY, -6, tempdate) as WeekStarting, tempdate as WeekEnding 
    FROM CTE_DatesTable 
    WHERE DATENAME(weekday, tempdate) = 'SUNDAY'        
    OPTION (MAXRECURSION 0)

END
GO

it generates table like this:

I was just thinking to provide a Year with Month and get a list of dates. Ex. if I provide year=2020, month=1, then I'll get a list with five sets of weekstarting and weekendings — Maksim, Feb 22 '20 at 22:01
Hm, so according to that procedure, 2019's last week ends with Dec 29th 2019 and the next week starts with Jan 1st. There are two days that belong to no week? Sounds like a bug. You can use the Calendar class to calculate these values or use the more modern java.time package. — Lothar, Feb 22 '20 at 22:28

score 3 · Accepted Answer · answered Feb 22 '20 at 22:36

Java 8 code:

import static java.time.DayOfWeek.SUNDAY;
import static java.time.temporal.TemporalAdjusters.nextOrSame;

import java.time.LocalDate;

static void genDataforPeriodsTable(LocalDate endDate) {
    String sql = "SELECT MAX(WeekEnding) FROM Periods";
    LocalDate maxWeekEnding = /* Result of running query */;
    genDataforPeriodsTable(maxWeekEnding.plusDays(1), endDate);
}

static void genDataforPeriodsTable(LocalDate startDate, LocalDate endDate) {
    System.out.println("Year Period WeekStarting WeekEnding");
    System.out.println("==== ====== ============ ==========");
    for (LocalDate tempdate = startDate.with(nextOrSame(SUNDAY));
                   tempdate.compareTo(endDate) <= 0;
                   tempdate = tempdate.plusDays(7)) {
        int year = tempdate.getYear();
        int period = tempdate.minusDays(3).getMonthValue();
        LocalDate weekStarting = tempdate.minusDays(6);
        LocalDate weekEnding = tempdate;
        System.out.printf("%4d %-6d %-12s %s%n", year, period, weekStarting, weekEnding);
    }
}

Test

genDataforPeriodsTable(LocalDate.of(2019, 10, 25), LocalDate.of(2020, 5, 5));

Output

The output here is in reverse order of what you included, but the data is the same, except for that start date error in the first week of 2020, as commented by Lothar.

Year Period WeekStarting WeekEnding
==== ====== ============ ==========
2019 10     2019-10-21   2019-10-27
2019 10     2019-10-28   2019-11-03
2019 11     2019-11-04   2019-11-10
2019 11     2019-11-11   2019-11-17
2019 11     2019-11-18   2019-11-24
2019 11     2019-11-25   2019-12-01
2019 12     2019-12-02   2019-12-08
2019 12     2019-12-09   2019-12-15
2019 12     2019-12-16   2019-12-22
2019 12     2019-12-23   2019-12-29
2020 1      2019-12-30   2020-01-05
2020 1      2020-01-06   2020-01-12
2020 1      2020-01-13   2020-01-19
2020 1      2020-01-20   2020-01-26
2020 1      2020-01-27   2020-02-02
2020 2      2020-02-03   2020-02-09
2020 2      2020-02-10   2020-02-16
2020 2      2020-02-17   2020-02-23
2020 2      2020-02-24   2020-03-01
2020 3      2020-03-02   2020-03-08
2020 3      2020-03-09   2020-03-15
2020 3      2020-03-16   2020-03-22
2020 3      2020-03-23   2020-03-29
2020 4      2020-03-30   2020-04-05
2020 4      2020-04-06   2020-04-12
2020 4      2020-04-13   2020-04-19
2020 4      2020-04-20   2020-04-26
2020 4      2020-04-27   2020-05-03

Nice one. The more I look at examples and apply new (to Java 8) LocalDate types, the more I'm getting convinced that they are easier to deal with than good old Date() :). Thank you — Maksim, Feb 23 '20 at 07:01

Convert Sql Server stored procedure to Java

1 Answers1