Finding And Grouping Records Off Of A Given Field Value
Jun 16, 2007
In the "tblEmailGroupLink" table...
I need to find all records with the "UnSubscribed" field having a "True" value. All these records will have a corresponding "Emailid" field.
In the "tblEmailAddress" table...
The same "Emailid" field has a corresponding "EmailAddress" field.
What needed is all the email addresses found in the "EmailAdddress" field of these records.
I'm very new at this so I hope I explained this right. I'd really appreciate any help I can get.
Thanks,
Bill
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
All,Given multiple records with identical values in all fields except asingle varchar field, is there an efficient query that will group therecords into a single record and concatenate the aforementionedvarchar field into a single field with each of the source records'values separated by commas?Example:Record 1 'Doug' , '1'Record 2 'Doug' , '2'Output record 'Doug' , '1,2'Thanks in advance,Doug
View 2 Replies
View Related
Apr 2, 2008
Hi all experters,
Please suggest how to build the report in below case:
Raw data:
ID
Member
Functions
1
Alan
A
1
Alan
B
2
Tom
A
2
Tom
B
2
Tom
C
3
Mary
D
3
Mary
E
Report Shows:
ID
Member
Functions
1
Alan
A,B
2
Tom
A,B,C
3
Mary
D,E
I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.
Any good suggestion?
Thanks in advance,
Steve Wang
2008/4/2
View 6 Replies
View Related
Mar 7, 2001
Hi,
OK, here's a question for all you SQL Gurus.
I want to find all the records that are in t2 but are not in t1.
(NOTE: table print outs at bottom of post)
I could write this:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~``
select t2.t from t2 where t not in (select t from t1)
t
-----------
6
(1 row(s) affected)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~``
BUT it takes to long.
So I thought of this:
select t2.t , t1.t from t2 left join t1 on t2.t = t1.t where t1.t = null
BUT it doesn't work.
WHY NOT?
Is there anything similar that i can write???
It should work, heres the output for the simple left join:
select t2.t , t1.t from t2 left join t1 on t2.t = t1.t
t t
----------- -----------
1 1
2 2
3 3
4 4
5 5
6 NULL
7 7
(7 row(s) affected)
Thanks,
Benny Hill
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~``
select * from t1
t
-----------
1
2
3
4
5
7
(6 row(s) affected)
select * from t2
t
-----------
1
2
3
4
5
6
7
(7 row(s) affected)
View 4 Replies
View Related
Mar 10, 2004
I am trying to find records that are orphaned in a data base after thier parent record (in another table) has been deleted.
Something happened to the constraints and I had no idea until now this had happened.
I have the faint rustle of a query in query sort of thing but my knowledge needs expansion.
PS: sorry for the cross post - just noticed that I was in the wrong forum before.
Thanks,
Matt
View 11 Replies
View Related
Oct 29, 2007
Hello,
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!
Thanks,
D.
View 1 Replies
View Related
Jul 20, 2005
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!
View 1 Replies
View Related
May 14, 2008
Hi Guys,
I want to group the records on the time difference
declare @tbl as table(id int,intid int,val int,dt datetime)
insert into @tbl
select 1,1,10,'03/31/2006 15:05:22' union all
select 2,1,12,'03/31/2006 15:10:22' union all
select 3,1,15,'03/31/2006 15:15:22' union all
select 4,1,12,'03/31/2006 15:25:22' union all
select 5,1,8,'03/31/2006 15:30:22' union all
select 6,1,6,'03/31/2006 15:35:22' union all
select 7,1,4,'03/31/2006 15:40:22' union all
select 8,1,3,'03/31/2006 15:45:22' union all
select 9,1,10,'03/31/2006 15:50:22'
declare @tbl1 as table(intid int,Tm int,val int)
insert into @tbl1
select 1,5,10
I want a output such that when the val in @tbl goes below the val in @tbl1 for the Tm mentioned in @tbl1
then the time difference should be shown.For example record 1 it starts with 10 the records remain more than 10 till
record number 5.From 5 the records remains lower than 10 till record number 9.So I need to show the the time
difference from record number 5 till 9.
But there is a catch.In @tbl1 there is column named Tm.The time difference sould be calculated only if the diff
more than Tm value in @tbl1.
For example if the value of Tm is changed to say 25 then the there is no need to show the time difference since the
time difference value from record 5 to record 9 is less than 25.
Hope I am clear.
Please help me out.
Regards
View 3 Replies
View Related
May 12, 2008
I have a report that requires SubTotals for each page.
To do this successfully, I had to introduce the following grouping:
Code Snippet=Int((RowNumber(Nothing)-1)/5
This groups every 5 records.
Unfortunately, the length of data in my records is not uniform. For example, certain records have long(over 4 lines) comment field while others are short(1 line).
This means that even though I have an artificial grouping, the group may still be split up over two or more pages.
Is there a way to Group records PER PAGE dynamically?
View 5 Replies
View Related
Jan 17, 2002
I'm adding up quantities of an item that are entered in one after another, so the date is the same, but the time differs.
I used the "first" function in access which works the way I want, but sql7 doesn't use the same function. The "convert" function does not work in access but Here is the gist of the query:
SELECT first(CONVERT(varchar,transactions.tran_date,101)) AS [trandate], transactions.tran_type,
SUM(transactions.qty) AS totqty,...etc.
thanx,
Herb
View 1 Replies
View Related
Jul 20, 2005
I would like to retrieve 10(dynamic) records of table x (proucts) foreach user in table y (users). Can this be done?I would like the end result to be something like this: (would this bea union?)__________________________y.name | x.pid | x.pnameBob | 1 | forkBob | 2 | spoon.... | |Bob | 10 | potatoJeff | 11 | penetc.....__________________________But also with the number to return based off of a query, ex-select @pcount = count(products)select @ucount = count(users)select @pcount / @ucount10And lump all this in an Stored procedureex-get number of total records in x, divide by total y = zselect z records for each user in y.You would be a master in my book if you can give me hints on this one!Thanks,Jeff
View 1 Replies
View Related
Jun 15, 2007
Hello,
I am using the following expression to strip the last 11 characters in a field group and it returns an error saying that Len cannot use a negative number. It must be 0 or greater. Is there a better alternative?
Code Snippet
left(Fields!TestName.Value,len(Fields!TestName.Value)-11))
View 2 Replies
View Related
Feb 18, 2008
I have the following generic table structure.
CREATE TABLE tableName (
...
, datetime_created datetime DEFAULT GetDate()
, created_by_user char(12)
)
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
Any more information needed, let me know!
View 8 Replies
View Related
Jun 19, 2012
I've got a subquery that keeps throwing up an error but I can’t think of another way of completing the query.
Firstly I need to find records where TestQty =3
(this would find 2 records with TestQty =3, TestNumber, 7171003 and 7088650)
The TestNumber is not unique so I would like the final set of records to include all the records with TestNumber, 7171003 and 7088650
TestNumberTestQty
7088650____________3
7088650____________1
7088650____________2
7088650____________1
7088650____________2
7171003____________1
7171003____________3
7171003____________2
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)
[Code] .....
View 5 Replies
View Related
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?
View 4 Replies
View Related
Jul 27, 2015
I have results from a query that have anywhere from 1-4 results. I have a subid of 1-4 which is grouped by a certain criteria. so for example...
id subid text
1 processing A records
2 1000 records processing
3 importing A records
4 1000 records processed
1 processing B records
2 500 records processing
3 importing B records
4 1000 records processed
Here is what my desired output is giving each group of subids 1-4 an id to be grouped together.
id subid text
1 1 processing A records
1 2 1000 records processing
1 3 importing A records
1 4 1000 records processed
2 1 processing B records
2 2 500 records processing
2 3 importing B records
2 4 1000 records processed
View 1 Replies
View Related
Jul 20, 2005
I need to group records and assign a setid to the group. I have atable with data that looks like thisColA ColB94015 0106594016 0106594015 0108594015 0108633383 0091232601 00912I need to create a resultset using just sql to look like thisColA ColB GRP94015 01065 194016 01065 194015 01085 194015 01086 133383 00912 232601 00912 2The tricky part is resolving the many to many issue. A value in ColAcan belong to multiple values in ColB and a value in ColB can havemultiple values in ColA.
View 6 Replies
View Related
May 18, 2006
I have tried to process > 3 million Fuzzy grouping records on two different servers with no success. 3 mill works but anything above 4 mill doesn't. Some background:
We are trying to de-dup our customer table on: name (.5 min), address1 (.5 min), city (.5 min), state (exact). .8 overall record min score.
Output includes additional fields: customerid, sourceid, address2, country, phonenumber
Without SP1 installed I couldn't even get a few hundred thousand records to process
Two different servers - same problems. Note that SSIS and SQL Server are running locally on both
The higher end server has 4GB RAM, the other 2.5 GB RAM. Plenty of free disk space on both
SQL Server is configured to use 2 GB of RAM max
The page file is currently at 15GB
After running a number of test on both servers trying different batch sizes etc. the one thing I noticed is that it seems to always error out when SSIS takes over and starts chewing up all the available RAM. This happens after the index is created and SSIS starts "warming caches". On both servers SQL Server uses up about 1.6GB of RAM at this point while SSIS keeps taking over RAM until all physical RAM is used up.
Some questions:
Has anyone been able to process more then 3 million records and if so what is your hardware configuration?
Should we try running SSIS from a different server so it has access to the full amount of physical RAM? (so it doesn't have to fight for RAM with SQL Server)
Should we install Win 2003 Enterprise Server so we can add more RAM?
Any ideas why switching to the page file might be causing errors?
Thanks!!
Keith Doyle
View 17 Replies
View Related
Jan 19, 2006
How do I find the current size of each field in a table's column?
I have a table with a field for notes/memos. I need to see which ones are about to reach the size limit and what the current size is.
Does this make sense?
Thank you,
Karen
View 2 Replies
View Related
Apr 26, 2006
Hi,
is there any "robust" way to find out the name of a field in the pipeline by it's Lineage ID programatically? There is a sample code out there (on one of the blogs) but it seams not to be reliable...
The usecase is easy... What is the field name in an error output of that column that causes the error? We don't want to have hardcoded LineageIDs in the error handling so I think it's the best idea to go with field names... However we only get that LineageID...
Thanks,
View 1 Replies
View Related
Mar 26, 2008
I want to group by a fields value in a table. I have a purchase table where contracts and orders are stored in together. Now what I need is, I have to group only by the rows that contain an identical contract_no and order_no as those records represent the actual contract. the other rows contain order data-sets which need to be subtracted from the contracts values.
datasource:
contract_no order_no name amount
12 12 contract1 1,000,000
12 215 order215 50,000
12 460 order460 75,000
280 280 contract2 500,000
280 340 order340 10,000
280 410 order410 9,000
I want to display each order that took place via drilldown/hide function in the table, and calculate the amount from the contract that is left.
desired result report:
12 contract1 1,000,000
order 215 50,000
order 460 75,000
amount left 875,000
View 1 Replies
View Related
Jun 15, 2015
I am creating a report that shows orders created by our employees but I am having trouble with one employee. She changed her last name this year and so in the report, it shows a line her with her maiden name and one with her new name.
Is there a way to combine all the data from both names into one name so it appears on one line and looks like a single person instead of two.
View 4 Replies
View Related
Jan 9, 2008
Is there a way in Reporting Services layout mode to get the report to display more than one response for one field in the same cell?
More specifically, I have a list of classes that lists section, title, and instructors. If the class has more than one instructor, I want the instructor cell to list both instructors in one cell, separated by a semi-colon, rather than listing all the class information - along with instructor - in two separate rows. Any suggestions?
something like this:
Section
Title
Instructors
15
Class 1
Jones
16
Class 2
Jones; Smith
View 5 Replies
View Related
Jul 9, 2007
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
View 2 Replies
View Related
Jul 30, 2007
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..
View 4 Replies
View Related
Nov 18, 2015
I tried to ask a similar question yesterday and got shot down, so I'll try again in a different way. I have been looking online at the gaps and islands approach, and it seems to always be referencing a singular field, so i can't find anything which is clear to get my head around it.In the context of a hotel (people checking in and out) I would like to identify how long someone has been staying at the hotel (The Island?) regardless if they checked out and back in the following day.
Data example:
DECLARE @LengthOfStay TABLE
(
PersonVARCHAR(8) NOT NULL,
CheckInDATE NOT NULL,
CheckOutDATE NULL
[code]...
View 7 Replies
View Related
Jun 8, 2005
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
View 2 Replies
View Related
Aug 9, 2007
I have a table with several million rows of data. There is a date field defined as a char(8) with some bad rows. i tried to locate them with below
select date_stopped from patient_medication
where isdate(convert(datetime,date_stopped)) = 1
This won't work, I get the
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
Any way around this?
View 2 Replies
View Related
Jul 27, 2015
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.
View 4 Replies
View Related
Nov 20, 2015
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)
View 9 Replies
View Related
Nov 6, 2015
I have 2 tables A, B with 1 to many relationship
Table A(ProductID), TableB(ProductID, FileID)
I need to find only the records in Table A that may have more than one FileIDs in Table B, since some ProductIDS have multiple FileIDs in Table B...
View 8 Replies
View Related
Feb 18, 2014
I am trying to build a select statement that will
1). find records where a duplicate field exists.
2.) show the ItemNo of records where the Historical data is ' '.
I have the query which shows me the duplicates but I'm stuck when trying to only show records where the Historical field = ' '.
Originally I thought I could accomplish this goal with a sub query based off of the original duplicate result set but SQL server returns an error.
Below you will find a temp table to create and try in your environment.
create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int
[Code] ....
Ultimately I need a result set that returns 'ATMR10 MFZ N', and 'QBGFEP2050 CH DIS'.
View 3 Replies
View Related
Nov 5, 2014
The database consists of the following tables:
create table dbo.customer (
customer_id int identity primary key clustered,
customer_name nvarchar(256) not null
)
create table dbo.purchase_order (
purchase_order_id int identity primary key clustered
customer_id int not null,
amount money not null,
order_date date not null
)
Implement a query for the report that will provide the following information: for each customer output at most 5 different dates which contain abnormally high or low amounts (bigger or less than 3 times SDTDEV from AVG), for each of these dates output minimum and maximum amounts as well.
Possible result: [URL] ......
View 7 Replies
View Related
