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.
finding one set of data within another set of data for update,so if within column 1 it finds the results from another select on another table, it updates a different column with the result.
Example
SELECT Title from H..Import_Table returns 'PRN - Concord' 'PRN - San Jose' 'SLP - PRN - San Jose' 'San Jose - PT - PRN'
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:
I have like 20 databases on my SQL Server. I have like 30 tables in each Database. I would like to find out all the rows in all tables in all database that has a certain text?
SELECT MAX(TIME_VAL) AS Duration1 , MIN(TIME_VAL) AS Duration2 , -- VALUE OF LATITUDE CORRSPONDING TO MAX(TIME_VAL) -- VALUE OF LATITUDE CORRSPONDING TO MIN(TIME_VAL) (Select LONGITUDE from (select LONGITUDE , row_number()
[Code] ....
I am not getting Correct values for StartLongitude , EndLongitude. How can i achieve this to get the val of latitude corssponding to max/ min of time_val column.
I have a table. In that table I have a list by student number that lists the entry dates into a particular grade. When trying to list only the first time entered, there is no unique way to identify one row from another other than the date. Is there a way to use max or min to only pull one date per student number? I have done a series of case when statements and I am able to get it down to 1 to 2 entries per student number, but I need to get it down to only 1 date per student number.
Thank you for your help
SELECT DISTINCT mx.stu_num,
CASE WHEN er.STU_NUM = ep.STU_NUM
THEN er.enterdater
ELSE CASE WHEN ea.STU_NUM = ep.STU_NUM
THEN ea.enterdatea
ELSE CASE WHEN eb.STU_NUM = ep.STU_NUM
THEN ep.enterdatep
ELSE eb.enterdateb
END
END
END AS entrydate
FROM dbo.mx AS mx LEFT OUTER JOIN
dbo.v_EntryDate9_R AS er ON mx.stu_num = er.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_P AS ep ON mx.stu_num = ep.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_A AS ea ON mx.stu_num = ea.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_B AS eb ON mx.stu_num = eb.stu_num
I have a table that stores part numbers and manufactuers. Somehow this table has become corrupt showing different manufacturers with the same part numbers.
I know this will take a bit of manual digging to fix, but I want to find a way to pull all rows that have the same part number that have different manufacturers, or just pull up any "duplicate" part numbers and I can determine what is right or wrong as far as the manufactuers and make those changes.
I have tried this, but it does not seem to want to work.
Code:
Select * from my_table Where partnumber = (select partnumber from my_table) and compName <> (select compName from my_table)
I have tried other variations of the same, but nothing seems to want to show me just the items that have the same part numnbers and different manufacturers. I do not care if there are duplicates of the same part number/manufacturer entries, just if the part number is duplicated where the manufacturers are not the same.
These are the rows I want to edit and group by part number. I have almost a million rows of entries and this is not something I want to go through row by row. :-P
I have a table of employee assignments that I'm narrowing down to a specific group. Employees have multiple assignments (job positions essentially) and each has start and end dates. I need to identify the date range(s) that are covered by the assignments in my data set. Here's an example of data for one person ID. NULL in the end_date just means it is an active assignment. The pos_id column isn't necessary, but it define the data I'm looking at. The end result won't use it.
In this case I want results to say that ID 999 has a range from 9/2/2011 to NULL. There are no gaps in the date range. Or to say it differently, there's always an assignment starting the next day after an end_date. Or an assignment that overlaps the end and beginning of another assignment.
Here's another example where there is a gap in the ranges.
There would be 2 result rows for this with a range from 2011-09-02 to 2013-01-06 and a second for 2013-09-01 to NULL.
The end result would be to have a row per date range for every ID. I've written a script that will find the ranges but it is a painful RBAR solution that takes forever to run. Every different path I've gone down trying to solve it ends in evaluating row by row grouped by the Person ID. The assignments are not always continuous, so I can't use a MAX and MIN and there may be 1 or more gaps in the dates to account for.
currently I am facing a complex escenario related with gaps and sequences, but I was trying with diferent cases but I did not get the correct results, I am sure about the use of windows functions. I have a table with the information grouped by PublicationId, Provider, MetricId and Amount by Date, one row by each month, but in some cases these data don't have a sequencial values, for example I have the data for the next sequence:
I need to get the sequence by each month, in this case I need to project the month from February to May (with the last previous value, for this case of January) , this is:
The data for testing are:
DECLARE @PublicationsByUser AS TABLE ( Id INT, PublicationId INT, MetricId INT, ProviderId INT, DateCreated DATE, Amount FLOAT
Report Builder 3.0 . I have two datasets and I am using LookupSet to return data (a list of dates) but what I am after is the LAST date in the set. I have tried to use Last but I keep getting an error.
I even tried doing a Last on the above statement with a Join thrown in, but that just gets me errors as well. I tried to Code in a function to go through the dataset and return the last piece of data, but again...error.
Function LastLookup(ByVal items As Object()) As Date If items Is Nothing Then Return Nothing End If Dim suma As Date = New Date() For Each item As Object In items suma = CDate(item) Next
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?
(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 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 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 ?
