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:
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?
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.
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:
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.
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).
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.
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)
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
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:
?* 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.
I'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
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:
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.
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
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 ?
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
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.
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?
