Create Query Based On A Field That Won't Be The Same Value In Both Tables
Jul 20, 2005
I have two tables: TestA and TestB. Both tables have 3 fields: ID,
Name, and RunDate. I need to create a query which will join the two
tables first on Name but then I need to match up the RunDates even
though the RunDates won't be the same.
CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)
CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)
INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')
INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')
INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')
Here's a common scenario:
User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Then
the user updates TestB data for Account 1, 15 minutes later. I want
these two records to match. The user must always update TestA data
before they update TestB data. Therefore, there might be more rows in
TestA then in TestB
Here's what the results should look like for the above data.
I want to group by a fields value in a table. I have a purchase table where contracts and orders are stored in together. Now what I need is, I have to group only by the rows that contain an identical contract_no and order_no as those records represent the actual contract. the other rows contain order data-sets which need to be subtracted from the contracts values.
I'm looking for the best solution for creating database tables based on a Word Document with a table. I've attached part of the table from the word doc. This document is base on a template for audit reporting.
What I assume is I would need first to start of by creating two tables. One table would consist of the fields of the horizontal upper most data e.g. conformance, noncomformance etc. The other table would consist of the vertical fields of the word doc table, e.g. editorial, Auditee Name etc. If my assumption is correct, what primary key and foreign key would I need to have?
create table dbo.#Status( ID varchar(50), Status varchar(50), EffectiveStartDate datetime, EffectiveEndDate datetime, Is_Current bit
[code].....
I want result as the attached image.
Create table query for result is: CREATE TABLE dbo.#Result( ID varchar(50), Fee varchar(100), Bill varchar(50), A_Date date, B_Date date, Status VARCHAR(50), EffectiveStartDate datetime, EffectiveEndDate datetime )
I need to search a nvarchar field based on the format of the text. This field holds values in two formats: 000 000 000 000 and 000000. I only want to search through the records that are in the 000 000 000 000 format. Can anyone give me direction on how to go about doing this or give me some key words to search for on Google? Fixing this problem is not an option. This is a county tax DB from a poor county with almost a million records in it. Thanks for the help!
I need to write a t-sql query that will take the value of the previousrecord into consideration before calculating the current row's newcolumn value...Here's the situation...I have a query which return the following table structure...Full_Name Points----------------- ------------Name1 855Name2 805Name3 800Name4 775Name5 775Name6 741etc.... etc...I need to create a calculated column that tells me where the personranks in point position. The problem i run into is that in thesituation where two or more people have the same point value i need thecalculated rank column to display the same rank number (i.e. 4th orjust "4") I'm not sure how to to take into consideration the previousrow's point value to determine if it is the same as the current onebeing evaluated. If i new they were the same i could assign the samerank value (i.e. 4th or just "4").If any one has any insight that would be great.ThanksJeremy
I simply need to list the most recent value for each employee here... the value at each person's maximum date
Sample Detail Data:
EmpID                 Date                    Value 1                             1/1/14                 27 1                             2/12/15               333 2                             5/5/15                  255 3                             5/4/15                  110  3                             1/1/13                  67                          3                             3/2/14                  80
[Code] ....
What is the most efficient way to display the most recent value for each employee ID via MS SQL.?
We are using SQL2005. I have a stored proc that runs a Select query based on a complex view. The sproc has two input date parameters (StartDate and EndDate). We are experiencing SQL timeout problems when the sproc is run with certain Start and End Dates. We have run the SQL Profiler and created a trace (trc) file (We've used the 'Default' trace configuration). We have used the trace file in SQL Server Management Studio to try and automatically create indexes on some of our tables. Unfortunatly SQL Server Management does not make any index recommendations. I think we are not capturing the right information in our trace file to allow SQL Server Management Studio to do its job. How do I use SQL Profiler to capture a trace of my sprocs query, so that it can be used by SQL Server Management Studio, to recommend index changes? Any help appreciated. Reagrds, Paul.
I'm very new to SQL server and can use some help. MyTable has ColumnA, which contains strings composed of 1 to 4 numeric characters (0 thru 9) followed by alphabetic characters. For example, "53ASDF". In my query, I need to create ColumnB, which takes the numeric prefix from ColumnA's string and prepends it with zeros, if necessary, to create a string of exactly 4 numeric characters. For example, I could get the following result:
ColA ColB "6abc" "0006" "457def" "0457" "7232hij" "7232"
I have implemented a temporary solution using a CASE statement: SELECT ColA, ColB = CASE WHEN ISNUMERIC(LEFT(ColA, 4)) = 1 THEN (LEFT(ColA, 4)) WHEN ISNUMERIC(LEFT(ColA, 3)) = 1 THEN '0' + (LEFT(ColA, 3)) WHEN ISNUMERIC(LEFT(ColA, 2)) = 1 THEN '00' + (LEFT(ColA, 2)) WHEN ISNUMERIC(LEFT(ColA, 1)) = 1 THEN '000' + (LEFT(ColA, 1)) ELSE '' END FROM MyTable
Because of additional complexities, I need to implement the solution with a loop instead of a CASE statement. Can someone please describe such a solution?
I'm very confused about how variables work in SQL Server, but made an attempt to implement a solution. Hopefully, someone can make corrections and describe how to use it with a SELECT statement. I would greatly appreciate any suggestions. This is what I started with:
DECLARE @ColBstring char(4) DECLARE @num int SET @ColBstring = '' SET num = 1; -- Get the numeric prefix from ColumnA's string WHILE(isnumeric(substring(colA, 1, num)) = 1) @ColBstring = (substring(colA, 1, num) num = num + 1
-- Prepend the ColumnB string with zeros WHILE(LEN(@ColBstring) < 4) @ColBstring = '0' + @ColBstring
flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.
well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.
select flowid, stepid from stepdefinition where steptype = -3
Now, I want to find all taskids in task for each flowid/stepid combination
I still don't get some of the more complex stuff in SQL, so I've got a question about grouping:
I've got a table with articles and a table how much articles I got in stock. The ArticlesInStock-table might contain more than one row for an article, so to check how many items I got in stock I use this group by-SQL:
SELECT [ArticleID], SUM([InStock]), SUM([InStock] * [Price]) FROM dbo.[ArticlesInStock] GROUP BY [ArticleID]
this does work nicely. But now I'd like to get the order no of the article, too (and maybe sort by it), and this is where I get stuck.
I thought it might be something like this:
SELECT [ArticleID], SUM([InStock]), SUM([InStock] * [Price]), [Articles].[OrderNo] FROM dbo.[ArticlesInStock], dbo.[Articles] WHERE dbo.[ArticlesInStock].[ArticleID] = dbo.[Articles].[ArticleID] GROUP BY [ArticleID]
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
Guys, New to SQL and I'm trying to create a new field, entitled “Name� and see all employee names from my "Employee" table using the following format: LastName, FirstName, jobcode, emailaddress and hire date.
after much effort my code is as follows:
CREATE VIEW 'Name' AS SELECT LastName, FirstName, JobID, Email, Hiredate AS Relationship FROM 'Name' Create View 'Name' as Select LastName, FirstName, JobID, Email, Hiredate From EMPLOYEE Group By LastName, FirstName, JobID, Email, Hiredate Order By LastName
But i'm still getting the following errors:
Msg 111, Level 15, State 1, Line 196 'CREATE VIEW' must be the first statement in a query batch. Msg 102, Level 15, State 1, Line 199 Incorrect syntax near 'Name'. Msg 111, Level 15, State 1, Line 200 'CREATE VIEW' must be the first statement in a query batch.
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click Dim addtimestamp As String Dim f As String Dim z As String Dim g As String Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm") z = "C:Program FilesVSoftAppMissNewAppDB.mdb" g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.") If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then End If End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click Dim Filename As String Dim Restart1 As New RestoreRestart Dim overwrite As Boolean overwrite = True Dim xi As String
With OpenFileDialog1 .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*" If .ShowDialog() = Windows.Forms.DialogResult.OK Then Filename = .FileName
'Strips restored database from the timestamp xi = "C:Program FilesVSoftAppMissNewAppDB.mdb" File.Copy(Filename, xi, overwrite) End If End With
'Notify user MsgBox("Data restored successfully")
Restart() If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click Dim L As New DatabaseEraseWarning Dim Cat As ADOX.Catalog Cat = New ADOX.Catalog Dim Restart2 As New NewDBRestart If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub Else File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") End If End If Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection 'Dim Cat As ADOX.Catalog Dim Tablename As ADOX.Table 'Taylor these according to your need - add so many column as you need. Dim col As ADOX.Column = New ADOX.Column Dim col1 As ADOX.Column = New ADOX.Column Dim col2 As ADOX.Column = New ADOX.Column Dim col3 As ADOX.Column = New ADOX.Column Dim col4 As ADOX.Column = New ADOX.Column Dim col5 As ADOX.Column = New ADOX.Column Dim col6 As ADOX.Column = New ADOX.Column Dim col7 As ADOX.Column = New ADOX.Column Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection Cat = New ADOX.Catalog Tablename = New ADOX.Table
'Open the connection Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want) Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType! col.Name = "ID" col.Type = ADOX.DataTypeEnum.adInteger col1.Name = "MA" col1.Type = ADOX.DataTypeEnum.adInteger col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable col2.Name = "FName" col2.Type = ADOX.DataTypeEnum.adVarWChar col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable col3.Name = "LName" col3.Type = ADOX.DataTypeEnum.adVarWChar col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable col4.Name = "DOB" col4.Type = ADOX.DataTypeEnum.adDate col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable col5.Name = "Gender" col5.Type = ADOX.DataTypeEnum.adVarWChar col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable col6.Name = "Phone1" col6.Type = ADOX.DataTypeEnum.adVarWChar col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable col7.Name = "Phone2" col7.Type = ADOX.DataTypeEnum.adVarWChar col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable col8.Name = "Notes" col8.Type = ADOX.DataTypeEnum.adVarWChar col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
'You have to append all your columns you have created above Tablename.Columns.Append(col) Tablename.Columns.Append(col1) Tablename.Columns.Append(col2) Tablename.Columns.Append(col3) Tablename.Columns.Append(col4) Tablename.Columns.Append(col5) Tablename.Columns.Append(col6) Tablename.Columns.Append(col7) Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection Cat.Tables.Append(Tablename)
'User notification ) MsgBox("A new empty database was created successfully")
'Restart application If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then Application.Restart() End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click Dim JRO As JRO.JetEngine JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name. JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted) File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme. Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification MsgBox("The database was compacted successfully")
I need to update the status of a client when they make a payment of a certine amount. My problem is this, the two pieces of information needed to do this are comming from two tables. For example; @ClientID Int, @PmtAmt Money IF @PmtAmt >= tblSettings.TopAmt THEN Update tblClients SET ClientStatus='High' WHERE ClientID=@ClientID ELSE Update tblClients SET ClientStatus='Medium' WHERE ClientID=@ClientID ENDIF How do I do this in a stored procedure? I need to select the TopAmt from the table tblSettings and then update the table tblClients.
I have two tables and they have a foreign key relationship. It will be a 1 to Many realationship. Table1- the primary key table has the following columns ContentID - int identity field DateAdded- datetime Table2 - The foreign key table, has the following relevant fields FK - int foreign key version - int When an initial record is added there is a record added to each table. The ContentID in Table1 will match the FK field in Table2. Then a user will be able to "edit" the record in Table2, but instead of writing over the record that is already there, a new record will be added to Table2. I would like to calculate the version field where each time a record is added for the particular ContentID/FK it adds 1 to the last version that was added to that particular ContentID/FK. Does that make sense. I am trying to implement some type of revision tracking for my CMS that I am building and this is the only way I can come up with because my client is worried that somebody will go in and make incorrect changes to their site and they want to be able to roll it back to a previous version. I would appreciate any ideas. Thanks, laura
Hi I hop i make sense with this, i have a page where you can update a products price based on the year, so for instance some products will still have prices set in 2007, while some may have prices which will be set in 2008, what i am trying to do is if the price has been set in 2008 it shows this value. The fields in the table which control the values are Rate(controls 2007), and Rate2007Period2. What i am trying to do is if Rate2007Period2 is null or 0, then display the 2007 rate, and if Rate2007Period2 is greater than 0 then display that value. Here is the view which displays the rates; SELECT dbo.tblRateSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblRateSchedule.DESCRIPTION, dbo.tblRateSchedule.SWT, dbo.tblRateSchedule.Rate, dbo.tblRateSchedule.Tstamp, dbo.tblRateSchedule.RateSchedule_ID, dbo.tblRateSchedule.RateScheduleUnit_ID, dbo.tblRateSchedule.RateScheduleType_ID, dbo.tblRateSchedule.WorkType_ID, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description, dbo.tblRateScheduleType.Type, dbo.tblRateSchedule.Work_Type_Description AS Expr1, dbo.tblRateSchedule.Rate_Active, dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2007Period1, dbo.tblHistoricalRateSchedule.Rate2007Period2FROM dbo.tblRateSchedule INNER JOIN dbo.tblHistoricalRateSchedule ON dbo.tblRateSchedule.MA_Code = dbo.tblHistoricalRateSchedule.MA_Code LEFT OUTER JOIN dbo.tblRateScheduleType ON dbo.tblRateSchedule.RateScheduleType_ID = dbo.tblRateScheduleType.RateScheduleType_ID LEFT OUTER JOIN dbo.tblWorkType ON dbo.tblRateSchedule.WorkType_ID = dbo.tblWorkType.WorkType_ID LEFT OUTER JOIN dbo.tblRateScheduleUnit ON dbo.tblRateSchedule.RateScheduleUnit_ID = dbo.tblRateScheduleUnit.RateScheduleUnit_IDWHERE (dbo.tblRateSchedule.Rate_Active = '1')
Here's a question for the SQL gurus out there: I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be 8060 8060,4450 8060,4123,3245 Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example CODE1=8060, CODE2=4450, CODE3=NULL. I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field? Any help very greatly appreciated. Thanks, Simon.
I have one table with text column(varchar50) and 2 value columns(INT)
it looks like this: c1, c2, c3 1, null, text1 2, null, text2 3, null, text3 4, 3, text4
What i want to do is to combine c3 on row 3 and 4 so the output would look like this: c1, c2, c3 1, null, text1 2, null, text2 3, null, text3 4, 3, text3 ext4
I have tried to use CASE with no luck.
edit: output like this is also fine c1, c2, c3 1, null, text1 2, null, text2 3, null, text3 4, 3, text4 5, null, text3 ext4
Having a brainfart....I need a query that returns a record count, based on two distinct fields.For example:Order Revision Customer001 1 Bob001 2 Bob002 1 John003 1 John004 1 John005 1 Bob006 1 Bob006 2 BobThe query on the above data should return a count of orders, regardless ofthe revision numbers (each order number should only be counted once).So WHERE Customer = 'Bob', it should return OrderCount = 3TIA!Calan
