Transact SQL :: Limit A Query Results When All Of Line Items Under Group Meet Certain Condition
Oct 1, 2015
I have a query that returns the data about test cases. Each test case can have multiple bugs associated to it. I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data
TestCaseID TestCaseDescription BugID BugStatus
1 TestCase1 1 Closed
2 TestCase1 2 Open
3 TestCase2 11 Closed
4 TestCase2 12 Closed
5 TestCase2 13 Closed
How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.
View 3 Replies
ADVERTISEMENT
Nov 20, 2015
I have records that I get in this format:
ID Customer Type TypeNUm
100 Tiger Item T100
100 Tiger Item T200
100 Tiger Item T300
100 Tiger Shiper SAAA
100 Tiger PO POAAA
200 Panera GL WE
200 Panera PO POBBB
The reftypes are not always the same, what I need is to get it in this form
ID Customer Type TypeNUm
100 Tiger Item T100,T200, T300
100 Tiger Shiper SAAA
100 Tiger PO POAAA
200 Panera GL WE
200 Panera PO POBBB
View 6 Replies
View Related
Oct 22, 2015
Here's what my table looks like;
UniqID | Code |
1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC
I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.
View 9 Replies
View Related
Aug 5, 2004
Hi there,
I have the following script that selects tables from my database with the same column name and then I delete data that falls within a specified condition. However what I need to be able to do is just select these tables that meet the condition and then just delete the data because at the moment it's also returning tables that I don't need.
So I just want to use a cursor on a table list that meet the criteria:
1) have qid column name
2) qid >= 5000000 and qid < 1500000000 '
Example
declare @strqry varchar(1000)
declare dailyYear cursor
for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE 'qid' = COLUMN_NAME order by table_name asc
open dailyYear
fetch next from dailyYear into @DelTable
while @@FETCH_STATUS = 0
begin
Set @strqry = 'Delete from '+@DelTable+' where qid >= 5000000 and qid < 1500000000 '
exec(@strqry)
fetch next from dailyYear into @DelTable
end
close dailyYear
deallocate dailyYear
Any help would be greatly appreciated!!
Thanks
S
View 3 Replies
View Related
Jul 16, 2015
I am using SQL 2005. I have some data from an old application that did not follow the rules for normalization. The table is for Invoices, and the table allows for 13 purchase items per record. So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12. For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc. It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date"). My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.
View 15 Replies
View Related
Jan 31, 2007
Hi below sample data incoming from a source that cannot be changed. Please ignore the mishandling of zls. Obviously it is not insurmountable - I am just interested in why it is happening because I cannot explain it. DECLARE @t TABLE(the_data CHAR(73)) SET DATEFORMAT dmySET NOCOUNT ON INSERT INTO @tSELECT ' 11'+SPACE(5)+'1649KN889001 2'+space(10)+'0'+space(10)+'08 01 2002'+space(10)+'04 10 2002'UNION ALLSELECT ' 11'+SPACE(5)+'1649KN889001 2'+space(10)+'109 08 2004'+space(20)+'21 07 2005'UNION ALLSELECT ' 11 13026721XX198734 1'+space(10)+'0'+space(10)+'XXXXXXXXXX'+space(10)+ '09 01 2003' SELECT CAST(REPLACE(REPLACE(date1_text,' ','/'),'XXXXXXXXXX',NULL) AS SMALLDATETIME) AS date_1_prob,CAST(REPLACE(REPLACE(date1_text,' ','/'),'XXXXXXXXXX','') AS SMALLDATETIME) AS date_1_ok_ish,CAST(NULLIF(REPLACE(date1_text,' ','/'),'XXXXXXXXXX') AS SMALLDATETIME)AS date_1_fine, date1_textFROM--derived table - selecting relevant substring(SELECT LTRIM(RTRIM(SUBSTRING(the_data, 44, 10))) AS date1_textFROM @t)AS der_t date_1_prob date_1_ok_ish date_1_fine date1_text----------------------- ----------------------- ----------------------- ----------NULL 2002-01-08 00:00:00 2002-01-08 00:00:00 08 01 2002NULL 1900-01-01 00:00:00 1900-01-01 00:00:00 NULL 1900-01-01 00:00:00 NULL XXXXXXXXXX Can anyone explain the result in the first row first column? Thanks
View 8 Replies
View Related
Jun 27, 2005
I have table1 with orderID and demographic info.
Table2 with orderID and items.
I would like to have a results display like this:
OrderIDDemographicInfo Item1Item2Item3....ect
One line per order. When I do a join I displaying all items in different rows.
View 1 Replies
View Related
May 20, 2015
I need to group by transactions at line item level. As the each item in the transaction may belong to different category and I'musing case statement to identify particular category for one column , I'm using sub-query to retrieve the results at item level.
Here is the code:
SELECT
fs.TransactionId
,fs.DateKey
,dc.FirstName
,dc.LastName
,dc.Company
[code]....
Due to case by statement and having multiple category I have to use multiple group by.
select TransactionId
,DateKey
,FirstName
,LastName
,Company
,City
,Sum(CategoryAPurchase) CategoryAPurchase
[code]....
View 5 Replies
View Related
Jul 23, 2005
Let's say I have the following table:entry product quality1 A 802 A 703 A 804 B 605 B 906 C 807 D 808 A 509 C 70I'm looking for a way to find the average "quality" value for aSEQUENTIAL GROUPING of the same Product. For exmple, I need anaverage of Entry 1+2+3 (because this is the first grouping of the sameproduct type), but NOT want that average to include row 8 (which isalso Product A, but in a different "group".)I'm sure it can be done (because I can describe it!), but I'll be amonkey's uncle if I can figure out how. I would imagine it wouldinvolve some sort of running tally that references the next record asit goes... to see if the product type has changed. Perhaps use of atemporary table?Muchas gracias!!Cy.
View 9 Replies
View Related
Aug 11, 2005
Hi,I need to limit results in the following query type:http://www.somewhere.com/php/sql-a....ql_order=&pos=1I found a reference that says I should be able to use LIMIT x[,y], butI don't know where/exactly how to add that to the string. Once I knowwhat it's supposed to look like, and can write something to generateit.If someone could post an example using the above and limiting theoutput to 100 records starting at position 1, that would be great.
View 1 Replies
View Related
Jun 4, 2015
I have a query that pulls back task and user assigned. Each task can have multiple users assigned. I want to pull back the single task and all the users assigned in one row.
Current Query:
select
t.Name 'Task',
d.FirstName + d.LastName 'User'
from [dbo].[Tasks_TemplateAssignTo] a join
Task_Template t on a.template_id = t.ID join
Doctor d on d.id = a.provider_id
Results from query above:
TaskUser
Call CustomerJohn Smith
Call CustomerBetty White
Call CustomerTammy Johnson
Order suppliesGreg Bullard
Order suppliesJosephine Gonzalez
Expected Results:
TaskUser
Call CustomerJohn Smith, Betty White, Tammy Johnson
Order SuppliesGreg Bullard, Jospehine Gonzalez
View 4 Replies
View Related
Apr 10, 2015
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). In some cases, items can be components of another item (called "Kits"). In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". In our Items table, we have a field called "Parent_Item_Id". Records for Child Items contain the Item Id of the parent. So a sample of my database would be the following:
ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. So an example of the report would be the following:
Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100
How I can setup my report to group properly?
View 6 Replies
View Related
Oct 11, 2013
I have one table which contines 3 columns they are
1.emp
2.monthyear
3.amount
in that table i am having 6 rows like below.
emp monthyear amount
1 102013 1000
2 102013 1000
1 112013 1000
1 112013 1000
2 122013 1000
2 122013 0000
i want a total on group by condition on each employee. which will have the data from NOV to dec 2013 only.
out put should be like this
1 2000
2 1000
View 9 Replies
View Related
Sep 23, 1999
How to get result from Query by group of (n) rows ?
- Is cursors the better way (and the only) ?
-Is there something as ROWNUM in Oracle or LIMIT in MySql ?
Example plz !
Thanks.
Alex
View 3 Replies
View Related
Jan 16, 2008
User's requirement:
use the SP get the dataset from DB at once.
Want to make an accurate count of paging ( 200 rows /page) at the SSRS side.
Need to provide sorting, user just need to click the according column header's caption.
The design is:
we add group to devide the data into 200 per unit. Choice 'page break at end'.
add 2 Report Parameters, SD & SF, means sorting direction and Field.
In the Table Parameters add:
=IIF(Parameters!SD.Value="Ascending",Fields(Parameters!SF.Value).Value,"")
=IIF(Parameters!SD.Value="Descending",Fields(Parameters!SF.Value).Value,"")
everything seems OK at this time, and the rpt is very quick.
The Bug is:
Test team found out the sorting was broken by group, because we Choice 'page break at end'.
Noe the sorting scope is just the first page (first group)
Help wants:
query DB once , Accurate paging, full scope sorting.
View 5 Replies
View Related
Nov 29, 2015
I have a query that returns material(items) that are used in an event on a certain day.
SELECT C.categoryName, count(I.itemID) AS InMission
from items as I
RIGHT JOIN Categories AS C on I.categoryID = C.categoryID
INNER JOIN LinkMissionItem as LM on I.itemID = LM.itemID
INNER JOIN Missions as M on LM.missionID = M.MissionID
where '2015/12/19' BETWEEN M.freightLeave and M.freightReturn AND isReturned = 0
GROUP BY C.categoryName, C.categoryID
ORDER BY C.categoryID
There are a total of 20 categories and I would like all the categories listed in the result even though there are no items booked in a mission. At the moment, I can only get the categories that have items in that category booked in a mission. I hoped that the RIGHT JOIN on the categories table would do the trick but it doesn't.
View 4 Replies
View Related
Jun 17, 2015
I have a query where i would calculate the counts of three Servers
declare @Managed float
declare @Active float
DECLARE @Values varchar(1000)
SET @Values = 'WE,EE,CO'
select @Managed = count(Name0) from v_R_System where Operating_System_Name_and0 like '%Workstation%'
[Code] ...
Here i need output like below
Workgroup Managed Active
'WE' 255 ,400
'EE' 300 ,450
'CO' 155, 600
So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?
View 3 Replies
View Related
Feb 9, 2006
For example..
select * from mytable where MyNum = 7
If this brings back more than 1 row, I want to display a message that says,
Print 'There is more than one row returned'
Else (If only 1 row returned), I don't want to print anything.
Can I do this? Thx!
View 1 Replies
View Related
Oct 5, 2006
Hi folks,I'm trying to write a simple SELECT statement that will execute inquery analyser but will just have the data with no column names, or thedotted line between them and the data. I also want to avoid thestatement at the end which says nnn rows affected. any ideas? I want todo this because I intend to write the results to a flat file.Thanks for your helpDanny....
View 2 Replies
View Related
May 1, 2014
select top 15 count(*) as cnt, state from table
group by state
order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
View 9 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Jun 15, 2015
I have to use a Group By in Sub query that will give me my desired results.
SELECT count(hc), CS, S_W, (SELECT COUNT(Reg_Temp)
FROM CS_S
WHERE Reg_Temp = "Reg") as [RegOnly]
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W
The query I have above counts ALL the Reg, I'd want it to give only the count that I'd get by using Group By clause i.e. S_W and CS.
View 8 Replies
View Related
Aug 20, 2007
I am trying to do a select statement and input the result to a different table how can this be done in one step? Now I am just coping to excel and importing back in this is a real pain.
Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips
Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate
Order by LeaveDate, LeaveTime, Trip
View 5 Replies
View Related
Jun 6, 2007
I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.
Example:
Now:
Code Snippet
Column Column
Section1 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Section2 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Should be:
Code Snippet
Column Column
Section1 -
Section1 Mnemonic
Section1 Mnemonic
Section1 Mnemonic
Section2 -
Section2 Mnemonic
Section2 Mnemonic
Section2 Mnemonic
View 2 Replies
View Related
Jul 10, 2015
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
View 6 Replies
View Related
May 17, 2012
The following
SearchCondition[] sc = {new SearchCondition() {
Name = "TypeName"
,Values = new string[] {"Report"}
,Condition = ConditionEnum.Equals
,ConditionSpecified = true
}};
catalogItems = ReportService2010.FindItems("/"
,BooleanOperatorEnum.And
,new Property[] {new Property(){Name = "Recursive",Value="True"}}
,sc
);
Returns the following error
System.Web.Services.Protocols.SoapException: The TypeName field has a value that is not valid. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidElementException: The TypeName field has a value that is not valid.
at Microsoft.ReportingServices.WebServer.ReportingService2010Impl.FindItems(String Folder, BooleanOperatorEnum BooleanOperator, Property[] SearchOptions, SearchCondition[] SearchConditions, CatalogItem[]& Items)
at Microsoft.ReportingServices.WebServer.ReportingService2010.FindItems(String Folder, BooleanOperatorEnum BooleanOperator, Property[] SearchOptions, SearchCondition[] SearchConditions, CatalogItem[]& Items)
The type appears to be correct. I've tried type of "Folder" and receive the same error.
View 5 Replies
View Related
Sep 16, 2015
After running a query (from the Query Builder) in SQL Server 2008 sometimes I can right-click on the results pane and "Save results as CSV file", other times it's not an option. After running a query for 24 hours (several million record results) I can't seem to do anything with the results. I have my settings:
Options | Query results | SQL Server | Default Destination for Results
set to "Results to File" and a path entered, but it doesn't work. Is there wording I can add to the end of my SQL statement such as "TO FILE xxx.csv" or something?
View 4 Replies
View Related
Oct 17, 2015
I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :
create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc
[Code] .....
View 3 Replies
View Related
Aug 8, 2015
I’m running a data integrity procedure from an agent job that is a scheduled weekly maintenance task which emails the results of my query, and is working properly.
I would however like this to only receive the email it the query contains results, and not send it no records are found to prompt me to take action.
My code less the personal information
EXEC msdb.dbo.sp_send_dbmail
@profile_name
= '',
@recipients
= '',
@subject
= 'Database Integrity - Import Errors',
[Code] ....
View 5 Replies
View Related
Aug 20, 2015
I didn't work with XML before and so I'm posting this question on how we can generate serialized XML. I have the following table -
DECLARE @Population TABLE (CountryId INT IDENTITY(1,1), CountryName VARCHAR(15), StateName VARCHAR(20), PopulationCount INT)
INSERT INTO @Population (CountryName, StateName, PopulationCount)
VALUES ('USA','California',300000),
('USA','Chicago',500000),
('Australia','Queensland',550000),
[Code] ....
Please note that I can have another country with 10 states or 30 states, so State Name is dynamic. Can this be done in SQL ? or we have to use .NET ?
SQLServer2014
-12.0.2000.8(X64)
View 6 Replies
View Related
Jun 16, 2015
I have three tables, Accounts, AccountCustomer and Customers, and the data-relationshiop between are defined according to the image below:
I created also a query (the sql-query below), displaying the customers for every account that is on the table "Accounts", and I got the results, as we can see in the image below:
SELECT A.AccountID,
c.CustomerNo,
c.Surname,
c.Name,
c.TaxNum
FROM Accounts A
left join AccountCustomer ac on ac.AccountID = A.AccountID
left join Customers c on c.CustomerNo = ac.CustomerNo
order by A.AccountID;
As we understand, an "AccountID" have multiple customers, so I want to transform tha multiple results to one row, grouping by AccountID (one account belongs to one or many Customers), like the image below:
I tried to use row_number()-expression to get this, but I didn't make it. So my question is, how can I alter my sql-query to get the final result like image above?
View 6 Replies
View Related
May 6, 2015
Below is my SQl which just counts the number of appointments and grouped by clinic. This is great but what I'd like to add is the percentage within each clinic.
For example Clinic BRESRAD1 has a total of 61 appointments, of which 75.41% are Normal Appointments and 24.59% are Diagnostic, Ideally I would like the percentage in the next column.
BRESRAD1 Normal Appointment 46
BRESRAD1 Diagnostic Appointment 15
BRESRAD2 Normal Appointment 17
BRESRAD2 Diagnostic Appointment 12
BRESRAD3 Normal Appointment 34
BRESRAD3 Diagnostic Appointment 43
My SQL is as follows:
SELECT ClinicCode,
CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END AS [Diagnostic Appt],
COUNT(OPAppointmentID) AS CountOfOPAppointmentID
FROM dbo.OP_APPOINTMENT
WHERE (AttendStatusNatCode IN ('5', '6'))
AND (ApptFinYr = '2014/15')
GROUP BY ClinicCode,
CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END
ORDER BY ClinicCode
View 13 Replies
View Related
Aug 13, 2015
I've got this set of registers (just an example) after ordering by the first 3 columns:
value_A value_B value_C ID date
1 2 3 YVIR 29/08/2015
1 2 3 ANTE 27/04/2015
1 2 3 REGO 20/02/2015
I need to get as a final result:
value_A value_B value_C ID date
1 2 3 REGO 29/08/2015
In other words, I need to get, after ordering the result by the date field, the most recent date but at the same time the oldest ID in the list.
I've been trying to do this with the group by clause:
select
value_A, value_B, value_C, min(ID), max(date) -- or max(ID)
from table
group by value_A, value_B, value_C
But in the field ID I'm getting the wrong result because this value is been associated with the alphabetic order.
In access this query involves the function LAST, but in SQL I have not found a good way to perform this. And I am asking because I have seen some possible solution but almost all of them involving the UNION operation, but my problem is, this table can have more than 350.000 registers.
This table is update by some one else, I just can access the information and use it as a source.
View 3 Replies
View Related
