Using With Clause in Load Statement

[Stefan_Weber]

Hello Qlikview Community,

I hope I am not bothering you with all those questions, but I got one more:

I am trying to Load the following statement:

SQL

DECLARE @fromDate DATE = '2008-01-01',
DECLARE @toDate DATE = convert(nvarchar(10),DATEADD(Day, -1, Dateadd(Year, Datediff(Year,0,Getdate())+1,0)), 120),
WITH dates AS(
SELECT [ProductionDate] = @fromDate,
[Year] = DATEPART(year,@fromDate),
[Month] = DATEPART(month,@fromDate),
[Week] = DATEPART(week,@fromDate),
[Day] = DATEPART(day, @fromDate),
[YearMonth] = CONVERT(nvarchar(7),@fromDate,120),
[YearMonthExclude] = CONVERT(nvarchar(7),@fromDate,120)

UNION ALL

SELECT [ProductionDate] = DATEADD(DAY, 1, [ProductionDate]),
[Year] = DATEPART(year,DATEADD(DAY, 1, [ProductionDate])),
[Month] = DATEPART(month,DATEADD(DAY, 1, [ProductionDate])),
[Week] = DATEPART(week,DATEADD(DAY, 1, [ProductionDate])),
[Day] = DATEPART(day, DATEADD(DAY, 1, [ProductionDate])),
[YearMonth] = CONVERT(nvarchar(7),DATEADD(DAY, 1, [ProductionDate]),120),
[YearMonthExclude] = CONVERT(nvarchar(7),DATEADD(DAY, 1, [ProductionDate]),120)

FROM dates WHERE DATEADD(DAY, 1, [ProductionDate]) <= @toDate
)

SELECT [ProductionDate],
[Year],
[Month],
[Week],
[Day],
[YearMonth],
[YearMonthExclude]
FROM dates OPTION (maxrecursion 10000);

Unfortunately With requires a semicolon which terminates the SQL statement.

Does anyone of you have an idea how I could get the required results.

(Dates from 01.01.2018 until the end of the current year seperated in Day, Week, Month etc)

Without using the With statement?

Appreciate your support.

Stefan

[Stefan_Weber · Accepted solution]

Hello,

I solved it for this case by using the following statement.

LET vStartDate = DATE(MakeDate(2008, 01, 01),'YYYY-MM-DD');
LET vEndDate =  DATE(MakeDate(YEAR(NOW()), 12, 31), 'YYYY-MM-DD');

DO

  LET vProductionDate = DATE('$(vStartDate)','YYYY-MM-DD');
  LET vYear = NUM(YEAR('$(vStartDate)'));
  LET vMonth = NUM(MONTH('$(vStartDate)'),00);
  LET vWeek = NUM(WEEK('$(vStartDate)'));
  LET vDay = NUM(DAY('$(vStartDate)'),00);
  LET vYearMonth = '$(vYear)' & '$(vMonth)';
  LET vYearMonthExclude = '$(vYear)' & '$(vMonth)';

  PRODUCTION_DATES:
  Load * inline [ProductionDate, Year, Month, Week, Day, YearMonth, YearMonthExclude

                 $(vProductionDate), $(vYear), $(vMonth), $(vWeek), $(vDay), $(vYearMonth), $(vYearMonthExclude)];


  LET vStartDate = DATE('$(vStartDate)' + 1,'YYYY-MM-DD');
 

LOOP WHILE '$(vStartDate)' <= '$(vEndDate)'

Regards

Stefan