Displaying Results In Aggregate Query Without Grouping Everything?
Mar 13, 2012
I have a query where I have customers, date they ordered a swatch, date they ordered an item, and eh date diff between the two. I want to show the MIN date diff for each customer, and also show the swatch date and item date as well. But to use the MIN aggregate, it forces me to group everything, where I just want to group by customer, and have the 2 dates tag along, because i only want one record per customer. What is the easiest way for me to accomplish this?
Hye Friends, I'm using a DMX query to get some predictions out of my MiningModel
my DMX query is as follows :
SELECT predict([x SalaryPredictor].[Emp Gross],20),predict([x SalaryPredictor].[Emp Basic],20) From [x SalaryPredictor]
This query is returning me 2 objects of type expressions in my Dataset. The problem is when I try to drag these 2 fields in my Table i get a "#Error" value
I executed the query in 'Data' tab and found that data is getting returned is a tree like format where "Expression" is at top & values expected ar its child nodes.
I also found out that the returned objects are actually AdomdDataReader , but i'm not able to write a expression to get its values in my tables....
I want to display the values in the following format....
I've got a union query (below)and it returns rows that have duplivate itemno's, descrip's, imsrp3's, and imsrp4's, while the remaining columns are not duplicate for the same row. An Excel report uses this query to populate itself and for a more visually appealing look, I'd like to skip the duplicated columns in the display. I'm not sure how to use the Distinct or Group by in this case, since technically I'm dealing with two separate queries, neither one separately returning any duplicate rows. thanks for any suggestions...
~ select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco, watrdj,wapddj,wauorg,wauser from nowo where nowo.wasrst <='40' union select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco, watrdj,wapddj,wauorg,wauser from nopo where nopo.wasrst <='499'
I have a problem... I have a SELECT query I have used in my PHP report. It is as follows:
SELECT DISTINCT callref, CASE WHEN (stage.due_date < stage.completed_date) THEN 'SLA Breach' ELSE ' In SLA' END AS sla FROM tableX.... WHERE call_status=open.....
I wish to search through all stages (i.e response, fix, end) of the calls logged in our database, & then return 'In SLA' for each call that had all its stages completed within the SLA, & 'Breach' for all calls that had even one of its stages completed outside its SLA.
At the moment the SELECT query above gives me the following results:
Call Ref sla
10001 In SLA 10002 Breach 10002 In SLA 10003 In SLA 10004 In SLA 10005 Breach 10005 In SLA ... ...
What it should look like is this....
Call Ref sla
10001 In SLA 10002 Breach 10003 In SLA 10004 In SLA 10005 Breach ... ...
Please let me know if anyone has the answers or any clues to this! thanks.
Hi All, I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
I have several tables that I need to summarize data from two tables based upon a dates passed in and group that data. I have attached my table layout, some sample data, and how I would like the results to look.
I am trying to create a user permission system that is stored in a database. What is the best table structure for accomplishing this? How could I display the permissions in a grid? Currently I have a users table and a permissions table. I created a map between the two. However, I don't see how this allows me to display a grid. All my "columns" for permissions are actually rows from the permissions table. So ideally my grid would look something like this. User | P1 | P2 | P3 |A | T | F | T |B | T | T | T |Thanks for any help. I am relatively new to SQL so please explain gently.
I want to return a table that shows each toy and what colors it is available in. Column1 - Toys | Column2 - Comma separated list of colors Ball - Green, Blue Kite - Red Frisbee -
I have absolutely no idea how to do this even after googling all morning. Please help if you can. There must be a way to do this!
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:
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_]
I have the following select statement on my page: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:prbc_hrConnectionString %>" SelectCommand="SELECT emp_lname + ', ' + emp_fname + ' ' + emp_minitial + '.' AS emp_fullname FROM employee WHERE (emp_username = @emp_username)"> <SelectParameters> <asp:SessionParameter Name="emp_username" SessionField="Username" Type="String" /> </SelectParameters>I want to say "Welcome, emp_fullname" at the top of the page, but can't figure out how to write the results to the page. I am moving to ASP.NET 2.0 from PHP and am banging my head against the wall trying to figure out how to do these little things. I appreciate any help you can give.
Hey All, This is really simple basic question, and it's just so I can start to learn about MVS. I have a sql-database called "Test" with a Table called "Agents" with just three colulms "ID", "FNAME" and "LNAME". On the Default Page I have three Textboxes and a submit Button, All I want to do is type "Select" criteria in Textbox1 and have Textbox2 Display the "Fname" and Textbox3 Display the "Lname". (Using VB in ASPX) Dim i As New SqlDataSource() i.ConnectionString = ConfigurationManager.ConnectionStrings("Test").ToString() i.SelectCommandType = SqlDataSourceCommandType.Text i.SelectCommand = ((((((Now What???)))))))
I am designing a database to store data from a leak tester.
We want to display the results between dates, I mean, the results of the leaking test are going to be stored as well as the datetime in which they have been performed.
ID (int) RESULT (float) TS (datetime)
The query will be, of course:
SELECT * FROM TABLE Where TS BETWEEN DT1 and DT2
This table is growing by 10000 rows a day, it is possible that in a year getting the values between two dates became impossible.
Using a index with a datetime field sounds like a crazy idea.
I have created a new reports application project in VS 2008. I have a dataset with 2 tables: Customer and CustomerAddress with one to many relationship. I want to have a simple table in my report which displays data in the following format: Customer Name Address ---------------------------------------------------------- ABC Add1 ABC Add2
XYZ Add1 XYZ Add2
Here Address is obviously from CustomerAddress table. I have tried few options but it's mainly disgusting to work with Reports Application project when there was an amazing Busines Intelligence Reports project available in VS 2005 and equally good designer interface (Dataset, Design and Preview tabs for each report). Please give me a solution to this.
Im trying to display all twelve results from a dataset in reporting services. On the layout tab I can get the first and last record in my dataset to display by using the commands:
I have an aggregate transform that outputs two columns, a group by (DT_STR) and a count(column name) (DT_UI8). The results are put into a Recordset Destination. When I attempt to map these columns to variables in a Foreach Loop Container (using a Foreach ADO Enumerator), I get the error:
Error: 0xC001F009 at ExtractNNRPersonUpdates: The type of the value being assigned to variable "User::NNRPersonCount" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC001C012 at Foreach Household Loop Container: ForEach Variable Mapping number 2 to variable "User::NNRPersonCount" cannot be applied.
The variable mentioned in the error message is setup as a UInt64. I've tried all other integer data types and nothing works. I also tried changing the data type of the count coming out of the Aggregate transform but received a warning stating this isn't possible.
I have been tasked with creating a report that shows sales of our products grouped into buckets of 5 each, DESC. I have a table that has the itemNo and revenue. The final report would be something like:
Top 5 Spreads$695,066 Next 5 Spreads$467,845 Next 5 Spreads$416,946 Next 5 Spreads$361,946 Next 5 Spreads$305,607 Next 5 Spreads$270,567 Bottom Spreads$15,954
My initial thinking was to use row_number() and partition to label the rows in groups of 5 like:
Hello,I have a very simple problem which I will illustrate with an example:I have the following records in my table:A 1 CA 2 CA 3 CB 8 KB 9 KI now want to group them and the result has to be:A 1,2,3 CB 8,9 KSo the results in the second row have to be concatenated. I guessthere is no function to do this... What is the simplest solution?Kind regards,Bart Warnez
I have created a view thats pulling data from two different tables to combine them into one report.
table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.
When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.
Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?
Script:
SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department, C.objInstID AS ClientID FROM dbo.cdbClient AS C INNER JOIN dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode ORDER BY ClientID
Hi everyone. I am updating a table with aggregate results for multiplecolumns. Below is an example of how I approached this. It works finebut is pretty slow. Anyone have an idea how to increase performance.Thanks for any help.UPDATE #MyTableSET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChangedelse null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),TerminationDate=(select Max(Case When Type = 'N' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),ReHireDate=(select MAX(Case When Code = 'HIRE' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and #MyTable.HRRef=HREH.HRRef )
ALTER PROCEDURE [dbo].[ADAMHsp_BSS_GetNonMedicaidReportTotals] @pstrProviderNameVARCHAR(100) AS BEGIN
[Code] ....
See my result set in the picture below. The rows with NULL in the 'ProcGrp' column have the totals of the groupings by FY that I am looking for - that's great. What I want to do now is have another row that contains the sums of the values from any row where 'ProcGrp' is null so that I have a totals row.
I have a view I've created which displays client sortname, partner and date added which displays 7 results.
When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script: SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added], dbo.cdbAttribInst.ainTVal AS Inudstry FROM dbo.cdbClient LEFT OUTER JOIN dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
[Code] ....
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...
Declare @SQuery nvarchar(3000) set @TblName1 = '[' + @TblName1 + ']' set @TblType = '[' + @TblType + ']'
SELECT @SQuery = 'select top 10 a.commodity1 as HS4, b.descrip_1 as Description, sum(a.all_val_mo) as [Amount],
(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM ' + @TblName1 + 'a where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share]
FROM ' + @TblName1 + ' a left outer join ' + @TblType + ' b on a.commodity1=b.commodity1 where a.stat_month <=' + @Month + ' and a.district=' + @District +' Group by a.commodity1, b.descrip_1 order by [Amount] desc'
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist. My current SQL statement is as follows. SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))" So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated. Thanks, James.
Hi, I have we have a client who gives their invoices in a flat file format, we import it into a SQL Server table. Nothing is normalized – everything is repeated in every record. The fields are: customerNumberInvoice_numberPO_numberQtyDescriptionLine_numberLine_totalFreightTaxInvoice_date So an if an order has 10 line items, the header information (invoice number, PO number, ivoice date) are repeated on each of the lines I am writing a query to show the following Order number, Invoice total, Date select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice_date from invoices group by invoice_no, Invoice, customerNumber This works great - for each invoice I get the invoice number, InvoiceTotal, and Date Then I was asked to add the PO Number – this is where I can’t get it right. When I added “PO_number� to the query, I got two lines for each invoice select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice, PO_number from invoices group by invoice_no, Invoice, Sold_To_Cust_No, PO_number Please help - I need to end up with: invoice_no, invoiceTotal, customerNumber, Invoice_date and PO_number (sequence does not matter) Thanks
I am attempting to wrie a query that will return aggregate totals from two different tables. The problem is that the TotalForecast totals are way to high. How do I write a query to obtain the correct totals?Table 1 - dbo.QM_ResultsColumns - dbo.QM_Results.Special8, dbo.QM_Results.SessionName, dbo.QM_Results.PersonNumberTable 2 - dbo.PM_ForecastViewColumns - dbo.PM_ForecastView.Hierarchy, dbo.PM_ForecastView.ForecastSelect substring(dbo.QM_Results.Special8,0,6) AS Hierarchy, substring(dbo.QM_Results.SessionName,0,11) As CourseCode,count(dbo.QM_Results.PersonNumber) TotalAssociates,sum(dbo.PM_ForecastView.Forecast) TotalForecastFrom dbo.QM_Results INNER JOIN dbo.PM_ForecastView ON dbo.PM_ForecastView.Hierarchy = substring(dbo.QM_Results.Special8,0,6)where SessionMid in ('96882139', '23620891', '45077427', '29721437')AND substring(dbo.QM_Results.Special8,0,6) in ('EZHBA')Group By substring(dbo.QM_Results.Special8,0,6),substring(dbo.QM_Results.SessionName,0,11)Sample of data returned with my current query.Hierarchy CourseCode TotalAssociates TotalForecastEZHBA CARD167200 1179 141480EZHBA CARD167201 1416 169920EZHBA CARD167202 1119 134280EZHBA CARD167204 99 11880Results when I run aggregate query separatelyActual Total takenHierarchy CourseCode TotalTakenEZHBA CARD167200 393EZHBA CARD167201 472EZHBA CARD167202 373EZHBA CARD167204 33Forecasted Total takenHierarchy CourseCode ForecastEZHBA CARD167200 999EZHBA CARD167201 900EZHBA CARD167202 800EZHBA CARD167204 800
I have two tables tb1 with item and qtyOnHand and a second table tb2 with item and qtyOrdered I am trying without success to make this happen;select sum (onHand-Ordered) from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as OrderedI kind of gathered it would work based on this http://weblogs.asp.net/jgalloway/archive/2004/05/19/135358.aspxI have also tried this;select tb1.item from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as Ordered, sum (onHand-Ordered) as available from tb1 where tb1.item = RD35Any ides, there are multiple rows of each item in each table tb1 is inventory with several different locations and tb2 is an orders table.
I would like to find the first transaction_date with the criteria below and return all transactions after. i would need to use the seqn number since many of the transactions could occur on the same day.
SELECT c.MEMBER_TYPE , c.DATE_ADDED , h.ID , h.ACTIVITY_TYPE , h.TRANSACTION_DATE , h.UF_1 , min (h.seqn) FROM
I have two tables ItemHistory and ItemStock. I would like to write a query which checks last years history and let us know if we have enough items in stock this year for a given span date.
First, It should get all items from @ItemHistory where WHERE DateSold >= '1/10/2007' AND DateSold < '1/11/2007' and then checks if corresponding items are found in @ItemStock, and then returns all the ItemID where sum(@ItemStock.Quantity) < sum(@ItemHistory.Quantity)
Thank You.
Here is the DDL and DML
DECLARE @ItemHistory TABLE ( ItemID INT, Quantity INT, DateSold DATETIME ) INSERT INTO @ItemHistory SELECT 12, 18, '2007-01-10' UNION ALL SELECT 12, 18, '2007-01-10' UNION ALL SELECT 26, 12, '2007-01-10' UNION ALL SELECT 28, 06, '2007-01-10' UNION ALL SELECT 29, 06, '2007-01-10' UNION ALL SELECT 30, 06, '2007-01-10' UNION ALL SELECT 31, 06, '2007-01-10' UNION ALL SELECT 31, 06, '2007-01-10' UNION ALL SELECT 32, 12, '2007-01-10' UNION ALL SELECT 33, 01, '2007-01-10' UNION ALL SELECT 33, 06, '2007-01-10' UNION ALL SELECT 36, 01, '2007-01-10' UNION ALL SELECT 52, 12, '2007-01-10' UNION ALL SELECT 83, 01, '2007-01-10' UNION ALL SELECT 36, 12, '2007-01-10' UNION ALL SELECT 37, 01, '2007-01-10' UNION ALL SELECT 38, 12, '2007-01-10' UNION ALL SELECT 17, 01, '2007-01-10' UNION ALL SELECT 17, 08, '2007-01-10' UNION ALL SELECT 12, 20, '2007-02-20' UNION ALL SELECT 26, 10, '2007-02-20' UNION ALL SELECT 30, 08, '2007-02-20' UNION ALL SELECT 31, 12, '2007-02-20'
DECLARE @ItemStock TABLE ( ItemID INT, Quantity INT ) INSERT INTO @ItemStock SELECT 12, 20 UNION ALL SELECT 12, 10 UNION ALL SELECT 14, 48 UNION ALL SELECT 17, 24 UNION ALL SELECT 19, 36 UNION ALL SELECT 19, 72 UNION ALL SELECT 20, 72 UNION ALL SELECT 26, 24 UNION ALL SELECT 28, 12 UNION ALL SELECT 29, 12 UNION ALL SELECT 30, 12 UNION ALL SELECT 31, 18 UNION ALL SELECT 32, 20 UNION ALL SELECT 32, 68 UNION ALL SELECT 33, 10 UNION ALL SELECT 35, 18 UNION ALL SELECT 36, 46 UNION ALL SELECT 36, 40 UNION ALL SELECT 37, 30 UNION ALL SELECT 38, 10 UNION ALL SELECT 38, 33
I have a database table that contains rows containing defect data. The two columns I'm interested in are device and severity. Device is the name of an item that contains the defect, and severity can be one of three values. I'd like to query the table so it shows the device, and the number of instances of each of the three values for that device. For example, the result table should look like this:
I have a table similar to the following: ID ¦ Name ID ¦ Period From ¦ Period To ¦ Percentage ¦ --------------------------------------------------------------------------- Important - Each person can have more than one entry. What I am trying to do is get the last percentage that each person obtained. The only way I have been able to do this is by the following: SELECT * FROM myTable LEFT OUTER JOIN ( SELECT NameID, MAX(PeriodTo) as PeriodTo FROM myTable GROUP BY NameID) t1 ON myTable.NameID = t1.NameID WHERE myTable.PeriodTo = t1.PeriodTo
I was wondering if there was another way of doing this, or whether this is an efficient method of doing this kind of query. Jagdip
