What I need is the start and end time of each task, but the issue is there is no unique task number to bind them together.. So for instance the task starts with 'Open-Submitted' and ends with 'Task Approved'. The issue is there can be multiple occurrences in the same file number. I need to be able to split these into multiple tasks with the associated start and stop times.
File IDDatetimesTask Event StatusTask Event NameTask IDEvent ID
File 16/3/13 16:33Open-SubmittedTask is retrievedTSK-12345612345
File 16/3/13 16:44Open-ApprovedTask ApprovedTSK-12345623456
File 16/20/13 18:11Open-SubmittedTask is retrievedTSK-12345634567
File 16/21/13 14:42Open-ApprovedTask ApprovedTSK-12345645678
Hey all,I am having some serious trouble getting the correct syntax for a select statement to work the way I need it, any help I could get on this would be greatly appreciated.I have a table called Units which stores computers and a table called Software which stores software. I have a bit field in Units called OEM, when this is set to true I don't want the select statement to pull this unit down when I am assigning software to other units.Here is my select statement: SELECT Software.SID, Software.SN, Software.Name, Users.First + ' ' + Users.Last AS 'Assigned User', Units.Make + '-' + Units.Model AS 'Assigned Unit' FROM dbo.Software LEFT JOIN dbo.Units ON Software.SN = Units.SN LEFT JOIN dbo.Users ON Units.UID = Users.UID WHERE (Units.OEM = 'FALSE') AND (Software.SN LIKE '%' + @SearchString + '%')Everything works as expected, unless of course the unit has no software assigned to it yet, it won't return it because its not tied to a Units.OEM field. Is there anyway to have it return ALL records that even arn't joined OR are joined but have OEM set to false?Thanks, let me know if I need to clear anything up.-Matthew
Hi All,I'm trying to solve this for a few days now and I just can't figure itout...I have three tables set up, I'll simplify them for this question:Table 1: HOTELSColumns: HOTEL_ID, HOTEL_NAMEExmple row: 123 || 'Hotel X'Table 2: SERVICESColumns: SERVICE_ID, SERVICE_NAMEExample rows:1 || 'Breakfast in bed'2 || 'King size bed'Table 3: LINK_HOTELS_SERVICESColumns: FK_HOTEL_ID, FK_SERVICE_ID, SERVICE_VALUEExample rows:123 || 1 || 1123 || 2 || 1In table 3 I link different services to different hotels. In the same tableI set the "value" for the service.The first example row of table 3 means something like: Hotel X offersBreakfast in bed. In this case 1 stands for TRUEThe second example row of table 3 means: Hotel X offers King size beds(again: 1 stands for TRUE).What I'm struggling with is selecting the hotel ID's which offer multipleservices. To stay in the example: how can I select all hotels whereSERVICE_ID = 1 AND SERVICE_ID = 1. I can't seem to figure out how to doit...I hope anyone can help... Thanks a lot in advance!!!Robert
I need to list all the records in Table2 which don't have matching field values in Table1.
This the the exact opposite of what I need: SELECT DISTINCT Field1, Field2, Field3, Field4, Field5 FROM [Table1] WHERE EXISTS( SELECT DISTINCT FieldA, FieldB, FieldC, FieldD, FieldE FROM [Table2] )
The above seems to give me all records in Table1 in which the five fields match the five fields specified in Table2. What does not show up is the test record I put in Table2 which is not in Table1.
What I need, however, is the exact opposite.
I tried the above using NOT EXISTS but I get no records at all.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1) and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
I have a database which has a field called fldTimes. basically this field records the number of hits a file gets. How can I choose the most 5 popular files with the greatest hits. Thanks
This might be a simple question. I have a LIKE statement that is working fine, however I am not sure if something else is possible.
I can pull all records on a query for a person's name with a parameter value of "MARTIN". It will also pull records for "LYNN MARTIN". However, what if I would like to have that search also pull "LYNN M MARTIN"? Currently "LYNN MARTIN" is not finding "LYNN M MARTIN".
When the end user wants to search on LYNN MARTIN and that is what they input, I want SQL to find all records that match LYNN MARTIN and also find records that HAVE LYNN % MARTIN.
I hope this make sense? I guess I need to build my select statement using a WHERE LIKE statement, but I am not sure of the syntax.
Hi, I'm just wondering if someone can help me with some SQL syntax stuff.I want to take this sql statement: "SELECT TOP 50 tblProfile.chName, tblProfile.intCount FROM tblProfile, tblLinks WHERE (tblLinks.MemberID = tblProfile.MemberID) ORDER BY tblLinks.dtDateAdded DESC;" and select only unique "chName's" records
Hello Everyone and thanks for your help in advance. I have a SQL Server Table wtih approximately 100,000 records. I need to determine if there are duplicate records in this table. My probelm is that there is a unique ID column that was added for each row, so I'm not exactly sure how to filter the rows. Any help on this would be greatly appreciated. Thanks.
hi again, i just want to ask if how can i randomly select 5 distinct records from a table w/ a hundreds of records everytime i exec a stored procedure?? thanks
I have a reviews table where all reviews are submitted. On the main page I want to display the 10 most reviewed products. I have a Product_ID column in this table which identifys the product. How can i write a query which will select the product_ID of records which have the most frequent product_ID's?
I came up with something like this: "Select Top 10 Product_ID, COUNT(*) AS Occurances FROM reviews GROUP BY Product_ID ORDER BY occurances DESC"
But it does not work.?? It says "Declaration expected" as error
Hi, my sql is not too hot so i hope someone can help me. I need to select all the records from one table that do not exist in 2 other tables. I know it sounds simple enough but for some reason i can not get it working. It may have something to do with the fact that the field i am searching on are datetime fields. Here is a shortened version of my code.
SELECT DateOfStats FROM table1 WHERE (DateOfStats NOT IN (SELECT dateofstats FROM table2)) and (DateOfStats NOT IN (SELECT dateofstats FROM table3))
i need to select records from table "A" if only the "PK" of "A" exists in table "B". I need to return a resultset not just a single record. The problem is table "B" is not a table in database instead a user supplied table which can be a datatable in memory.
I am trying to create a select query similar to the following but the problem I am having is that I want to only select one record where there may be several with the same dw_order_no. I have tried various ways using SQL developer but without success
SELECT VE_EZP_ORDER_TRANS.EZP_BILL_STATUS AS EZP_BILL_STATUS1, VE_EZP_AGED_CUSTOMER_DEBT.SURNAME, VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE_CATEGORY, VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE, VE_ORDERLINE.DW_ORDER_NO,
Is there a way to see a list of duplicate records?? EG There is a field named "Invoice" in a table named "Orders" and I want to see only records where the same invoice shows more than once.
Good Morning,I have a view that contains rate information, contractIDs, and effectivedates.I need to select the rate info based on contractID and date.I can provide a date and contractID, and I need to select the rate info forthat contract where the effective date is <= dateprovided.I need the 1 record that is closest to that date.I am thinking something with max() perhaps. Any ideas? The <= effectivedate will return several rows, I just need the one closest to the date Iprovide.Thanks for any advice,CK
I have a table contains information related to sales:
SO number Order Date Customer SellingPerson 1001 2012/07/02 ABC Andy 1002 2012/07/02 XYZ Alan 1003 2012/07/02 EFG Almelia 1004 2012/07/02 ABC John 1005 2012/07/02 XYZ Oliver 1006 2012/07/02 HIJ Dorthy 1007 2012/07/02 KLM Andy 1008 2012/07/02 NOP Rowan 1009 2012/07/02 QRS David 1010 2012/07/02 ABC Joey
Now, i want to write a query using CTE that gives me first five distinct customer in result set:
SO number Order Date Customer SellingPerson 1001 2012/07/02 ABC Andy 1002 2012/07/02 XYZ Alan 1003 2012/07/02 EFG Almelia 1006 2012/07/02 HIJ Dorthy 1007 2012/07/02 KLM Andy
I wrote this query :
With t(so_number,order date,customer, SellingPerson) as (select top 5 so_number,order date,customer, SellingPerson from t) select distinct billingcontactperson from t order by so_id
And getting this error:
Msg 252, Level 16, State 1, Line 1 Recursive common table expression 't' does not contain a top-level UNION ALL operator.
Hi! I have a table Tbl1 has to columns: A B _________ Ibm Me Sony Me Me Bob Me Frank I'd like to select all rows where B=ME and A=Me Thanks for the help
I need to select records by random order everytime user go to that page. QuestionID is the uniq field in this table.
SELECT * FROM QuestionMaster.
I tried to use the following statement to get the random records but I can't get the value in the variable out of this statement that I can use in my above select statement. Please HELP.
I want to run a query that returns say 100 records...but I only want to return first 10 for first page on a web page, then on page 2 the webpage will return rows 11 to 20 of the same SQL statement...page 3 returns rows 21 to 30 rows etc....(eg. like Google or bulletin boards, browsing auctions in ebay etc.).
I could probably get my application logic to handle this (ASP.net), for instance I could possibly get a datareader to skip the first 10, output the next 10 then stop for page 2 (records between 11 amnd 20) but is there way to do this in SQL Server at the database level using an SQL Statement?
I can use TOP 10 to get the first set of records for the first page eg.
SELECT TOP 10* FROM Suppliers
...but how do I get between 11 and 20, 21 and 30 and so on?
I've already mentioned I could handle this in my application logic, but then each time the same SQL Statement is fetching all 100 records, even though the web page will only display a certain range. I'm building an intranet website that can potentially run queries that return 100'000s records, even though initially only top 20 or so records are display, each page they subsequently go to will rerun that same query that returns all 100'000 records. So handling this as part of the query would be better for performancr I reckon.
I have a table of approximately 10 million rows. The table has 3 field making up the key, namely: ID, Date, Program
I need to extract all the distinct Program's from the table. I have don so with: Select distinct Program from table This unfortunately takes roughly 2 minutes which is far to long. Is there something I can do to help speed this process up?
