Greetings,
I could not figure out an easy way to Calc a group Avg in the group footer of the table.
Lets say I have UserID,GradeID,Score :
I create a group for GradeID and in the gradeid footer I use Avg(Score). Is there a way to tell RS you want the avg only for the group in scope?
I am trying to group on the number of distict field values. Basically:
=Ceiling(CountDistinct(Fields!ClientID.Value, Nothing)/10)
So for every 10 different clients, I get a group. I get an error with the above function saying aggregates are not allowed in a grouping expression. I tried creating a text box with the running value:
=RunningValue(Fields!ClientID.Value,CountDistinct,Nothing)
The value of the text box is correct, but I can't reference that text box in the grouping function. It says the textbox is undefined.
Any suggestions for a workaraound?
Thanks.
I have taken three dtsx files and re written them into one each in its own container. I use the XML Task task alot which the File connection is set by a variable and the variable value is evaluated by expression (the expression makes up the path/filename from other variable values). All the variables that make up the connection are at the container scope. The package will not run now because it is saying that the source (created by variables) for the file connection do not exist.
It seems the answer is that file connections exist at the package level therefore the variable has to be at the package level. This seems to be alot of variables i now have to move to package level to generate the XML source connection. Which in essence makes it confusing as to which variables operate in which container.
My question is can we easily move variable scope (Not ideal as we have alot of variables at package level) Or Can we do the same for connection managers as we do for variables and have them only used in a scope? (this will be ideal as some connections only need to be at a container scope)
(MSSQLS7) I have a table with fields similar to the
following:
StudentID
CourseID
CourseArea
EarnedHours
DateTaken
I need to create a report that totals all EarnedHours broken
down by category for a student for a date range. However,
courses can be taken multiple times, but should not count
more than once during the date range (the highest EarnedHours
should be used in the report preferably).
Currently, I've approached this with an SP that creates
a #temp table with CourseID, CourseArea, and MAX(EarnedHours)
GROUP BY CourseID for the date range and student specified,
then I'm selecting SUM(EarnedHours) GROUP BY CourseArea from
that.
Somehow, my current solution seems inelegant, but I haven't
been able to figure out a good way around it. It seems a real
waste to create a temp table, especially since this is a
high-use SP and the actual average subset of records involved
is really low (under 50). I'm probably missing something I
should already know... any ideas?
Hi,
I have a SQL issue which I know can be solved ( reasonably simply ) but I can't seem to figure it out ( sometimes hard to think in sets ). Basically I have a table:
Report_ID Version date_created created_by
----------- ----------- --------------------------- -----------
17 1 2001-02-21 00:00:00.000 1
17 2 2001-02-22 00:00:00.000 4
17 3 2001-02-24 00:00:00.000 7
12 1 1999-12-01 00:00:00.000 7
12 2 1999-12-03 00:00:00.000 9
what I want to do is pick out all the details of the most recent recent version of each report_ID. i.e.
Report_ID Version date_created created_by
----------- ----------- --------------------------- -----------
17 3 2001-02-24 00:00:00.000 7
12 2 1999-12-03 00:00:00.000 9
I can get a query to return distinct report_ID's with most recent versions, but not all the details.
Any help is appreciated.
Tony.
Here is the code I have:
=Sum(IIF(sum( Fields!Total_Amount.Value, "Collat_Acct_Group2") < 0, 1,0))
I am trying to do a count but only if the sum of a value is greater/less than 0.
The error I get is:
The value expression for the textbox 'textbox146' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.
Please help .
Thanks
Elias
Hello:
I've been researching a likely common problem with reporting services: the inability to get an aggregate of an aggregate. One particular discussion thread comes close to solving my specific problem:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2021871&SiteID=1
Here's my problem. I have a table that groups data per month based on Count(). I'd like to get the max(count()) -- i.e., which month has the highest count?
For example:
JAN 30
FEB 20
MAR 25
I'd like to identify the month that has the max count. In this case, I'd like to capture the aggregate value "30" as being the max value of the three months displayed.
My goal is to embed a horizontal stacked-bar chart into the table (to the left of the count() values). Various types of medical services are being counted per month: Inpatient Stay, Outpatient Service, PCP visit. The key to displaying the horizontal stacked-bar charts is to make sure the maximum value of the chart is the same for all charts -- i.e., I need to know which month has the highest count and then set that value as the max limit on the chart.
Robert Bruckner's technical article on "Get More out of SQL Server Reporting Services Charts" briefly touches on the topic of embedded charts in a table, but doesn't go into the level of detail I'm considering.
I've also come across related information from SSW Rules to Better Reporting Services. Similar to Robert's article, SSW doesn't address scaling an in-line chart based on data that is aggregated, but rather scaling the in-line chart based on the values found in a specified data field.
Ultimately, I'd like to create an in-line bar chart that appropriate shows the month of JAN as having the longest horizontal bar, and FEB/MAR having appropriately scaled smaller bars.
Thanks in advance!
--Pete
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions
The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc
Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
So I already no this can't be done... but I need a suitable alternative (if one exists) so I don't have to competely re-write this already too huge query.
Anyways, in my select I have something like this:
sum( case when code in (1,2,3,4) then 0 else 1 end ) as total
which has now increase from four static values to a more dynamic format, that being a table with 47 values which may shrink or grow over time. Attempting the following fails:
sum( case when code in (select code_id from ExcludedCodes) then 0 else 1 end ) as total
because apparently you can't use selects or aggregates within an aggregate.
So any ideas on how I can get this working... is there no Array or List type variable I could just substitute in? I've already tried using a Table Variable, but that failed as well.
Please keep in mind, this is one line of a very large select containing many other fields and aggregates (on a fair amount of joins) which is used in at least four differerent reporting queries. If there is no quick and easy replacement trick I can do just let me know so I can start re-writing all of them (which is seriously going to make my head hurt).
I have data that looks like this
create table #TestData
(clt_num int,
proc_cde varchar(10))
insert #TestData (clt_num,proc_cde)
select 1000, 'H0017' union all
select 2000, 'T1016' union all
select 2000, 'H0036' union all
select 2000, 'T0017' union all
select 3000, '99999' union all
select 3000, 'AAAAA' union all
select 3000, 'H0039'
select 4000, '99999' union all
select 5000, 'H0017' union all
select 5000, 'H0066' union all
select 100, 'H0036;TT' union all
select 200, 'T1016;XX' union all
select 200, 'H0036' union all
select 300, '99999;HH' union all
select 300, 'AAAAA' union all
select 400, '99999' union all
select 500, 'H0017;15' union all
select 500, 'H0036;XX'
I want to select records for a given clt_num based on weights (precedence) given to the different proc_cde(s). The rules for selecting the rows are:
If clt_num only has one row select that row
Select row that has the highest proc_cde by precedence for given clt_num
If clt_num has two or more rows but no proc_cde with an assigned precedence select both rows.
Note that there is a twist with the proc_cds in that they can have garbage at the end in the data records and I don't care about the garbage H0036, H1036;XX will have the same weight.
Therefore my expected results are:
clt_numproc_cde
100H0036;TT
200T1016;XX
30099999;HH
300AAAAA
40099999
500H0036;XX
1000H0017
2000T0017
300099999
3000AAAAA
400099999
5000H0039
In attempting to solve this problem I created a temp table called ProcCde_Weights:
create table #ProcCde_Weights
(proc_cde varchar(10),
weight int)
insert #ProcCde_Weights
(proc_cde,weight)
select 'H0039', 10 union all
select 'T1017', 20 union all
select 'T1016', 30 union all
select 'H0036', 40 union all
select 'T2011', 50 union all
select '90806', 60 union all
select '90862', 70 union all
select 'T1002', 80 union all
select 'H2031', 90 union all
select 'H2023', 100
And wrote this query:
select t.clt_num,
t.proc_cde,
case when p.weight is null then 1000
else p.weight
end as weight
from #TestData t left join #ProcCde_Weights p on t.clt_num = t.clt_num
and p.proc_cde = left(t.proc_cde,5)
which gives me this:
clt_numproc_cdeweight
1000H00171000
2000T101630
2000H003640
2000T00171000
3000999991000
3000AAAAA1000
4000999991000
5000H00171000
5000H00661000
100H0017;TT1000
200T101630
200H003640
30099999;HH1000
300AAAAA1000
400999991000
500H0017;151000
500H0036;XX40
… but now I need to modify the query with using the min function or max function (I think) to only select rows according to the rules I gave.
… and once again I seem to have a mental block when it comes to composing queries with aggregate functions.
Thanks in advance for any help.
Laurie
p.s. can anybody point me to any books or tutorials that can help me wrap my mind around aggregate functions.
Hi, I have the following calculation in Textbox6
=round(sum((Fields!bill_hrs.Value)+(Fields!sales_hrs.Value)+(Fields!proj_hrs.Value))/24,0)
I need to avg the groups in the footer, but of course I cannot nest aggregates, so I tried this, but it doesn't work either. Any suggestions???
=Avg(ReportItems("Textbox6").Value)
=(Avg(ReportItems!Textbox6.Value))
Hi
I have similar problem and i explained in detail here http://www.balainfo.com/forums/viewtopic.php?t=39
Pls help me to fix this sql
Regds
Bala
I want to create a query to summarize sales for each of the last five weeks and group by product type ie.
select product, sum(amount this week), sum of (amount last week) ... group by product
my sales table looks like this
product date amount
x d1 xx
x d2 xx
y d3 xx
y d4 xx
x d5 xx
How can this be done?
thanks
Hi,
I have the following test data:
create table #TestData
(clt_num int,
ins_num int)
Insert into #TestData
(clt_num, ins_num)
Select 16, 1 union all
Select 16, 90 union all
Select 16, 999999 union all
Select 16, 389 union all
Select 18, 1 union all
Select 18, 90 union all
Select 18, 999999 union all
Select 24, 999999 union all
Select 24, 1 union all
Select 31, 1 union all
Select 31, 999999 union all
Select 31, 90 union all
Select 31, 389 union all
Select 35, 999999 union all
Select 35, 389 union all
Select 283, 1 union all
Select 283, 90 union all
Select 283, 999999 union all
Select 283, 310 union all
Select 500, 1 union all
Select 100, 90
… which I then combine the ins_num into insurance types:
select clt_num,
case
when ins_num = 1 then 'Caid'
when ins_num = 90 then 'Care'
when ins_num in (189,195,310) then 'HMO'
when ins_num between 381 and 389 then 'TPO'
when ins_num = 999999 then 'TPO'
end as InsuranceType
from #TestData
order by clt_num
… and get the following results:
clt_numInsuranceType
16Caid
16Care
16TPO
16TPO
18Caid
18Care
18TPO
24TPO
24Caid
31Caid
31TPO
31Care
31TPO
35TPO
35TPO
100Care
283Caid
283Care
283TPO
283HMO
500Caid
But what I really want is only one line per clt_num and where if clients have more than 1 insurance type the insurance type should be replaced with dual:
clt_numInsuranceType
16Dual
18Dual
24Dual
31Dual
35TPO or Dual (?*)
100Care
283Dual
500Caid
?* This is a special case and I do not know if the results should say Dual or not. I think TPO stands for Third Party Only (in which case they wouldn't care how many third parties the client has and the results should be TPO) … but if I am wrong then this should be Dual. I will ask my boss tomorrow to clarify, but it would be nice to have a solution for both ways.
I know I need to do something with count and stuff, but for some reason I have a mental block when it comes to agregate functions.
Thanks in advance for any help finishing up the query.
Laurie
I am having a similar issue in a Matrix report. The following is a message from someone else having the same problem.
View 8 Replies View RelatedI'd like to merge the 2 statements shown below into one. I'm wondering if there is a method of using PIVOT to get the SUM and COUNT aggregates in one statement. The only option I can get working is to use these as sub-queries but I'm hoping there is a better approach.
An answer would be great as would a better on-line resource than the BOL "Using PIVOT and UNPIVOT" topic.
Any guidance much appreciated.
/********
Get account type totals
********/
SELECT PVT.ACCOUNT_MANAGER_OID,
ISNULL(PVT.[CUSTOMER], 0) AS 'CUSTOMERS',
ISNULL(PVT.[OTHER], 0) AS 'OTHERS'
FROM ( SELECT A.OID, A.ACCOUNT_MANAGER_OID,
1 AS 'REVIEW_IND',
CASE WHEN A.TYPE = ( 'Customer' )
THEN TYPE
ELSE 'OTHER'
END AS TYPE
FROM ACCOUNTS A LEFT OUTER JOIN
( SELECT ACCOUNT_OID,
1 AS [REVIEW_IND]
FROM dbo.ACCOUNT_HISTORY
WHERE TABLE_NAME = 'ACCOUNTS' AND
FIELD_NAME = 'REVIEW DATE'
) AS DRV_R ON DRV_R.ACCOUNT_OID = A.OID
WHERE A.ACCOUNT_MANAGER_OID IS NOT NULL
) A PIVOT ( COUNT(A.OID) FOR TYPE IN ( [CUSTOMER], [OTHER] ) ) AS PVT
ORDER BY PVT.ACCOUNT_MANAGER_OID
/**********
Get accounts review totals
***********/
SELECT PVT.ACCOUNT_MANAGER_OID,
ISNULL(PVT.[CUSTOMER], 0) AS 'CUSTOMERS_REVIEWED',
ISNULL(PVT.[OTHER], 0) AS 'OTHERS_REVIEWED'
FROM ( SELECT A.OID, A.ACCOUNT_MANAGER_OID,
1 AS 'REVIEW_IND',
CASE WHEN A.TYPE = 'Customer' THEN TYPE
ELSE 'OTHER'
END AS TYPE
FROM ACCOUNTS A LEFT OUTER JOIN
( SELECT ACCOUNT_OID,
1 AS [REVIEW_IND]
FROM dbo.ACCOUNT_HISTORY
WHERE TABLE_NAME = 'ACCOUNTS' AND
FIELD_NAME = 'REVIEW DATE'
) AS DRV_R ON DRV_R.ACCOUNT_OID = A.OID
WHERE A.ACCOUNT_MANAGER_OID IS NOT NULL
) A PIVOT ( COUNT(A.OID) FOR TYPE IN ( [CUSTOMER], [OTHER] ) ) AS PVT
ORDER BY PVT.ACCOUNT_MANAGER_OID
I have a report that consists of three nested group levels. Each level can be expanded/collapsed. I wanted to have at each level the summed values:
e.g.
+ Group Header 1 Sum1 Sum2 etc..
+ Group Header 2 Sum1 Sum2 etc..
+ Group Header 3 Sum1 Sum2 etc..
Rather I have had to output the aggregated values like so ..
+ Group Header 1
+ Group Header 2
+ Group Header 3
Total Group Footer Sum1 Sum2 etc..
Total Group Footer Sum1 Sum2 etc..
Total Group Footer Sum1 Sum2 etc..
Is there a way to display the aggregated values at the header level of the grouping. I thought this could be possible if I could hide the Group Footer and directly reference the footer sum total values in the header. Any help would be greatly appreciated.
Theo
I have to write an aggregate which accumulates values in a relation to a parameter. Therefore I tried to create an aggregate with an additional input parameter.
While creating the aggregate, I get the following error message:
CREATE AGGREGATE failed because type 'MKT' does not conform to UDAGG specification due to method 'Accumulate'.
Does anybody know, how I could solve this?
Public Sub Accumulate(ByVal value As SQLDouble, ByVal param1 as SQLDouble)
Const uGK as double = 0.008
result = result + Math.Exp(-param1/(uGK *value) )
End Sub
Hi,
I would like to change the aggregate "sum" for "avg" for some of the measures of my cube.
I know it's possible with calculated member (i have already done that) but i would like a more efficient method, in fact i would like the results to be stored in my cube... is that possible ?
Thanks for your help
Regars
Francois
How would you get all those aggregates from the second SELECT statement worked into the first SELECT statement? Can this stuff even all be put into one statement?
What I'm trying to end up with is a table listing the Professor, Course, Year, Registration, and then the amount of A's, B's, etc.
What I have is a table listing the Professor, Course, Year, and Registration. I can also get a list with the counts for each grade. But I need to get the two together somehow. Any thoughts?
SELECT (gp.last_name + ', ' + gp.first_name) AS 'Prof',
gs.course,
LEFT(gs.quarter_year,4) AS 'Year',
COUNT(gs.enroll_id) AS 'Reg'
FROM section s INNER JOIN person p
ON p.person_id = s.person_id
INNER JOIN grade_sheets gs ON gs.section_id = s.section_id
WHERE s.quarter_year = 20073
GROUP BY p.last_name, p.first_name, s.course, s.quarter_year
ORDER BY 'Prof'
SELECT
Count_A =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'A' AND gs.section_id = @sectionID),
Count_B =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'B' AND gs.section_id = @sectionID),
Count_C =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'C' AND gs.section_id = @sectionID),
Count_D =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'D' AND gs.section_id = @sectionID),
Count_F =(SELECT COUNT(*) FROM grade_sheets gs WHERE gs.final_grade = 'F' AND gs.section_id = @sectionID)
Are there any conditional aggregate functions, such as SUM()?An example would probably be the best way to describe what I'mtrying to do...I have the following table, named Orders, with the following records:ItemNo qty_ord paid------ ----------- ------T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 0.00T101B 1 0.00T101B 1 0.00T101B 1 0.00Z200L 1 50.00Z200L 2 100.00I want to produce the following result set:ItemNo qty_gross qty_net------ ---------- -------T101B 11 7Z200L 3 3The "qty_gross" column in the result set is the sum oftotal items ordered within the ItemNo grouping.Easy enough. However, I also want a column "qty_net" thatis the sum of qty_ord but ONLY IF the amount in the"paid" column is > 0.I tried using the HAVING clause, but that produces acatch 22 situation. If I say "HAVING paid > 0" thenthe qty_gross column is wrong because it leaves out rowsthat contain records with paid = 0 values. If I leaveout the HAVING clause, then the "qty_net" is wrong.Any ideas?select ItemNo, Sum(qty_ord) as qty_gross, Sum(qty_ord) as qty_netfrom Ordersgroup by qty_ord, paid, ItemNohaving paid > 0 ?????Thanks,Robbie
View 1 Replies View RelatedHi all!
I have created (in CLR) a user defined aggregate. The scan order of this aggregate is important, because it return different results for different orders.
When i use it with a single group (using order by and where) is working fine.
For example
select id, dbo.cmp(myclolumn) as myres from (select top 100 percent * from mytable order by id,clmdate) where id=10 group by id
This works correctly. Now lets expand it by removing where id=10 clause
select id, dbo.cmp(myclolumn) as myres from (select top 100 percent * from mytable order by id,clmdate) group by id
I get slightly different results from what the right result must be.
Any idea?
Hi, all experts here,
Thank you very much for your kind attention.
I am having some questions on indexed views and aggregate tables.
My question is: To improve the performance of the queries, is it better to use indexted views or aggregates tables for those aggregates which are often queried?
I am looking forward to hearing from you.
Thank you very much in advance for your help.
With best regards,
Yours sincerely,
I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID
View 9 Replies View RelatedI have been using tis page as a reference http://forums.asp.net/thread/1511323.aspxbut i cant seem to get this to work. The above page suggests using Dim newId As Object = e.Command.Parameters("@RETURN_VALUE").Value to get the value but when i do that i get an error that Command is not a member of system.web.ui.webcontrols.formViewInsertedEventArgs Can anyone help?ThanksMatt
View 1 Replies View RelatedHelloI am trying to run a program to check for transaction scopeI have written the following code.But it seems I need to add a namespace or referenceWhat namespace am I supposed to addthanksusing (TransactionScope scope = new TransactionScope(TransactionScope.Required, options)){SqlConnection MyCon = new SqlConnection("server=hemalatha\sqlexpress;integrated security=sspi;database=demo");MyCon.Open();SqlCommand Mycmd=new SqlCommand("insert into t1 values 574,'scope','10/10/2007',3,3,3");Mycmd.ExecuteNonQuery();SqlConnection MyCon1 = new SqlConnection("server=hemalatha\sqlexpress;integrated security=sspi;database=persons");MyCon1.Open();SqlCommand Mycmd1 = new SqlCommand("insert into persons values 'scope',123,123,12,'scope'");Mycmd1.ExecuteNonQuery();}
View 2 Replies View RelatedHi folks
I'm using a function to create a record on a database, and then I want to return the ID of that record to passinto another function. I believe its scope identity that does this, but I'm not sure how to do it. public static void putrecordin(string record) { SqlCommand cmd = new SqlCommand("insert Table (record) values (@record; scope identity)" conn.Open(); cmd.Parameters.Add(new SqlParameter("@record", record)); cmd.ExecuteNonQuery(); conn.Close(); another(new-record-ID, anothervalue); } public static void another(string new-record-ID) {do stuff
}
so you'll see I have a function called putrecordin, and at the end of the sql statment I want to return the id of the new record and pass it into another function called another.
Anyone know how to do this?
Thanks!
I have created a proc that uses @@datefirst to have the weekending on Friday and starting on Saturday.
If I execute the proc from w/in a cursor, a nested cursor, will I have to constantly check and confirm the @@datefirst value?
TIA
JeffP....
In the following Query, is Table1.Column3 updated in the subquery for each row in Table1, or does it take the first value, or is it NULL, or is it ____?
Select Column1, Column2 from Table1 where Column1 IN
(Select Col1 from Table2 where Col2 > Table1.Column3
Group by Col1 Having count(Col1) > 1) and Table1.Column3 < Column4
OK, So I'm Getting some XML Like this
<PifToMepData Mode="3">
<MEP MEPName="Test Combining PIFs" MEPType="Close" PIFRecId="12" IsPrimaryPIF="1"><AssignedTo>X000525</AssignedTo></MEP>
<MEP MEPName="Test Combining PIFs" MEPType="Close" PIFRecId="13" IsPrimaryPIF="0"><AssignedTo>X000525</AssignedTo></MEP>
</PifToMepData>
I then use
INSERT INTO #myTemp99 (
Mode
, MEPName
, MEPType
, PIFRecId
, IsPrimaryPIF
, AssignedTo)
SELECT *
FROM OPENXML (@idoc, './/AssignedTo',3)
WITH(
Mode varchar(20) '../../@Mode'
,MEPName varchar(2000) '../@MEPName'
,MEPType varchar(500) '../@MEPType'
,PIFRecId int '../@PIFRecId'
,IsPrimaryPIF varchar(20) '../@IsPrimaryPIF'
,AssignedTo varchar(20) '.'
)
To Parse it out. This then has to be inserted into another table with an identity column (damn Identity column), and I need to grab the generated id for each row, because then there are other children tables that need to be populated.
Question: Is there any set way to grab multiple generated id's?
Or do I need to loop or use a cursor?
Is the 1% of the time that they are needed?
Any ideas?
Here's the temp table DDL
CREATE TABLE #myTemp99 (
Mode varchar(20)
, MEPName varchar(2000)
, MEPType varchar(500)
, PIFRecId int
, IsPrimaryPIF varchar(20)
, AssignedTo varchar(20))
And the document prep
DECALRE @idoc varchar(8000)
--Just assign the sampel data
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
EXEC sp_xml_removedocument @idoc
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
Hi,
Is there any way to change the scope of a user defined variable?
Hi,
how to change the a scope of a user defined variable?
Is an index in a database the equivalent for a <TH scope="col"> in a columnof atable in the html code?--Luigi ( un italiano che vive in Svezia)https://www.scaiecat-spa-gigi.com/s...kor-italien.php
View 3 Replies View Related