I am trying to get counts of contacts.
So how do I subset, to gain a the accounts with the products then query to gain the contact counts from those accounts?
But for some reason it's giving me counts of products.
select distinct
a.region__c as [Region],
a.SFTX_ID as [SFTRX ID],
count(c.Contact_ID) as [Contact Count]
from vwContact c
[Code] .....
i got this error message in my browser
SELECT permission denied on object 'Contacts', database 'Contacts', owner
'dbo'.
this is the code where it stopped
sqlDataAdapter1.Fill(dataSet11);
//Update the data grid
DataGrid1.DataBind();
Now i finally i grant the access to the database for user ASPNET but it
seems not working yet.
I am running SQL Server
What i am missing ?
Thanks
Hi all,
I’ve a table with production objects, and another with possible items composition of the object. I need to count how many occurrences of each standard composition appears:
Table PROD:
PROD_ID COMPONENT TYPE
--------- ----------- -----
1 AAA X1 BBB Y2 AAA X3 BBB Y4 AAA Y5 AAA X5 BBB YTable ITEM_COMPITEM_ID COMPONENT TYPE-------- --------- -----7 AAA X7 BBB Y8 AAA X9 BBB Y
The result should be:
ITEM_ID OCCURRENCES-------- ----7 28 19 1
Table PROD have millions of rows, my way is too slow (I’ve a loop where each PROD object are separately queried against the ITEM_COMP), some have an idea for a most efficient way?
ThX
NeuralC
Hi everyone,
I have to extract, dayly a list of contacts on a exchange server in a table on our EDW on sql server 2005. Is it possible to get the information directly from a dataflow or i will have to developpe a script task ?
Need help desperatly !!!
How would I write a select statement that would return multiple fields in a records based on a a distinct of one of those fiels.
Example
Table Name : Sales Table
Field Name : Name Address Phone Zip Sale
Rec1: Peter Smith 12 Market St 999-999-9999 12345 99.99
Rec2: John Jones 73 Broadway 999-999-8888 12345 12.34
Rec3: Charle Brown 42 Peanuts Ave 999-999-7777 12345 34.56
Rec4: Peter Smith 12 Market St 999-999-6666 12345 67.89
Rec5: John Jone 73 Broadway 999-999-5555 12345 36.52
How would I be able to return the columns Name Address and Phone based on the distinct of Name.
I have a need to dump a subset of a database from the server (SQL Server) to a notebook via the network, for data entry to be done on the notebook when it is in the field & not connected to the network & then the changes made to this data on the notebook to be applied to the database on the server.
The application for the front end to this is in Access. Would MSDE be the way to go for the database on the notebook ?
It's a small application with not many users, likelihood of conflicting edits is small.
Would the data transfer best be done with replication or with DTS ?
Presumably replication would allow options for control over conflicts, such as the same bit of data being changed on the server & on the notebook’s copy of the data ?
I need guidance re direction to head in with this.
Thanks
Hi,I was wondering if you guys have any nth script to reads from tableand outputs into a temp table subset of records. There was a nth toolI used to use it was GROUP1 which was written in C and it used to bevery fast on nth -in a flat file. In this program we used to pass fewparamaeters. For example if I want 30,000 records from the file of500,000. The function seams to work something like this. you dividethe 30,000 records of 500,000 which will result with .090909090909.Now we would pass only the first 7 digit (0909090) as parameter thatwould nth the file down to 30,000 records. This function allwaysworked whichever number you use as long as the read file is largerthan output fileI like to use the similar concept in Sql Server and I was wondering ifanyone has any script to do this or how to go about this?Thank you. I appreciate your feedbackagron
View 1 Replies View RelatedHere is my requirement.
There is a production database which has ever increasing data. For testing purposes though, I would like to build a test database with exactly the same schema but only a subset of data copied from the production database . I'll specify the criteria (something like a where clause in select query) for copying the data from the production database.
Is there a tool that anyone has come across to do this job ?
DB design question
I have multiple tables with information about a user. The tables are Roles, Users, Groups and Profiles.
For a user session I need information from all those tables. Would it be better to make a table called UserSession and collect the necessary data from the above mentioned tables and stick them in one the UserSession table or should I just write a query that goes out and gets the data from the different tables.
What is better practice and what is faster?
hi all
Any day, another question. I love you guys.
I want to select a subset of data from a dataset returned by either another subquery or a union.
e.g. this is how i would do it in oracle, but i have no idea how this can be done in mssql or whether it's possible at all.
select * from
(
select col1, col2, col3 from table1
union
select col1, col2, col3 from table 2
)
where col1 = 'blah'
in essence oracle treats the data returned by the subquery as a table that it would select from.
how would i do the same in mssql?
thank you
James :)
I use this query:
SELECT TOP 4000 * FROM wce_contact
where website not like ''
and expressemail like '%@%'
and idstatus like ''
and salutation not like '%there%'
and task not like '%x%'
I know that I can update a field like this...
update wce_contact
set task = ''
where task in ('x')
But how would I update a field that matches the above query? If you read my other post, this would eliminate the need for me to select a range of contacts.
Instead of selecting records like this:
Select website from wce_contact where uniqueid like
'O,U4Ba-l2Nmc' or uniqueid like
'O90kja8,aofc' or uniqueid like
'OIiZnaAHj1fc' or uniqueid like
'oho9mc2HA4fc' or uniqueid like
'ogJrMa.YV9mc' or uniqueid like
'PNocYaQtA6fc' or uniqueid like
'NLWTfcUSbXec' or uniqueid like
'O_eHjaNhPtfc' or uniqueid like
'OK(Qkau,H(fc' or uniqueid like
'nYcUfcCQbXec' or uniqueid like
'O-R5oa2C-2fc' or uniqueid like
'O-R5oa2C-2fc' or uniqueid like
'nPYhfat5eJec'
Is there anyway i can select these contacts without having to copy and paste 'or uniqueid like' numerous times?
Thanks!
Hello!
I am currently workin on clearin a litte in our data collation, Can anyone tell me how i will get this to work..
I have this selected so far [URL=http://img247.imageshack.us/my.php?image=sqlresultatct2.png][/URL]
But i only want those selected who have the highest value in the last 3 columns in relation with their counterpart.... Anyway of doin that??
this is what i have to get the result-set
SELECT contact.contact_id,
contact_p.p_name,
contact_adr.adr_zip_code,
contact_p.p_first_name,
contact_p.p_last_name,
form_land_i_os.cdp_1y81__,
form_land_i_os.cdp_totalha,
form_land_i_os.cdp_ha_krak
FROM contact,
contact dub_contact,
contact_p,
contact_p dub_contact_p,
contact_adr,
contact_adr dub_contact_adr,
form_land_i_os,
form_land_i_os dub_form_land_i_os,
po_userdw
WHERE (contact.deleted IS NULL AND
dub_contact.deleted IS NULL AND
dub_contact_adr.adr_zip_code IS NOT NULL AND
contact.contact_status = 3 AND
dub_contact.contact_status = 3 AND
contact.p_id = contact_p.p_id AND
dub_contact.p_id = dub_contact_p.p_id AND
contact.adr_id = contact_adr.adr_id AND
dub_contact.adr_id = dub_contact_adr.adr_id AND
form_land_i_os.id = po_userdw.id AND
dub_form_land_i_os.id = po_userdw.id AND po_userdw.relation_id = contact.contact_id AND
contact_adr.adr_zip_code = dub_contact_adr.adr_zip_code AND
contact_p.p_first_name = dub_contact_p.p_first_name AND
contact_p.p_last_name = dub_contact_p.p_last_name AND
contact.contact_id <> dub_contact.contact_id AND
contact.adr_id <> dub_contact.adr_id AND
contact_p.p_name <> 'X')
ORDER BY contact_p.p_name
Thank you in advance wise gentlemen
Dont cross the road, if you cant get out of the kitchen
I'm new to SQL with 2 weeks under my belt....lol, so this may be a simple edit:
When I run the following query, I can get a list of all dups in the contact field:
++++++++++++++++++++++++++++++++++
SELECT full_name,
COUNT(full_name) AS NumOccurrences
FROM contact
GROUP BY full_name
HAVING ( COUNT(full_name) > 1 )
++++++++++++++++++++++++++++++++++
However:
I need to make sure I am de-activating (active = 0) only the contacts where they are listed more then once within the same company table (company.company_id) and the condition is that phone is NULL. I can't seem to make it work. Does anyone have any suggestions for an UPDATE I can use?
Guys
Does anyone know of a 3rd party module/plug-in for VS or even some
downloadable code that enables the import of Outlook/ACT and similar contact
information through an ASP.NET page into a SQL Server table with ongoing
1-click button sync.
I need to replicate something similar to the Intellisync tool used in Yahoo
Mail.
regards
Andy
I'm in the process of trying to identify duplicate contacts. I doing this for millions of contacts and have gotten stuck and could use some elegant solutions!
The business rule is this:
Any contact that has the same name, phone and email address are the same contact
Any contact that has the same name, and email address are the same contact
Any contact that has the same name, email address, but different phone are a different contact.
Any contact that has the same name, email address, and a blank phone can be the same contact as one that has the same name, email address, and has an email address
Rank by the DataSource_fk. 1 being the highest
Put another way:
If 3 contacts have the same name, 2 have phone '1112223344' and all three have the email address 'johndoe@gmail.com' they are the same contact and the lowest DataSource_fk should be ranked the highest.
I've used the Row_number over (Partition by) in the past, but am unsure how to deal with the blanks in email and phone.
DROP TABLE [dbo].[TestBusinessContact];
GO
CREATE TABLE [dbo].[TestBusinessContact]
(
[TestBusinessContact_pk] INT IDENTITY(1,1)NOT NULL,
[Business_fk]INT NOT NULL CONSTRAINT DF_TestBusinessContact_Business_fk DEFAULT(0),
[Code] ......
There is a requirement with our customer about grouping contact details based on certain fields from in the contact table.
Â
I have built a SSIS package and using Script Manager and Fuzzy Grouping Component. and its working perfectly as per the requirement.
Â
But unfortunately Client is using SQL Server 2012 Standard Edition.
Â
and in SQL Server 2012 Standard Edition , Fuzzy Transactions components are not supported.
Below is my sample data. I can't figure out how to select Unique phonenumber contacts for the same Ranked values from the set.
Basically the table is a mix of contactIDs. Some of them have duplicate phone numbers and through a separate mechanism we have ranked them.
It's easier then to pull out max(ranked) CLI_Numbers and their counterpart contactID(s). But I am also getting 2 or more records where the rank happens to be the same. I don't want that. Any one of the contactID will do for me.
The table has also same cliNumbers with different rank values, which are then correctly being picked up in the query below.
Note: ContactId is a unique value for each person in the table.
RecordID is simply RowID.
( I have attempted to populate a sample data suited for this forum - not sure how it comes out on the browser)
if object_id('tempdb..#MyData') is not null
drop table #MyData
create table #MyData
(
RecordID int,
contactID int,
forename varchar(25),
surname varchar(25),
[Code] ....
This is my query attempt
With RankedmobileDuplicateSet
as(
select cliNumber, max(ranked_value) as ranked_max_value
from #temp_UK_mobiledata
group by cliNumber)
[Code] .....
I get the error "The column prefix 'contacts' does not match with a table name or alias used in the query".I am trying to obtain all fields from the communications table whether it is used or NULL.
SELECT organisations.organisation_number, organisations.contact_number, organisations.name, organisations.address_number,
organisations.std_code, organisations.telephone, organisations.status, contacts.title, contacts.initials, contacts.forenames,
contacts.surname, contacts.contact_number, contacts.label_name, contact_roles.role, addresses.address, addresses.town, addresses.county,
addresses.postcode, addresses.country, communications.device, communications.notes
[code]....
I am working on a package to insert and update contacts from a database into an application. To insert into application I am using script component.
So my question is can I do both insert and update script seperately in two different script components of same package.
My package looks something like this.
Can we push new inserts into one script component and updates to other script component?
Does both the script components execute at the same time?Will there be any conflicts between insert and update in the application?
I have an employee table (empl), and a labor table (lab). The labor table is populated whenever an employee clocks in/out. If an employee does not clock in, they do not appear in the labor table.
I need to capture employees whose time is less than 8 hours, or whoever did not log in at all that date. If I run the following query without a WHERE clause, I get all of my employees, with NULL data in the labor table if they did not log in that day. This is good.
However, if I add
WHERE (DATEDIFF(s, tt.StartTime, tt.EndTime) < 28800) OR (tt.StartTime = NULL)
I get all employees whose time is less than 8 hours, but do not get any of the employees who did not work at all that date, and that's expected... there should NEVER be a NULL in the labor table - it is populated whenever somebody clocks in/out.
So, how would I query the resulting table to get those that worked less than 8 hours, and those that did not?
select
et.EmpNo
et.FirstName,
et.LastName,
tt.StartTime,
tt.EndTime,
DATEDIFF(s, tt.StartTime, tt.EndTime) as Seconds
FROM (select
empl.EmpNum as EmpNo,
empl.FFName as FirstName,
empl.FLName as LastName
from
empl where empl.ftermdate IN ('1900-01-01 00:00:00.000') AS et
LEFT JOIN (select
MIN(lab.StartDt) as StartTime,
MAX(lab.EndDt) as EndTime,
lab.WorkDt as WorkDate,
lab.EmpNum as EmpNo
from
lab where lab.WorkDt in ('2008-03-03 00:00:00.000')
group by lab.WorkDT, lab.EmpNum) AS TT
ON et.EmpNo = tt.EmpNo
hi i have a table as follows:
col1 col2 col3
2 BB 2
1 AB 3
3 CA 3
1 AC 1
2 BA 4
3 CB 2
2 BC 6
1 AA 2
3 CC 5
i want to output the whole row of each unique col1 where its col3 is max, so that i will get the ff result set
1 AB 3
2 BC 6
3 CC 5
i have an idea which is to use a cursor but i don't know the best/fastest way of doing it.
thanks heaps in advance!
I am trying to exclude patients from a dataset. There are multiple records per patid in this dataset. I have the following code:
SELECT meds.PATID, meds.MEDICATION, meds.MEDTYPE
FROM meds INNER JOIN patient ON meds.PATID = patient.PATID
WHERE (((meds.MEDTYPE) Not In ("FI (Fusion Inhibitor)","NNUC (","Non-nucleoside","NRTI & NNUC","NRTI (Nucleoside/tide Rev","PI (Protease Inhibitor)")));
I want to exclude all patient records if the patient had any of the above exclusions ever. If they have the exclusion if one record get rid of the rest of the records for that patient. Right now the code only excludes the particular record.
My table contains customer records with multiple records per customer. As a result of a query, I´m only interested in one record per customer with the highest value of a certain field in the record.
I thought of using DISTINCT, but can I use DISTINCT on a subset of all fields? Or sort the table in a certain way that the query result only shows the first unique records for a customer.
Other ideas are welcome to.
I am trying to update a small subset of records of a given table (TRValue) using the records contained in ParcelTemp. The difficult part is getting the summation from a child file, TRGreen, for those same parcels contained in ParcelTemp. Instead of updating just a few records, all the records in TRValue are being updated, with the wrong values of course!
Basically, Update records in TRValue that are equal to:
Year = P.Year
Code = 'LG01'
Parcel = P.Parcel
with the summation of child records where the child records needed are:
Year = P.Year
Parcel = P.Parcel
Code:
UPDATE TRValue SET
Acres = SumAcres,
CurrentMarket = SumMarket,
CurrentTaxable = SumTaxable,
CurrentTaxAmt = ((SumTaxable * D.CertifiedRate) + 0.50)
FROM ParcelTemp P
[code]....
The problem is to find a subset of rows such that each value in each of two columns (animals and food brands in this example) appears in at least one row. The purpose is to produce a set of samples from a large table. The table has a animal_name column and an food_brand column; I want a set of samples that contains at least one of each animal_name and at least one of each food_brand, but no more than necessary.
CREATE TABLE Feeding_Options
(license_nbr INTEGER NOT NULL PRIMARY KEY,
animal_name VARCHAR (10) NOT NULL,
food_brand VARCHAR(15) NOT NULL);
INSERT INTO Feeding_Options
VALUES
(6401715, 'rat', 'IAMS'),
[code]....
To frame the problem better, her are the values in each column:
animals = {'rat', 'dog', 'cat', 'fish', 'fox'}
food = {'IAMS', 'Sci Diet', 'Purina', 'Alpo'}
In this data, (6401715, 'rat', 'IAMS') and (1058337, 'rat', 'IAMS') are interchangeable, as are some of the other rows. There can be more than one minimal solution whcihmight be the whole set.
I'm beating my head up against a wall here. I have one very LARGE table called op_hist.
CREATE TABLE [dbo].[op_hist] (
[op_trnbr] [int] NOT NULL ,
[op_type] [varchar] (80) ,
[op_date] [smalldatetime] NULL ,
[op_act_setup] [decimal](38, 10) NULL ,
[op_act_run] [decimal](38, 10) NULL ,
[op_qty_comp] [decimal](38, 10) NULL ,
[op_wkctr] [varchar] (80) ,
[op_part] [varchar] (30)
)
I need to be able to sum items grouped by op_wkctr and op_part and op_date. Here's my latest version which of course does not work.
SELECT op_wkctr, pcs, hrs.b
FROM
dbo.op_hist
INNER JOIN
(
SELECT op_part, SUM(op_qty_comp) as pcs
FROM dbo.op_hist
where op_type='BACKFLSH'
group by op_hist.op_part
UNION ALL
SELECT op_part, SUM(op_act_setup + op_act_run) as b
FROM dbo.op_hist
where op_type='LABOR'
group by op_hist.op_part
) AS hrs
ON op_hist.op_part = hrs.op_part
where (op_wkctr = 'P-P36' or op_wkctr = 'P-P38' or op_wkctr='P39') and op_date ='10/22/07'
Help me oby one - you're my only hope.
Sincerely,
Frustrated!
I could do this even with my limited knowledge of SQL but I'm surethere's a slick way that might be dead easy. My way hardly seems worththe effort . If not thanks anywayCol1Col2Col3QWQWQWQAQBQBQXWQXWQXWQXAQXBQXBEvery time there is a change of group (Col1 and Col2), I want to startand increment Col3 so I end up with;Col1Col2Col3QW001QW002QW003QA001QB001QB002QXW001QXW002QXW003QXA001QXB001QXB002
View 1 Replies View RelatedCan I install SQL Server on a machine and use less than the # of processor on the machine. In a UNIX world, I'd call it LPARing with Oracle and AIX, and they only let me do this with Enterprise Edition. With Windows, I think the only way is using virtual machines and attaching processors to them? Do any vendors offering LPARing? Can I take any edition of SQL Server and subcapacity price so that I only pay for the processors I'm using?
What about SS Express? It only scheds to a single core - so could I put that on a larger machine?
Thanks!
This question has been posted on the site before but I could not find any resolution....I want to return rows 11 - 20 from a query that returns 100 records without using a cursor or temp table.
The closest query I have found is a query that numbers the rows, but I can't seem to use rownumber in a between clause...
Use Pubs
SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY emp_id
Thanks,
Jim
how do i calculate all the checkdigits for a subset of ids and return all the ids and checkdigits? basic calculation isn't the problem, just how to select the ids and utilize the ids in the calc. I am new to trying to do calculations on data.
View 7 Replies View RelatedHi
I'm sure this is an easy problem but my brain is fried today...however how do I do the following:
I have a two column table. One is a key field where duplicates can arise and the other is a datetime field. So you might have some records looking like this:
1231999-06-14 12:17:11.000
1231999-06-14 12:17:31.310
1231999-06-14 12:17:31.000
1231999-06-14 12:22:56.000
1231999-06-14 12:22:58.000
8901999-06-15 10:00:18.000
8901999-06-15 10:03:30.340
8901999-06-15 10:03:30.000
8901999-06-15 10:03:40.000
OK, how do I get the top 1 of each key so that I get a subset of records looking like the following:
1231999-06-14 12:17:11.000
8901999-06-15 10:00:18.000
Thanks in advance
Bazza
I want to break up a set of search results into small chunks. For instance, think about how Google displays a block of ten results out of the entire set. Selecting the top 10 is dead easy with "TOP 10"; how do I select the next 10?
Obviously one option would be to select the top 20, and programmatically discard the first 10, but surely there is a better way? I am doing this for a ASP.NET 2.0 application, and if I can retrieve just what I want, I can DataBind to a Repeater, and let ASP.NET do all the hard work of displaying the data.