Fill in missing dates in advanced sql
Application Type
Reactive

I am pulling sql data that has a date. Looking for a way in Outsystems advanced sql to add blank records for any missing dates.  Yes I can walk the list afterwards but I have seen some ways to do this in sql but haven't gotten it to work in Outsystems. Anyone got this working and if so can you post an example?

mvp_badge
MVP

Hi Jason,

J think it will ease someone to help you if you share your SQL statement.

Regards,

Daniel

Hi Jason, you will have to get a little bit creative here.
In the example app attached I have created a Calendar helper entity and use it to add non existing days to the result set.

Please check out the .oml attached and let me know if you need further assistance.

Cheers,
Toine

MyReactiveWebApp.oml

Hi Jason.

You can have something like this:

;WITH DateRange (CalcDate) AS (
SELECT CAST(@DateStart AS DATETIME)
UNION ALL
SELECT DATEADD(DAY, 1, CalcDate) FROM DateRange WHERE DATEADD(DAY, 1, CalcDate) < DATEADD(DAY, 1, @DateEnd)
)
SELECT DateRange.CalcDate, {Orders}.*
FROM DateRange
LEFT JOIN {Orders} ON {Orders}.[Date] = DateRange.CalcDate
AND {Orders}.[CustomerId] = @CustomerId
AND {Orders}.[Date] BETWEEN @DateStart AND @DateEnd
ORDER BY {Orders}.[Date]
OPTION(MAXRECURSION 0)


Be careful, this query uses recursion and may require heavy processing if you use a long date interval.

And it helps if (in this case) {Orders} entity is properly indexed.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.