Finding Identical Values For A Given Set Of Records??
Mar 13, 2008
Here's what I'd like to be able to do: I have a queue that holds any number tasks, so something like this here:
queue_1 task_a
task_b
task_c
task_d
Workers are assigned to teams, Red team, Blue team, Green team. What I need to do is identify instances where all tasks for a given queue have been handled by one team. Once a task has been assigned to a queue any team can work on it, but when only one team has completed every task in a queue a bonus should be awarded.
I'm looking for this to award a bonus:
queue_num task_num team
queue_1 task_a red
task_b red
task_c red
task_d red
No bonus for any team here
queue_num task_num team
queue_1 task_a red
task_b blue
task_c red
task_d green
So the red team earns a bonus. Now, I have thousands of queues each containing any number of tasks. Using T-SQL how can I find all queues where only one team was responsible for completeing every task assigned to the queue? Do I have to use a cursor and eval each task coming through or is there a faster, more efficient way to handle this in SQL?
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..
I have a robust query that returns a dataset and the data is good, however some of the records contain the exact same data with the exception of the 'Price' field. I want to combine the records that are identical and SUM the values in the 'Price' field. My query and example return dataset is below.
Query: -------- select distinct 'On-Demand' as 'Business Line', o.OrderID as 'Order #', isnull(d.DisplayCode,'UNK') as Hub, isnull(rz.RouteID,'UNK') as 'Default Route', 'On-Demand' as 'Assigned Route',
CREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)
I have implemented a script to perform a MD5 hash on each row processed by the SSIS package so that it can be compared with a stored value to see if there has been a change in the record. This package processes over 1 million rows. In 12 of these rows I get a hash value that is different than the stored value despite the fact that the rows "look" identical. Curious about this, I used the both the CheckSum and Binary_Checksum feature from t-sql to check the rows and they both show the identical checksum value. I have exported the rows into text and did a compare and the records are identical. I assume there must be some hidden characters that is causing the hash to be different, has anyone else run into this issue? Any help is much appreciated.
i created a simple table to record all uploaded files to my website. now, it works, but the problem is, it posts to the table 2 times, as in it executes "Button1_Click" event twice. The result is i get two records which are the same, and only differs in primary key (because i set it as an autonumber). how do i fix this? thanks in advance here's the code: HTML: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Base_Files] ([User_ID], [Date_Posted], [File_Type], [File_Size], [File_Name], [File_Description]) VALUES (@User_ID, @Date_Posted, @File_Type, @File_Size, @File_Name, @File_Description)">
Im wondering if it is possible to write a procedure that check two identical tables for any missing records. The table design is excatly the same, but some records (of the 40,000) have not copied over to the second table.
Hi,Currently, I am developing an application for my client. The issue is that the client has the office and warehouse which are in different locations. He wants the application run and updated all the data in to a database (called warehouse database). Every month, all records in warehouse database will be synchronized to another database which is identical with the warehouse database but it is run in his laptop at the office. Since networking is not an option, I have to create two identical database like this. Does any one know what is the good solution to this? Is there any synchronize feature in SQL Server 2005?Many thanks,
We have written an application which splits up our customers data intotheir individual databases. The structure of the databases is thesame. Is it better to create the same stored procedures in eachdatabase or have them in one central location and use the sp_executesqland execute the generated the SQL statement.Thank you.Mayur Patel
Hi I am new to SQL. My scenario is I get a string from the user I need to match this string with value present in 2 cloumns. If the string is present in either of the column then i should select that particular record from the table. Now my question is if i Have to match with one cloumn i can used charindex function is there any function with which i can match the string with data present in more than one cloumn.
I searched for all the posts which covered my question - but none were close enough to answer what i'm trying to do. Basically, the scenario is thus;
Table1 contains values for UserID, Account code, and Date.
My query (below) is trying to find all the accounts assigned to a particular user ID, but also those duplicate account codes which belong to a second user ID. The date column would be appended to the result set.
The query I'm using is as follows;
select acccountcode, userid, date from dbo.table1 where exists (select accountcode from dbo.table1 where accountcode = table1.accountcode group by accountcode having count(*) > 1) and userid = 'x-x-x' order by accountcode
What I think this produces is a list of all files where a duplicate exists, but of course it leaves out the 2nd UserID...which is crucial.
Hopefully this makes sense. Any insight my fellow DBA's can share would be greatly appreciated!
I need to create a table that would be the result set of a comparisonbetween table a and table b? Table a and b first 2 fields will always bethe same (CustomerName and CustomerNumber). But if the Address1 fieldchanges in table a, I would like to throw that whole row into mycomparison table. Almost like a Select Into with a sub query that wouldinclude a WHERE TableA.field <> TableB.field. I would need to do thiscomparison for about 8 fields. Help appreciated for my syntax is prettybad. Thanks.Steve*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I want to write a query to bring out any records in a table where any of its fields contain a null value. I could do it in this way:
select *
from customers where name is null or role is null or email is null u003c/p>u003cp styleu003d"margin:0cm 0cm 0pt">u003cfont faceu003d"Times New Roman" sizeu003d"3">u003cfont coloru003d"#000000">However I want to perform a similar function for a number of tables and the method above would mean more sql and maintenance.u003cspan>Â u003c/span>I was hoping there was a simple way to do the same thingu003cspan> ie find any rows with a blank fieldu003c/span>?u003cspan>u003cfont faceu003d"Arial" sizeu003d"2">Â u003c/font>u003c/span>u003c/font>u003c/font>u003c/p>u003c/span>u003c/font>u003c/div>",0] ); D(["ce"]);
//--> However I want to perform a similar function for a number of tables and the method above would mean more sql and maintenance. I was hoping there was a simple way to do the same thing ie find any rows with a blank field?
I have a web form with a check box list with 5 values. Each of them is an int value, and the user can select multiple values from the check box list. For example:
Status:
1) Single 2) Married 3) In Relationship 4) Divorced 5) Any
These values correspond to a column called "RelationshipStatus" which holds an int value of NULL or 0-5.
So the value passed to my SQL query would be any combination of 1, 2, 3, 4, or 5 (Which denote any of the above). It could look like this:
@Status = '132'
Now my question is how would I do a select statement that finds any row with one of those value (Any row with 1, or 3, or 2)? If there is a '5' in the varible then the select statement should return any row regardless of the value.
Table Name: "USER_TABLE" Column Name: "Relationship_Status" Value Type: INT
I have a query I am currently attempting to optimise. Â The query joins in on a sub table (queries the same table) to garner a maximum date value for each row to display. Â For example:
SELECT Column1 ,Column2 ,DateColumn FROM Table1 T INNER JOIN ( SELECT Column1 + Column2 AS ConcatColumn, Column3, MAX(DateColumn) AS dt FROM Table1 GROUP BY Column1 + Column2 ) X ON X.ConcatColumn = T.Column1 + Column2 AND X.dt = T.DateColumn
Is there any way I can write this another way (preferably more cleaner/optimised)?
There are around 6 more classes which I haven't mentioned. The resultant table that I need would be something like this.
Sections Class1 Class4 Others
-------------------------------------------------
A 30 40 50
B 40 10 25
C 20 5 15
Total 90 55 90
What I want is the top two maximum values to appear and everything else to be clubbed into Others and correspondingly their numbers to be summed up i.e. Dynamically depending on the total only those classes with maximum 'Total' value to appear.
I want to find out which records were created within a given period of time per user. For example I want to know if the user "georgev" (or any users) has created more than one record in this table within a 2 minute period.
Hopefully thsi will warm your brains this cold Monday morning :p
Code below: [Code SQL] USE TestWarehouse IF ('dbo.TestItems') IS NOT NULL DROP TABLE [dbo].[TestItems]; GO CREATE TABLE [dbo].[TestItems] ( TestItem int not null IDENTITY (1,1)
Hi all...I've got a group of tables and I wanted to first see which tablescontained a fields containing the "Date/Time" data type. And I wassuccessful in doing so...here's the query:(select a.name tablename, b.name colnamefrom sysobjects a, syscolumns bwhere a.name = object_name(b.id)AND B.TYPE = 61 AND A.XTYPE = 'U')Now...my only issue is find which query I can run (On each table)which returns to me ONLY the values containing the date/time stampwith milli seconds (Ex: 01/01/2007 10:10:50:987)I require this since we are planning on shifting those tables to theOracle platform, and we've had issues doing this while loading theextracted information, since it was not taking the values containingthe "milliseconds".Is there a way to do this? Or is it better to simply remove themilliseconds all togeather? I wouldn't mind doing so provided that itdoesn't includes changes to the actual data type.
I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
I need to find the name of the user that signed in to the web page. For this, I have a web page with a text box, the program should access a SQL database use, use the userID and return the name. My code (working) does the following:string mySqlQuery = ""; string strCnn = "";SqlDataReader myDataReader = null; mySqlQuery = "SELECT c_name FROM database WHERE c_user='demo'"; //connect to the databasestrCnn = "Data Source=" + connectionServer + "; Initial Catalog=" + connectionDatabase + "; user id=" + connectionUserID + "; password=" + connectionPassword + "; Integrated Security=false";cnn = new SqlConnection(strCnn);
try { cnn.Open();MessageBox.Show("Success Opening the Database"); } catch {MessageBox.Show("Problems with the database"); }SqlCommand sqlSelectCommand = new SqlCommand(mySqlQuery, cnn);
tbName.Text = "me"; cnn.Close();
This works, I just need to retrieve the value of c_name. Any help, thanks
I am in a situation where I need to find out unique field names with different values in a table having 200+ columns. Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values. Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values. I would appreciate If anybody gives me hint about achieving desired result
SELECT * INTO TEMP FROM (SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '01-01-2014' AS STARTDATE, '01-31-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '02-01-2014' AS STARTDATE, '02-28-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '03-01-2014' AS STARTDATE, '03-31-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A2000' AS CODE, '04-01-2014' AS STARTDATE, '04-30-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '05-01-2014' AS STARTDATE, '05-31-2014' AS ENDDATE) X
I need to extract the date that the value in CODE column changes to another code for each value of CATEGORY and if there is no change, to record the original CODE value and its startdate for each CATEGORY.
INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd) SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL
Basically I'd like to mark the first Activity for each OpportunityID as a First Visit if its ScheduledEnd falls on the same day as the FirstVisit, and otherwise mark it as a Repeat Visit.
I have this so far, but it doesn't pick up on that the ScheduledEnd needs to be on the same day as the FirstVisit date to count as a first visit:
SELECT*, CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit) WHEN ScheduledEnd THEN 1 ELSE 0 END AS isFirstVisit, CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit) WHEN ScheduledEnd THEN 0 ELSE 1 END AS isRepeatVisit FROM#Visits
I have a table which maps two related IDs. That table has 3 columns: ID, BHID & EPID. I need to find all of the BHIDs where the EPID is unique. It seems easy enough, but I keep going in circles..
USE [CGB] GO /****** Object: Table [dbo].[ePID_BHID] Script Date: 04/15/2015 15:48:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
Hi all!In a statement I want to find the IDENTITY-column value for a row thathas the smallest value. I have tried this, but for the result i alsowant to know the row_id for each. Can this be solved in a neat way,without using temporary tables?CREATE TABLE some_table(row_id INTEGERNOT NULLIDENTITY(1,1)PRIMARY KEY,row_value integer,row_name varchar(30))GO/* DROP TABLE some_table */insert into some_table (row_name, row_value) VALUES ('Alice', 0)insert into some_table (row_name, row_value) VALUES ('Alice', 1)insert into some_table (row_name, row_value) VALUES ('Alice', 2)insert into some_table (row_name, row_value) VALUES ('Alice', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 2)insert into some_table (row_name, row_value) VALUES ('Bob', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 4)insert into some_table (row_name, row_value) VALUES ('Celine', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 6)select min(row_value), row_name from some_table group by row_name
I have a CRM database that has a lot of tables and would like to be able to extract the last 'x' records in descending order from each table based on a common a field 'modifiedon' that is in every table and is auto populated by the system.
I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.
At first, I wanted to get a count of how many there are.
I need to compare records where these fields are matched:
MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile). MATCHED: (email, firstname, mobile) But not MATCH: (lastname, phone) MATCHED: (email, firstname, lastname) But not MATCH: (phone, mobile)
