SQL Summarizing data in final row

Question

I have a query that generates rows of detail information about checks that are issued. In the final output, the data is all concatenated and put into one column (long story short, it needs to be run from Infomaker and exported to text with no delimiting).

Anyway, at the end of these rows I need a summary row that contains two values that are sums of the detail used in the previous rows, along with other values that are hard-coded. This final row also needs to be concatenated to one column, and appear after all the detail rows.

Example of how the summary row should appear with the computed values in brackets:

00123456789999999999940[CHECK AMOUNT SUMMARY][TOTAL NUMBER OF CHECKS (ROWS)]000

Again, no spaces, tabs, or any other delimiters allowed.

I'm stumped on how to achieve this. I have had suggestions of using UNION but I'm not sure exactly how to make that work for this situation.

Current query:

declare @checkDate date = '08/30/13'

select 
record = (
-- Checking account number (Record positions 1-9)
cast(cna.BANK_ACCT_NUM as varchar(9)) +    

-- Check number (Record positions 10-19) -- must always be nine characters  
(case 
    when LEN(cr.CHECK_NUM_NUMERIC) = 1 
    then '00000000'
    when LEN(cr.CHECK_NUM_NUMERIC) = 2 
    then '0000000' 
    when LEN(cr.CHECK_NUM_NUMERIC) = 3 
    then '000000'
    when LEN(cr.CHECK_NUM_NUMERIC) = 4 
    then '00000'
    when LEN(cr.CHECK_NUM_NUMERIC) = 5 
    then '0000'
    when LEN(cr.CHECK_NUM_NUMERIC) = 6 
    then '000'
    when LEN(cr.CHECK_NUM_NUMERIC) = 7 
    then '00'
    when LEN(cr.CHECK_NUM_NUMERIC) = 8 
    then '0'
    else ''
    end + cast(cr.CHECK_NUM_NUMERIC as varchar(9))) +

-- Record positions 20-21 - as determined by the bank
'20' +

-- Check amount (Record positions 22-31) -- must always be 10 characters
(case 
    when LEN(cr.CHECK_AMT) = 1 
    then '000000000'
    when LEN(cr.CHECK_AMT) = 2 
    then '00000000' 
    when LEN(cr.CHECK_AMT) = 3 
    then '0000000'
    when LEN(cr.CHECK_AMT) = 4 
    then '000000'
    when LEN(cr.CHECK_AMT) = 5 
    then '00000'
    when LEN(cr.CHECK_AMT) = 6 
    then '0000'
    when LEN(cr.CHECK_AMT) = 7 
    then '000'
    when LEN(cr.CHECK_AMT) = 8 
    then '00'
    when LEN(cr.CHECK_AMT) = 9 
    then '0'
    else ''
    end + cast(REPLACE(cr.CHECK_AMT,'.','') as varchar(10))) +

-- Date issued (MMDDYY)(Record positions 32-37)
cast(REPLACE(convert(char(10),cr.CHECK_DTE,101), '/', '') as varchar(10)) +   

-- Record positions 38-40 - as determined by the bank
'000' +

-- Payee information line 1 (Record positions 41-90)
cr.CHECK_NAME)

from chk_num_alpha_ctl cna,   
chk_reconciliation cr 
where ( cr.check_num_alpha = cna.check_num_alpha ) and  
( ( cr.check_rtn_void_dte is null ) AND  
( cr.check_dte = @checkDate ) ) AND  
( cna.bank_acct_num = 'xxxx-xxxx' )   
order by cr.check_dte ASC

Union would only work if your rows were one column, or you were willing to add a column to the output which was only populated on the last one. If neither is the case then it's going to be a separate query and you'll have to bolt them together in whatever's running it now. — Tony Hopkinson, Sep 04 '13 at 16:59

score 1 · Accepted Answer · answered Sep 04 '13 at 17:59

-- First, you can simplify your query using this type of 'right-justify-zero-fill' statement (adjust if more or less than 9-characters):

select right('000000000' + cast(cr.CHECK_NUM_NUMERIC as varchar(9)),9)

-- Then try something like this (I'm not able to test it, so there may be some adjustments):

UNION
select '00123456789999999999940' 
+ right('000000000' + cast(sum(cr.CHECK_AMT) as varchar(9)),9) 
+ right('000000000' + cast(count(cr.CHECK_AMT) as varchar(9)),9) 
+ '000'
from chk_num_alpha_ctl cna,   
chk_reconciliation cr 
where ( cr.check_num_alpha = cna.check_num_alpha ) and  
( ( cr.check_rtn_void_dte is null ) AND  
( cr.check_dte = @checkDate ) ) AND  
( cna.bank_acct_num = 'xxxx-xxxx' )   
GROUP BY cr.check_dte 
order by cr.check_dte ASC

Worked like a charm, thanks so much - and thanks for the tip on using the RIGHT function! — Jana, Sep 05 '13 at 16:18

SQL Summarizing data in final row

1 Answers1