Change Data Appearance, Vertical to Horizontal
-
Hello, my question involves a stored proc for an invoicing module. I am creating it for attachment to a form in Crystal Reports. My current procedure returns the right data, but I want it to appear differently than it appears currently.
Script:
declare @BUS_ID int
set @bus_id = 1019635
SELECT P.Period, CV.VariableLabel, IsNull(dbo.GetUsedValue(P.BusinessPeriodID, CV.CalculationVariableID), '') AS Value
FROM
VW_LMS_CalculationVariables AS CV
INNER JOIN VW_LMS_BusinessPeriods AS P
ON CV.BusinessClassID = P.BusinessClassID AND
dbo.forceDate(P.EffectiveDate) = dbo.forceDate(CV.EffectiveDate)
WHERE P.BusinessID = @BUS_ID
ORDER BY CV.VariableLabel, P.Period
This returns (shortened version):
Period VariableLabel Value
2003 Additional Rate 347.85
2004 Additional Rate 360.15
2003 Base Rate 115.98
2004 Base Rate 120.09
2003 BID Assessed? False
2004 BID Assessed? False
2003 Original Tax Due 463.83
2004 Original Tax Due 480.24
2003 Tax Total 115.98
2004 Tax Total 120.09
and what I would like to have returned looks like:
Period Additional Rate Base Rate BID Assessed?
2001 283.40 109.02 False
2002 429.02 112.95 False
2003 347.85 115.98 False
2004 360.15 120.09 False
Period Original Tax Due TaxTotal
2001 392.42 109.02
2002 541.97 112.95
2003 463.83 115.98
2004 480.24 120.09
In other words, instead of appearing vertically I would like it to appear horizontally. I could always use a cursor, temp table, etc. but SPEED is very important while thousands of invoices are printing. Any ideas, gurus? Thanks so much..... Linda
-
Grumble grumble grumble. The first reply was extensive and when I hit Preview.....gone. This will be a little more curt (believe it or not).
Create a variable based table with BusinessClassID, ViarableLabel, CalculateVariableID, and EffectiveDate columns. I know you said no temp tables, but I don't know what you had in mind and I think this will perform fine (I'm assuming you are running SQL Server 2000 and can use declared tables instead of having to create temporary ones). SELECT from the variables (CV) table into the declared table where the ClassId is in the list of ClassIDs from the Periods table matching the BusinessId. I am assuming (ok, hoping) here that the BusinessId is indexed along with ClassId and that BusinessId filters out the vast majority of the rows.
Next, SELECT from the Periods table returning 6 columns. The first is Period direct from the table. The next five columns return IsNull(dbo.GetUsedValue(P.BusinessPeriodID, CV?.CalculationVariableID), '') where ? gets changed to the associated LEFT JOIN table for that column. This means there will be 5 LEFT JOINs against the variable based table you just populated each one filtering on EffectiveDate and BusinessClassID and a specific VariableLabel.
This ultimately involves 1 INSERT, 2 SELECTS, 1 Sub-select, and 5 joins, but so long as BusinessId filters out that vast majority of the rows (or its a small table which I really doubt) I think this should perform quite well. Especially since the Periods table accesses will likely be index bound (ok, I'm guessing).
-
All right, thanks to you I am closer, but I don’t quite have my head wrapped around how to make the left joins against the variable label unique for each label. And yes, we are on SQL Server 2000, BusinessId and ClassID are indexed, and BusinessID filters out the vast majority of the rows. Period table is also indexed.
Here is my code so far, which of course hangs up toward the end:
drop table #DetailTempTable
declare @BUS_ID int
set @bus_id = 1019635
CREATE TABLE #DetailTempTable
(BusinessClassID INT, VariableLabel Varchar(50), CalculationVariableID INT, EffectiveDate DateTime)
INSERT #DetailTempTable (BusinessClassID, VariableLabel, CalculationVariableID, EffectiveDate)
SELECT CV.BusinessClassID, CV.VariableLabel, CV.CalculationVariableID,CV.EffectiveDate
FROM
VW_LMS_CalculationVariables AS CV
INNER JOIN VW_LMS_BusinessPeriods AS P
ON CV.BusinessClassID = P.BusinessClassID AND
dbo.forceDate(P.EffectiveDate) = dbo.forceDate(CV.EffectiveDate)
WHERE P.BusinessID = @BUS_ID
ORDER BY CV.VariableLabel, P.Period
SELECT P.Period, d1.VariableLabel, IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D1.CalculationVariableID), '') as value
FROM
VW_LMS_BusinessPeriods AS P
Left Join #DetailTempTable AS D1
on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d1.EffectiveDate)
and d1.BusinessClassID = P.BusinessClassID
and P.BusinessID = @BUS_ID
and d1.VariableLabel in
(
SELECT CV.VariableLabel
FROM
VW_LMS_CalculationVariables AS CV
INNER JOIN VW_LMS_BusinessPeriods AS P
ON CV.BusinessClassID = P.BusinessClassID AND
dbo.forceDate(P.EffectiveDate) = dbo.forceDate(CV.EffectiveDate)
WHERE P.BusinessID = @BUS_ID
)
Thanks for your help
-
SELECT P.Period, d1.VariableLabel, IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D1.CalculationVariableID), '') as value
FROM
VW_LMS_BusinessPeriods AS P
Left Join #DetailTempTable AS D1
on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d1.EffectiveDate)
and d1.BusinessClassID = P.BusinessClassID
and P.BusinessID = @BUS_ID
and d1.VariableLabel in
(
SELECT CV.VariableLabel
FROM
VW_LMS_CalculationVariables AS CV
INNER JOIN VW_LMS_BusinessPeriods AS P
ON CV.BusinessClassID = P.BusinessClassID AND
dbo.forceDate(P.EffectiveDate) = dbo.forceDate(CV.EffectiveDate)
WHERE P.BusinessID = @BUS_ID
)
This is more what I had in mind:
SELECT P.Period
, IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D1.CalculationVariableID), '') as [Additional Rate]
, IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D2.CalculationVariableID), '') as [Base Rate]
, IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D3.CalculationVariableID), '') as [BID Assessed?]
, IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D4.CalculationVariableID), '') as [Original Tax Due]
, IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D5.CalculationVariableID), '') as [TaxTotal]
FROM VW_LMS_BusinessPeriods AS P
Left Join #DetailTempTable AS D1
on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d1.EffectiveDate)
and d1.BusinessClassID = P.BusinessClassID
and d1.VariableLabel = 'Additional Rate'
Left Join #DetailTempTable AS D2
on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d2.EffectiveDate)
and d2.BusinessClassID = P.BusinessClassID
and d2.VariableLabel = 'Base Rate'
Left Join #DetailTempTable AS D3
on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d3.EffectiveDate)
and d3.BusinessClassID = P.BusinessClassID
and d3.VariableLabel = 'BID Assessed?'
Left Join #DetailTempTable AS D4
on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d4.EffectiveDate)
and d4.BusinessClassID = P.BusinessClassID
and d4.VariableLabel = 'Original Tax Due'
Left Join #DetailTempTable AS D5
on dbo.forceDate(P.EffectiveDate) = dbo.forceDate(d5.EffectiveDate)
and d5.BusinessClassID = P.BusinessClassID
and d5.VariableLabel = 'TaxTotal'
WHERE P.BusinessID = @BUS_ID
Note that the above should actually be changed to not run forceDate(x) on the #DetailTempTable table since it would be more efficient to run it during the INSERT INTO step. Also note that you should be using a variable based table instead of a temporary table. temporary tables have a physical presence and are logged. Variable based tables have neither. They are also cleaned up automatically as soon as you leave scope like every other declared variable.
declare @DetailTempTable TABLE
( BusinessClassID INT
, VariableLabel Varchar(50)
, CalculationVariableID INT
, EffectiveDate DateTime )
INSERT INSERT @DetailTempTable (BusinessClassID, VariableLabel, CalculationVariableID, EffectiveDate)
....
I truely hope this helps and would be very interested if you obtain any performance comparison between this and your current approach. If you already tried a cursor or similar approach and have performance information on that it would be of interest to me as well. Our database has had a couple stored procedures that contained a cursor or even --- eck --- a nested cursor that took on the order of 3 - 5 minutes to run (even the fairly simple single cursor one). I recently recoded them to perform data gathering into a variable based table and then massage the results using up to around 8 queries against it and cut the run times down to 2-4 seconds. I'd be interested in knowing whether the trick holds up here as well.
Good luck.
-
In reviewing my original example I see that it was sorely lacking, in that I did not properly convey that the variable label MUST be dynamic, i.e. each entity type that we invoice has different business rules, which means all kinds of possible labels depending on the business and the rules applied. We might need a series of invoices with each having only 2 labels per period, or we might need a series of invoices with each having 15 labels per period. So, I need to write this series of left joins using (possibly) a table (?) with the returned labels. I'm not sure how to step through the table in this case and can't even consider the dreaded cursor.
You've been great with help, thank you -- this was presented to me as a "relatively simple task", yet I've seen the other developers over here scratching their heads and putting it off (so yes, it ended up on MY plate). Any other ideas will be appreciated as I keep slugging away at it. I suppose the concept of "simple" depends on whether you have the answer or not!
-
You should probably review and watch:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=132614
FYI, my original first post started out asking if the columns were fairly consistent or highly variable but that got dumped on me and I forgot to include it in the second version. Oh well.
As the referenced thread mentions it can be done using dynamic SQL, but I'm assuming you've already nixed that idea. At least you can always throw it into a dynamic SQL statement, call it simple, and request a hardware upgrade.
-
If you CAN tollerate dynamic SQL the following should do it. Unfortunately it uses a true temp table as well since it has to survive the scope change into the dynamic SQL.
declare @BUS_ID int
declare @cmd varchar(8000)
CREATE TABLE @DetailTempTable
( BusinessClassID INT
, VariableLabel Varchar(50)
, CalculationVariableID INT
, EffectiveDate DateTime )
declare @VariablesTempTable TABLE
( id_num smallint IDENTITY(1,1)
, VariableLabel Varchar(50) )
set @bus_id = 1019635
INSERT INTO #DetailTempTable ( BusinessClassID, VariableLabel, CalculationVariableID, EffectiveDate )
SELECT CV.BusinessClassID, CV.VariableLabel, CV.CalculationVariableID, dbo.forceDate(CV.EffectiveDate)
FROM VW_LMS_CalculationVariables AS CV
INNER JOIN VW_LMS_BusinessPeriods AS P
ON CV.BusinessClassID = P.BusinessClassID
AND dbo.forceDate(P.EffectiveDate) = dbo.forceDate(CV.EffectiveDate)
WHERE P.BusinessID = @BUS_ID
ORDER BY P.Period, CV.VariableLabel
INSERT INTO @VariablesTempTable
SELECT DISTINCT VariableLabel
FROM #DetailTempTable
set @cmd = '
SELECT P.Period'
SELECT @cmd = @cmd + '
, IsNull(dbo.GetUsedValue(P.BusinessPeriodID, D' + id_num + '.CalculationVariableID), '') as [' + VariableLabel + ']'
FROM @VariablesTempTable
ORDER BY id_num
set @cmd = @cmd + '
FROM VW_LMS_BusinessPeriods AS P'
SELECT @cmd = @cmd + '
Left Join #DetailTempTable AS D' + id_num + '
on dbo.forceDate(P.EffectiveDate) = D' + id_num + '.EffectiveDate
and D' + id_num + '.BusinessClassID = P.BusinessClassID
and D' + id_num + '.VariableLabel = ''' + VariableLabel + ''''
FROM @VariablesTempTable
ORDER BY id_num
set @cmd = @cmd + '
WHERE P.BusinessID = @BUS_ID
ORDER BY P.Period'
EXEC( @cmd )
DROP TABLE #DetailTempTable
P.S. I coded the above to ultimately look almost exactly like the previous proposal. However, it later occurred to me that depending upon the size of the table the code may perform better if you select the BusinessClassIDs and Period from VW_LMS_BusinessPeriods into yet another #Temp table and then use those results in the other two queries. Note that this is only better if the overhead of creating, populating, and dropping the #temp table is greater than the overhead in scanning the actual data table's index a second time. If the table is large enough that will be the case.
-
Sorry for the delay in response, the weekend got in the way. I was able to sit down & work on this today, and it is now functioning quite nicely, and it's fast. Not only is my task almost complete, but I learned quite a bit as well! Thank you very much for your help and the time you put into responding to my questions.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply