Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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