Generate dates in a date range using U-SQL

Question

I need to populate a rowset with all the dates between a defined Start date and End Date. If my start date is 19/7/2017 and the end date is 21/7/2017 then the rowset should contain 19/7/2017, 20/7/2017 and 21/7/2017.

I was wondering if there was an easy way to do this using U-SQL

Answer 1

The easiest way to do this would be to export your favourite date dimension from your favourite warehouse and import it into a U-SQL table.

You could also do this using custom U-SQL code, something like this:

DECLARE @outputFilepath string = "output/output74.csv";

//DECLARE @startDate DateTime = DateTime.Parse("19/7/2017");
//DECLARE @endDate DateTime = DateTime.Parse("21/7/2017");

DECLARE @startDate DateTime = DateTime.Parse("1/1/2000");
DECLARE @endDate DateTime = DateTime.Parse("31/12/2017");


// User-defined appliers
// Take one row and produce 0 to n rows
// Used with OUTER/CROSS APPLY
@output =
    SELECT outputDate
    FROM(
        VALUES ( 1 ) 
        ) AS dummy(x)
        CROSS APPLY new USQLtpch.makeDateRange (@startDate, @endDate) AS properties(outputDate DateTime);


OUTPUT @output
TO @outputFilepath
USING Outputters.Tsv();

The code-behind file:

using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace USQLtpch
{

    [SqlUserDefinedApplier]
    public class makeDateRange : IApplier
    {
        private DateTime startDate;
        private DateTime endDate;

        public makeDateRange(DateTime startDate, DateTime endDate)
        {
            this.startDate = startDate;
            this.endDate = endDate;
        }

        public override IEnumerable<IRow> Apply(IRow input, IUpdatableRow output)
        {

            // Initialise
            DateTime outputDate = this.startDate;


            // Loop until date range has been filled out
            while (outputDate <= endDate)
            {
                output.Set<DateTime>("outputDate", outputDate);

                // Increment date
                outputDate = outputDate.AddDays(1);

                yield return output.AsReadOnly();

            }
        }
    }
}

I've done this using a custom Applier which takes 1 row and converts it to 0 or n.