I'm having much difficulty figuring out how to write the following
query. Please help!
I have this table:
Event
EventId int Primary Key
PatientId int
SeverityLevel int
What I want returned in my query is a list of all (distinct)
PatientIds appearing in Event, with the *most severe* EventId returned
for each Patient. The higher the value of SeverityLevel, the more
severe that Event is considered to be.
The problem I am having is that I can't figure out how to (a) group by
PatientId, AND (b) return the EventId of the highest-severity Event
for *each* PatientId (Order By SeverityLevel Desc).
since events 3 and 4 are the most severe events for patients 1 and 2,
respectively.
Any help would be greatly appreciated. This seems to be something that
could be handled easily with a FIRST() aggregate operator (as in MS
Access) but this is apparently lacking in SQL Server. Also note there
may be multiple Events with a given PatientId and SeverityLevel, in
that case I'd want only one of the EventIds (the Max() one).
Many thanks,
Joel Thornton
Developer, Total Living Choices
<joelt@tlchoices.com>
(206) 709-2801 x24
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'm developing a c++application with connections to a database, and got a little problem with the construction of a specific SQL Query. I was hoping that some of you guys maybe could help me out...
the problem is: The table, table1, has two cols: Key and Item wich contains numbers only. Both are set to primary keys.
I want to find the records where Keys values 1, 2 has the same Item value (and if they don't I don't want to find any post at all, of course)
something like this: SELECT * FROM table1 WHERE ???
ex of table1: Key | Item ---------- 1 | 1 1 | 2 2 | 2 3 | 1
I have a table that keeps track of account access errors. When there are three access errors in one day, the account is locked out. How can i construct a query to select any accounts that have three access errors on the same date. The pertinent fields would be ACCOUNTNUMBER AND ERRORDATE.
I have a feeling it is very easy to do what I want. But I don't know how. I have 2 queries that return 2 results sets. I'd like to have just 1 query that returns 1 result set that contains all the data of the 2 results sets.
Query 2 returns Item Actual ------------------- Lion 1 Bear 1
What I want is 1 query that will combine the results Item Expected Actual ------------------------------- Lion 2 1 Tiger 2 0 Bear 2 1
I tried using a unions between the 2 queries but that doesn't work. I am pulling my hair out. I have been struggling with this for several days now. Any help would be greatly appreciated.
I'm developing a c++application with connections to a database, and got a little problem with the construction of a specific SQL Query. I was hoping that some of you guys maybe could help me out...
the problem is: The table, table1, has two cols: Key and Item wich contains numbers only. Both are set to primary keys.
I want to find the records where Keys values 1, 2 has the same Item value (and if they don't I don't want to find any post at all, of course)
something like this: SELECT * FROM table1 WHERE ???
ex of table1: Key | Item ---------- 1 | 1 1 | 2 2 | 2 3 | 1
I need to return a number of records at specifik days, i do it with this query;
SELECT LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10) AS Days, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)) AS Numbers_total, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)) AS Numbers_In FROM tb_SecurityLog WHERE (CONVERT(varchar, CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND (SL_PolicyName LIKE N'%') GROUP BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10) ORDER BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)
i also need to have a criteria at that second COUNT and if the criteria is not met that row should not be counted, is this possible at all?
One of the reports the user needs to see looks like this:
ProductNumber of MachinesTotal Sales Magazine/Drink2$120.00 Drink2$70.00 Magazine1$30.00 Food/Drink1$70.00
To clarify:
There are two magazine/drink machines (ID 1 and 3) There are two drink only machines (ID 2 and 6) There is one magazine only machine (ID 4) There is one food and drink machine (ID 5)
How do I do this query?
Ideally, I wouldn't limit the number of products in a given machine, but I can do so if necessary.
I'm using SQL Server 2000 so I can't use the newer PIVOT/UNPIVOT functions in SQL Server 2005.
I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).
I have 2 tables joined together by the IDs, People and the pets theyownPEOPLEID NAME1 JohnSMith2 JaneDoePETSID PET1 Dog2 Cat2 Hamster2 Hamster2 FishI have create another where the PETS are in one column separated bysemi-colons and removing the dupsNEW TABLEID NAME ALLPETS1 JohnSmith Dog2 JaneDoe Cat;Hamster;FishWhat is the best way to do it? The only way I can think of is to runan update where it checks to see if the value already existsTHanks!
I'm self-taught at SQL, so this may be an easy one for others, but I can't even figure out how to search for an answer.
I need to put together a query as a datasource for a chart showing the firm's top ten clients by revenue AND the top ten clients by hours worked. It's easy to do either query separately, but the problem comes in when the two are combined. Then top ten by revenue doesn't always include all the top ten by hours clients, and vice versa [at the moment, I'm running a top twenty for each, then hand-compiling the top ten in Excel--oy!].
How can I write a query that will guarantee to include the top ten of both revenue and hours lists?
We've a table which has about 1.5 mil records. The table has info like AccountNum FName LName, Flag, Address etc. There are duplicate Account Numbers.
What we're trying to accomplish is:
If I query the table as in the following, SELECT AccountNum, Flag, COUNT(*) AS CountStar FROM Table1 GROUP BY AccountNum, Flag HAVING COUNT(*) > 1
I'll get something like this:
AccountNum Flag CountStar 1234567 Y 2 9876543 Y 4 9184382 Y 3 7439831 Y 5 6958373 Y 4 ....... . . ....... . . etc..
First, I want to display the result as in the following:
AccountNum Flag 1234567 Y (along with other columns) 1234567 Y 9876543 Y 9876543 Y 9876543 Y 9876543 Y 9184382 Y 9184382 Y 9184382 Y ....... . ....... . etc...
Is it possible?
Once I've the result in the above format, the next step in plan is to update the flag with 'N' leaving the first occurrence flag as 'Y' but all others as 'N' for a particular AccountNum. Once I do this, the result should look like the following:
AccountNum Flag 1234567 Y (along with other columns) 1234567 N 9876543 Y 9876543 N 9876543 N 9876543 N 9184382 Y 9184382 N 9184382 N ....... . ....... . etc...
Can anybody suggest any ideas how to accomplish this?
Now, I've been racking my brain on how to list the Buyers and the number of related rows in the Orders table. Add to this that I only want to count the Orders where DatePaid IS NOT NULL.
Hello, I'm trying to find the most optimal way to perform a trickyquery. I'm hoping this is some sort of standard problem that has beensolved before, but I'm not finding anything too useful so far. I havea solution that works (using subqueries), but is pretty slow.Assume I have two tables:[Item]ItemID int (Primary Key)ItemSourceID intItemUniversalKey uniqueidentifierPrice int[Source]ItemSourceIDPriorityI'm looking for a set of ItemIDs that match a query to the Price(something like Price < 30), with a unique ItemUniversalKey, taking thefirst item with each key according to Source.Priority.So, given Item rows like this:1 2 [key_one] 152 2 [key_two] 253 1 [key_one] 15and Source rows like this:1 12 2I want results like this:2 2 [key_two] 253 1 [key_one] 15Row 1 in Item would be eliminated because it shares an ItemUniversalKeywith row 3, and row 3's Source.Priority is lower than row 1.Help!?
I have a SQL7 database that was installed as case-insensitive./* Sort Order = 52, Case-insensitive dictionary sort order. */This database contains a table that has a varchar column which containsdata such as:'JUDICIARY; EDUCATION; Subcommittee on Justice and Judiciary''Subcommittee on Justice and Judiciary; TRANSPORTATION''Subcommittee on Cities; JUDICIARY; TRANSPORTATION'I want to write a SELECT statement that gives me only those rows (1stand 3rd) that have JUDICIARY (not Judiciary) in the varchar column.This is SQL7 so I can't use COLLATE.I triedSELECT mycolFROM mytableWHERE mycol LIKE '%JUDICIARY%'AND CAST(SUBSTRING(mycol ,PATINDEX('%JUDICIARY%',mycol),LEN('JUDICIARY')) AS VARBINARY) = CAST('JUDICIARY' AS VARBINARY)But this leaves out the row with JUDICIARY and Judiciary in it (onlyreturns 3rd row).Any suggestions?
I need to write a sql query which is a master-detail query. Here's the example structure of tables:
Master table: ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint
Child table -- Wheel: ColID as longint, ColA as int, ColB as int Child table -- Door: ColID as longint, ColA as int, ColB as int Child table -- Window: ColID as longint, ColA as int, ColB as int ..... etc
From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).
My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.
Hi, I have this query... cmd = New SqlCommand("SELECT name, webd_category_desc.category_id, (name + cast(webd_category_desc.category_id as nvarchar)) as CNameID, link_id FROM webd_category_desc left outer join webd_link_category on webd_category_desc.category_id = webd_link_category.category_id where display = 'True' order by CNameID, link_id ;", SqlConnection1) It produces the following output (trunctated by me for this post example).
Architecture 5 Architecture5 90 I would like it to display instead (where 8 and 2 are the counts): Accounting/Bookkeeping 8 Architecture 2 Seeing that I had to join a few tables to get the above output, how can I now group on it to get the name, count(name) output I desire.I'm using ADO.NET in a VB.NET/ASP.NET 2.0 webapp. The data is in SQL Server 2000. I was hoping to do it in one SqlCommand statement if possible. I guess I can drop it into a view and then run my group by query on the view if I had to. I am getting a variety of 'field in select list must be used in a function or aggregate' errors in the attempts I have tried so far. Thanks in advance, Stewart
Hi folks. Hopefully this is a simple question. What's the easiest and most efficient way to group by a dateTime field in an SQL query? Here is exactly what I'm trying to do. I have a database table that contains transactions from an email maillog, so there are dateTime entries every second or so. I'm trying to build a query that will group a count of messages per hour for a given day. How can I make an hourly grouping of the total number of messages?SELECT count(*) FROM emailTable WHERE (delivDate >= '2007-10-03 00:00' AND delivDate < '2007-10-03' 01:00) Thanks, Chris
select (select fieldx from tabley where pk = tz.fk) as field1, field2 from tablez tz group by field1
this doesn't work..i get an error that field1 is not valid...so is there a way to do this that does work?
please realize that the above example is exactly that..and had i needed to do something that easy, join would be the easy choice..what i'm trying to do requires a sub query
and now for the $1M question...How ? I've tried the following select, but it is not working the way I want it to..
SELECT c2.ACCNO, c2.Rundate, c2.TrdCap, c2.TRANQTY, c2.DLPRCE, c2.NOTEAMNT FROM CLIENTSHAREDEALS c2 FULL OUTER JOIN (SELECT c1.ACCNO, c1.SHARENAME, SUM(c1.TRANQTY) AS Expr1 FROM CLIENTSHAREDEALS c1 WHERE (c1.ACCNO = '275479') GROUP BY c1.ACCNO, c1.RUNDATE, c1.SHARENAME) c1 ON c1.ACCNO = c2.ACCNO AND c1.RUNDATE = c2.RUNDATE WHERE (c2.ACCNO = '275479') ORDER BY c1.RUNDATE
I have a table with the following structure:main_category| category| sub_category| answer|dateBasically, the data will be along these lines:Neuro | LOC | Status | answer1|dateNeuro | LOC | Status | answer2|dateNeuro | LOC | Status | answer3|dateSenso| Visi | Clarity | answer1|dateSenso| Visi | Clarity | answer2|dateetc...I am trying to query the db and present the user with the data in thefollowing structure:Main CategoryCategorySub Categoryanswer1answer2answer3...Main CategoryCategoryEtc...There are literally 3 dozen main categories, categories, andsub-categories each with distinct answers.I could really use some help on a query to group the data in this way!Thanks in advance!!!Frank
HI all, I got a tsql that needs to be simplified. Select * from Table1 where condition1 and id not in ( Select id from table1 where condition2 and id in ( Select id from Table1 where condition1 ) ) basicly all records thats in condition1 but that doesnt have condition2 but limited to condition1. I'm probably maken this to complicated. but im tired and im losing time just on one stupid query. Thanks for the help.
Hi,I have data stored as in below sample :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-14 00:00:0063.00002005-11-14 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-16 00:00:0045.00002005-11-16 00:00:002005-11-17 00:00:0045.00002005-11-17 00:00:002005-11-18 00:00:0045.00002005-11-18 00:00:002005-11-19 00:00:0045.00002005-11-19 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-21 00:00:0063.00002005-11-21 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------I have to group the select query in this way :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------When I run below grouped statement, I get follewed result:SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,Rate FROM X GROUP BY Rate-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-22 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.0000-------------------------------+---------------------------------+--------------How can I do a query like in 2nd sample from top?best regards,rustam bogubaev
I have a table similar to the following (XYZ). I would like to write a select statement that will return the count of the unique items for each user that also happen to be less than 1 year old. The less than one year old part is rather easy dateadd(year, -1, getdate()), but I seem to be having a hard time figuring out how to get my desired result without using subselects. Any help greatly appreciated. Thanks in advance - Dan.
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?
