SQL stored procedure - help needed

SQL stored procedure - help needed

Post by ckoster.. » Thu, 06 Apr 2000 04:00:00



Good morning!

I have a complicated sp that results in a table as follows:

ID_Deal   ForecastYear   Probable_YN   Jan   Feb   Mar..etc.  YearTotal
501       2000           1             50    0     0          50
501       2000           0             0     50    50         100

What I need to do is make a table that results in the following:

ID_   Forecast         Jan_         Feb_         Mar_
Deal  Year       Jan   Prob   Feb   Prob   Mar   Prob..etc.  YearTotal
501   2000       50    1      50    0      50    0           150

How would I go about making the result table?

Thanks for your help!  It is greatly appreciated.

Regards,
Cora

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

SQL stored procedure - help needed

Post by Robert Leithise » Thu, 06 Apr 2000 04:00:00


Without getting into all of the details, create derived columns based on the
value of Probable_YN and use a select case statement to determine where the
figure should come from.  (select case probable_yn = 1 then jan else 0) as
Jan_prob...

Robert Leithiser


Quote:> Good morning!

> I have a complicated sp that results in a table as follows:

> ID_Deal   ForecastYear   Probable_YN   Jan   Feb   Mar..etc.  YearTotal
> 501       2000           1             50    0     0          50
> 501       2000           0             0     50    50         100

> What I need to do is make a table that results in the following:

> ID_   Forecast         Jan_         Feb_         Mar_
> Deal  Year       Jan   Prob   Feb   Prob   Mar   Prob..etc.  YearTotal
> 501   2000       50    1      50    0      50    0           150

> How would I go about making the result table?

> Thanks for your help!  It is greatly appreciated.

> Regards,
> Cora

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

SQL stored procedure - help needed

Post by C. E. Buttle » Thu, 06 Apr 2000 04:00:00


Redesign the table and rewrite the SP (which you did not include, by the
way).


Quote:> Good morning!

> I have a complicated sp that results in a table as follows:

> ID_Deal   ForecastYear   Probable_YN   Jan   Feb   Mar..etc.  YearTotal
> 501       2000           1             50    0     0          50
> 501       2000           0             0     50    50         100

> What I need to do is make a table that results in the following:

> ID_   Forecast         Jan_         Feb_         Mar_
> Deal  Year       Jan   Prob   Feb   Prob   Mar   Prob..etc.  YearTotal
> 501   2000       50    1      50    0      50    0           150

> How would I go about making the result table?

> Thanks for your help!  It is greatly appreciated.

> Regards,
> Cora

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

SQL stored procedure - help needed

Post by ckoster.. » Thu, 06 Apr 2000 04:00:00


Here's the initial sp.

CREATE PROCEDURE [usp_CTT_LookupForecast] AS







if exists (select * from sysobjects where id=object_id
(N'LookupDollarAmounts') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table LookupDollarAmounts

CREATE TABLE LookupDollarAmounts(
ID_Deal int,
ForecastYear int,
Probable_YN int,
Jan money,
Feb money,
Mar money,
Apr money,
May money,
Jun money,
Jul money,
Aug money,
Sep money,
Oct money,
Nov money,
Dec money,
YearTotal money)

if exists (select * from sysobjects where id=object_id
(N'LookupForecast') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table LookupForecast

CREATE TABLE LookupForecast(
ID_Deal int,
ForecastMonth varchar(20),
ForecastYear int,
Probable_YN int,
PotentialFee money)

DECLARE Forecast_cursor CURSOR FOR
SELECT tblForecast.ID_Deal, ForecastMonth, ForecastYear, Probable_YN,
tblForecast.PotentialFee
FROM (tblForecast LEFT JOIN
        tblDealHistorys ON (tblForecast.ID_Deal =
tblDealHistorys.ID_Deal))
ORDER BY tblForecast.ID_Deal

OPEN Forecast_cursor

FETCH FROM Forecast_cursor


        INSERT INTO LookupForecast VALUES

        FETCH FROM Forecast_cursor


END

select * from LookupForecast

INSERT INTO LookupDollarAmounts
SELECT P1.*, (P1.Jan + P1.Feb + P1.Mar + P1.Apr + P1.May + P1.Jun +
P1.Jul + P1.Aug + P1.Sep + P1.Oct + P1.Nov + P1.Dec)
AS YearTotal
FROM
(select ID_Deal, ForecastYear, Probable_YN,
        SUM(CASE ForecastMonth WHEN 'January' THEN PotentialFee ELSE 0
END) AS Jan,
        SUM(CASE ForecastMonth WHEN 'February' THEN PotentialFee ELSE 0
END) AS Feb,
        SUM(CASE ForecastMonth WHEN 'March' THEN PotentialFee ELSE 0
END) AS Mar,
        SUM(CASE ForecastMonth WHEN 'April' THEN PotentialFee ELSE 0
END) AS Apr,
        SUM(CASE ForecastMonth WHEN 'May' THEN PotentialFee ELSE 0 END)
AS May,
        SUM(CASE ForecastMonth WHEN 'June' THEN PotentialFee ELSE 0
END) AS Jun,
        SUM(CASE ForecastMonth WHEN 'July' THEN PotentialFee ELSE 0
END) AS Jul,
        SUM(CASE ForecastMonth WHEN 'August' THEN PotentialFee ELSE 0
END) AS Aug,
        SUM(CASE ForecastMonth WHEN 'September' THEN PotentialFee ELSE
0 END) AS Sep,
        SUM(CASE ForecastMonth WHEN 'October' THEN PotentialFee ELSE 0
END) AS Oct,
        SUM(CASE ForecastMonth WHEN 'November' THEN PotentialFee ELSE 0
END) AS Nov,
        SUM(CASE ForecastMonth WHEN 'December' THEN PotentialFee ELSE 0
END) AS Dec

FROM LookupForecast
GROUP BY ID_Deal, ForecastYear,Probable_YN) AS P1

CLOSE Forecast_cursor
DEALLOCATE Forecast_cursor

The table - LookupDollarAmounts - is where the initial result set comes
from:

ID_Deal   ForecastYear   Probable_YN   Jan   Feb   Mar..etc.  YearTotal
501       2000           1             50    0     0          50
501       2000           0             0     50    50         100

I am not quite sure how to go about getting the table into the format
that I need:

ID_   Forecast         Jan_         Feb_         Mar_
Deal  Year       Jan   Prob   Feb   Prob   Mar   Prob..etc.  YearTotal
501   2000       50    1      50    0      50    0           150

Any help you are able to provide is greatly appreciated.

Regards,
Cora



> Redesign the table and rewrite the SP (which you did not include, by
the
> way).



> > Good morning!

> > I have a complicated sp that results in a table as follows:

> > ID_Deal   ForecastYear   Probable_YN   Jan   Feb   Mar..etc.
YearTotal
> > 501       2000           1             50    0     0          50
> > 501       2000           0             0     50    50         100

> > What I need to do is make a table that results in the following:

> > ID_   Forecast         Jan_         Feb_         Mar_
> > Deal  Year       Jan   Prob   Feb   Prob   Mar   Prob..etc.
YearTotal
> > 501   2000       50    1      50    0      50    0           150

> > How would I go about making the result table?

> > Thanks for your help!  It is greatly appreciated.

> > Regards,
> > Cora

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

1. SQL 7 Stored procedure question Help needed

Thank you for reading
I want a stored procedure to process table1 based on the order selected
in a table2

First table
Name varchar()    ID integer
Dupont                   120
Zou                         100
Second table

Field1           Sort_ORDER   DESC_ASC      (sort_order column is
updated by users)
Name               1                      DESC             (DESC_ASC is
updated by users)
Id                     4                      ASC

In dynamic sql it's easy to retrieve field1 based on the sort_order and
construct an sql statement such as "select name,id from first table
order by"+ var1+","+var2
Is it possible to do that in a stored proc by passing the order by
parameter How to do that ? Notice I've about 10 fields and don't want to
write all the combinations of the select phrase

Hope you can help me.Need that urgently. If not possible need also a
reply.
Don't want to search for nothing.Thanks in advance
Michel Dupont (Belgium)

2. Visual Cafe and Postgres

3. Need help on recursive SQL in a stored procedure

4. how do I apply

5. Need help on retrieving OUTPUT parameter in stored procedure and Visual Interdev 6.0 - SQL Server 6.5

6. sqlldr and LOBFILE

7. Novice needs help -- syntax issue MS-SQL 6.5 stored procedure

8. Reading Bit in Access

9. I Need Help With T-SQL Stored Procedures

10. Need help: Calling PL/SQL stored Procedures which use htp.print from SQLJ

11. Need Help on SQL Stored Procedure Delay!

12. PL/SQL - need help with stored procedure

13. Help: need to create dynamic SQL inside a stored procedure