question

Eshwar-8614 avatar image
0 Votes"
Eshwar-8614 asked Viorel-1 published

Get the count of details until next header

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.

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 published

Try one of approaches:

 ;
 with Sample as
 (
 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'
 ),
 cte as
 (
     select *, lead(ROWNUMBER) over (order by ROWNUMBER) nrn
     from Sample
     where RECORDTYPE = 'HD'
 )
 select DATEED, FIELD1, FIELD2, RECORDTYPE, DATA, ROWNUMBER, 
     (select count(*) from Sample where ROWNUMBER > t.ROWNUMBER and (nrn is null or ROWNUMBER < nrn)) as COUNTS
 from cte t
 order by ROWNUMBER

The ROWNUMBER column was fixed to be an integer instead of text, otherwise some casts are needed.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.