December 22, 2009 at 6:41 am
abcim (12/22/2009)
I think this is very nice but the problem is that there is not a single Row. So @local_variable must NOT be used to SELECT Query to return multiple Rows (near to 50-52)
If you want multiple rows to be returned from a single call of the sp, then you must return a result set (or a table variable as an output parameter).
Start small and build from there. How many rows are returned by this:
SELECT
d.[Maturity_DetaillId],
d.[ReportId],
d.[RowId],
d.[UpToOneMonths],
d.[OneToThreeMonths],
d.[ThreeToSixMonths],
d.[SixToTwelveMonths],
d.[OneToTwoYears],
d.[TwoToThreeYears],
d.[ThreeToFiveYears],
d.[FiveToTenYears],
d.[MoreThanTenYears],
d.[Total],
d.[PerOfTotalAssets],
d.[Delta]
FROM Maturity_Detail d
WHERE d.[ReportId] = @ReportId -- <--- replace this with a known value
ORDER BY d.[RowId]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 22, 2009 at 6:44 am
Thanks
52 Rows
December 22, 2009 at 6:54 am
abcim (12/22/2009)
Thanks52 Rows
How many different values of RowID do you have?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 22, 2009 at 6:55 am
abcim (12/22/2009)
Thanks52 Rows
Is this 52 weeks, or is the 52 just a concidence?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 22, 2009 at 7:02 am
>>How many different values of RowID do you have?
All are different. 1,3, ... 52, so on
>> Is this 52 weeks, or is the 52 just a concidence?
No
This is not exactly 52 but atleast or atmost 52.
December 22, 2009 at 7:06 am
Sorry to join in late, but looking at your stored procedure in the first post, all your stored procedure is doing is setting local variables to what will most likely be the last values from the query, and then doing nothing with them. There isn't even any method for returning the values back to the calling routine.
So, with that, what are you doing with this stored procedure and how are the returned values supposed to be used?
December 22, 2009 at 7:11 am
Cool - now the biggie: are the values of RowID unique within the table?
If not, are they always uniquewithin a single [ReportId]?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 22, 2009 at 7:15 am
>> Sorry to join in late, but looking at your stored procedure in the first post, all your stored procedure is doing is setting local variables to what will most likely be the last values from the query, and then doing nothing with them. There isn't even any method for returning the values back to the calling routine.
You are 100% right
>>So, with that, what are you doing with this stored procedure and how are the returned values supposed to be used?
The reasons for these variables are simply use it in another column.
For example,
@RowId is used in @Total2
@Total2,@Total are used in @Difference
Hope fully you help me.
December 22, 2009 at 7:17 am
>> are the values of RowID unique within the table
No
>> are they always unique within a single [ReportId]
Yes
December 22, 2009 at 7:36 am
Let's take another step back. Help us help you, read the first the article I have referenced below in my signature block. Follow the instructions in that article and provide us with the DDL for the tables, some sample data (doesn't have to be actually data, just data the properly represents the actual data), and (even though not directly mentioned in the article) the expected results from the procedure based on the sample data.
If you do this, you will get much better assistance, and as a bonus you will also get tested code.
December 22, 2009 at 7:39 am
abcim (12/22/2009)
>> are the values of RowID unique within the tableNo
>> are they always unique within a single [ReportId]
Yes
Thanks. Last question, I think: for each RowID in the table Maturity_Detail, you want a value or two from the same table, for the same RowID, but for the ReportID which corresponds to the previous month - picked from MaturitiesReports?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 22, 2009 at 7:42 am
Lynn Pettis (12/22/2009)
Let's take another step back. Help us help you, read the first the article I have referenced below in my signature block. Follow the instructions in that article and provide us with the DDL for the tables, some sample data (doesn't have to be actually data, just data the properly represents the actual data), and (even though not directly mentioned in the article) the expected results from the procedure based on the sample data.If you do this, you will get much better assistance, and as a bonus you will also get tested code.
Hey Lynn, sorry mate we seem to be cross-posting here. I agree wholeheartedly with your comments, @abcim take note that although you may get a solution from this, it will of course be untested.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 22, 2009 at 7:58 am
Try this:
DECLARE @ReportId int, @ReportId2 INT
SET @ReportId = -- YOUR VALUE
SELECT Top 1 @ReportId2 = ReportId
FROM MaturitiesReports
WHERE [Report_Month] < (
SELECT [Report_Month]
FROM MaturitiesReports
WHERE [ReportId] = @ReportId)
ORDER BY [Report_Month] DESC
SELECT
d.[Maturity_DetaillId],
d.[ReportId],
d.[RowId],
d.[UpToOneMonths],
d.[OneToThreeMonths],
d.[ThreeToSixMonths],
d.[SixToTwelveMonths],
d.[OneToTwoYears],
d.[TwoToThreeYears],
d.[ThreeToFiveYears],
d.[FiveToTenYears],
d.[MoreThanTenYears],
d.[Total],
d.[PerOfTotalAssets],
d.[Delta],
d2.[Total] AS Total2
FROM Maturity_Detail d
LEFT JOIN Maturity_Detail d2 ON d2.[RowId] = d.[RowId] AND d2.[ReportId] = @ReportId2
WHERE d.[ReportId] = @ReportId
ORDER BY d.[RowId]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 22, 2009 at 8:39 am
Chris Morris-439714 (12/22/2009)
Lynn Pettis (12/22/2009)
Let's take another step back. Help us help you, read the first the article I have referenced below in my signature block. Follow the instructions in that article and provide us with the DDL for the tables, some sample data (doesn't have to be actually data, just data the properly represents the actual data), and (even though not directly mentioned in the article) the expected results from the procedure based on the sample data.If you do this, you will get much better assistance, and as a bonus you will also get tested code.
Hey Lynn, sorry mate we seem to be cross-posting here. I agree wholeheartedly with your comments, @abcim take note that although you may get a solution from this, it will of course be untested.
Not a problem, I did join this thread late.
December 23, 2009 at 3:40 am
Sorry this will not work,
However I say, Thank you all of you who Help me in this regard, especially Chris and Lynn.
I had sorted out by creating Custom Function,
Here is complete SQL which may helps other
CREATE FUNCTION [dbo].[GetTotal2](@RowId varchar(50),@ReportId varchar(50))
RETURNS varchar(50)
BEGIN
DECLARE @result varchar(50)
(SELECT @result=[Total] FROM Maturity_Detail WHERE ([RowId]=@RowId)
AND ([ReportId]=(SELECT Top 1 ReportId FROM MaturitiesReports WHERE [Report_Month]
< (SELECT [Report_Month] FROM MaturitiesReports WHERE [ReportId]=@ReportId) ORDER BY [Report_Month] DESC)))
RETURN @result
END
CREATE FUNCTION [dbo].[GetDifference](@Total varchar(50),@Total2 varchar(50))
RETURNS varchar(50)
BEGIN
DECLARE @result varchar(50)
SELECT @result=CASE WHEN ISNUMERIC(@Total) = 1 THEN
CASE WHEN ISNUMERIC(@Total2) = 1 THEN
CASE WHEN Convert(float,@Total) - Convert(float,@Total2)>0 Then
1-- >0
ELSE
CASE WHEN Convert(float,@Total) - Convert(float,@Total2)<0 Then
2-- <0
ELSE
0-- ==0
END
END
ELSE
-1 -- for not converted
END
ELSE
-1 -- for not converted
END
RETURN @result
END
CREATE PROCEDURE [dbo].[Maturity_Report_And_Detail_GetByReportId]
@ReportId int
AS
SELECT
[Maturity_DetaillId],
[ReportId],
[RowId],
[UpToOneMonths],
[OneToThreeMonths],
[ThreeToSixMonths],
[SixToTwelveMonths],
[OneToTwoYears],
[TwoToThreeYears],
[ThreeToFiveYears],
[FiveToTenYears],
[MoreThanTenYears],
[Total],
[PerOfTotalAssets],
[Delta],
[dbo].GetTotal2([RowId],@ReportId),
[dbo].GetDifference([Total],[dbo].GetTotal2([RowId],@ReportId))
FROM Maturity_Detail
WHERE
[ReportId]=@ReportId
ORDER BY [RowId]
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply