0

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

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
user3349850
  • 225
  • 1
  • 3
  • 21
  • Do you have a "dates" table somewhere that lists all dates in ranges you care about? Hana doesn't support recursive CTE; so we can't easily dynamically generate all dates in a 2014 year. https://stackoverflow.com/questions/69829941/with-recursive-common-table-expressions-in-sap-hana – xQbert Mar 21 '22 at 13:37
  • What data types are your dates string? Decimal, date... – xQbert Mar 21 '22 at 13:48
  • CREATE COLUMN TABLE "d" ( "date_start" DATE CS_DAYDATE, "date_end" DATE CS_DAYDATE ) – user3349850 Mar 21 '22 at 15:00

1 Answers1

1

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.

halexish
  • 9
  • 5