Summary data - TSql

Question

Post reply

Summary data - TSql

KrishnaChaitanya

SSC-Addicted

Points: 482
More actions
December 20, 2010 at 10:59 pm

#230904

Hi,

I have a requirement as described below:

Table1:

CustomerName

CustomerRefNumber

InvoiceNumber

InvoiceAmount

I have to create a report from the above table in the below mentioned manner

Customer Name CustomerRefNumber InvoiceNumber InvoiceAmount

AAA 111 Inv001 1000

AAA 111 Inv002 1100

Total 2100

BBB 222 Inv003 3000

BBB 222 Inv004 2000

BBB 222 Inv005 5000

Total 10000

I want a report which shows me the all the invoices grouped by the Customer Name and its corresponding total and the next customer name and its total amount. Kindly let me know if it is achievable in SQL Server T-SQL?

Regards,

Krishna Chaitanya.
December 21, 2010 at 12:21 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

Do you need to have the results displayed just the same as what you displayed in your original post? Basically all the records for each customer and then the summary results in between the the detailed results of each customer?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Hardy21

SSCrazy Eights

Points: 9708

December 20, 2010 at 11:52 pm

#1264903

You can use GROUP BY with ROLLUP.

You can refer: http://msdn.microsoft.com/en-us/library/ms177673.aspx

Thanks

srikant maurya Hall of Fame Points: 3293 More actions · Answer 3

T-SQL code

declare @t table (

CustomerName VARCHAR(30),

CustomerRefNumber int,

InvoiceNumber VARCHAR(20),

InvoiceAmount decimal(15,2))

insert into @t

select 'AAA',

111,

'Inv001',

1000

union all

select 'AAA',

111,

'Inv002',

1100

union all

select 'BBB',

222,

'Inv003',

3000

union all

select 'BBB',

222,

'Inv004',

2000

union all

select 'BBB',

222,

'Inv005',

5000

;

select *

from (

select distinct CustomerName,

sum(InvoiceAmount) Total

from @t t

group by CustomerName WITH ROLLUP

) t

where CustomerName is not null

kokkula SSC-Addicted Points: 495 More actions · Answer 4

Hello,

If you want a report in the specified output then you need to look for two areas.

1. SQL Query

2. Report Design

SQL Query:

==========

In the report design in the data tab create a dataset and the query should be like below:

SELECT CustomerName,

CustomerRefNumber,

InvoiceNumber,

InvoiceAmount

FROM TableName

Report Design:

==============

1. In the report design in the design tab drag and drop a tablix control.

2. Assign the dataset to the table.

3. In the groupings create a group on CustomerName.

4. In the details row drag and drop the required columns in the cells.

5. In the CustomerName group footer display the total amount (Expression: =Sum(Fields!InvoiceAmount.Value))

Hope its clear & helpful....

Pavan Kokkula Tata Consultancy Services.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 5

stewartc-708166 (12/21/2010)

This is possible in T-SQL,

SELECT CustomerName, CustRefNo, InvNo, invoiceamount
FROM #customer UNION ALL SELECT CustomerName, CustRefNo, 'Total' AS InvNo, SUM(invoiceamount) AS invoiceamount FROM #customer GROUP BY CustomerName, CustRefNo
ORDER BY 1,3

However, if this is to be a report, why not use SSRS, where this functionality is already built-in?

My thoughts as well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events