I need some help aggregating values in a single table, where neither a simple Sum() nor a simple First() function will do... Would like to do Sum(First()) but that's not allowed!
Sample dataset (select * from cs_view):
Gender | Program | Student | Class_Section | Heads | Credits
------ | ------- | ------- | ------------- | ----- | -------
Female | English | Elena | Phys 101-b | 1 | 4
Female | English | Elena | Hist 101-c | 1 | 4
Female | English | Elena | Engl 101-a | 1 | 4
Female | English | Elena | Engl 105-b | 1 | 4
Male | History | Rich | Phys 105-a | 1 | 4
Male | History | Rich | Engl 101-c | 1 | 4
Male | History | Rich | Hist 101-b | 1 | 4
Male | History | Jacob | Phys 101-a | 1 | 4
Male | History | Jacob | Hist 101-b | 1 | 4
Male | History | Jacob | Engl 101-c | 1 | 4
Male | History | Jacob | Phys L-101-a | 1 | 0
Dataset has one row per student enrollment in class section. No trouble summing credits by student or by program (or gender). HOWEVER, aggregate head-count should add each student only once.
Desired table:
Gender Program Heads Credits
------ ------- ----- -------
Female English 1 16
Male History 2 24 - --- 3 40If I add a third grouping level, that is, add a student-level grouping to the desired table, First(Fields!Heads.Value) will return the correct student-level head count; however, I don't know how to sum up the student-level group header rows ('subtotal' rows), to aggregate head count by gender or by program.
I have an employee, who received an initial starting bonus of $50k. This value will be static from day 1 to day n and will never change. I want to see the intial starting bonus, but any sets higher than the employee dimension will need to aggregate the starting bonus. Is there an easy way to do this?
If I just look at the data from an employee's perspective, I can do this by making the measure a Min, Max, or Avg Aggregate function. But, if for instance I want to view the data from the perspective of departments, it would need to sum() the data instead (which min/max/avg don't do).
If I make the starting bonus a member property of the employee, and a calculated measure off the member property, it aggregates the data when it shouldn't.
I am using the following query (which works fine):
select min(timex) as start_date ,end_date ,entityid ,entityname ,locationid
[code]....
However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.
Problem 2
I have the following table which shows the location of different people at 1 hour intervals
I'm facing a big problem in my actual report development task... however, what i need to do is (in my opinion) so basic that there has to be a way to do this...
What does it all about:
My customer needs a report in which different measures are shown in rows in a matrix table. columns are reserved for the months, simulating a sort of calendar aspect.
However, the matrix has one additional group in it's rows and the whole matrix is contained within a list.
A possible situation could be following:
Coca Cola
Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec TOT %
The problem are those percentages in red. They are calculated on total amount of BRU/NET without distinction of the first group in the table (PM or IN). If I set the value for the textbox to the following expression:
=(sum(Fields!value.Value)/sum(Fields!value.Value,"Level2"))*100 Where Level2 is the Details specified in the list (in our case Coca Cola or Pepsi Cola) Reporting services makes the sum of the value field without making the distinction of which type of value it is dealing with...
My question is now rather obvious: Is this calculation possible and if yes how?? I've thought to write a custom method in Reporting Services (no assembly) but I've no Idea how i can 1) access the dataset. 2) I need to be able to specifiy the name of the measure for which the calculation needs to be done and 3rd I also need to pass the current detail of the list item to be able to apply the necessary filters on the dataset to perform the aggregation....
I have what I thought would be a simple problem, maybe my approach is all wrong!
Say you have customers, registered in a year/month group. They can be active in another year/month group.
So we get a simple table with registration year/month down the side and activity along the top.
This shows "of all the people p who register in month m, how many are active in month a?"
So assuming that all people registered in month m are also active in month m, the max(activity) for that slice (ie the max value for any active month for the registered month) should be the value if we collapse the activemonth group to activeyear.
Let me try to draw it :D
Registered/Active 2006 2007 ... Jan Feb Mar ... Jan Feb Mar ... ... 2 Jan 20 10 10 20 . 5 3 1 5 20 0 Feb 0 25 15 25 . 8 5 5 8 25 0 Mar 0 0 40 40 . 11 1 6 11 40 6 ... 20 35 65 65 . 24 9 12 24 85 2 Jan 0 0 0 0 . 50 34 44 34 50 0 Feb 0 0 0 0 . 0 45 40 41 45 7 ... 20 35 65 65 . 50 79 44 75 95 ..... 20 35 65 65 . 74 88 56 99 180
I think. The ...'s represent the grouping band total.
I've built a cube which has all the measures and dimensions I want, unfortunately the numbers are off: the product prize is aggregated then rather simply being fetched from the underlying oledb destination file in the data warehouse. So if product x costs 10 USD and has been purchased 3 times in a certain month by a customer, the cube shows a product prize of 30 USD. I've switched the measure's usage from SUM to no aggregations but I still don't get the simple value list without aggregation. Why is this happening?
The actual schema I'm working against is proprietary and also adds more complication to the problem I'm trying to solve. So to solve this problem, I created a mock schema that is hopefully representative. See below for the mock schema, test data, my initial attempts at the query and the expected results.
-- greatly simplified schema that makes as much sense as the real schema CREATE TABLE main (keyvalue INT NOT NULL PRIMARY KEY, otherdata VARCHAR(10)); CREATE TABLE dates (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, datevalue DATE NULL, PRIMARY KEY(datekeyvalue, keyvalue)); CREATE TABLE payments (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, paymentvalue INT NULL, PRIMARY KEY(datekeyvalue, keyvalue));
[Code] ....
Desired results:
SELECT 1 AS keyvalue, 'first row' AS otherdata, '2015-09-25' AS nextdate, 30 AS next_payment UNION ALL SELECT 2, 'second row', '2015-10-11', 150 UNION ALL SELECT 3, 'third row', NULL, NULL
I know I'm doing something wrong in the last query and I believe another sub-query is needed?
Let me answer a few questions in advance:
Q: This schema looks horrible! A: You don't know the half of it. I cleaned it up considerably for this question.
Q: Why is this schema designed like this? A: Because it's a 3rd-party mainframe file dump being passed off as a relational database. And, no, I can't change it.
Q: I hope this isn't a frequently-run query against a large, high-activity database in which performance is mission-critical. A: Yes, it is, and I left out the part where both the date and the amount are actually characters and have to pass through TRY_CONVERT (because I know how to do that part).
I'm trying to create a query to return Open, Close, Max and Min Price for each 1 minute interval. Source data has two fields - Price, and Datestamp at 5 second intervals.
I can calculate the Max and Min (below) and set the datestamp to the middle of the interval, but get stuck on how to also return the Open and Close price for each interval.
SELECT MAX(price) AS MaxPrice, MIN(price) AS MinPrice, DATEADD(ss, 30, DATEADD(n,DATEDIFF n, '1/1/2006', DateStamp),'1/1/2006')) AS DateStamp FROM MasterData GROUP BY DATEDIFF(n, '1/1/2006',DateStamp)
What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.
This is probably a stupid question, but here goes: I am trying toupdate a table differently based on what is in one of the columnswithin the table. For example, I want to set Fld1 to be "On" if Fld2is "A", and I want to set Fld1 to be "Off" if Fld2 is "B". This iseasy to do in two SQL statements, but I was wondering if there's someway to do it in a single SQL statement. Thus far, I have had no luckin figuring out how to do this. Any ideas would be appreciated!!
I think this may be an increadibly simple one, but it's been driving me up the wall. I have a dataset that contains some summary information about a group of companies which i'd like to represent in a report. The dataset looks like this:
I can't figure out how to select a specific field from the dataset (e.g. Company = Company1 and Lead_Type = Internal). This sort of thing must be done all the time, I'm sure I'm missing something fairly obvious. Any posts back gratefully received.
I'm pararmeterizing a SQLDataSource, but I've encountered a problem. The users want all records returned if no ID is provided. So when they first hit the page, they will see all records, then they can filter down to one ID if needed. However, if I add a selectparameter of ID, I have to default it to a value, which won't give the users all records. Suggestions?
I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable. The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
I have a sql server table column that can contain the following possible values. 1. 766/IT 2. 777/HR3. 890/EG4. 012/AS5. Trainee6. Contractor 7. Others I want to write an SQL query grouped by this column, Trainee should be one group, Contractor should be another different group, Others should be another different group and then every thing else ( 766/IT ,777/HR ,890/EG, 012/AS) should be grouped together as one group. Think of it in terms of a pie chart with those groups.
I have a am doing some date calcs () . The situation is that I have a Move date (date a customer moved to a new home). I want to calculate their sales for the following 0-3 months after the move (month 0 being the move month). I have the month and year of the move (MthStart, YrStart), and I am adding 3 to MthStart to get the MthEnd of that 0-3 month period. I will then find sales BETWEEN YrStart&MthStart AND YrEnd&MthEnd (there is a YrMth field in the sales table)
Of course, for MthStarts 10, 11, and 12, the ends are 13, 14, and 15. So for these, I need to subtract 12, and increment the YrEnd by 1.
I am wondering if there is a way to update both the MthEnd and YrEnd fields at one time instead of separate SETs (or maybe I am just thinking about this the hard way to begin with). Is there a way to update both in a single CASE statement like WHEN MthEnd> 12 THEN MthEnd-12 AND YrEnd+1?
Is there a way to make a sql table trigger fire off conditionally on calling app?We have this vendor app database which also have in-house custom built app operates on them. Some one are thinking using triggers to audit second part of the app activities, but want to skip the vendor app part of operation for at least performance reason.To my knowledge, the answer is no. An insert trigger will fire off whenever there is an insert. It doesn't provide a mechanism to check which app/process first, then conditionally fire or not fire.
I have a report that in most cases takes 4 parameters. In a few special cases, though, it takes 5 parameters. I have my code modified enough such that it can handle this exception, however I cannot figure out how to conditionally show the 5th parameter based on the 4th parameter. Is this possible?
Hi all, I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet. I need to build a package that 1) counts the rows from a view 2) if rowcount >0 extracts the data into a file
I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two. I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.
Using Row Count, I have to build 2 data flow tasks. Is there a way to do this in a single data flow? May be using an Execute SQL Task instead of row count?
Any suggestions will ge greately appreciated IgorB
Here is my dataset used by my report definition. The combo of barcode and order id is unique. The 'isDiscountedItem' field indicates if the customer used a coupon to purchased an item at a lower price.
I want to group my report by department id, class id and barcode. Then, I want to count all distinct order ids for which there was at leat one discounted item.
My report would produce the following output considering the above dataset:
Merchandise Number of customers who used a coupon -------------------------------------------------------------------------------------------------------------- Department 1 2
Class 1 2
Barcode 123 2 Barcode 789 1 Department 2 0
Class 7 0
Barcode 456 0
I've been looking at a possible solution using hash tables defined in the report code but I would like to find a 'cleaner' solution. Any help would be appreciated.
I just spent about 30mins searching through the forums for this and saw several posts, but I didn't find a straight answer that seems like it would work for my report. How can I add only 2 data rows to a group (to hide them via the group's visible properties) and keep the rest of the rows outside of the group, but still in the same column (vertical area), as shown:
I created a subtotal for my column group by right-clicking on the group and selecting subtotal. I would like to add the condition to the subtotal, that if the value is less than 0 to print zero and not the negative subtotal amount. Is there a way to do this? I haven't been able to find a way to add an expression to the greyed out subtotal field. Thanks.
I apologize if I'm posting this question in the wrong forum.
I have a query that exports data from an Oracle database to an Excel spreadsheet. The application that executes the query is Computer Associates Eureka Report writer. I do not have direct access to the database or any of its objects. With the data in Excel, I run a VBA macro-driven inventory report. The Excel spreadsheet row limitation is not a problem presently, however, the query exports a lot of extraneous data that I don't need. I actually export about a dozen columns of data however I'm only listing 4 in the example below.
Example:
CaseNum ActionDate ActionType ComplCd 1029901 09/08/2006 F 0 1029901 09/11/2006 C 0 1029901 08/18/2006 C 1 1029901 08/17/2006 F 1 1029901 08/01/2006 F 1
When the ComplCd = 1 I only want the query to export one row of data for each CaseNum. Is there a way to code my query to accomplish this?
In a UDF, how is the best way to extend a query with additional clauses based on expressions? The user input here is used to refine the basic query by introducing additional clauses. Is there something like the following?
-- The basic query SELECT column FROM table WHERE clause
-- Additional clause, only appended to query -- if expression evaluates to true IF @parameter <> default_value BEGIN AND additional_clause END
Right now I'm using CASE like the following, but it necessarily makes the query longer. Is there a more efficient way?
-- The basic query
SELECT column FROM table WHERE clause
-- Additional clause, should only effect result -- set when the parameter is not default_value AND table.column = CASE @parameter WHEN default_value THEN -- identity, table.column=table.column, -- should have no effect other than just a long query table.column ELSE @parameter END
Having one invoice table (ord) and one 'person' table (actor) I would like to include the name of the person who is responsible for an invoice: SELECT Ord.OrdNo, Ord.Selbuy, AC.Nm AS 'Responsible', AC.EmpNoFROM OrdLEFT OUTER JOIN Actor AS AC ON Ord.Selbuy = AC.EmpNoWHERE ord.ordno = 23505 This works perfectly fine if Ord.Selbuy has a corresponding value in Actor:|26914 |21|Yvonne| 21| or if there is no corresponding value in Actor:|26914 |21|NULL| NULL| But what if Ord.Selbuy=0? Then I end up with 3285 rows from Actor! This happens because Actor.EmpNo=0 is allowed. Persons which have never been employed or used to be employed gets Actor.EmpNo=0.Can I create a SELECT statement which only returns data from the INVOICE row if Ord.Selbuy=0?|26914 |21|NULL| NULL| p.s. I'm not able to change table structure/behavior of update procedures, because the tables/code belong to a "bought from the shelf"-business system.
This should be a simple solution, but it has been a long time since I've done any query writing (mostly in Oracle) and I am stumped, so here goes:
We are in the process of converting Access database to MSSQL with web form front ends.
I have a table, all columns are nullable, and want users to be able to query from a form, which has a field for each column and defaults to a % wild card for the entered value.
I want the users to be able to put any string in any field, and have it return each row that matches that, including rows with null values in the other columns, but not the column with the entered criteria.
Here is a sample of the data:
Code: SQL> select * from test;
COL1 COL2 COL3 COL4 ----- ----- ----- ----- this is a test this is not test this is not this is test too is test too is too is too
7 rows selected.
Now, if I have this SQL run, it will return only rows that have no nulls in any columns:
Code: select col1, col2, col3, col4from test where col1 like'th%' and col2 like '%' and col3 like '%' and col4 like '%';
COL1 COL2 COL3 COL4 ----- ----- ----- ----- this is a test this is not test this is test too
Now, if I use an OR clause for each column, this mostly works, but the trouble is it will also return rows with null values for the field that has criteria entered in it:
Code: select col1, col2, col3, col4from test where (col1 like'th%' OR col1 is null) and (col2 like '%' OR col2 is null) and (col3 like '%' OR col3 is null) and (col4 like '%' OR col4 is null); COL1 COL2 COL3 COL4 ----- ----- ----- ----- this is a test this is not test this is not this is test too is test too is too is too
The idea is to only select the first 4 rows in the above example.
I was playing with ISNULL in the select clause, but all it does is substitute a string for a null, and I think CASE will do the same thing.
Is there a way I can write this query so it will return rows with NULL values in any column, except the one(column) that has user entered criteria in it?
Does anyone know how to do the following. I'm trying to mimicreplication with triggers.I have 2 databases, each have these 2 tables.1.USERSID intNAME varchar(20)2.CHANGESTABLE varchar(20)TYPE varchar(10)col1 varchar(20)col2 varchar(20)On the USERS table I have a for insert trigger. Whenever a new user isadded the trigger puts an entry into the CHANGES table such as("USERS", "INSERT", "1", "Fred")I now have an application (vb.net) that monitors the CHANGES table onserver1. If it finds an entry it determines the table using the TABLEcolumn and performs the necessary insert and deletes the entry fromCHANGES. Now the problem is server2 also has an for insert trigger onthe USERS table so it puts an entry into CHANGES on server2. As youcan imagine this goes around in a loop.What I was hoping for was someway of saying, "I'm inserting from myapplication so don't do the trigger".Any ideas gratefully appreciated.Steve.
