Skip to Content
0
Former Member
Feb 15, 2015 at 09:59 PM

GROUPING in HANA SQL

64 Views

Hi Experts,

Here is what I am trying to do,

I have first 4 columns on the existing Table (HCC_PERNR, HCC_ZBEGDA, HCC_ZENDDA and HCC_OBJ)

I am trying to code for the last Column HCC_GROUP (group the values on change of HCC_OBJ)

Note : I can do that easily but there is one catch ; see HCC_OBJ -> 36000360 is coming twice , once from 2010 to 2011 and then from 2014 to 9999

_HCC_PERNR _HCC_ZBEGDA _HCC_ZENDDA _HCC_OBJ _HCC_GROUP 10007304 20070607 20080405 0 1 10007304 20080406 20090905 35002004 2 10007304 20090906 20090919 35002004 2 10007304 20090920 20100710 35002004 2 10007304 20100711 20101225 36000360 3 10007304 20101226 20110723 36000360 3 10007304 20110724 20120107 36000361 4 10007304 20120108 20120331 36000361 4 10007304 20120401 20120624 36000361 4 10007304 20120625 20130427 36001383 5 10007304 20130428 20130720 36001383 5 10007304 20130721 20140104 36001383 5 10007304 20140105 20140118 36000360 6 10007304 20140119 20140301 36000360 6 10007304 20140302 99991231 36000360 6

after I get above I want to write following

SELECT HCC_PERNR, MIN(HCC_ZBEGDA) , MX(HCC_ZENDDA), HCC_OBJ, HCC_GROUP

FROM <ABOVE SELECT>

GROUP BY HCC_PERNR,HCC_OBJ, HCC_GROUP

The above statement should give me two rows for 36000360 (once during 2010/2011 & other one During 2014/9999)

If I just write the GROUP statement without the HCC GROUP , I would loose the split of 36000360 ; it would actually tell me 36000360 was from 2010 to 9999 ; which is wrong.

Thanks

Meps