How to calculate Row count?

I assume that this question follows the one asked yesterday, which was eventually resolved with the query returning data from 2 tables as one CRLF delimited string. I suppose that this string is then written to the destination flat file. If you would like to continue with this route, but add some extra info to the file (header line on the top and the trailer line on the bottom which will display the number of records) then you can just add one small select for each and union all the results. Here is the sample based on the query in yesterday's comments (I removed majority of the columns from there to keep my answer shorter in size):

select '_Header Row and SomeHeaderInfo'
union all
select 
    stuff(replace(
    (
        select distinct
            char(10) + convert(varchar(1), RecordType) +
            convert(varchar(3),BrokerDealNumber) +
            convert(varchar(3),BranchNumber) +
            convert(varchar(9),PershAcctNumber)+
            (select top 1 
                convert(varchar(6), TransactionBookKeepingDate, 112)
                from dbo.PershExtractTableA aa 
                where atab.PershAcctNumber = aa.PershAcctNumber) +
            (
                select char(10) +
                    convert(varchar(1), RecordType)+
                    convert(varchar(3), BrokerDealNumber)+
                    convert(varchar(3), BranchNumber) +
                    convert(varchar(9), PershAcctNumber) +
                    convert(varchar(1), PershingAccountType)
                    from dbo.PershingExtractTableT ttab 
                    where ttab.PershAcctNumber = atab.PershAcctNumber
                    for xml path('')
            )
            from dbo.PershExtractTableA atab
            for xml path('')), char(10), char(13) + char(10)), 1, 2, '')
union all
select '_Trailer Row: ' + convert(varchar(10), 
    (select count(1) from dbo.PershExtractTableA) +
    (select count(1) from dbo.PershingExtractTableT));

However, I would like to point out that it is not really necessary to use for xml to concatenate the records like this if SSIS is used to actually create and populate the file. If you have a task pumping data from your 2 tables to the destination flat file and the Row Delimiter property has already been configured to be {CR}{LF} then you should simply return the union all of your selects:

• header
• records from dbo.PershExtractTableA
• records from PershingExtractTableT
• trailer info (sum of both counts)

and let SSIS task to take care of adding your records to the file. The only small issue that you need to overcome is the correct sorting of your results. Here is the sample query which is constructed in the way to guarantee that your file will consist of the header on first line, parent and child tables records arranged such that the parent record is followed by all its child records followed by next parent etc, followed by the trailer record displaying the total count of the records in the file (excluding the count of header and trailer lines of course):

;with records(Order1, Order2, Data) as
 (
    select 
        0 Order1, 0 Order2, '_Header Row and SomeHeaderInfo' Data
    union all
    select distinct
        row_number() over (order by PershAcctNumber), 0, 
        convert(varchar(1), RecordType) +
        convert(varchar(3),BrokerDealNumber) +
        convert(varchar(3),BranchNumber) +
        convert(varchar(9),PershAcctNumber)+
        (select top 1 
            convert(varchar(6), TransactionBookKeepingDate, 112)
            from dbo.PershExtractTableA aa 
            where atab.PershAcctNumber = aa.PershAcctNumber)
        from dbo.PershExtractTableA atab       
    union all
    select
        dense_rank() over (order by PershAcctNumber), 1,
        convert(varchar(1), RecordType)+
        convert(varchar(3), BrokerDealNumber)+
        convert(varchar(3), BranchNumber) +
        convert(varchar(9), PershAcctNumber) +
        convert(varchar(1), PershingAccountType)
        from dbo.PershingExtractTableT ttab 
    union all             
    select 2147483647, 0, '_Trailer Row: ' + convert(varchar(10), 
        (select count(1) from dbo.PershExtractTableA) +
        (select count(1) from dbo.PershingExtractTableT))
)
    select Data from records order by Order1, Order2;

You can use any of the queries above, but I really believe that the second one is more suitable for SSIS solution while the first one is good enough for a small result set which is easy enough to copy from the query results window by hand (assuming results to text option).

Oleg

If what @oleg has asked is correct, that you don't infact have a file with the footer right now you can use a union to add a sum column at the end. Pretty simple to do. I will post an example of a query I have recently created to do the same.

SELECT Inst , COUNT(Inst) AS Totals
FROM docs
WHERE status = 'N'
GROUP BY INST
UNION
SELECT 'TOTAL' AS INST , COUNT(*) AS Totals
FROM dbo.DOCS
WHERE STATUS = 'N'
ORDER BY INST ASC