Aggregation using a combination of rows and columns

Question

Examples of data from ranges A table of named ranges is shown below:

+-----------------+-------------------+----------+----------+ | SectionCategory | RangeName | LowerEnd | UpperEnd | +-----------------+-------------------+----------+----------+ | Sanction | 0-7 days | 0 | 7 | | Sanction | 8-15 days | 8 | 15 | | Sanction | More than 15 days | 16 | 99999 | | Disbursal | 0-7 days | 0 | 7 | | Disbursal | 8-15 days | 8 | 15 | | Disbursal | More than 15 days | 16 | 99999 | +-----------------+-------------------+----------+----------+ 

Sample data from the Delays table is shown below:

 +-----------+---------------+-----------------+ | Loan No. | SanctionDelay | Disbursal Delay | +-----------+---------------+-----------------+ | 247 | 8 | 35 | | 661 | 18 | 37 | | 1235 | 12 | 6 | | 1235 | 8 | 15 | | 1241 | 28 | 9 | | 1241 | 11 | 9 | | 1283 | 22 | 20 | | 1283 | 28 | 41 | | 1523 | 1 | 27 | | 1523 | 6 | 28 | +-----------+---------------+-----------------+ 

The required output is shown below:

 +-----------+-------------------+-------+ | Section | Range | Count | +-----------+-------------------+-------+ | Sanction | 0-7 days | 2 | | Sanction | 8-15 days | 4 | | Sanction | More than 15 days | 4 | | Disbursal | 0-7 days | 1 | | Disbursal | 8-15 days | 3 | | Disbursal | More than 15 days | 6 | +-----------+-------------------+-------+ 

Currently, two separate requests are being written, and UNION is used to match the output.

In terms of maintainability, is it possible to do this in a single request? (For the Sanction in Ranges table, use the SanctionDelay column from the Delays table, and for Disbursal, use the DisbursalDelay column.) The need is that the number of stages in the loan life cycle is expected to increase and there are more UNIONs to collect the results.

Answer 1

This can be done with CROSS JOIN , not sure how effective it is.

Sample data:

 declare @Ranges table (SectionCategory varchar(10) not null,RangeName varchar(20) not null,LowerEnd int not null,UpperEnd int not null) insert into @Ranges (SectionCategory,RangeName,LowerEnd,UpperEnd) values ('Sanction','0-7 days',0,7), ('Sanction','8-15 days',8,15), ('Sanction','More than 15 days',16,99999), ('Disbursal','0-7 days',0,7), ('Disbursal','8-15 days',8,15), ('Disbursal','More than 15 days',16,99999) declare @Delays table (LoanNo int not null,SanctionDelay int not null,DisbursalDelay int not null) insert into @Delays (LoanNo,SanctionDelay,DisbursalDelay) values ( 247, 8,35), ( 661,18,37), (1235,12, 6), (1235, 8,15), (1241,28, 9), (1241,11, 9), (1283,22,20), (1283,28,41), (1523, 1,27), (1523, 6,28) 

The request (should be executed in the same package as the sample data):

 select r.SectionCategory, r.RangeName, SUM(CASE WHEN r.SectionCategory='Sanction' and d.SanctionDelay BETWEEN r.LowerEnd and r.UpperEnd then 1 WHEN r.SectionCategory='Disbursal' and d.DisbursalDelay BETWEEN r.LowerEnd and r.UpperEnd then 1 else 0 end) as Cnt from @Ranges r cross join @Delays d group by r.SectionCategory, r.RangeName order by SectionCategory,RangeName 

Results:

 SectionCategory RangeName Cnt --------------- -------------------- ----------- Disbursal 0-7 days 1 Disbursal 8-15 days 3 Disbursal More than 15 days 6 Sanction 0-7 days 2 Sanction 8-15 days 4 Sanction More than 15 days 4 

---

In terms of serviceability, it may be better to have a single delay column in the delay table and an additional column that defines the type of delay. At the moment, this looks like some form of attribute separation - in the Ranges table the type is represented as a column value ( Sanction , Disbursal , etc.), but in the delay table the same “type” is presented in the table metadata, in terms of different column names.

You say that “the number of stages in the life cycle of a loan is expected to increase,” and I expect that this crossroads (representing attributes as data in some tables and metadata in others) will increase the pain by writing worthy queries.

Answer 2

try it

 SELECT SectionCategory ,RangeName ,CASE WHEN R.SectionCategory='Sanction' THEN (SELECT COUNT(1) FROM Delays D WHERE D.Sanction_Delay BETWEEN R.LowerEnd AND R.UpperEnd) WHEN R.SectionCategory='Disbursal' THEN (SELECT COUNT(1) FROM Delays D WHERE D.[Disbursal Delay] BETWEEN R.LowerEnd AND R.UpperEnd) END as cnt FROM Ranges R 

Here is the SQLFiddle demo