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
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 RelatedI am trying to write query that will select calls that only show up when the resolution returned from the case in the sub query is 'y'. When I do the group by some calls will show up twice because of a null and a 'y' resolution return. I want to only select a call if there is a 'y' that shows for all resolutions associated with that call. If there are any null resolutions for the call I don't want it to show up in my returned values. This is what I have so far, but I can't figure out how to make it drop both instances of the callID if one instance is null.
Code:
SELECT C.CallID, P.PrimarySupportGroupID, P.PrimaryTeamName, T1.Resolution, CASE C.CallID WHEN T1.Resolution IS NOT NULL THEN 'Y' ELSE 'N' END
FROM HEAT.dbo.CallLog C,
(SELECT CallID, CASE WHEN A1.Resolution = '' THEN 'Y' ELSE NULL END AS Resolution
FROM HEAT.dbo.Asgnmnt A1) T1, HEAT.dbo.Profile P
WHERE C.CallID = T1.CallID AND C.CustID = P.CustID AND (P.PrimarySupportGroupID = 'ATS') AND (P.PrimaryTeamName = 'Beta')
GROUP BY C.CallID, P.PrimarySupportGroupID, P.PrimaryTeamName, T1.Resolution
If anyone could help me out with this or at least give me a little information and point me in the right direction I would really appreciate it.
Thank you
Court
Dear GroupI'm having trouble with the statement below. I tried CASE and IFwithout success. What I'm trying to do:There is a field in the database called Business_TelNo. If the fieldhas some value, I would like to return a generated field(LaBusinessTelNo), which is the label of Busines_TelNo, reading'Phone:'If Business_TelNo has no value, the label should be set to ''.Something like this:SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,IF (LEN(Business_TelNo) > 0) BEGIN SELECT 'Phone: ' AS LaBusinessTelNoEND ELSE BEGIN SELECT '' AS LaBusinessTelNo ENDFROM i2b_vw_contactThis is working:SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,'Phone: ' AS LaBusinessTelNoFROM i2b_vw_contactPS: I know it would be much easier to add some logic in theapplication but need to do this in SQL.Thanks very much for your time and efforts!Martin
View 2 Replies View RelatedHello dbForumers,
Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ?
Thank You!
Table with water consumption per month and customer.
I want to sum up total consumption per customer with a select statement
Is there a statement to change the column in a select clause?
For example:
select Groups, sum ((if group like '%total%' then select TotalHours else if group like '%Direct%' then select DirectHours endif endif)) as Hours, count(*) from tblGroups. group by Groups.
TSQL statement, it should select the first date of the month at 11:59 PMÂ as Firstdate and Closedate as 10 days later at 11:59 PM.
View 14 Replies View RelatedI have several places where I need to get the id (primary key) of a resource, inserting a row if the resource does not exist (i.e. an artificial key to be used as an FK for another table). I should probably change this varchar key lookup to use a hash index, but that is beside the point.
So the table is essentially like:
CREATE TABLE MyLookup(id int identity primary key nonclustered, mykey varchar(256));
CREATE CLUSTERED INDEX mylookup_cidx_mykey ON MyLookup(mykey);
I see two main approaches for how I can do my get-id-with-insert-if-needed.
(Approach 1)
DECLARE @id INT;
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value';
IF (@id is null)
BEGIN
INSERT MyLookup ('some key value');
SET @id = SCOPE_IDENTITY();
END
(Approach 2)
DECLARE @id INT;
INSERT MyLookup SELECT 'some key value' WHERE NOT EXISTS (SELECT id FROM MyLookup WHERE mykey = 'some key value');
IF (@@ROWCOUNT = 0)
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value';
ELSE
SET @id = SCOPE_IDENTITY();
From some quick tests in profiler, approach 2 seems to be a bit faster and have lower resource utilization. But I'm not sure if it maybe takes some more aggressive locks even in the unnecessary case where the mykey row value of 'some key value' already exists. Approach 2 also looks cleaner to me, but I don't mind a bit of extra code if it gives me better scalability through less lock contention.
Any tip on what is considered the best practice for a conditional insert like this, or a tip on how to get detailed lock info for a query? The lock info for profiler was all greek to me, it just had a hex value with each lock acquired/released, so I have no idea what it was telling me. Is my only solution to just run exhaustive tests and look at the perf numbers from the black box?
Hi all,
I created a stored procedure which perfoms a simple select like this:
CREATE PROCEDURE Reports_Category
( @loc varchar(255), @pnum varchar(255) )
AS declare @vSQL nvarchar(1000) set @vSQL = 'SELECT ' + @loc + ', ' + @pnum + ' FROM Category ' exec sp_executesql @vSQL
RETURNGO
It takes field names as parameters. What happens is that when I supply value to only one parameter, the procedure gives error as it is expecting both values. Is there a way to make these parameters work like an 'OR' so that the procedure returns a dataset even if there is only one value supllied.
Please help,
Thanks,
bullpit
Hi All,
I am trying to write a Transact-SQL statement and am having no success. I have a customers table that has some duplicate Customer Numbers in it, however the records do have a unique Reference Number. I am trying select all records that match a list of Customer Numbers but if there are more than 1 matching Customer Number I only want the one with the largest Reference Number.
My BASIC Select Statement is:
SELECT Cust_Ref_No, Customer_No, Customer_Name, Address1,
Address2, Suburb, State, Postcode, Phone
FROM Customers
WHERE Customer_No IN
(SELECT Customer_No FROM temp_Customers)
Much to my HORROR I found that what I am trying to do is simple in MS Access using the “First” Function – Such as below:
SELECT First(Cust_Ref_No) AS Cust_Ref_No, Customer_No
FROM Customers
GROUP BY Customer_No
ORDER BY First(Cust_Ref_No), Customer_No;
There appears to be no “First” Function in Transact-SQL. Is someone able to show me how to achieve the same results in Transact-SQL as I can get with MS Access (I’d HATE to think that MS Access has more functionality in querying data than SQL Server in any way at all)?
P.S. I really need to run the select statement as one step rather than splitting it up into parts.
Regards, Trog28
(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
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
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 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.
Hello everyone,
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query
executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
Any help or suggestions will be much appreciated.
Thanks,
Larry
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 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)
Hi 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,
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies View RelatedI have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.
=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")
I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.
Is it because this is a matrix report? What am I doing wrong?
Thanks in advance . . .