(id_text int
,text_name varchar(10)
,id_author int)
insert into test (id_text, text_name, id_author) values (205, 'name1', 123)
insert into test (id_text, text_name, id_author) values (205, 'name2', 124)
insert into test (id_text, text_name, id_author) values (205, 'name2', 125)
insert into test (id_text, text_name, id_author) values (206, 'name2', 124)
insert into test (id_text, text_name, id_author) values (207, 'name2', 122)
insert into test (id_text, text_name, id_author) values (207, 'name2', 128)
select id_text, id_author from test
and i need to get out only id_text with the first author.
any solutions for sql server 2000?
I am very new to SQL and I have an issue. We have a website that has pictures on the site. We want to take them down but the pictures are somehow tied into the database, they aren't on any of the servers. How can I find these pictures that seem to be somehow hard coded into the database?
I have a report that is grouped by week. The details of the week have the date, time interval (half hour), and the maximum value for that day. So in the example below I am showing the week of May 21st. Then I have all of the days of that week, sorted by date as requested, with the halfhour the maximum value occured in on that day. So Monday the 22nd has the maximum value for the week at 254 (and is highlighted in red). Where the ????? are below, I need to display '1400' which is the interval, but have not yet been able to figure out how to do this.
I tried the following expression: =iif(max(Fields!MaxValue.Value) = Fields!MaxValue.Value, Fields!Interva.Value, "X")
but this only works if the first day is the maximum value.
I have, say 4 rows for 1 client I want to find the date difference between two(or more) of the rows when the first has a true flag and the next does not Row1 Date1,falseRow2 Date2,trueRow3 Date3,falseRow4 Date4,false so I want to find the difference between Row2 Date2 and Row3 Date4I know I can loop through the rows but with 000's of clients each with x numbers of rows this will surely be very slow
I have a form where users can enter an appointment on this form some personal details are entered. I want the users to be able to write in a textbox the users surname. they will then press a button next to the textbox that will bring up a smaller screen displaying that users details : SELECT * FROM Members where surname = (textbox1) Alternatively the users can click the button and bring up a screen where the users can put the surname into a textbox and then bring up the users details this way. I am not sure how to do this or how to select from the database where a field in the database is the same as the information put in to a text. Is this possible? Thanks in advance Mike
I have a table TEST. I want to find the triggers which use this table test. For example: If there is a table A, and a insert in A causes a trigger to insert data in TEST, I want to find this particular trigger. Any ideas? I am using SQL server 2000 thank you
hai I have to delete a single row from a table , where there are multiple rows with the same data and i want to delete only one row and i cannot alter my table structure. Thanks in advance satya
I have a customer database with the following structure:
Dept ID (int) Section (varchar)
I need to find only occurrences of a section (eg Admin) where the section name has a record in Dept 1,2 and 3 - only return the result if the record for Admin is associated will these depts.
My problem is a little tricky. I am trying to get the first distinct row(s) from a table where one column will have only a few differences, while a second is unique.
Now using "distinct" doesnt help as even though distinct(column1) may return only a few rows, because the next column is unique you will still have all rows returned.
How do I return only 1 row per distinct(column1) and drop the rest? What makes this so annoying is that all columns need to be returned, but only in accordance to distinct(column1)
Hi, sorry if this comes across as a stupid question, but i'm new to SQL. I have had a look on Google and on these forums but not found the answer.. I am running MS SQL 7 with a database containing around 130 tables.. i want to know if there is a query/command that i can type that will look through all the tables and tell me which ones reference a specified table (through it's key). Hope this makes sense! i've looked at some of the stored procedures but not found any that give me anything useful.
EDIT: Just so you are aware, i did not create this database, hence why i am not aware of the relationships myself, i have only just started working on it.
I have a column of data of type varchar. it contains a mix of text id's and numeric id's. all i want is the highest purely numeric value The closest i can get is to use a regex expression, but it don;t work and i just know there is a better way of doing it.
I have a script that creates these spin off tables (used for lightning fast access) on a near daily basis. But it is possible the table does not exist for that day - in which the script need to check one day back - so on and so forth until finding the most recently created table.
The script I have written fails with overflow error. I was hoping someone could tell me the correct syntax to accomplish this and also why i recieve this overflow error...
Code:
Dim str_SQL_Asset_1 daysBack = 1 tblFound = FALSE Dim xbl_Holdings_date Dim xMonth Dim xDay xbl_Holdings_date = date() If Len(Month(xbl_Holdings_date))=1 Then xMonth = "0" & Month(xbl_Holdings_date) Else xMonth = Month(xbl_Holdings_date) End If If Len(Day(xbl_Holdings_date))=1 Then xDay = "0" & Day(xbl_Holdings_date) Else xDay = Day(xbl_Holdings_date) End If
IF MM_checkCmd.EOF Then xbl_Holdings_date = (xbl_Holdings_date - daysBack) daysBack = daysBack + 1 tblFound = FALSE ELSE tblFound = TRUE MM_checkCmd.CLOSE() SET MM_checkCmd = Nothing END IF loop
The goal of this snippet is simply to identify and set the value for the existing table name. An example table name would be "xbl_Holdings_2006_12_10".
I could have posted this question in the VB forum but I would like to push as much of the work onto SQL as possible and also - this sort of operation is more likely to be required of a dba imo.
I am trying to complete an insert from query but the problem is I have duplicates, so I'm getting an error message. So to correct it I am creating a Find Duplicates statement in the Query analyzer but Its not working can someone tell me whats wrong with this statement (by the way I'm in SQL 2000 Server)
thank you
SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups FROM TERMINATION GROUP BY [TM#] HAVING Count([TM#])>1; GO
SELECT PR.WBS2, SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR' THEN Ledgerar.amount * - 1 ELSE '0' END) AS BillExt FROM PR LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3 WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001' AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND pr.wbs1 = '001-298' GROUP BY PR.WBS2
It prints out: Wbs2 BillExt 0141 0 0143 0 1217 20580
I want the code to return the wbs2 code '1217' because it has the highest amount in BillExt '20580'.
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 ?
Our products exist in batches. These batches reference a warehouse.Each warehouse carries a set of 'roles'.Users on our system also carry 'roles'. Comparing user and warehouse roles tells the system what warehouses a user can access.
So, if:
WAREHOUSE1 has roles A + B WAREHOUSE 2 has role B WAREHOUSE 3 has roles A + C and USER1 has role A USER2 has roles A + B + C USER3 has role C then USER1 will have access to WAREHOUSE1 and WAREHOUSE3 USER2 will have access to all 3 warehouses USER3 will have access to WAREHOUSE3.
What I need to do is write some SQL which allows a part number to be entered, and a list of batches for that part to be shown with a list of users who *CANNOT* access that part - because they do not have the relevant 'roles' for the warehouse(s) the part is in.
I can do the *opposite* of this quite easily (ie: show users who HAVE got access):
select distinct sb.batchnumber, ur.userid from stockbatches sb inner join warehouseroles wr on sb.warehouseid = wr.warehouseid inner join user_roles ur on wr.roleid = ur.roleid where sb.partid = @part order by sb.batchnumber, ur.userid
- it's a straightforward join between the warehouse and user roles, with a 'distinct' to eliminate any duplications.
Fair enough, but it's not what I want.
Using the role data above, with the following batch info:
We have licensed software from a 3rd party and we do not have the source code, nor can we do anything to change the database schema, write our own procs, triggers, etc.
With that said, we want to know which records in certain tables have been added or modified since a certain time (for example, in the past 24 hours). However, none of the tables has any datetime or timestamp columns on them for create date, update date, etc.
I tried to look into hacking thru DBCC log (dbname, 2) to see if that would help, what a mess. Using a tool like that from Luminent or Red Gate doesnt help either, because long term this needs to be automated if possible. Does anyone have any ideas on how to do this, or is the answer simply, "Sorry mate, you need to get them to allow you to change the database to add new columns and/or triggers". :)
Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average".
I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below.
declare @Weekday tinyint, @hour int select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())
if (@Weekday= 1 and (@hour>= 7 AND @hour<19)) begin Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average' SELECT AVG(Sat_Night) FROM Weekly end
if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7)) begin Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average' SELECT AVG(Sun_Day) FROM Weekly end
I our firm there are 4 people who are working on a database. I need to edit all of my views. Is there any way I can find out which view was created by me?
I was just wondering if any one knows how to find duplicate keys using more than one field. I used the below key to find those people who exists in list1 but don't exists in list2. I realized that the results had some duplicates which was expected but how do I then find all those duplicate people. I know how to do it if there was a primary key present I would have done a count (distinct cardnumber) > 1 and i would have done the select statement like this distinct cardnumber, but how do I do it with more that one key??
We have about 60 several tables with order id as a column in all of them . Every day I have to check if certain order id’s exist in the 60 tables. I just need to get the name of the table if it exists. Is there a way to dynamically keep changing the name of the table instead of having all 45 tables in the script?
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.
