Summarized SQL Server T-SQL Report

Question

Post reply

Summarized SQL Server T-SQL Report

KrishnaChaitanya

SSC-Addicted

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

#99446

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.

Viewing 4 posts - 1 through 3 (of 3 total)

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

harinerella Mr or Mrs. 500 Points: 548 More actions · Answer 1

create table cust (CustomerName char(4),

CustomerRefNumber int,

InvoiceNumber varchar(10),

InvoiceAmount int)

insert into cust values ('AAA', 111, 'Inv001', 1000)

insert into cust values('AAA', 111, 'Inv002', 1100)

-----

insert into cust values('BBB', 222, 'Inv003', 3000)

insert into cust values ('BBB', 222, 'Inv004', 2000)

------

select CustomerName,CustomerRefNumber,InvoiceNumber,InvoiceAmount from (

select CASE WHEN (GROUPING(CustomerRefNumber) = 1) THEN 'Total' ELSE CustomerName end as CustomerName,CustomerRefNumber,InvoiceNumber,sum(InvoiceAmount) InvoiceAmount,

CASE WHEN (GROUPING(CustomerRefNumber) = 1) THEN 1 ELSE 0 end as CRefNumgrp,

CASE WHEN (GROUPING(InvoiceNumber) = 1) THEN 1 ELSE 0 end as Inumbergrp,

CASE WHEN (GROUPING(CustomerName) = 1) THEN 1 ELSE 0 end as cnamegrp

from cust

group by CustomerName,CustomerRefNumber,InvoiceNumber with rollup) a

where (CRefNumgrp = 1 and Inumbergrp =1 and cnamegrp =0 ) or (CRefNumgrp =0 and Inumbergrp = 0 and cnamegrp =0)

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

If you are using 2005 or 2008

Then use GROUP BY

A general GROUP BY clause includes GROUPING SETS, CUBE, ROLLUP, WITH CUBE, or WITH ROLLUP.

A simple GROUP BY clause does not include GROUPING SETS, CUBE, ROLLUP, WITH CUBE, or WITH ROLLUP. GROUP BY (), grand total, is considered a simple GROUP BY.

See in Help there are lot of things are there ,opt your option

Thanks

Parthi

Thanks
Parthi

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

This type of totaling should really be done in the front end report engine. Access, Crystal, etc. all have this type of totaling available.

Todd Fifield