Hi,
I have a requirement to get the record count of details for each header sometimes the header can be same so i cannot directly use group by. The details will come in sequence after corresponding header it will not overlap.
Please help with the query.
Input data:
SELECT '20210614' DATEED,'' FIELD1,'' FIELD2,'HD' RECORDTYPE,'000000000{000000000{00002ABC 0555 ' DATA,'1' ROWNUMBER UNION ALL
SELECT '20210614','0852','90033901440250001 ','90','06 TEST placement...everything ok XABC ABC CARD ','2' UNION ALL
SELECT '20210614','0852','90033901440250001 ','90','07 canceled the DLB XABC ABC CARD ','3' UNION ALL
SELECT '20210614','','','HD','000000000{000000000{00001ABC 0555 ','4' UNION ALL
SELECT '20210614','0852','90033901440250001 ','MT','MASSTS507 XABC ABC CARD ','5' UNION ALL
SELECT '20210614','','','HD','000000000{000000000{00002ABC 0555 ','6' UNION ALL
SELECT '20210614','0852','90033901440250001 ','90','01 #EST placement...everything ok XABC ABC CARD ','7' UNION ALL
SELECT '20210614','0852','90033901440250001 ','90','02 c3nceled the DLB XABC ABC CARD ','8' UNION ALL
SELECT '20210614','','','HD','000000000{000000000{00002BCD 0555 ','9' UNION ALL
SELECT '20210614','0852','90033901440250001 ','90','01 #EST placement...everything ok XBCD BCD CARD ','10' UNION ALL
SELECT '20210614','0852','90033901440250001 ','90','02 c3nceled the DLB XBCD BCD CARD ','11' UNION ALL
SELECT '20210614','0852','90033901440250001 ','90','02 c3nceled the DLB XBCD BCD CARD ','12'
Expected output is header fields and details record count:
SELECT '20210614' DATEED,'' FIELD1,'' FIELD2,'HD' RECORDTYPE,'000000000{000000000{00002ABC 0555 ' DATA,'1' ROWNUMBER,2 COUNTS UNION ALL
SELECT '20210614','','','HD','000000000{000000000{00001ABC 0555 ','4',1 COUNTS UNION ALL
SELECT '20210614','','','HD','000000000{000000000{00002ABC 0555 ','6',2 COUNTS UNION ALL
SELECT '20210614','','','HD','000000000{000000000{00002BCD 0555 ','9',3 COUNTS
Regards,
EShwar.