Ok,
In one table, I need to find all instances of multiple entries of an
invoice number in the column InvoiceNumber.
For example, there may be more than one entrie of 321, 567, and 987
in column InvoiceNumber.
321 skin care product
321 vitamins
321 bottled water
567 rubbing alcohol
567 dental floss
987 fabric softener
987 tissue paper
987 marlboro cigaretts
Any help is appreciated.
Thanks,
Trint
Hi,
I need help desperately. The following is an example of the data that I have.
I have two tables one name Customers and the other named Orders. The tables have the following attributes
CUSTOMER
CustomerNum, CustomerName, OrderNum
ORDER
OrderNum, OrderDesc, CustomerNum
Bare in mind that the CustomerNum can have multiple customers attached to it, example a whole family (I know bad database design but it's too late to change) Let's use the following info for the tables respectively
CUSTOMER
0001 Sharon Bigbsy 1234
0001 Dale Bigbsy 1235
0001 Omar Bigbsy 1236
ORDERS
1234 Chips 0001
1235 Gatorade 0001
1236 Candy 0001
The query i'm using is select Customer.CustomerNum, Customer.CustomerName, Orders.OrderDesc from CUSTOMER, ORDERS where CUSTOMER.CustomerNum = ORDER.CustomerNum
The results are as follows
0001 Sharon Bigbsy Chips 1234
0001 Sharon Bigbsy Gatorade 1235
0001 Sharon Bigbsy Candy 1236
0001 Dale Bigbsy Chips 1234
0001 Dale Bigbsy Gatorade 1235
0001 Dale Bigbsy Candy 1236
0001 Omar Bigbsy Chips 1234
0001 Omar Bigbsy Gatorade 1235
0001 Omar Bigbsy Candy 1236
It's giving all the orders place to each customer name but I need only the following
0001 Sharon Bigbsy Chips 1234
0001 Dale Bigbsy Gatorade 1235
0001 Omar Bigbsy Candy 1236
I'm using SQL in MS Access 2003. Please disregard the abnormalities of the tables, it's someone else's work I inherited with over 6000 entried.
Any help will be greatly appreciated
Hi All,
I'm using Microsoft SQL Server Management Studio with SQL Server 2005 (SP2).
I have not had much experience with t-SQL and iterative logic implementation through SQL, therefore I think my problem is fairly basic for most of the pros here.
Here it goes...
I have two tables at hand, TestTab1 and TestTab2.
TestTab1 contains two attributes (columns) namely account_name (nvarchar(50)) and entry_count (int). For all rows in TestTab1, account_name is unique, i.e. for each account_name there is only 1 row in TestTab1. This is my source table.
TestTab2 contains one attribute namely account_name (nvarchar(50)). This table is empty and is my destination table.
I need to insert X entries (rows) for each account_name into TestTab2 where X is the int value in entry_count againt each account_name in TestTab1.
In other words, I need to insert account_name into TestTab2 as many times as the number in entry_count indicates.
I tried reading through the documentation but the help was not friendly enough for me to understand how to implement this.
Looking forward to the pro support.
Thanks,
The log for our SQL 2000 database shows constant "starting up database dbname" entires. Is there a option that causes the databases to constantly be starting or is this a new feature of SQL 2000.
View 1 Replies View RelatedVery basically, I need to return a result set based on another value and only if there are multiples of that other value.
Example. select * from mytable
Returns (--column separater)
John---1---1
John---1---1
John---2---2
John---3---3
John---3---3
John---4---4
So I want a query that would return only
John---1---1
John---1---1
John---3---3
John---3---3
The query cannot be something as simple as this:
Select * from mytable where John=1 or John=3
I have to many possible results to plan like that. I am thinking some kind of aggregate with a group.
Looking for returning multiple entries from a time span. I have a date, start-time, end-time and duration. I need the start-times separated in a list. It's fine if temp tables are needed - I have that clearance.
Entry might look like:
(datetime) date: 7/10/2014
(int) start-time 820
(int) end-time 1000
(int) duration 20
The result would needed:
(datetime) 7/10/2014 08:20:00
(datetime) 7/10/2014 08:40:00
(datetime) 7/10/2014 09:00:00
(datetime) 7/10/2014 09:20:00
(datetime) 7/10/2014 09:40:00
I am trying to find books which have the same title and publisher name as at least two other books and need to also show the book ref (ISBN number). I have the below script so far:
SELECT isbn, title, publishername
FROM book
WHERE title in (SELECT title
FROM book
GROUP BY title
HAVING count(title)>2 or count(publishername)>2)
order by title;
This is a snap shot of the output:
ISBN Title Publishername
0-1311804-3-6 C Prentice Hall
* 0-0788132-1-2 C OSBORNE MCGRAW-HILL
* 0-0788153-8-X C OSBORNE MCGRAW-HILL
* 0-9435183-3-4 C Database Development MIS
* 1-5582806-2-6 C Database Development MIS
[Code] ....
What I should be seeing is only the ones I have put an * next to. What am I missing from the scrip?
I work for an organization that repairs serialized devices. Each time a device is repaired it's serial number is recorded in a database table along with the date it was repaired along with other information about the device. There are multiple cases where a unit has been repaired more than once.
I am trying to write a query that will return the serial only once and that record will be the record of the latest repair date. To sum it up,
Return a list of serials where if a serial exists more than once in the table, return only the instance of the serial record(s) with the max(created_dt). The end result will be a list of distinct serial numbers.
Here is my Query. The problem I believe is in my sub-query but I am not sure how to structure it.
SELECT
S.Id
, RMA
, PinSerial
, L4Serial
, L4Model
[Code] ....
If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it?
I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above.
------ SQL -------
SELECT
job.job_number,
(SELECT MAX(jsl.job_log_number)
FROM job_status_log jsl
WHERE
job.job_number = jsl.job_number AND
jsl.status_code = '0130') as Last_Early_Warning_Status_Entry
[code].....
In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.
Below are the registry entries I want to update but "Trusted_Connection" = "Yes" does not seem to have any effect when it is run. Any ideas??
REGEDIT4
[HKEY_CURRENT_USERSoftwareODBCODBC.INIAAED]
"Driver"="C:WINNTSystem32sqlsrv32.dll"
"Description"="AAED"
"Server"="SIM0108"
"Trusted_Connection"="Yes"
[HKEY_CURRENT_USERSoftwareODBCODBC.INIODBC Data Sources]
"AAED"="SQL Server"
Hi, I am searching for the most easy SQL solution:
Lets say i have 500000 rows of cars in one table with 30000 different car ID's.
This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?
Thx for reply.
dajm
Hello
my table :
Report :
R_id (PK)
RName
RDate
i am having a few 10.0000 lines and i want to keep the last 10 (or less if not in the table) rows maximum for each name
i can have 100 report by name (100 rows with the same name and of course R_id and RDate are different)
how can i do it ?
thanks a lot for helping
I have a table which stores phone numbers of a customer in a table.
Say this table is as below
CustomerName - PhoneNumber
Customer 1 - Phone number 1
Customer 2 - Phone number 2
Customer 2 - Phone number 3
Customer 3 - Phone number 4
What would be the best approach to prevent adding another entry against Customer 2. I should be able to add new customers and add multiple phone number against all other Customers. The restriction should be only against Customer 2.
I am trying to pull the last three entries from a table in my database but I am having trouble writing the correct query. The database has multiple entries for each item in my database but I want to pull just the last three for each item. I have tried the TOP function with the items ordered in descending order but that only works when I define each item seperatley. I need to know how to pull the last three entries for every item I have in the table.
Thanks,
Tim
Hello
Without technical information and DB design, I wish to know the maximum entries in MS SQL in million. I guest for access are a limit of 2 million and a 2GB DB.
How much for MS SQL in max. entries and how much in GB?
A lot of thx for your feedback.
Dominique Javet
I'm extracting data from a log (log_history) of patients where nurses perform various actions on a call, such as assessing and reassessing, despatching etc. This is the script:
Select
L.URN,
LH.THE_TIMESTAMP,
LH.ACTION_TYPE,
LH.ACTION_BY,
LH.ACTION_REQD,
LH.NOTE,
em.position_type_ref
From
LOG L
Join Log_history LH on (L.URN = LH.LOG_URN)
left outer join employee em on (em.code = LH.action_by)
Where
(L.Taken_at >= :DateFrom and L.Taken_at <= :DateTo) and (LH.ACTION_TYPE = 'D') and (em.position_type_ref ='NU')
Order By
L.URN ASC, LH.THE_TIMESTAMP DESC
The result I get shows duplicate 'timestamp' entries and I only want to return unique timestamp entries. Does anyone have any ideas. I'm self taught and have hit a wall
I have an application that allows the user to enter data into a table. There are multiple users so I put in some code that, I thought, would keep 2 users from creating a new record at the same time. The IDs for the records are identical and this is causing a problem.
The IDs are in the format of ####-mmyy. at the start of each month the #### part goes back to 1.
We tried a test today where we had 2 users click on the New button at exactly the same time. The IDs that were created were identical. Is there anyway on the database that I can prevent this from happening?
Here is how I create the new record id:
I get the MAX(ID) from the table
I add 1 to the ID and then insert a new record with the new ID into the table.
Any help is appreciated.
Thanks,
enak
Hi all
can some one there please let me know how to configure error logs. what i observe on some of the servers error log got entries for all conenction, database backup, logbackup etc..... but on some there r entries only about database backup.
thanks in advance
Hello,does anyone know what could be wrong? There's a lot of similar entriesin the SQL Server current log:2004-01-21 17:54:00.01 spid57 Starting up database 'DB_NAME'2004-01-21 17:54:06.45 spid57 Starting up database 'DB_NAME'2004-01-21 17:54:59.13 spid56 Starting up database 'DB_NAME'There are hundreds of similar entries that repeat every few seconds. Thedatabase works quite slowly and the users get 'Connection timeout'message very often.Thanks for any advice....:: fabio
View 1 Replies View RelatedHi All
In my application i have to get the data from .csv file. My requirement is that file may consists of duplicate entries
I ant to remove the dup entries and i want to place in the table.
Waiting for valuable replies
Thank u
Baba
I have an issue where certain parts of data are repeated several times after i create my query. Without providing my SQL code for now could anyone suggest possibly the main reason(s) for data being duplicated?
Thanks
Hi,I have a form that should show 2 pictures based on table entries.I want those 2 pictures to be randomly selected based on a database table. So, my table has all the entries, and I want to pull out a random entry that has been approved to display it.Can someone help me with the sql query?I can do SELECT VoteId FROM tblVotes WHERE Approved=True..But how do I make selection a random one that changes every time the user gets another entry?
View 2 Replies View RelatedHello,
I have a reference table. This table has an effective date and end date, and I need to get a single set of values at a point in time. Suppose the following hotel rating values are the values that I want to pull out of the table:
1 Star2 Star
3 Star
4 Star
5 Star
This value has other information associated with it (benchmarks and such) and so it's possible that one of the 2 star entries gets end-dated, and then a new 2 Star entry gets created with an effective date and no end date.
How can I query so that I only get one 2 Star entry at any point, and get the point in time where the date is between the effective and end date, but only pull back a single set of entries, one distinct rating value. So, suppose the first entry is end-dated 7/1, if I look at old data, I want to see the old benchmarks and so I want the previous entry end-dated 7/1; otherwise, I want to see the new end-date, with the new 2 star entry with no end date.
How do I do that? Make sense?
I am a newb at ms sql and was hoping someone could help me
eliminate duplicate PRODUCT.PRODUCT from this statement. I have tried using DISTINCT with the same results.The ProductImage table is causing this because
the duplicates are from the PRODUCT.PRODUCT that have more than 1 image.
If anyone could rewrite this statement so I can learn from this, it would
be most appreciated!
Thank you for your time
<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"SelectCommand="SELECT Product.Product.productid,Product.Product.catid,Product.Product.name,Product.Product.smalltext,Product.Product.longtext,Product.Product.price,Product.ProductSpecial.saleprice, Product.ProductSpecial.feature,Product.ProductImage.imgId, Product.ProductImage.imgUrlFROM Product.ProductINNER JOIN Product.ProductSpecialON Product.ProductSpecial.productid = Product.Product.productidINNER JOIN Product.ProductImageON Product.Product.imgid = Product.ProductImage.imgId"></asp:SqlDataSource>
hi, i have a table where i need to update all entries (columns/rows) in 1 queryto make this one clear, if i have table_id firstname lastname1 clayton111 dylan1112 phoebe111 miller111 from the above example, i just want to eliminate all the 111 from the firstname, lastname fields, however, im not sure how to do that in a single query. i asked this because i have like 100 rows and each entries have "111" and i need to remove those. thanks
View 7 Replies View RelatedIs there a way I can have all entries in a table automatically in caps once entered by user, import or any other way ?
Thanks a lot in advance.
-PFD
I have a field called RegId. RegId is of datatype NVARCHAR (20).
RegId
-----
12322
2122111
23423
etc
etc
I want to run a query to find out if there are duplicate entries in this field.
Any ideas on how I can achieve this?
Thanks in advance,
Anthony
heyas,
i wanna know how can i select only entries with at least 2 rows?
What i mean is that i want to make a select * from table1 where columnA = 'Something' order by columnB
but addicionally i want it to return only register that happen at least 2 times for columnB
thanks in advance!
I have just started learning PHP and MS SQL. I was having a problem viewing the database (column entries) in the MS SQL server managemnet studio. I can see the column names but I cannot see the entries that I save in it.
Ne help pls !!!
Any yes the data is saved because when I use the "select" command I can print the data.
For example some data has entered into a table in a random manner i.e the pk filed value is not in a serial fashion.Is there any table or index that holds the entries of rows into a particular table as entered .
i.e
'some_table' has data like this
3,entry3
2,entry2
4,entry4
1,entry1
I want some DB table or Index that holds data like this about above 'some_table'
row_id .... .... ....
1
2
3
4
here 1 refers to entry of the first column in 'some_table' i.e 3,entry3
and so on...
Hey,
I have some field values entries in my database.. that are spaces like ' '. i wanna eliminate them.
When i use IS NOT NULL in query it only eliminates the rows with NULL values so how could i modify the query to eliminate the rows with spaces in the field value..
Thx in advance..
Hi All,
I have a DB of transaction log grown to 19GB. I have some questions.
1. Is there any way that we can minimize the entries to transaction log.
2. If i use Transactions in my stored procedure will it be usefull to automatically remove the transaction entries in transaction log once after the commit transaction is given.
3. Is there a way to reduce my current transaction log and not to grown in such a manner futher.
Thanks in Advance
Sri
SELECT [TimeSheetConsolidatedId]
,[EmployeeId]
,[ActivityId]
,[StartedAt]
,[HoursWorked]
FROM [SR].[dbo].[TimeSheetConsolidated_All]
WHERE [StartedAt] > '2012-08-06 00:00:01.000'
AND [StartedAt] < '2012-08-010 23:59:59.000'
AND [ActivityId] = 10
ORDER BY [EMPLOYEEID]
This displays days that all employees have worked with a certain activity ID. Max 4 days Min 0 days. What I would be looking to do is remove any Employees from the table if they had worked all 4 days leaving only the Employees that have had a day or more off.
I might have approached this the wrong way but it is the only way I can think of getting the results I want.
I'm trying to run this as a Single Query in SQL Server Manager 2008.
Attachment shoes to Employees one with 4 entries who would be removed from the view the other with 3 who would remain the focus of the report.