March 22, 2012 at 9:19 am
Hello all,
One thing before my story. I am not looking for a complete solution but I am looking for an opening to sort out myself!
I am curious if there is a way to calculate a percentage-total in a field that is in a record with fields used to show progress milestones.
Imagine a 10 field record with an identifier, 8 progress fields that will be filled with an 'x' and a field showing the percentage of progress.
In a seperate table I have 2 fields, fieldname of field in first table and a certain value. If a field has an 'x' the valaue will be copied from table2. If more fields are filled then a Sum of all values must show in the special field.
A user will keep hold of the changes in a nice form and the calculation will take place while he/she is putting in the milestone 'x'. In Access I can use a vba but in SQL Server I can't. I am thus looking for the SQL way to a solution.
Can somebody lead the way to a solution? At this moment I am working in MS Access but I have to port my tables to SQL Server later.
Thx in advance, Rene
March 22, 2012 at 10:20 am
It is doable... but we need some input from you before we start on leading you. Please provide us readily consumable data so that we can get started, as mentioned in this article : http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2012 at 1:09 pm
I hope the next code is allright but take care, the puzzel is the part I like most. I am not here to get a total solution. I will be very happy with a direction.
CREATE TABLE #ProgressList
(
LineNumbervarchar(15),
TotalProgressint,
_3Dstartedvarchar(1),
_3Droutingvarchar(1),
_3Ddetailsvarchar(1),
_3Dsupportsvarchar(1)
)
INSERT INTO #ProgressList
(LineNumber, TotalProgress, _3Dstarted, _3Drouting, _3Ddetails, _3Dsupports)
SELECT 'Line 100', '', 'x', 'x', 'x', '' UNION ALL
SELECT 'Line 101', '', 'x', 'x', '', ''
CREATE TABLE #Valuelist
(
FVfieldnamevarchar(15),
FVvalueint
)
INSERT INTO #Valuelist
(FVfieldname, FVvalue)
SELECT '_3Dstarted', 10 UNION ALL
SELECT '_3Drouting', 25 UNION ALL
SELECT '_3Ddetails', 5 UNION ALL
SELECT '_3Dsupports', 10
March 22, 2012 at 1:42 pm
Here is the algorithm (as you said you needed only directions)
1. PIVOT the #ValueList table to contain 4 columns
2. Use CROSS APPLY/OUTER APPLY to apply the pivoted table to the #ProgressList table
- this will produce 4 new columns with the values
3. Use CASE statement agianst each the _3D columns in ProgressList and determine the corresponding value column from the pivoted and applied output
4. Add the values of each CASE and update the TotalProgess
Now i have coded also according to the algorithm and here it is
SELECT P.LineNumber
,TotalProgress =
CASE WHEN P._3Dstarted = 'X' THEN OtrApp._3Dstarted ELSE 0 END
+ CASE WHEN P._3Drouting = 'X' THEN OtrApp._3Drouting ELSE 0 END
+ CASE WHEN P._3Ddetails = 'X' THEN OtrApp._3Ddetails ELSE 0 END
+ CASE WHEN P._3Dsupports = 'X' THEN OtrApp._3Dsupports ELSE 0 END
,P._3Dstarted
,P._3Drouting
,P._3Ddetails
,P._3Dsupports
FROM #ProgressList P
OUTER APPLY
(
SELECT MAX( CASE WHEN FVfieldname = '_3Dstarted' THEN FVvalue ELSE NULL END) [_3Dstarted]
,MAX( CASE WHEN FVfieldname = '_3Drouting' THEN FVvalue ELSE NULL END) [_3Drouting]
,MAX( CASE WHEN FVfieldname = '_3Ddetails' THEN FVvalue ELSE NULL END) [_3Ddetails]
,MAX( CASE WHEN FVfieldname = '_3Dsupports' THEN FVvalue ELSE NULL END) [_3Dsupports]
FROM #Valuelist
) OtrApp
When u feel tha the algorithm is overwhelming, please use this code as a start point.
March 22, 2012 at 2:24 pm
Thank you, I will certainly start from here. As said, I am here for a direction. As a medior programmer it is the biggest fun to sort out a problem like this. This problem/idea is to get ridd of the amount of Excelsheets and create a centralized solution.
again thx. If I get stuck I will post a new question.
Rene
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply