December 23, 2009 at 4:08 am
abcim (12/23/2009)
Sorry this will not work
It's always good to see the OP (original poster) posting up their final solution, it's often a source of learning and discussion. However, I feel in this case that the final solution is a long way from being a "good solution", it's hacking rather than programming by design and will perform poorly against large tables or in a busy environment. It's easy to say "there's no time to do it properly right now, maybe later", but right now is the only time to do it properly because if it doesn't fail, it will be forgotten, and if it does fail, then it will be rewritten again in a hurry and under pressure.
Please abcim, take a little time to test the solutions offered to you and say why they "will not work". I'm sure it won't take long for someone to design and code a perfectly scalable and performant solution for you, something which you can in all confidence forget about. In the process you are highly likely to learn a good lesson or two about programming in TSQL.
Best regards
ChrisM
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 23, 2009 at 5:34 am
What do you mean Function is a poor programming practice?
December 23, 2009 at 6:06 am
abcim (12/23/2009)
What do you mean Function is a poor programming practice?
Good Lord, no - functions can be incredibly useful.
Can you answer this: for any one reportID passed into the sproc to retrieve your data, what is the reportID returned by this bit:
... SELECT Top 1 ReportId FROM MaturitiesReports ...
It's constant for each execution of the stored procedure right? But you're evaluating it for every row of your output.
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 23, 2009 at 6:21 am
>>Can you answer this: for any one reportID passed into the sproc to retrieve your data, what is the reportID returned by this bit:
... SELECT Top 1 ReportId FROM MaturitiesReports ...
It's constant for each execution of the stored procedure right? But you're evaluating it for every row of your output.
Without knowing about all Db tables, how can you say this?
For answering this you need to understand all Db Tables structure and their relationships and keys information.
December 23, 2009 at 6:35 am
abcim (12/23/2009)
>>Can you answer this: for any one reportID passed into the sproc to retrieve your data, what is the reportID returned by this bit:... SELECT Top 1 ReportId FROM MaturitiesReports ...
It's constant for each execution of the stored procedure right? But you're evaluating it for every row of your output.
Without knowing about all Db tables, how can you say this?
For answering this you need to understand all Db Tables structure and their relationships and keys information.
Not at all. You pass a reportid into your stored procedure, and a resultset is extracted from a table using the reportid as a filter. In addition, you look at a setup table and collect the reportid for the same report, for last month. Then using the reportid for the report for last month, you compare this month's value with last month's value. It's easy.
(SELECT Top 1 ReportId
FROM MaturitiesReports
WHERE [Report_Month] < (
SELECT [Report_Month]
FROM MaturitiesReports
WHERE [ReportId] = @ReportId)
ORDER BY [Report_Month] DESC)
For any value of @reportid, the value of reportid for the previous month is fixed and constant. You don't need to evaluate it for every row of your output.
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 23, 2009 at 6:55 am
Yes this is very good Point to implement this
December 23, 2009 at 7:04 am
abcim (12/23/2009)
Yes this is very good Point to implement this
Well I'm relieved that we agree on this point because it paves the way to make your query much simpler and faster than you are currently using.
So you pick up rows from Maturity_Detail which have reportid = @reportid,
and compare rows from Maturity_Detail, with the same rowid, but with the reportid for the report for the previous month, correct?
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 23, 2009 at 7:08 am
Now what do you say man
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER 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]=@ReportId
RETURN @result
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER 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
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Maturity_Report_And_Detail_GetByReportId]
@ReportId int
AS
DECLARE @ReportId2 int
SELECT Top 1 @ReportId2=ReportId FROM MaturitiesReports WHERE [Report_Month]
< (SELECT [Report_Month] FROM MaturitiesReports WHERE [ReportId]=@ReportId) ORDER BY [Report_Month] DESC
SELECT
[Maturity_DetaillId],
[ReportId],
[RowId],
[UpToOneMonths],
[OneToThreeMonths],
[ThreeToSixMonths],
[SixToTwelveMonths],
[OneToTwoYears],
[TwoToThreeYears],
[ThreeToFiveYears],
[FiveToTenYears],
[MoreThanTenYears],
[Total],
[PerOfTotalAssets],
[Delta],
[dbo].GetTotal2([RowId],@ReportId2),
[dbo].GetDifference([Total],[dbo].GetTotal2([RowId],@ReportId2)) AS Differences
FROM Maturity_Detail
WHERE
[ReportId]=@ReportId
ORDER BY [RowId]
December 23, 2009 at 7:13 am
Correct
YES
December 23, 2009 at 8:12 am
abcim (12/23/2009)
Correct
YES
OK, now try running the code I posted a couple of pages earlier. Note that it doesn't do the calculation between the two values yet, but it does show them as adjacent columns, so you can eyeball them.
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 23, 2009 at 9:33 am
abcim (12/23/2009)
>>Can you answer this: for any one reportID passed into the sproc to retrieve your data, what is the reportID returned by this bit:... SELECT Top 1 ReportId FROM MaturitiesReports ...
It's constant for each execution of the stored procedure right? But you're evaluating it for every row of your output.
Without knowing about all Db tables, how can you say this?
For answering this you need to understand all Db Tables structure and their relationships and keys information.
You are right, we don't know your table structures and relationships, and other key information.
Which goes back to my post where I asked you to read the first article I reference in my signature block regarding asking for assistance and following the instructions in that article regarding the items YOU should post (table definitions, sample data), as well as asking for the expected results based on the sample data.
Since we can't see that, you need to provide us with that information. If not, all we can do is take pot shots in the dark and hope we are able to help you. Which means we get that constant back and forth, "No, that didn't work (with little or no added info usually)," followed by more shots in the dark.
Give us all the info up front, as shown in that article, plus expected results and what you have already tried and in return you will probably learn something new as you may get several different workable answers (as more people may jump in to help) plus the bonus of TESTED code.
December 28, 2009 at 10:43 am
As Lynn stated, it is very difficult to provide a good solution without table structures and sample data however here is another take utilizing CTE's. I have no idea if this works or if it comes close to providing you with a solution but it's an example to work with...
DECLARE
@ReportId INT
SET @ReportId = 1;
;WITH cteRowID AS
(
SELECT
[RowID]
FROM
Maturity_Detail
WHERE
[ReportId]=@ReportId
),
cteReportMonth AS
(
SELECT
reportid
FROM
MaturitiesReports
WHERE
[Report_Month] <
(
SELECT
TOP 1 [Report_Month]
FROM
MaturitiesReports
WHERE
[ReportId] < @ReportId
ORDER BY
[Report_Month] DESC
)
),
cteTotal AS
(
SELECT
cteRM.reportid,
md.[Total] AS [Total2]
FROM
Maturity_Detail md
JOIN cteRowID cteRI ON
cteRI.rowid = md.rowid
JOIN cteReportMonth cteRM ON
cteRM.reportid = md.reportid
)
SELECT
md.[Maturity_DetaillId],
md.[ReportId],
md.[RowId],
md.[UpToOneMonths],
md.[OneToThreeMonths],
md.[ThreeToSixMonths],
md.[SixToTwelveMonths],
md.[OneToTwoYears],
md.[TwoToThreeYears],
md.[ThreeToFiveYears],
md.[FiveToTenYears],
md.[MoreThanTenYears],
md.[Total],
md.[PerOfTotalAssets],
md.[Delta],
cteTOT.[Total2],
CASE WHEN ISNUMERIC(md.[Total]) = 1 THEN
CASE WHEN ISNUMERIC(cteTOT.[Total2]) = 1 THEN
CASE WHEN CONVERT(FLOAT,md.[Total]) - CONVERT(FLOAT,cteTOT.[Total2])>0 THEN
1-- >0
ELSE
CASE WHEN CONVERT(FLOAT,md.[Total]) - CONVERT(FLOAT,cteTOT.[Total2])<0 THEN
2-- <0
ELSE
0-- ==0
END
END
ELSE
-1 -- for not converted
END
ELSE
-1 -- for not converted
END
FROM
Maturity_Detail md
JOIN cteTotal cteTOT ON
cteTOT.reportid = md.reportid
ORDER BY
md.[RowId]
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply