Need help in constructing temporary table through stored procedure  
Author Message
Kanneily





PostPosted: Wed Aug 08 17:11:40 CDT 2007 Top

SQL Server Developer >> Need help in constructing temporary table through stored procedure

I got a sql server table which is like the following

Group Description Amount
3000 Net Sales 100000
5000 Total Var Cost 75000
7000 Total Other Cost 20000

Now I need to make a Net Profic Description field out of the above table

So I am thinking of creating a stored procedure using temporary table to
give the following output. Thus 6000 group will be calculated as 3000-5000
while the 9000 group is also a caculated group which will be 6000-7000.

I would like to know how to build this temp table through stored procedure.
Thanks


3000 Net Sales 100000
5000 Total Variable Cost 75000

6000 Gross Margin 25000

7000 Total Other Cost 20000

9000 Net Profit 5000

SQL Server162  
 
 
Tom





PostPosted: Wed Aug 08 17:11:40 CDT 2007 Top

SQL Server Developer >> Need help in constructing temporary table through stored procedure You probably don't need a temp table:

select * from MyTable
union all
select 6000, 'Gross Margin', sum (case when [Group] = 3000 then Amount
else -Amount end)
from MyTable
where Group in (3000, 5000)
union all
select 9000, 'Net Profit', sum (case when [Group] = 3000 then Amount
else -Amount end)
from MyTable
where Group in (3000, 5000, 7000)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau




I got a sql server table which is like the following

Group Description Amount
3000 Net Sales 100000
5000 Total Var Cost 75000
7000 Total Other Cost 20000

Now I need to make a Net Profic Description field out of the above table

So I am thinking of creating a stored procedure using temporary table to
give the following output. Thus 6000 group will be calculated as 3000-5000
while the 9000 group is also a caculated group which will be 6000-7000.

I would like to know how to build this temp table through stored procedure.
Thanks


3000 Net Sales 100000
5000 Total Variable Cost 75000

6000 Gross Margin 25000

7000 Total Other Cost 20000

9000 Net Profit 5000

 
 
Jack





PostPosted: Wed Aug 08 20:34:02 CDT 2007 Top

SQL Server Developer >> Need help in constructing temporary table through stored procedure Thanks for your help Tom. I appreciate it. However the sql is not running
with an error missing operator in expression. Any thoughts. Thanks




> You probably don't need a temp table:
>
> select * from MyTable
> union all
> select 6000, 'Gross Margin', sum (case when [Group] = 3000 then Amount
> else -Amount end)
> from MyTable
> where Group in (3000, 5000)
> union all
> select 9000, 'Net Profit', sum (case when [Group] = 3000 then Amount
> else -Amount end)
> from MyTable
> where Group in (3000, 5000, 7000)
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>


> I got a sql server table which is like the following
>
> Group Description Amount
> 3000 Net Sales 100000
> 5000 Total Var Cost 75000
> 7000 Total Other Cost 20000
>
> Now I need to make a Net Profic Description field out of the above table
>
> So I am thinking of creating a stored procedure using temporary table to
> give the following output. Thus 6000 group will be calculated as 3000-5000
> while the 9000 group is also a caculated group which will be 6000-7000.
>
> I would like to know how to build this temp table through stored procedure.
> Thanks
>
>
> 3000 Net Sales 100000
> 5000 Total Variable Cost 75000
>
> 6000 Gross Margin 25000
>
> 7000 Total Other Cost 20000
>
> 9000 Net Profit 5000
>
>
 
 
Tom





PostPosted: Wed Aug 08 20:42:17 CDT 2007 Top

SQL Server Developer >> Need help in constructing temporary table through stored procedure Might be that Group is a reserved word. Let's try:

select * from MyTable
union all
select 6000, 'Gross Margin', sum (case when [Group] = 3000 then Amount
else -Amount end)
from MyTable
where [Group] in (3000, 5000)
union all
select 9000, 'Net Profit', sum (case when [Group] = 3000 then Amount
else -Amount end)
from MyTable
where [Group] in (3000, 5000, 7000)

If that doesn't do it, please post the DDL for your table.


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau




Thanks for your help Tom. I appreciate it. However the sql is not running
with an error missing operator in expression. Any thoughts. Thanks




> You probably don't need a temp table:
>
> select * from MyTable
> union all
> select 6000, 'Gross Margin', sum (case when [Group] = 3000 then Amount
> else -Amount end)
> from MyTable
> where Group in (3000, 5000)
> union all
> select 9000, 'Net Profit', sum (case when [Group] = 3000 then Amount
> else -Amount end)
> from MyTable
> where Group in (3000, 5000, 7000)
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>


> I got a sql server table which is like the following
>
> Group Description Amount
> 3000 Net Sales 100000
> 5000 Total Var Cost 75000
> 7000 Total Other Cost 20000
>
> Now I need to make a Net Profic Description field out of the above table
>
> So I am thinking of creating a stored procedure using temporary table to
> give the following output. Thus 6000 group will be calculated as
> 3000-5000
> while the 9000 group is also a caculated group which will be 6000-7000.
>
> I would like to know how to build this temp table through stored
> procedure.
> Thanks
>
>
> 3000 Net Sales 100000
> 5000 Total Variable Cost 75000
>
> 6000 Gross Margin 25000
>
> 7000 Total Other Cost 20000
>
> 9000 Net Profit 5000
>
>

 
 
Jack





PostPosted: Thu Aug 09 14:20:04 CDT 2007 Top

SQL Server Developer >> Need help in constructing temporary table through stored procedure Tom,
I have tried to change the group name to a different name. However, it did
not work. Never mind Tom. I am trying to handle this in a different way and
am not pursuing the way I thought I would. Thanks again for your generous
help.
Best regards.



> Might be that Group is a reserved word. Let's try:
>
> select * from MyTable
> union all
> select 6000, 'Gross Margin', sum (case when [Group] = 3000 then Amount
> else -Amount end)
> from MyTable
> where [Group] in (3000, 5000)
> union all
> select 9000, 'Net Profit', sum (case when [Group] = 3000 then Amount
> else -Amount end)
> from MyTable
> where [Group] in (3000, 5000, 7000)
>
> If that doesn't do it, please post the DDL for your table.
>
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>


> Thanks for your help Tom. I appreciate it. However the sql is not running
> with an error missing operator in expression. Any thoughts. Thanks
>
>

>
> > You probably don't need a temp table:
> >
> > select * from MyTable
> > union all
> > select 6000, 'Gross Margin', sum (case when [Group] = 3000 then Amount
> > else -Amount end)
> > from MyTable
> > where Group in (3000, 5000)
> > union all
> > select 9000, 'Net Profit', sum (case when [Group] = 3000 then Amount
> > else -Amount end)
> > from MyTable
> > where Group in (3000, 5000, 7000)
> >
> > --
> > Tom
> >
> > ----------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >


> > I got a sql server table which is like the following
> >
> > Group Description Amount
> > 3000 Net Sales 100000
> > 5000 Total Var Cost 75000
> > 7000 Total Other Cost 20000
> >
> > Now I need to make a Net Profic Description field out of the above table
> >
> > So I am thinking of creating a stored procedure using temporary table to
> > give the following output. Thus 6000 group will be calculated as
> > 3000-5000
> > while the 9000 group is also a caculated group which will be 6000-7000.
> >
> > I would like to know how to build this temp table through stored
> > procedure.
> > Thanks
> >
> >
> > 3000 Net Sales 100000
> > 5000 Total Variable Cost 75000
> >
> > 6000 Gross Margin 25000
> >
> > 7000 Total Other Cost 20000
> >
> > 9000 Net Profit 5000
> >
> >
>
>
 
 
Tom





PostPosted: Thu Aug 09 14:51:36 CDT 2007 Top

SQL Server Developer >> Need help in constructing temporary table through stored procedure Well, if you post your DDL, we can solve the query for you.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau




Tom,
I have tried to change the group name to a different name. However, it did
not work. Never mind Tom. I am trying to handle this in a different way and
am not pursuing the way I thought I would. Thanks again for your generous
help.
Best regards.



> Might be that Group is a reserved word. Let's try:
>
> select * from MyTable
> union all
> select 6000, 'Gross Margin', sum (case when [Group] = 3000 then Amount
> else -Amount end)
> from MyTable
> where [Group] in (3000, 5000)
> union all
> select 9000, 'Net Profit', sum (case when [Group] = 3000 then Amount
> else -Amount end)
> from MyTable
> where [Group] in (3000, 5000, 7000)
>
> If that doesn't do it, please post the DDL for your table.
>
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>


> Thanks for your help Tom. I appreciate it. However the sql is not running
> with an error missing operator in expression. Any thoughts. Thanks
>
>

>
> > You probably don't need a temp table:
> >
> > select * from MyTable
> > union all
> > select 6000, 'Gross Margin', sum (case when [Group] = 3000 then Amount
> > else -Amount end)
> > from MyTable
> > where Group in (3000, 5000)
> > union all
> > select 9000, 'Net Profit', sum (case when [Group] = 3000 then Amount
> > else -Amount end)
> > from MyTable
> > where Group in (3000, 5000, 7000)
> >
> > --
> > Tom
> >
> > ----------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >


> > I got a sql server table which is like the following
> >
> > Group Description Amount
> > 3000 Net Sales 100000
> > 5000 Total Var Cost 75000
> > 7000 Total Other Cost 20000
> >
> > Now I need to make a Net Profic Description field out of the above table
> >
> > So I am thinking of creating a stored procedure using temporary table to
> > give the following output. Thus 6000 group will be calculated as
> > 3000-5000
> > while the 9000 group is also a caculated group which will be 6000-7000.
> >
> > I would like to know how to build this temp table through stored
> > procedure.
> > Thanks
> >
> >
> > 3000 Net Sales 100000
> > 5000 Total Variable Cost 75000
> >
> > 6000 Gross Margin 25000
> >
> > 7000 Total Other Cost 20000
> >
> > 9000 Net Profit 5000
> >
> >
>
>