Here is my table and data of these tables Table name: Code CID Code 1 abc 2 def 3 xyz Table Name : Details ID Date Value1 CID 1 1/1/2009 12 1 2 1/1/2009 25 2 3 1/1/2009 18 3 4 1/2/2009 36 1 5 1/2/2009 45 2 6 1/3/2009 19 1 Resulta...
I need a SQL query that returns the top 2 Plans by PlanDate per ClientID. This is all on one table where PlanID is the PrimaryID, ClientID is a foreignID. This is what I have so far --> SELECT * FROM [dbo].[tblPlan] WHERE [PlanID] IN (SELECT TOP (2) PlanID FROM [dbo].[tblPlan] ORDER BY [PlanDate] DESC) This, obviously, only retu...
Want to clear some concepts about SQL internals. Suppose I have a table as: ---------tblXY----------- X int Y int Now it has records as: X Y --- 1 4 2 3 3 2 4 1 And I want the resulting table to be: X Y --- 4 1 3 2 2 3 1 4 So I wrote the query as: UPDATE tblXY SET [X] = Y ,[Y] = X and got the required result. But how d...
This might be a very simple question, but am just curious about it. Are these queries both representations of equi join? I find that both returns the same result. Query1: SELECT C.* FROM [Case] A, [Event] B, Notice C WHERE A.CaseID = B.CaseID AND B.EventID = C.EventID Query2: SELECT C.* FROM [Case] A join [Event] B on A.CaseID = B...
Table1 ------------ ID IdColumn1 Idcolumn2 Table2 ------------ ID IdColumn IdPair Both of them contains the same data. Table1 have both column populated, Table2 have those columns stored on two rows. So, if Table1 contains n rows, Table2 will have 2 * n rows Wich query is faster ? select * from Table1 where IdColumn1 = x or IdCol...
this is little hard one. i have latitude and longitude and i want to pull the record from the database, which has nearest latitude and longitude by the distance, if that distance gets longer then specified one, then don't retrieve it. Table structure: id latitude longitude place name city country state zip sealevel ...
i am working in winforms with C#. heres my code query = "SELECT max(Appointment_Time) FROM Appointments WHERE (Appointment_Status = 'D')"; dset = db.GetRecords(query,"Appointments"); ctime_Label.Text = dset.Tables["Appointments"].Rows[0]["Appointment_Time"].ToString(); db.GETRecords is a function of the class that provides me sql-serv...
Hi, I am writing a small DB test suite, which reads configuration files with queries and expected results, e.g.: query = "SELECT * from cities WHERE name='Unknown';" count = 0 level = 1 name = "Check for cities whose name should be null" suggested_fix = "UPDATE cities SET name=NULL WHERE name='Unknown';...
Hi, I'm trying to write a query for an online coding event website, but I'm finding it particularly difficult to deal with. I have a table submissions: submissions => subid | uid | pid | subts | status uid = userid pid = problem id subts = timestamp when the submission was submitted status = whether the answer is right or not A us...
Hi guys, I have a huge excel with thousands of rows and I need to generate an sql query to insert the data into a sql server database. The format of the excel is the following 1 | string1 | another string string2 string3 2 | AAA AAA | ZZZZZZZ BB BBBB CCCC CC The first column is a row counter, it doesn't matter. The ...
For the following table definitions: Name Null? Type Comments ------------------------------- -------- ---- ------------------------------------ ENUM NOT NULL NUMBER(4) ENUM should not exceed a length of 4. ENAME CHAR(15...
I have got a table in access that I loop through using a DAO recordset. For every recordset I take a bunch of data to an excel spreadsheet and run it through a model in excel. This produces a bunch of results in excel which are calculated in named cells. I want to be able to update the current recordset in access with these results but ...
I have the following query which have 1000 rows select staffdiscountstartdate,datediff(day,groupstartdate,staffdiscountstartdate), EmployeeID from tblEmployees where GroupStartDate < '20100301' and StaffDiscountStartDate > '20100301' and datediff(day,groupstartdate,staffdiscountstartdate)>1 order by staffdiscountstartdate desc i ...
Hi, I need to transform the rows into columns for the similar ID's in oracle e.g. The following is the result I will get if i query my database Col1 Col2 Col3 ---- ---- ---- 1 ABC Yes 1 XYZ NO 2 ABC NO I need to transform this into Col1 Col2 Col3 Col4 Col5 ...
I have been working on sql server and front end coding and have usually faced problem formulating queries. I do understand most of the concepts of sql that are needed in formulating queries but whenever some new functionality comes into the picture that can be dont using sql query, i do usually fails resolving them. I am very comfort...
I have got a bakcup of a live database (A copy of an ACCDB format Access database) in which I've worked, added new fields to existing tables and whole new tables. How do I get these changes and apply that fastly in the running database? In MS SQL Server, I'd right-click > Script Table As > Alter To, save the query and run it wherever ...
I am trying to fetch some records from table but when i use OR instead of AND it returns me few records but not in other case. dates given exactly are present in table. What mistake i am doing ? select newsid,title,detail,hotnews from view_newsmaster where datefrom>=CONVERT(datetime, '4-22-2010',111) AND dateto<=CONVERT(datetime, '...
I am trying to retrieve some records from table based on my query but it shows me an error Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '> select vd.LedgerId,(CreditAmt-DebitAmt) AS NET, CASE NET WHEN NET > 0 THEN 'Debit' WHEN NET < 0 THEN 'Credit' ELSE 'Nil'End from dbo.vdebit vd INNER JOIN dbo.vCredit vc ON vd....
Hi, I have the following Table definition with sample data. In the following table, Customer Product & Date are key fields Table One Customer Product Date SALE X A 01/01/2010 YES X A 02/01/2010 YES X A 03/01/2010 NO X A 04/01/2010 NO X ...
If it matters, I'm using Firebird 2.1 database. I have three tables, one with keywords, one with negative keywords, and the other with required keywords. I need to be able to filter the data so the output has just the keywords that meat the stipulation of not being in the negative keyword list, and IF there are any required words, th...