July 6, 2009 at 3:41 pm
We all have these issue once in a while ( please wait until I post my question or just say discussion). I am facing more at the present because I am working with retail company and very times I have to generate reports of such kind.
I wish Microsoft would add few more functions on next release so that these sorts of problems can be solved.
Let's go into situation. (I am using few calculations to insert into temp table, so my temp table is like this and the data are also below)
In the tempTableA (dept, amount)
DeptName SubTtlSaleTaxAmtByDept
Beauty School0.2475
Employee 3.2992
Green Card6.2874
List 4.6192
Net 0.0825
TOTAL 14.5358
Another tempTableB (dept, Amount)
DeptName SubTtlSaleTaxAmtByDept
Beauty School 0.2475
Employee 3.2992
Green Card 6.2874
List 4.6192
Net 0.0825
Now I wantt to display results as the way below ( or may be even create or insernt into another temp table)
DeptName SubTtlSaleAmtByDept SubTtlSaleTaxAmtByDept TOTAL
Beauty School 3.00 0.2475 3.2475
Employee 44.78 3.2992 48.0792
Green Card 76.21 6.2874 82.4974
List 61.98 4.6192 66.5992
Net 11.00 0.0825 11.0825
TOTAL 196.97 14.5358 211.5058Is there something in 2008 that can be done to get results as above. Or even a code ( joins, union, or other sorts of functions) ??????????
I am suggesting these to microsoft as to build two types of functions called ADDCOL() and ADDROW() that would add column and row respectively to ur dataset as when u write query for above result set like these
SELECT A.DeptName,
A.SubTtlSaleAmtByDept ,
B.SubTtlSaleTaxAmtByDept ,
ADDCOL(SUM(A.SubTtlSaleAmtByDept + B.SubTtlSaleTaxAmtByDept )) as TOTAL,
ADDROW('TOTAL', SUM(A.SubTtlSaleAmtByDept), SUM(B.SubTtlSaleTaxAmtByDept ), SUM(A.SubTtlSaleAmtByDept + B.SubTtlSaleTaxAmtByDept )) -- look ADDROW function requires 4 --- paramaters as no of colums in select list
FROM temapTableA A
INNER JOIN tempTableB B
ON A.DeptName = B.Deptname
WHAT YOU ALL HAVE TO SAY ABOUT THESE? IS THIS CAN BE DONE @ ANYCOST ????
July 6, 2009 at 3:48 pm
What UI are using to display the data? If you are using SSRS I think a matrix would do want you want. You can also search for crosstab and pivot on this site and see some articles that may be of help.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 6, 2009 at 3:50 pm
I am just writing T-sql inside the proc.
July 6, 2009 at 3:51 pm
SSIS (7/6/2009)
We all have these issue once in a while ( please wait until I post my question or just say discussion). I am facing more at the present because I am working with retail company and very times I have to generate reports of such kind.I wish Microsoft would add few more functions on next release so that these sorts of problems can be solved.
Let's go into situation. (I am using few calculations to insert into temp table, so my temp table is like this and the data are also below)
In the tempTableA (dept, amount)
DeptNameSubTtlSaleTaxAmtByDeptBeauty School0.2475
Employee3.2992
Green Card6.2874
List4.6192
Net0.0825
TOTAL14.5358
Another tempTableB (dept, Amount)
DeptNameSubTtlSaleTaxAmtByDeptBeauty School0.2475
Employee 3.2992
Green Card6.2874
List 4.6192
Net 0.0825
Now I wantt to display results as the way below ( or may be even create or insernt into another temp table)
DeptNameSubTtlSaleAmtByDept SubTtlSaleTaxAmtByDept TOTALBeauty School3.00 0.2475 3.2475
Employee 44.78 3.2992 48.0792
Green Card76.21 6.2874 82.4974
List 61.98 4.6192 66.5992
Net 11.00 0.0825 11.0825
TOTAL 196.97 14.5358 211.5058Is there something in 2008 that can be done to get results as above. Or even a code ( joins, union, or other sorts of functions) ??????????
I am suggesting these to microsoft as to build two types of functions called ADDCOL() and ADDROW() that would add column and row respectively to ur dataset as when u write query for above result set like these
SELECT A.DeptName,A.SubTtlSaleAmtByDept ,
B.SubTtlSaleTaxAmtByDept ,
ADDCOL(SUM(A.SubTtlSaleAmtByDept + B.SubTtlSaleTaxAmtByDept )) as TOTAL,
ADDROW('TOTAL', SUM(A.SubTtlSaleAmtByDept), SUM(B.SubTtlSaleTaxAmtByDept ), SUM(A.SubTtlSaleAmtByDept + B.SubTtlSaleTaxAmtByDept )) -- look ADDROW function requires 4 --- paramaters as no of colums in select list
FROM temapTableA A
INNER JOIN tempTableB B
ON A.DeptName = B.Deptname
select
A.DeptName,
A.SubTtlSaleAmtByDept,
B.SubTtlSaleTaxAmtByDept,
A.SubTtlSaleAmtByDept + B.SubTtlSaleTaxAmtByDept as Total
from
tempTableA A
inner join tempTableB B
on (A.DeptName = B.DeptName);
July 6, 2009 at 3:56 pm
Duh, once again I make the question more complex than it needs to be. :w00t:
Thanks for clearing it up Lynn.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 6, 2009 at 4:00 pm
Guys, I think we are not on the same page here. You got the total right on the isde. But I want the total as one more row below too. (please don't tell me to use union all and hardcord the first column as 'total'; actually that is what i have right now)
July 6, 2009 at 4:01 pm
SSIS (7/6/2009)
Guys, I think we are not on the same page here. You got the total right on the isde. But I want the total as one more row below too. (please don't tell me to use union all and hardcord the first column as 'total'; actually that is what i have right now)
Yep.
July 6, 2009 at 4:09 pm
Hi,
if I'd have ready to use data s describe in my signature (as well as in the sig of many other posters on this forum) I might be able to provide some code snippets using WITH ROLLUP (see BOL for details).
This would actually give you the totals on a separate row.
Maybe this hint will already help you to solve your task. If not, please provide consumable information.
July 6, 2009 at 4:15 pm
Lutz has set you on the right direction, I think. Here is what Lutz was looking for and leading to, I think:
-- create test tables
DECLARE @tableA table
(
deptname VARCHAR(25),
SubTtlSaleAmtByDept FLOAT
)
DECLARE @tableB table
(
deptname VARCHAR(25),
SubTtlSaleTaxAmtByDept FLOAT
)
-- load test data
INSERT INTO
@tableA
(
deptname,
SubTtlSaleAmtByDept
)
SELECT
'Beauty School',
3
UNION ALL
SELECT
'Employee',
44.78
UNION ALL
SELECT
'Green Card',
76.21
UNION ALL
SELECT
'List',
61.98
UNION ALL
SELECT
'Net',
11
INSERT INTO
@tableB
(
deptname,
SubTtlSaleTaxAmtByDept
)
SELECT
'Beauty School',
0.2475
UNION ALL
SELECT
'Employee',
3.2992
UNION ALL
SELECT
'Green Card',
6.2874
UNION ALL
SELECT
'List',
4.6192
UNION ALL
SELECT
'Net',
0.0825
-- query that returns the desired results - you would put the desired results here.
select
ISNULL(A.DeptName, 'Total') AS DeptName,
SUM(A.SubTtlSaleAmtByDept) AS SubTtlSaleAmtByDept,
SUM(B.SubTtlSaleTaxAmtByDept) AS SubTtlSaleTaxAmtByDept,
SUM(A.SubTtlSaleAmtByDept + B.SubTtlSaleTaxAmtByDept) as Total
from
@TableA A inner join
@TableB B
on (A.DeptName = B.DeptName)
GROUP BY
A.deptname
WITH Rollup ;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 6, 2009 at 4:41 pm
Jack Corbett (7/6/2009)
Lutz has set you on the right direction, I think. Here is what Lutz was looking for and leading to, I think:
@ Jack: that's pretty much exactly what I've been thinking of. 😀
Except for the fact that I would expect receiving consumable data from an OP with over 400 points - he/she should know by now.
If you look at the number of lines to set up the data compared to the "real effort" to solve the issue I just didn't feel the need to do the basics... 😉
Great job, anyway! :satisfied:
July 6, 2009 at 5:46 pm
Guys, really Appreciate ur efforts and time. Lmu92 I feel ur concerns. To be honest with I was not expecting a real good answer or solutions, as I have never heard something called "with rollup". It was more of a discussion. Besides all that, that is the only total amount of data I need to process. Those were real dev data, except for table name. Also, just wanted to let you know I tired several times to post the data in a real nice format, couldn't do it. If u are talking about all the create tbl structure and insert statemants (all DDL and DML) , I will keep this in mind, as I still consider myself a rookie here in this case. Anyway, thnaks to all who read and to ones those really got the things done.
Thanks,
July 7, 2009 at 3:14 pm
SSIS (7/6/2009)
... To be honest with I was not expecting a real good answer or solutions, ...
You might want to put some more confidence in this community! 😉
There are over a million people around - so the chances are pretty good to get (almost?) any issue resolved. Especially if you have folks around that use to live SQL rather than just using it... (I wouldn't consider myself belonging to this very special group of the former though...)
Making long story short: Glad we could help. 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply