Picking A Representative Subset?

Feb 5, 2013

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.

View 2 Replies


ADVERTISEMENT

Picking Out Duplicate Records

Mar 19, 2008

Hi, I have a student results table with the layout shown below (four records with the fields separated by dashes). Sorry its so messy. Anyway, you can see that there are duplicates. I want to write an SQL statement that will pick out only the 'supplemental' records if duplicates occur. Any ideas on how to do this?

ID - StudentNo - Subject - Term - Yearofstudy - YearTaken - Grade
1195- 11111111- MA1E2- Annual - 1- 2006- 34
1205- 11111111- MA1E2- Supplemental- 1- 2006- 40 (S)
1194- 11111111- MA1E1- Annual -1- 2006- 35
1204- 11111111- MA1E1- Supplemental- 1- 2006- 40 (S)

Here is the SQL I'm using to get all the records from the studentresults table, for first years only:

SELECT *
FROM studentresults
WHERE studentresults.StudentNo = 11111111 AND studentresults.YearOfStudy = 1

How do I change this to pick out only the supplemental exam results?

Regards,

sabatier

View 1 Replies View Related

Picking Records Having Both Of Two Criteria

May 14, 2008

Hi,

Slightly knotty and hard-to-articulate problem here, so please bear with me.

As part of a series of transactions, I have a table which contains data which looks like this:

AdBookingID adWeeks AdID clientId valueid
----------- ----------- ----------- ----------- -----------
97336 3 95127 248 1007
98220 1 94304 458 1007
98220 1 94304 458 7610
98386 1 88123 319 7604
98388 1 91484 319 7610
98390 1 91963 319 7610
98392 1 92468 319 7601
98392 1 92468 319 7608


The key to this problem is the "valueID". The first digit of the value is significant - the "1" and the "7" in the above mean the values are parts of different groups of search criteria.

What I need to do is to select from this list into a temp table all those bookings (identified by AdBookingID) which have valueIds of both types - i.e. an row in the table where the valueId starts with a 1 and a row in the table where the valueId starts with a 7 - in the above data sample the only AdBooking which qualifies is 98220.

I worked out a method of doing this going by the number of entries each item had in the table:

select count(distinct valueId)
as valueId, adWeeks, clientId, adID, AdBookingID
into #worktable
from #tmp
group by adWeeks, clientId, adID, AdBookingID
having count(distinct valueid) > 1

Which is largely accurate. But - and here's the killer - although each adBooking can only have one valueId starting with 1, it can have multiple entries starting with 7. These bookings are rare, but they do exist and are causing anomalies in the data returned which - if you recall - must only contain booking records for which there are valueId entries with both a 1 and a 7.

Can anyone suggest a way I can get just the data I need?
Cheers,
Matt

View 7 Replies View Related

Picking The Specific Data

May 16, 2007

I have in my table something like this
Col1 Col2
6 O
6 O
6 C
6 C
6 C
5 O
5 O

i want the value as 6 iff all the corresponding
records in col2 are C
similarly, since for 5 there are no C it should
not pick record 5

Please suggest me a query for this

View 10 Replies View Related

SQL Server Not Picking Up New NTLogin Name

Oct 11, 2007

Can anyone suggest an alternative to the following problem?

We have had 2 users change their NTLogin in AD. For example, from jsmith to jbloggs. NB this is a change, NOT a completely new login. When the users connect to sql server and call the function SYSTEM_USER, the login returned is the old login:

TheDomainjsmith

This is causing an audit trail problem. Exactly the same issue exists with sys.sysprocesses.

The only way I have found of correcting the problem is by restarting the SQL server service which is obviously not ideal for such a trivial issue.

You can recreate this problem via local users on a sql box as well:

1. Create a local user on the SQL box called FirstUserName and grant access to SQL
2. do a runas on management studio and runas the FirstUserName user
3. connect to SQL and run SELECT SYSTEM_USER, you will get [nameofsqlbox]FirstUserName which is correct
4. kill management studio
5. rename the user SecondUserName
6. do a runas on management studio and runas the SecondUserName user (note, same user, now has different login name)
7. connect to SQL and run SELECT SYSTEM_USER, you will get [nameofsqlbox]FirstUserName which is now incorrect
8. restart sql server service
9. connect to SQL and run SELECT SYSTEM_USER, you will get [nameofsqlbox]SecondUserName which is now correct

Any solutions other than just sucking up a restart everytime a user changes login name would be much appreciated!

View 15 Replies View Related

Problem With Picking The Right Algorithm

Feb 8, 2007

Hi

I'm using SQL Server 2005. The problem I have is as follows. I have several production lines and as with everything parts in the line tend to break. I have data from all the breaks that occurred in the last 2 years. What I want to do is predict the next break and the production line it's going to happen on. I would also like to go to a future date and check what possible breaks might occur on that date. I've run quite a few models but none of them helps me with future events. I think I might be using the wrong algorithm or I€™m just not doing it right. If somebody can please suggest an algorithm and maybe help me with a web site that has a tutorial similar to my problem

Thanks
Elmo

View 7 Replies View Related

Picking Up Data Meant For Many People

Nov 29, 2007

Hi,I have a gridview on a page, which should select data from a database. I want it to select the data row if it finds its UserName in the UserName column, but there will be many usernames in the username column, seperated by commas.Here is the select statement that I have at the moment, which doesnt return any data atall:SELECT [message], [senddate], [subject], [messageid], [sendername], [recievername] FROM [Messages] WHERE ([recievername] LIKE '% ' + @recievername + ' %')@recievername = Profile("UserName") which is the vwd wizards way of saying the current user. A Username column has data like this:bezzer , bezlan , beezer , beezler with a space between commas and a space at the start and the end.Thanks if someone can help!Jon  

View 2 Replies View Related

WHERE Clause Not Picking Up 2nd Field Criteria

Jun 20, 2001

Hello,

I have this SP that works, except I need to add another field value for the WHERE clause. As you can see I have "WM" but I need to add "PR", and those two are definitely in the table field. I've tried a variety of syntax arrangements using the AND operator, the OR operator, the & operator, just a comma between the two, nothing between the two. Can someone please show me what I'm doing wrong. It fileters for "WM" fine, but I also need it to filter in the WHERE clause for "PR". Here is the SP:

CREATE procedure spDemoSchedule (@beginDate varchar(10), @endDate varchar(10), @storeNum int)
AS

SELECT Progstats.[Program#], Progstats.KCKOFF, Progstats.ProgramName, Progstats.Parent, Store.[Str#], Store.Status, Progstats.Dept, Store.[Program#], Product.[Item#], Product.[Item]
FROM Progstats INNER JOIN Product ON Progstats.[Program#] = Product.[Program#] INNER JOIN Store ON Progstats.[Program#] = Store.[Program#]
WHERE Progstats.KCKOFF BETWEEN @beginDate AND @endDate AND Store.[Str#]=@storeNum AND Progstats.CLASS="WM"
GO

TIA,
Bruce Wexler

View 2 Replies View Related

Transact SQL :: Picking Up Average Last Two Payments

May 18, 2015

Got a table named Payments in the following form:

Report_DateCustomer_IDCustomer_NamePayment_DatePayment_Amount

The Customers Table have the fields:

Customer_IDCustomer_Name

Since the Customers Table contains the complete list of customers I seek to have a Join made between it and the Paymentstable to check the amount made by the customers.

However, the trouble arises when I seek to check the average amounts made by each customer during last two months in which such a customer had paid.

In further clarification, lets consider current month of May 2015 going on;

Customer_A had paid $10,000 in AprilCustomer_A had paid $5,000 in AprilCustomer_B had paid $10,000 in AprilCustomer_A had paid $18,000 in MarchCustomer_A had paid $2,000 in MarchCustomer_A had paid $10,000 in FebruaryCustomer_B had paid $8,000 in FebruaryCustomer_B had paid $4,000 in FebruaryCustomer_B had paid $3,000 in February...

View 4 Replies View Related

2 Structural Questions For Picking Field Type

Jul 25, 2006

Hi all,

Hi am creating a new database not sure which way is the best way to go. Here are the questions

I have a history table which has its an identity field and an identification field which shows what type of history field it is. As in payment, printout, change and so on.... For this field should i use
Full text which has the payment, printout, change and so on and make it easy on the eye and for queries and coded to use the same text all the time
have varchar(2) type of field which holds an understandable field PR, PO, CH and a reference table for it as in PR= PAYMENT, PO = Printout, CH as in change and so on
or have a integer field as in 1, 2, 3 and ref. table 1=payment, 2 = printout, 3=change and so on
I have a address table which holds multiple types of addresses which are really limited count maybe upto 4 different types
have a integer field which links to a reference table as above
have bit fields which can be used to check if it gets in one of the group types
have a full text option like above
Im open for any type of suggestions and i would love to hear the reasons why its picked TY

View 1 Replies View Related

Picking A Data Source File From Configuration

Mar 7, 2008



Hi there,
I believe this is simple requirement but to me it seems to be complicated. My data source is excel. I would like to configure the path of my data source in SSIS Package and file name is not a constant value. SSIS package should pick the file named as Today's (current) date from the configured path. As well, package should catch the exception for example file not found and log into custom log. It would be gr8 if some body helps me out.

View 7 Replies View Related

Package Not Picking Up Latest Config File Changes

Nov 7, 2006

Hi,

I've created a solution with 5 packages in. I've scripted a config file for each package where I would be able to change the source file connection and the database servers (source and destination). On the first package, I am able to change the config attributes and the affects are picked up, but the rest of the packages do no pick up the changes.

For example, if the source file is "C:Filessource.txt" and this is changed to "C:Filessource123.txt" (Just to make sure it fails) it wont pick up the new changes and still uses "C:Filessource.txt".

Also, I tried changing the name of config file itself and the package still ran as if nothing had changed. It's as if it's not recognising the config file.

Any ideas ?

Thanks, Richie.

View 9 Replies View Related

Report Not Picking Up Custom Style Sheet OR HtmlViewer.css Changes

Feb 27, 2007

I cannot seem to get the report to pick up the custom style sheet I placed into Reporting servicesReportServerStyles subdirectory. Nor will it pick up any changes to the HTMLViewer.css either. The report obviously isn't looking there perhaps? I've used the http: etc. ReportServer/Pages/ReportViewer.aspx?%reportname%&rs:Format=HTML4.0&rs:Command=Render&rc:StyleSheet=MyStyleSheetname

I've even modified the HTMLViewer.css file as well. Nothing. Nada. Is there somewhere else this is cached? Or some way I can find where the report looks for this?



View 4 Replies View Related

How Would I Get A Subset Of This?

Apr 30, 2008

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

View 2 Replies View Related

Integration Services :: For Each Loop Container Is Not Picking Up CSV Source File

Jul 2, 2015

My package is having .csv file as a source and I kept OLEDB destination to load it.

Stored the .csv file in a shared folder and the exact path is given in Enumerator configuration of the foreach loop container. When I execute my package, it is giving the warning as below:

It is saying that file is not there in the specified path and directory is empty. I am running the SSIS package from TFS. I am sure that I have read and write access for the shared folder for my userID. Is there any access there to pick up this file from path.

View 4 Replies View Related

Max Col In A Table Subset

May 1, 2007

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!

View 1 Replies View Related

Subset A Dataset

Jul 11, 2007

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.

View 6 Replies View Related

SQL Server Admin 2014 :: Picking Static Port Number For Named Instance

Apr 3, 2015

Basically the question is, which number should I pick?

View 4 Replies View Related

Use DISTINCT On Subset Of All Fields

Jul 5, 2001

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.

View 4 Replies View Related

Updates All Records Instead Of A Subset?

Dec 28, 2012

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]....

View 4 Replies View Related

Select A Subset Of Data

Feb 13, 2007

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.

View 4 Replies View Related

Same Table - Subset Of Info

Oct 25, 2007

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!

View 4 Replies View Related

Incrementing Within A Group/Subset

Oct 6, 2006

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 Related

SQL Server On A Subset Of Processors

Jul 10, 2006

Can 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!

View 5 Replies View Related

Returning A Subset Of Rows From A Query

Apr 19, 2001

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

View 1 Replies View Related

Calculate Check Digit On Subset Of Ids

Jul 17, 2006

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 Related

Selecting The First Instance Of Each Record From A Subset

May 21, 2001

Hi

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

View 1 Replies View Related

Transfer Of Subset Of Data To/from Notebook

Mar 25, 2004

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

View 2 Replies View Related

Count Of Contacts - Data Subset?

Jan 29, 2014

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] .....

View 1 Replies View Related

Nth Subset Of Data From Current Table

Jul 20, 2005

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 Related

Selecting A Subset Of The Search Results

Nov 2, 2007

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.

View 4 Replies View Related

Analysis :: Subset Function In MDX Query

May 18, 2015

I need other function act like subset function using mdxquery.

In my mdxqueries,contain subset,order functions are available.

subset and order is getting poor query performance.

View 8 Replies View Related

Return Subset With All Remaining Records

Feb 22, 2008

Hello,

I need to pull records from single table such that I get a subset defined like this:

(
acctcode = 'xh364'
and
product = 'T&E'
)

And return all of the rest of the records:

(
acctcode = '%'
and
product = '%'
)

Can I do this within a WHERE clause, or will this require CASE / ELSE? There will be other specific acctcode/product rules that will be added later. I could do this with a UNION, but I need to avoid that if possible.


Thank you for your help!


cdun2

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved