Post reply

Summarized SQL Server T-SQL Report

  • 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.

  • December 21, 2010 at 8:49 am

    #1265125

    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)

  • December 21, 2010 at 2:57 pm

    #1265297

    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

  • December 22, 2010 at 1:53 pm

    #1265684

    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

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

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