Comments and answers for "How to calculate Row count?"https://ask.sqlservercentral.com/questions/35492/how-to-calculate-row-count.html
The latest comments and answers for the question "How to calculate Row count?"Comment by Tim on Tim's answerhttps://ask.sqlservercentral.com/comments/35536/view.html
@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.Fri, 18 Mar 2011 15:05:42 GMTTimComment by AbhiD on AbhiD's answerhttps://ask.sqlservercentral.com/comments/35534/view.html
@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.Fri, 18 Mar 2011 14:37:23 GMTAbhiDComment by Oleghttps://ask.sqlservercentral.com/comments/35526/view.html
@AbhiD I think that my answer I just posted should help you getting what you need. Please let me know.Fri, 18 Mar 2011 11:47:59 GMTOlegAnswer by Oleghttps://ask.sqlservercentral.com/answers/35525/view.html
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-ssisFri, 18 Mar 2011 11:42:36 GMTOlegComment by AbhiDhttps://ask.sqlservercentral.com/comments/35515/view.html
i have my output file but only the number of record is to be included .....Fri, 18 Mar 2011 09:15:39 GMTAbhiDComment by Tim on Tim's answerhttps://ask.sqlservercentral.com/comments/35511/view.html
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.Fri, 18 Mar 2011 08:41:42 GMTTimComment by AbhiD on AbhiD's answerhttps://ask.sqlservercentral.com/comments/35510/view.html
@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.Fri, 18 Mar 2011 08:39:24 GMTAbhiDComment by AbhiDhttps://ask.sqlservercentral.com/comments/35509/view.html
@oleg
There are two tables which are the building blocks of this query.. suppose table A as
account_num account_cost
111 1000
112 2000
table B as
account_number Transaction_num trans_date
111 123 03/11/2011
111 234 03/12/2011
112 999 03/07/2011
112 888 03/14/2011
and i want my txt file output as
Headerrow as date 031711
111 1000
11112303112011
11123403122011
112 2000
11299903072011
11288803142011
trailerrow as 031711 and number of record is 6Fri, 18 Mar 2011 08:36:52 GMTAbhiDAnswer by Timhttps://ask.sqlservercentral.com/answers/35504/view.html
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 ASCFri, 18 Mar 2011 08:23:39 GMTTimComment by Oleghttps://ask.sqlservercentral.com/comments/35501/view.html
@AbhiD So, this means that you don't actually have the trailer in this file yet, but need to add one, right? If so then please perovide some details, such as where the file comes from, does the calculation have to be done with T-SQL?Fri, 18 Mar 2011 08:18:40 GMTOlegComment by AbhiDhttps://ask.sqlservercentral.com/comments/35498/view.html
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 ...Fri, 18 Mar 2011 08:17:17 GMTAbhiDComment by Timhttps://ask.sqlservercentral.com/comments/35496/view.html
Sorta getting it. Is this a file you are importing into a table? What version of SQL are you using?Fri, 18 Mar 2011 08:14:14 GMTTimComment by AbhiDhttps://ask.sqlservercentral.com/comments/35495/view.html
@TRAD r u getting my question?Fri, 18 Mar 2011 08:09:48 GMTAbhiDComment by AbhiDhttps://ask.sqlservercentral.com/comments/35494/view.html
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.Fri, 18 Mar 2011 08:09:01 GMTAbhiDComment by Timhttps://ask.sqlservercentral.com/comments/35493/view.html
What exactly is your question? What are you wanting to achieve?Fri, 18 Mar 2011 08:08:05 GMTTim