CODEWITHSUNDEEP
hana
user3349850
user3349850

Reputation: 225

get missing date ranges in sql hana

Created a table as below in sap hana and inserted some records

date_start date_end
2014-03-05 2014-04-10
2014-05-01 2014-06-05
2014-07-10 2014-08-15
2014-08-16 2014-08-20

Need output for the missing date ranges for above records

date_start date_end
2014-01-01 2014-03-04
2014-04-11 2014-04-30
2014-06-06 2014-07-09
2014-08-16 2014-12-31

saw a similar requirement in mysql here

Mysql Find missing date ranges

Regards Prasad

Upvotes: 0

Views: 581

Answers (1)

halexish
halexish

Reputation: 9

Do you have the ability to Generate Time Data? There is native functionality in HANA to generate time series data on the _SYS_BI schema under M_TIME_DIMENSION.

You'll need privileges on that schema in order to do this.

If you're using HANA via the Web IDE then refernece https://help.sap.com/viewer/e8e6c8142e60469bb401de5fdb6f7c00/2.0.06/en-US/6b9c2d4538004ef494ff977b733aa120.html

Else you can go into Studio under the Modeler perspective and select 'Generate Time Data' under Quick View. You can then choose what Calendar Type you'd like (Gregorian I'm assuming), in addition to the level of details and number of years for which you want to generate data.

Upvotes: 1

Related Questions