subtotaling... 1

[gjsaturday]

I'm having one heck of a time with "case" "grouping" and "cube". The problem is I really don't know how to utilize these to get the result I want. i have following "ugly" statement

select c.name,b.name, e.date, sum(e.bal)
from
table1 a,
table1 b,
table1 c,
table2 d,
table3 e,
table3 f,
where a.id = e.id
and e.id = f.id
and a.parent = b.id
and b.id = d.id
and b.parent = c.id
and e.type = 1
and f.type = 2
and e.date = CONVERT(VARCHAR,(GETDATE()-1),101)
and f.date = CONVERT(VARCHAR,(GETDATE()),101)
and b.usrflds9 = 'X'
group BY c.name,b.name, e.date
HAVING
sum(y.ships) <> 0
order by 1,2,3

I get a result set like this:
name name date sum
1 1 02/14/2005 5
1 2 02/14/2005 10
1 3 02/14/2005 2
2 1 02/14/2005 9
2 2 02/14/2005 8
2 3 02/14/2005 7
2 4 02/14/2005 12
2 5 02/14/2005 1
3 1 02/14/2005 20
4 1 02/14/2005 6
4 2 02/14/2005 7

I want
name name date sum
1 1 02/14/2005 5
1 2 02/14/2005 10
1 3 02/14/2005 2
1 Total 17
2 1 02/14/2005 9
2 2 02/14/2005 8
2 3 02/14/2005 7
2 4 02/14/2005 12
2 5 02/14/2005 1
2 Total 37
3 1 02/14/2005 20
3 Total 20
4 1 02/14/2005 6
4 2 02/14/2005 7
4 Total 13

I've been playing with "case" like this...
--CASE WHEN (GROUPING(a.name) = 1) THEN a.name+' '+'Total'
-- ELSE ISNULL(a.name,'unknown')
-- END AS a,
--WITH cube
--GROUPING(a.NAME) = 0
--grouping(b.NAME) <> 1
--AND GROUPING(e.DATE) <> 1

but don't quite what I want... and then too, my ordering gets all messed up.

Thanks in advance!
Jen

 

[donutman]

Don't you have a report writer like Crystal Reports or the one available from MS for SQL Server?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gjsaturday]

that's the thing, we don't. I've been asking for Crystal, as that's what I'm familiar with, but it's "still in the works" In the mean time I'm having to run this thing daily myself and then subtotal in excel because I can't schedule a DTS package to do it for me. Your so good, can't you help? I get really close, using the case thing, but I either get all nulls for the 1st column or all rows say "Total". Thanks in advance and I'll keep pushing for Crystal!

 

[gmmastros]

Try this....

Select your results in to a temporary table. Then create a second temporary table with the SUM information in it.

Then, insert the records from the second temporary table in to the first temporary table. Finally, return the results. You may need to play with the ordering a little to get the sub results returned in the right place, but it should work.

Hope I helped.

 

[dk87]

If you are familiar with VBA, you can write an Excel sheet that can do this for you. At least for now, until your "Crystal" ship comes in.

 

[rajeevnandanmishra]

Hello gjsaturday,

The solution pointed above are able to solve your problem. But if you don't have a very large set of data then probably you can use the following query for the time being:
----------------------------------------------
select c.name,b.name, e.date, sum(e.bal)
from
table1 a,
table1 b,
table1 c,
table2 d,
table3 e,
table3 f,
where a.id = e.id
and e.id = f.id
and a.parent = b.id
and b.id = d.id
and b.parent = c.id
and e.type = 1
and f.type = 2
and e.date = CONVERT(VARCHAR,(GETDATE()-1),101)
and f.date = CONVERT(VARCHAR,(GETDATE()),101)
and b.usrflds9 = 'X'
group BY c.name,b.name, e.date
HAVING
sum(y.ships) <> 0
UNION
select c.name, 'ZZTotal' , GETDATE(), SUM(e.bal)
from
table1 a,
table1 b,
table1 c,
table2 d,
table3 e,
table3 f,
where a.id = e.id
and e.id = f.id
and a.parent = b.id
and b.id = d.id
and b.parent = c.id
and e.type = 1
and f.type = 2
and e.date = CONVERT(VARCHAR,(GETDATE()-1),101)
and f.date = CONVERT(VARCHAR,(GETDATE()),101)
and b.usrflds9 = 'X'
group BY c.name
HAVING
sum(y.ships) <> 0
order by 1,2,3
----------------------------------------------


Raj

 

[gjsaturday]

Raj you are so AWESOME!!! Thanks so much, I love the simplicity!!!