Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
timark
Starting Member
11 Posts |
 Posted - 2007-12-19 : 13:52:45
|
| I'm calculating a series of values that are deposited into a series of columns in a table. I want to evaluate this series of column values using the aggregate functions avg, min and max. I've already discovered that you can't do that, so I set about developing a table variable that I populate for each iteration in an UPDATE statement. Following is the syntax I developed that doesn't work. My next thought was to use a select insert for each row in @SEACtemp, but it would require a very complex WHERE statement that I just plain don't want to sit and do. It seems the following, simpler logic should work. Can anybody help me make this work???Update EVdata.dbo.CADatadeclare @SEACtemp TABLE ( SEACval numeric(18,0))INSERT into @SEACtemp VALUES ([CAData.SEAC3av])INSERT into @SEACtemp VALUES ([SEACcrCPI])INSERT into @SEACtemp VALUES ([SEACcmCPI])INSERT into @SEACtemp VALUES ([SEACcs])INSERT into @SEACtemp VALUES ([SEAClr])INSERT into @SEACtemp VALUES ([SEACcpispi])INSERT into @SEACtemp VALUES ([SEACmicom])set SEACavg = avg ( SEACtemp.SEACval )set SEACmin = min ( SEACtemp.SEACval )set SEACmax = max ( SEACtemp.SEACval )Even better, is there some form of syntax for the min, max and avg functions that will allow me to specify the discrete column values as a vertical array???I only do databases as required to fulfill my regular duties in cost/schedul/ev analysis, so I'm just not familiar enough with database dev principles. This is my first foray into SQL Server (outgrew Access by several orders of magnitude for this task), so please be kind.Thanks! -tim |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-12-19 : 14:02:04
|
| I have no idea what you are trying to do ... can you provide a specific example, with some simple sample data and an explanation of exactly what you need to calculate and what the results (based on that sample data) should be?- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
timark
Starting Member
11 Posts |
Posted - 2007-12-19 : 15:00:13
|
| I have accumulated base data in the following columnar format (using the 'Script To Select' tool from the actual database).SELECT [CAID] ,[ValueType] -- dollars, hours or staffing ,[SPAE] -- ACWP, BCWP, BCWS or ECWS ,[Period] -- value ranging from 10/2003 to 9/2015 ,[PlusX] -- discrete value for the period ,[CumPlusX] -- cumulative value thru the period ,[AsOfDate] -- cutoff date for data, as text. ,[ReportDate] -- cutoff date for data, as smalldatetime. ,[UID] -- unique id for rowset FROM [EVdata].[dbo].[BaseData]All data from the above table is submitted to me on a monthly basis. This table relates to a table that provides descriptive details for each control account, as follows.SELECT [ProgramID] -- submitted ,[ContractID] -- submitted ,[CAID] -- submitted, joins BaseData.CAID ,[Calendar] -- submitted ,[CATitle] -- submitted ,[CAM] -- submitted ,[TeamCode] -- submitted ,[WBSCode] -- submitted ,[WBSParent] -- submitted ,[AsOfDate] -- submitted, joins BaseData.ReportDate ,[UID] -- unique id for rowset ,[Scr] -- drawn from BaseData ,[Scm] -- drawn from BaseData ,[Pcr] -- drawn from BaseData ,[Pcm] -- drawn from BaseData ,[Acr] -- drawn from BaseData ,[Acm] -- drawn from BaseData ,[Ecr] -- drawn from BaseData ,[Ecm] -- drawn from BaseData ,[BAC] -- calc'd ,[LRE] -- calc'd ,[ETC] -- calc'd ,[ptACWPexceedsBAC] -- calc'd ,[ctChargeToComplete] -- calc'd ,[ctChargeToUnopen] -- calc'd ,[ctBCWPplusnoACWP] -- calc'd ,[ctACWPplusnoBCWP] -- calc'd ,[ctNoBAC] -- calc'd ,[ctNoLRE] -- calc'd ,[ctComplETCzero] -- calc'd ,[ctBCWSoverBAC] -- calc'd ,[ctBCWPoverBAC] -- calc'd ,[ctACWPoverLRE] -- calc'd ,[ctChangeBAC] -- calc'd mult submittals ,[ctChangeBACnoChangeLRE] -- calc'd mult submittals ,[ctChangeLRE] -- calc'd mult submittals ,[ctNegCurA] -- calc'd ,[ctNegCurS] -- calc'd ,[ctNegCurP] -- calc'd ,[ctNegCumS] -- calc'd ,[ctNegCumP] -- calc'd ,[ctNegCumA] -- calc'd ,[ctBCWPnoACWP] -- calc'd ,[ctLREout] -- calc'd ,[ptComplete] -- calc'd ,[ptACWPequalBAC] -- calc'd ,[ptExcessSlip] -- calc'd ,[ptTCPILREexceedCPI05] -- calc'd ,[ptTCPILREexceedCPI10] -- calc'd ,[ptTCPILREexceedCPI25] -- calc'd ,[ptSPI80] -- calc'd ,[ptCPI80] -- calc'd ,[CVcr] -- calc'd ,[CVcrChange] -- calc'd mult submittals ,[CVcm] -- calc'd ,[CVcmChange] -- calc'd mult submittals ,[SVcr] -- calc'd ,[SVcrChange] -- calc'd mult submittals ,[SVcm] -- calc'd ,[SVcmChange] -- calc'd mult submittals ,[CPIcr] -- calc'd ,[CPIcrChange] -- calc'd mult submittals ,[CPIcm] -- calc'd ,[CPIcmChange] -- calc'd mult submittals ,[CPI6] -- calc'd mult submittals ,[CPI3] -- calc'd mult submittals ,[SPIcr] -- calc'd ,[SPIcrChange] -- calc'd mult submittals ,[SPIcm] -- calc'd ,[SPIcmChange] -- calc'd mult submittals ,[TCPILRE] -- calc'd ,[TCPILREchange] -- calc'd mult submittals ,[TCPIBAC] -- calc'd ,[TCPIBACchange] -- calc'd mult submittals ,[SEAC3av] -- calc'd mult submittals ,[SEAC6av] -- calc'd mult submittals ,[SEACcrCPI] -- calc'd mult submittals ,[SEACcmCPI] -- calc'd mult submittals ,[SEACcs] -- calc'd mult submittals ,[SEAClr] -- calc'd mult submittals ,[SEACcpispi] -- calc'd mult submittals ,[SEACmicom] -- calc'd mult submittals ,[SEACavg] -- calc'd mult columns ,[SEACmin] -- calc'd mult columns ,[SEACmax] -- calc'd mult columns ,[S3cm] -- Drawn from BaseData 3rd prior submittal ,[P3cm] -- Drawn from BaseData 3rd prior submittal ,[A3cm] -- Drawn from BaseData 3rd prior submittal ,[E3cm] -- Drawn from BaseData 3rd prior submittal ,[S6cm] -- Drawn from BaseData 6th prior submittal ,[P6cm] -- Drawn from BaseData 6th prior submittal ,[A6cm] -- Drawn from BaseData 6th prior submittal ,[E6cm] -- Drawn from BaseData 6th prior submittal ,[S1cr] -- Drawn from BaseData 1st prior submittal ,[S1cm] -- Drawn from BaseData 1st prior submittal ,[P1cr] -- Drawn from BaseData 1st prior submittal ,[P1cm] -- Drawn from BaseData 1st prior submittal ,[A1cr] -- Drawn from BaseData 1st prior submittal ,[A1cm] -- Drawn from BaseData 1st prior submittal ,[E1cr] -- Drawn from BaseData 1st prior submittal ,[E1cm] -- Drawn from BaseData 1st prior submittal ,[BAC1] -- Drawn from BaseData 1st prior submittal ,[LRE1] -- Drawn from BaseData 1st prior submittal ,[ptTrigs] -- Calc'd, number of pt* triggers per row ,[ctTrigs] -- Calc'd, number of ct* triggers per row ,[infMoSchange1] -- count period value changes since prior month ,[infMoPchange1] -- count period value changes since prior month ,[infMoAchange1] -- count period value changes since prior month ,[infMoEchange1] -- count period value changes since prior month ,[infMoSchange6] -- count period value changes over six months ,[infMoPchange6]-- count period value changes over six months ,[infMoAchange6]-- count period value changes over six months ,[infMoEchange6]-- count period value changes over six months ,[ctMoSchange1] -- flag monthly value changes ,[ctMoPchange1] -- flag monthly value changes ,[ctMoAchange1] -- flag monthly value changes ,[ctMoEchange1] -- flag monthly value changes ,[ctMoSchange6] -- flag monthly value changes ,[ctMoPchange6] -- flag monthly value changes ,[ctMoAchange6] -- flag monthly value changes ,[ctMoEchange6] -- flag monthly value changes ,[BCWR] -- calc'd ,[GEAC] -- calc'd if value not available in GEAC table. FROM [EVdata].[dbo].[CAData]ct* and pt* columns are all bit datatypes, used to flag exception triggers. I have everything worked out except the select statements to fill the prior BAC/LRE values, and the subject min/max/avg vals.Realizing that I could have actually developed the necessary select statements to make the logic work, I feel that there is a simpler solution, and I want to know what it is!!Thanks for your interest and assistance!-tim |
|
|
|
timark
Starting Member
11 Posts |
Posted - 2007-12-19 : 15:12:58
|
| Sorry. I did not answer your question. I want to update each rowset for min/max/avg.SEAC3av|SEAC6av|SEACcrCPI|SEACcmCPI|SEACcs|SEAClr|SEACcpispi|SEACmicom 500 510 520 550 495 480 620 400 SEACavg|SEACmin|SEACmax509.375 400 620Average is no prob, as the number of discrete values are known, but the min/max is problematic. |
|
|
|
Will H
Yak Posting Veteran
56 Posts |
Posted - 2007-12-19 : 15:15:51
|
| I'm also not sure what you are doing, but for cross column aggregates I'd suggest a user defined function or CASE statements. UDF's are pretty much the swiss army knife, but are prone to slowness.If you could explain your technical problems without any business logic, you'd probably get far more help. Invent a table with around 5 columns and 3-4 sample rows, and then try to explain what you want out of it.---------------------------------------------------------SSRS Kills Kittens. |
|
|
|
Will H
Yak Posting Veteran
56 Posts |
Posted - 2007-12-19 : 15:33:57
|
quote:
Originally posted by timark
Sorry. I did not answer your question. I want to update each rowset for min/max/avg.SEAC3av|SEAC6av|SEACcrCPI|SEACcmCPI|SEACcs|SEAClr|SEACcpispi|SEACmicom 500 510 520 550 495 480 620 400 SEACavg|SEACmin|SEACmax509.375 400 620Average is no prob, as the number of discrete values are known, but the min/max is problematic.
Ok, that makes more sense. I would suggest making a user defined function that takes as input all the fields you are trying to scan, and returns the max of those, something like:CREATE FUNCTION Maximum( @Num1 int, @Num2 int, @Num3 int)RETURNS INT AS BEGIN DECLARE @Ret int SET @Ret = ( SELECT MAX( ID ) FROM ( SELECT @Num1 ID UNION ALL SELECT @Num2 ID UNION ALL SELECT @Num3 ID ) f ) RETURN @retENDyou can call with:SELECT dbo.Maximum( 2, 4, 3)Generally, SQL handles Cross column values very poorly because a well designed normalized db won't be set up that way. Let this be your slap on the wrist. I'm not going to make any boasts about the speed of the above, but it should do the trick. If anyone manages to find a solution with a class SQL query, it would probably be faster. I'm just not sure such a solution exists. ---------------------------------------------------------SSRS Kills Kittens. |
|
|
|
timark
Starting Member
11 Posts |
Posted - 2007-12-19 : 15:46:08
|
| OK - I figured I'd get that slap on the wrist. I switched over to this practice from utilizing views because of performance issues... with only 6 months of data, BaseData has over 3.5 million rows, which have to be joined on two fields to CAData. Even indexed, takes forever for the results to build. Also, I want to distribute the result set (offsite connection to db ist verboten) and there is no need to distribute the base data - I'll just build the CADdata table and distribute that, with reports generated from it.I suppose I could just populate CAData with the data drawn from BaseData only, and develop a query that performs the calcs drawing from CAData. Only 320 rowsets per month.... would be much faster.Anyway - I know better, I just didn't know how to DO it and maintain quick response generating reports. |
|
|
|
|
|
|
|
|
Using aggregate functions across row columns