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
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)
I have a table the records the results of three different tests that are graded on a scale of 1-7. The table looks something like this. PersonId TestA TestB TestC
1 4 5 4 2 6 2 4 3 5 5 6 4 1 5 1
I would like to have a SQL statement that would pivot all this data into something like this
Test 1 2 3 4 5 6 7 A 1 0 0 1 1 1 0 B 0 1 0 0 3 0 0 C 1 0 0 2 0 1 0
Where the value for each number is a count of the number of people with that result.
The best solution that I have been able to come up with is to pivot each test and UNION ALL the results together. Is there a way to do this in a single statement?
(If this has already been covered I apologize, but I could not find the solution.)
I have three tables, Users, DocType and Docs. In the DocType table there are multiple entries for allowed document types, the descriptions and other pertinent data. In the Docs table, there are all manner of documents. In the User table are the users.
The DocType and Docs tables are relational. DocType.ID = Docs.tID The Users and Docs tables are relational. Users.ID = Docs.uID
Every user is allowed to have exactly one document of each type. Therefore if there are 10 document types in the DocType table, there may be as many as 10 matching documents in the Docs table.
What I need is a single record for each user returning a boolean for each document type, whether or not there is a matching record in the Docs table.
For example, there are 5 document types defined in the DocType table (types 1 - 5), so the DocType table has 5 rows. In the Docs table, there are 23 rows, and in the User table there are 10 rows. Given that each user may have only one of each DocType, there could be a maximum of 50 rows in the Docs table, but there are 23, meaning that on the average each user is missing one document.Now the challenge is to return a table of all the users (10 rows) with a boolean value for each of the rows in DocType (as columns) based on whether there is a value in the Docs table that matches both the DocType and User.
WITH TEST AS ( SELECT TL.TERMINAL_ID,T.IP_ADDRESS, T.LOGICAL_CONNECT_STATUS, SI.SCHEDULER_ID, SI.INSTRUCTION, SI.GROUP_ID, SI.MAX_READ_RETRIES, SI.DATA_CHAR, SI.SCHEDULE_TYPE,SI.FILEPATH_FLAG, T.STATION_NAME,T.BANK_ID FROM SCHEDULERINFO SI INNER JOIN TERMINALGROUP TG ON SI.GROUP_ID = TG.GROUP_ID INNER JOIN TERMINALGROUPLINK TL ON TG.GROUP_ID = TL.GROUP_ID
[Code] ....
I need to pass dynamic values in FOR SCHEDULER_ID COLUMN. Because I have huge data.
I am developing a database in PowerPivot and I am wondering how to create many relationships between the same 2 tables. All relationships must be active.
Let me give you a DUMMY example: let's say that the database has 2 tables, the Employee table and Manager table:
I am having difficulty with an SSIS package that is simply to pivot a table, and perform calculations used in reporting.
Background: A sample set of data is being tested to pivot for reporting purposes. The sample set of data being used is 2226 rows that will pivot to 6 rows having nearly 400 columns.
Problem 1: This all seems quite simple except that when I try to pivot on one set of data it works perfectly, I try two at a time, again great. However, as I add three or more groups of data from a view (thus creating 3+ pivoted rows) SSIS throws an error of "-1071636293" and points the to the column containing the values to be pivoted. The error output dumps ALL source data rows with the same error code and message, even on data groups that were successfully pivoted and written to the destination DB all with correct mappings (including the noted with the error code)
Running the package on each group of data one set at a time, creating one pivot output row, always works for all data sets without throwing any errors. How is it possible that SSIS would run perfectly on each data set creating one row without any problems/errors/warnings, but when when run on all data at the same time it would fail?
Problem 2: When running on multiple sets of data creating many pivoted output rows, the error output viewer and log file contain duplicated data rows from the original query. For example if I received the row the pivot id 1001 ONCE in the query from the DB as it is written out to the error file and viewer I see this ID TWICE for each data group. So if each data set were to contain 100 codes to match and I ran this on three data sets, creating an original 300 rows from the db query, the error file and viewer will have 303 rows. This occurs for only different ID's in depending on the data set, but one ID in particular is duplicated for ALL. How could a row be duplicated in the error output in one case but not another when the source data does not contain ANY duplicates?
Problem 3: When running the package for three sets of data SSIS will note an error and redirect the row to the file I specified with no problem. However, as I run it on 4 or more data sets that cause more errors I receive the message: "The buffer manager attempted to push an error row to an output that was not registered as an error output. There was a call to DirectErrorRow on an output that does not have the IsErrorOut property set to TRUE." Why would the error logging work for one failure but have problems with more?
I am monitoring the package execution by: 1) logging all available output from SSIS to a file. 2) redirecting all errors from the pivot to a file which works only on the small data sets where I am expecting 1-2 rows to be the pivot result. 3) Placing data viewers on all directional arrows from original db query and pivot
Finally, even with all the errors or warnings all data is pivoted as desired and inserted into the destination database as if nothing was wrong, sometimes. Same package, same data, same errors, but delaying the package with breakpoints causes SSIS to either be successful or fail. I'll question that one later as I gather more information.
Any help on one or more of these questions would be a great help...
Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX); SET @columns = N''; SELECT @columns += N', p.' + QUOTENAME(deck) FROM (SELECT p.deck FROM dbo.report AS p GROUP BY p.deck) AS x;
[Code] ....
I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!
I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.
Consider that we have Product Category, Product and its monthly sales information retrieved as follows:
I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.
Our business model involves a lot of dates and the business owners frequently want reports based on each of these different dates. For example in any given order there are as follows:
- Order created date - Client due date - Order first payment date (an order can have multiple payments) - Order fully paid date - Date assigned to vendor - Vendor return date - Date delivered to client
On top of that we have other areas of the business, the data from which ties into the above. Here we have more dates e.g.
- Date vendor recruited - Date vendor reviewed
At any given point the manager may want a report based on any of these dates. For example;
- Product type by order creation date (fiscal year / month) - Product type by first payment date (fiscal year / month) - Product type by client due date (fiscal year / month)
and so forth. I have been asked to create a report using all of the above on at least one occasion, many of them far more frequently. At the moment I have created a standard date table and then duplicated that for each type of date that I need however this is becoming excruciating to work with as I have approximately 10-12 date tables in my data model. Is there a better way of doing this now, in Excel 2013? If not, is there an improvement in 2016 that may make life easier?
I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:
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:
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.
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.
