x

How to calculate Row count?

Hi all,

I have a file with a single header row, details(suppose 100 rows) and a trailer row which contains number(or count) of rows in the same file excluding the header and trailer row? How to achieve this?

Thanks in advance...
more ▼

asked Mar 18 '11 at 08:06 AM in Default

AbhiD gravatar image

AbhiD
171 12 15 17

What exactly is your question? What are you wanting to achieve?
Mar 18 '11 at 08:08 AM Tim
And I want to calculate this row count of this file excluding the header and trailer row for the same file and include this in the same file.
Mar 18 '11 at 08:09 AM AbhiD
@TRAD r u getting my question?
Mar 18 '11 at 08:09 AM AbhiD
Sorta getting it. Is this a file you are importing into a table? What version of SQL are you using?
Mar 18 '11 at 08:14 AM Tim
i m creating this file from temp table using ssis ... sql server 2005..i m writing a Sql query on the oledb source to get this file ... i have the correct file with header , details and record but i m not able to include this row count in my trailer row.... how to do this ...
Mar 18 '11 at 08:17 AM AbhiD
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I assume that this question follows [the one asked yesterday][1], 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

[1]: http://ask.sqlservercentral.com/questions/34755/output-file-formatting-in-ssis
more ▼

answered Mar 18 '11 at 11:42 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

@oleg

Thx for the answer ... actually i figured out if i use a distinct in SElect statement for table A and no distinct select statement for table B then i will get my desired output ... as if i dont use distinct in table A then i m counting duplicates also where as i m not having any duplicated in the output file from table A.... r u getting me ..... anyways thx alot OLEG.
Mar 18 '11 at 02:37 PM AbhiD
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Mar 18 '11 at 08:23 AM

Tim gravatar image

Tim
35.5k 32 41 138

@trad

i have header and footer in my output but donno how how to get the row count of the file minus the the header and footer row in the same file.
Mar 18 '11 at 08:39 AM AbhiD
I think we have enough information on your issue where someone can help out. I however have just finished packing my bags and about to embark on a 5 hour road trip to SQL Saturday #70. I am certain someone will get you the solution to your problem very quickly.
Mar 18 '11 at 08:41 AM Tim
@abhid, I am so glad that @Oleg was able to get you the results you needed. I hope you continue coming back to ask.sqlservercentral.com to ask questions as well as help others out as well.
Mar 18 '11 at 03:05 PM Tim
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x900
x265

asked: Mar 18 '11 at 08:06 AM

Seen: 2221 times

Last Updated: Mar 18 '11 at 08:06 AM