I have a query that results in the table shown below:
select * from (select [iKey], [StartDate], [FirstFCDate] from (SELECT [iKey], min([Date]) as [FirstFCDate] from dbo.factFC group by [iKey]) as gp left outer join (select [StartDate], [Key] from dbo.iKeys) dw on gp.iKey = dw.[Key]) ft
Table1
iKey StartDate FirstFCDate
101 2017-01-13 2017-04-01
52 2016-11-11 2017-04-01
21 2017-02-23 2017-04-01
19 2014-01-21 2017-05-01
34 2016-08-18 2017-07-01
What I am trying to do is insert a row into my fact table ( a separate table called dbo.factProd) so that on that table there is a row for every Date between the start date and the FirstFCDate.
Currently, my fact table looks like this:
factProd
ID iKey Date pAmount fcKey
1 101 2017-04-01 123 1
2 101 2017-04-01 456 2
3 101 2017-04-02 789 1
4 101 2017-04-02 103 2
5 101 2017-04-03 192 1
6 101 2017-04-03 112 2
As you can see, for iKey 101, the first date on the factProd table is 2017-04-01 (which correctly corresponds to the FirstFCDate column in Table1).
What I want to do is add a row to this factProd table for each date between 2017-01-13 (from the StartDate column) for each iKey + fcKey combination.
So the end result should look like this (ID is autogenerated):
factProd
ID iKey Date pAmount fcKey
99 101 2017-01-13 0 1
100 101 2017-01-13 0 2
101 101 2017-01-14 0 1
102 101 2017-01-14 0 2
103 101 2017-01-15 0 1
104 101 2017-01-15 0 2
... ... ... ... ...
199 101 2017-03-31 0 1
200 101 2017-03-31 0 2
1 101 2017-04-01 123 1
2 101 2017-04-01 456 2
3 101 2017-04-02 789 1
4 101 2017-04-02 103 2
5 101 2017-04-03 192 1
6 101 2017-04-03 112 2
James,
You may find it helpful to build a CTE table with one row for all of the possible dates between a range (i.e. MIN and MAX dates from your fact table), and then LEFT JOIN to it to find missing dates. You may also want to consider INSERTING into your fact table using LEFT JOIN, IS NULL logic, to get the missing days added. Your scenario is a bit more complex than only that, but it may lead you down a good path. Here is some code to build that table, virtually:
DECLARE @FromDate datetime
DECLARE @ToDate datetime
SET @FromDate = '1/1/2017'
SET @ToDate = '1/1/2018';
WITH DayTable AS (SELECT CAST(@FromDate AS DATETIME) AS theDate UNION ALL SELECT DATEADD(dd, 1, theDate) FROM DayTable s WHERE DATEADD(dd, 1, theDate) <= CAST(@ToDate AS DATETIME))
SELECT * FROM DayTable
OPTION
(MAXRECURSION 32767)
Kindest regards...