Now I want the records having flag2=1 only.. I.e ID=3 has flag2=1 where as ID = 1 and 2 has flag1 and flag3 =1 along with flag2=1. I don't want ID=1 and 2.
I can't make ID unique or primary. I tried with case when statements but it I am somehow missing the basic logic.
tblPerson - holds basic person data. tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId tblWebUsers - holds login data specific to a web account, but not every person will have a web account
I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.
People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.
So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.
But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?
Straight TOP by itself won't do it. GROUP BY by itself won't do it.
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank FROM RPL ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
tblServer represents a server, tblInstance represents any SQL Server instances present on the server. They're related thru srvID.
I'd like to write a query that gives me servers with all instances, on one row. This dataset will populate a List in an SSRS report.
I have this but it's clunky and does not provide for more than 2 instances on the server. I'd like this to account for any number of instances all on one row without having to hard code multiple joins:
with serverInfo as ( select srv.srvType as Type ,srv.srvName as ServerName ,srv.srvIP as ServerIP ,ins.instNetName as InstanceNetName
i have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).
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.
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
I need to query to return a result for each unique machine with the latest date. The example result below would be returned because they have the latest date.
MachineA 5/7/2011 MachineB 5/5/2010
Select Distinct would almost do it, but I need each unique machine that has the latest date.
I have 2 tables in a 1: n relation. How can i get a select statement that the field in the n-relation with outputs, separated by a semicolon; Example: One person have many Job Titles
Table1 (tblPerson) Table2 (tblTitles) 1, "John", "Miller", "Employee; Admin; Consultant" 2, "Joan", "Stevens", "Employee, Software Engineer, Consultant" and so on .... 1 in select statement:
any useful SQL Queries that might be used to identify lists of potential duplicate records in a table?
For example I have Client Database that includes a table dbo.Clients. This table contains various columns which could be used to identify possible duplicate records, such as Surname | Forenames | DateOfBirth | NINumber | PostalCode etc. . The data contained in these columns is not always exactly the same due to differences caused by user data entry; so some records may have missing data from some of the columns and there could be spelling differences too. Like the following examples:
1 | Smith | John Raymond | NULL | NI990946B | SW12 8TQ 2 | Smith | John | 06/03/1967 | NULL | SW12 8TQ 3 | Smith | Jon Raymond | 06/03/1967 | NI 99 09 46 B | SW12 8TQ
The problem is that whilst it is easy for a human being to review these 3 entries and conclude that they are most likely the same Client entered in to the database 3 times; I cannot find a reliable way of identifying them using a SQL Query.
I've considered using some sort of concatenation to a new column, minus white space and then using a "WHERE column_name LIKE pattern" query, but so far I can't get anything to work well enough. Fuzzy Logic maybe?
the results would produce a grid something like this for the example above:
ID | Surname | Forenames | DuplicateID | DupSurname | DupForenames 1 | Smith | John Raymond | 2 | Smith | John 1 | Smith | John Raymond | 3 | Smith | Jon Raymond 9 | Brown | Peter David | 343 | Brown | Pete D next batch of duplicates etc etc . . . .
I have a table that I need to do some computations on all the data but first I need to remove the duplicate records and insert the results into a destination table. Here's the example below. My table has 3.1 million rows. I have tried using the DISTINCT and the GROUP BY but both ways to select the data takes about half a minute to run. I'm wondering if there is a way to increase performance. Users are ok with this time since the process runs overnight but improving it won't hurt. I do have a clustered index on these fields but that doesn't seem to improve any.
I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.
What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
I have a job below, which takes the results and send to the users in email.But I have a question, how can I send only one email with all rows, not to send the for every row on table separated email.
For example we've got a row from [Formula_Calc] table 'F1+F3' as a string that needs to be transformed as 240+160=400
The below code works for the above example but if I pick 'F11+F3' instead , returns 2561 which comes from 2401+16. Probably replaces F1 value instead of F11 and adds 1st digit (1) if I got it right ...
DECLARE @formula NVARCHAR(100); DECLARE @Total NVARCHAR(100); SET @formula = 'F11+F3';
SELECT @formula = REPLACE(@formula,RowNo,Total) FROM [Totals]
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
insert into table1 (colname) values (value1) can only insert one record into the table. How to insert multiple records as value1, value2, value3... into a table? We can of course use the above repeatedly, but if I don't know how many records (which is a variable), and I want to write a code which just take value1, value2, value3 .... from the clipboard, to paste as a input. How to insert those multiple records into table without split it. Thanks
Hi.. I really need a help. Is there any way to insert multiple records into a table in one go?
I have a table named Fruit. It contains FruitId,OwnerId,Colour
The list of colour is got from another table name FruitColour. FruitColour Consists of 2 column, FruitName and Colour.
Is it possible to insert multiple records into Fruit table with one query if only the colour is changed.
Sample case I have an Apple. Fruit id=2 OwnerId=2 Colour -- > Select Colour From FruitColour where FruitName='Apple' (Will return multiple records)
I tried to insert using this query: Insert into Fruit(FruitId,OwnerId,Colour) Values (2,2,Select Colour from FruitColour where FruitName='Apple').
Gives me this error Subqueries are not allowed in this context. Only scalar expressions are allowed.
I need to do this because actually I am inserting multiple fruit at one time, and each have multiple colour. If I need to insert the colour one by one for each fruit it will takes a very long time.
Any suggestion are welcomed. Thank you in advanced.
I am building an invoicing database. I have no problems searching fordue dates and generating the invoice header. The problem is generatingthe invoice detail.My customers may have more than one item that needs to go into theinvoice detail table.For example:customer #123 has 2 items that need to be placed into the detailtable.Rate 1 email accountRate 2 hosting accountI have to get both of these records into the detail table.When using the conventional method, I get something alongthe lines of" insert failed. more than one record was returned"-------INSERT INTO detailSELECT (SELECT max([id])FROM iheader),CustomerRates.custid,rates.Price, rates.nameFROM CustomerRates INNER JOIN Rates ON CustomerRates.Rateid = rates.IDWHERE NextBill > GETDATE()-------I have even considered a cursor to loop through the records but I cantmake it run properly. I am not crazy about the performance of cursorsanyway.Any aideas would be greatly apreciated.
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.
hello, I am new to Slq 2000 Database,Now I create an asp.net application with sql 2000, in my database I have two 2 table lets' say "OrderHead" and "OrderDetail",they look like this: OrderHead orderdetail ---order no ----orderno ---issuedate ----itemname ---supplier ----desccription ---amount -----price ----Qty Now I created a user-defined Collection class to storage order detail data in memory and bind to a datagrid control. I can transfer Collection data to xml file ,my problem as below : There have multiple records data in my xml file,and I want to send the xml file as argument to a store procedure in sql 2000
anyone can give me some advise or some sample code ?
I'm trying to update a checkbox from "False" to "True" within a single table for multiple records. I can update a single record using the script below. However, I'm having trouble applying additional Id's to the string.
(Works) - Update Name_Demo set KEY_CONTACT = 'true' where ID = 225249
(doesn't work) - Update Name_Demo set KEY_CONTACT = 'true' where ID = '225249, 210014, 216543'
It says query executes successfully but returned no rows.
