Creating Rows for Every Date between Two Date Fields Pulling Dating from MS SQL Server
Hello, I need to create a table that has a row for every date between two dates. It is very similar to this , but I need help with the syntax when pulling in data from a MS SQL sever. below is the framework for my script to pull the table. How can I integrate the part that creates the rows for the date?
LIB CONNECT TO 'Microsoft_SQL_Server_test.bi.com';
LOAD JobID, FirstDay, LastDay;
[Test_Table]: SELECT JobID, FirstDay, LastDay FROM "BD_Name".BI."Table_Name" WHERE FirstDate >= '1/1/2014';
Please let me know if there is any additional info I can provide to help?
I think this is getting close, but this has created two tables, is it possible to do it with one? Also, the calendar table only produced a range for a few dates in 2016, I have records that range from the year 2014-2020. Below is an example of what I'm after.
There could be some issues with the the date format, we need to check your data.
Please attach your Qvf with data so we can help you in a better way.
This script works in my case:
load * Inline [
for i=0 to NoOfRows('Test_Table')-1
Let varMinDate = Num(Peek('StartDay', i, 'Test_Table'));
Let varMaxDate = Num(Peek('EndDay', i, 'Test_Table'));
Let varJobID= Peek('JobID', i, 'Test_Table');
'$(varJobID)' as JobID,
Date($(varMinDate) + IterNo() - 1) as Date
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Drop Table Calendar;