SQL Help- Grand Total

[Dorko]

I am puzzled on how to get this report to show me a grand total.

The code is

SELECT TA.Location,
TA."Surname",
TA."Given Names",
SUM( CASE WHEN TA."Period End Date" = @[Last Pay Period End Date] AND TA."Pay Comp/Add Ded Code" = 'NetPay' THEN TA.Amount END) AS [Last Pay],
SUM( CASE WHEN TA."Period End Date" = @[This Pay Period End Date] AND TA."Pay Comp/Add Ded Code" = 'NetPay' THEN TA.Amount END) AS [This Pay]


FROM
"_ipvRBEmpTransactionsALL" TA


WHERE
CASE WHEN TA."Period End Date" = @[Last Pay Period End Date] AND TA."Pay Comp/Add Ded Code" = 'NetPay' THEN TA.Amount END IS NOT NULL
OR
CASE WHEN TA."Period End Date" = @[This Pay Period End Date] AND TA."Pay Comp/Add Ded Code" = 'NetPay' THEN TA.Amount END IS NOT NULL

GROUP BY

TA.Location ,
TA."Surname",
TA."Given Names"


I have tried to use a rollup or cube statement, but because this report is broken down by employee it gives me a subtotal at the employee level rather than a grand total.

Any suggestions? :angel:

Thanks in advance

Dorko

[spudboy]

Because you are grouping by Location/FirstName/Surname you will only get totals for these lines.

If you want grand totals, you will have to have a separate clause that does no grouping.

What are you using to report this? (e.g. Crystal Reports etc). Can you not use the reporting tool to do the summing of the detail lines?

Note that this query will give you unexpected results if you have 2 persons at the same location with the same names, as it will combine them.

[George130]

As spudboy said add the uique id of the employees to prevent those with the same name being combied.

You want to group by location for the sum. SQL won't like that so the best trick is to do it in the reports program or have a seperate SQL statement to do the grand total.

What is your reporting tool?

Oh
You could also play with a nested select.

[Dorko]

Thanks for your replies, I am writing it in Management Studio for our payroll system, the @[Last Pay Period End Date] is the way MicrOpay needs it so the report can have parameters.

I will look into 'Nested Select' Not sure how that works.

The 'With Rollup' gives me a subtotal under each employee. The 'With Cube' just makes a mess :(

I wish I could write this in Crystal....

I will keep you posted with the outcome.... but if anyone comes up with a idea, please let me know :)

Dorko

[spudboy]

It's a bit ugly, but if you were desperate you could UNION the query above with one that returned

...
UNION
Null, Null, Null, Sum(Blah blah blah...)

or

...
UNION
'Total', 'Total', 'Total', Sum(Blah blah blah...)

Where the first three fields filled in the Group By values of Locn/FN/Surname.

As an aside, I guess you don't have control of the schema, so the field names with spaces in them probably come from MicrOPay, but everything is much neater if you don't put spaces in the field names.

[Dorko]

Thanks, i will try for using a union...

Dorko

[spudboy]

I've had another thought!! Dangerous, I know.

You could do your original view with the rollup clause, which gives you too many levels of subtotal, as previously mentioned.

However, you could build another view on that view that took all of the rows with data values in the first three fields, but omitted rows with Null in the Surname/Firstname, apart from the last row which would have null in all 3 columns, sort of like this:

Select * from OriginalView
Where Surname is not Null -- This gets the normal lines
or (Location is Null and FirstName is Null and Surname is Null) -- This gets the grand total line

Maybe that would be easier?

Cheers
David

[Dorko]

Hmmm.... i see where you are going with this, but how will i tell it to keep the last line?

I will try this out on the weekend...At the moment i told the client cant be done, but i will research it further.

Thanks mate, could be on something!

Dorko

[spudboy]

Well, the last line in a rollup has all null values (apart from the values you are summing), so you can pick the last line by asking for all the "Group by" fields to be null.

If I assume you have a single field for firstname/Surname, your Location/name results rollup query will give you something like this:
East, Fred Smith, 1111, 1111
East, Mary Jones, 2222, 2222
East, NULL, 3333, 3333
West, David Jones, 2222, 2222
West, Jenny Jones, 3333, 3333
West, NULL, 5555, 5555
NULL, NULL, 8888, 8888

If you then use this as input to another query/view, and filter for the Name field to be not null, except where the location field is null you would get this recordset:

East, Fred Smith, 1111, 1111
East, Mary Jones, 2222, 2222
West, David Jones, 2222, 2222
West, Jenny Jones, 3333, 3333
NULL, NULL, 8888, 8888

What do you think of that solution?

[George130]

Well, the last line in a rollup has all null values (apart from the values you are summing), so you can pick the last line by asking for all the "Group by" fields to be null.

If I assume you have a single field for firstname/Surname, your Location/name results rollup query will give you something like this:
East, Fred Smith, 1111, 1111
East, Mary Jones, 2222, 2222
East, NULL, 3333, 3333
West, David Jones, 2222, 2222
West, Jenny Jones, 3333, 3333
West, NULL, 5555, 5555
NULL, NULL, 8888, 8888

If you then use this as input to another query/view, and filter for the Name field to be not null, except where the location field is null you would get this recordset:

East, Fred Smith, 1111, 1111
East, Mary Jones, 2222, 2222
West, David Jones, 2222, 2222
West, Jenny Jones, 3333, 3333
NULL, NULL, 8888, 8888

What do you think of that solution?

Thats the ested select ideas.
As I'm using Delphi and rave reports I do a few seperate queries and the join them is the rave as required. But then mine get to the point where every returned column is a seperate select with up to 15 different tables. But our database has over 500 tables in it:eek:.

[Dorko]

Thanks for all your help.

The company decide not to go down this track as it was getting messy in the code. We ended up doing the report in Report Writer

Appreciate your help and sorry for delay in saying thanks :)


Dorko

[Dorko]

Double post

Whoops