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.
Name TestA Date TestB Date
---- ---------- ----------
Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
Account 1 9/3/2004 3:00pm (NULL)
Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM
Any help would be much appreciated!!!!
View 2 Replies
ADVERTISEMENT
Mar 26, 2008
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.
datasource:
contract_no order_no name amount
12 12 contract1 1,000,000
12 215 order215 50,000
12 460 order460 75,000
280 280 contract2 500,000
280 340 order340 10,000
280 410 order410 9,000
I want to display each order that took place via drilldown/hide function in the table, and calculate the amount from the contract that is left.
desired result report:
12 contract1 1,000,000
order 215 50,000
order 460 75,000
amount left 875,000
View 1 Replies
View Related
Nov 23, 2007
Hello,
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?
Any help would be much appreciated.
Thanks
Karl
View 3 Replies
View Related
Sep 8, 2015
I have two tables like,
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
)
how to achieve this in sql server 2012.
View 6 Replies
View Related
Jan 23, 2002
Hi guys,
Can anyone please tell me how to create a table based on a query.
Thanks,
Joe
View 2 Replies
View Related
Mar 7, 2008
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!
View 2 Replies
View Related
Jan 18, 2007
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
View 2 Replies
View Related
Jun 18, 2015
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.?
View 4 Replies
View Related
Feb 18, 2015
I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
View 3 Replies
View Related
Mar 20, 2007
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.
View 1 Replies
View Related
Feb 17, 2014
I have two tables with this info:
TABLE 1
COL1 COL2 COL3
AAA BBB CCC
QQQ WWW EEE
AAA SSS DDD
WWW EEE RRR
BBB BBB BBB
TABLE 2
COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347
I want to insert TABLE 1 into TABLE 2 with a query that will auto increment to COL4 looking like this:
COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347
AAA BBB CCC 348
QQQ WWW EEE 349
AAA SSS DDD 350
WWW EEE RRR 351
BBB BBB BBB 352
I know this can be done easily by just altering the column to have an auto-increment datatype, but I currently cannot do that at this moment.
View 5 Replies
View Related
Dec 22, 2006
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
Thanks for any help,
Mike
View 1 Replies
View Related
Feb 18, 2015
I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
Table D
View 7 Replies
View Related
Apr 5, 2006
stepdefinition has
steptype
flowid
stepid
task has
taskid
flowid
stepid
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
here's a visual
http://www.filecabin.com/up1/1144249279-task.gif
View 2 Replies
View Related
Nov 27, 2007
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]
but this does not work, it just throws an error.
Can anyone help me how to build this query?
TIA,
Sam
View 3 Replies
View Related
Jan 29, 2004
Hello-
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".
How do i do this?
View 6 Replies
View Related
Jul 23, 2005
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.
View 26 Replies
View Related
Jul 20, 2005
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?
View 1 Replies
View Related
Jul 20, 2005
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.
View 1 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Jan 25, 2008
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.
Any assistance is greatly appreciated
View 6 Replies
View Related
Oct 10, 2005
Hey all.
I'm looking around for a solution on how to pull records back based on legnth.
So, if field A is more then 2 characters, return that row.
Thanks
Caden
View 2 Replies
View Related
Apr 18, 2008
Hi All,
For one of my reports, I need a expression smthing like this.
=IIf(Fields!Category.Value = "Decisioned" , THEN SHOW SUMMATION OF TOTAL APPLICATIONS WHERE Catergory = "Decisioned ," ")
Can anyone help me to write this expression.
Thanks
View 3 Replies
View Related
May 29, 2008
I have a report. I want to display title according to the following combination of two parameter values.
Line of Business
Product Type
Report Title
Total
Line, Loan
13 Month Home Equity Portfolio - Total
Cbank
Line, Loan
13 Month Regional Bank Portfolio - Total
DTC
Line, Loan
13 Month Direct to Consumer Portfolio - Total
Edirect
Line, Loan
13 Month Equity Direct Portfolio - Total
InstLend
Line, Loan
13 Month Institutional Lending Portfolio - Total
Total
Line
13 Month Home Equity Portfolio - Lines
Cbank
Line
13 Month Regional Bank Portfolio - Lines
etc
Line of Business and product type are two parameters.
Product type is a muliple parameter. i.e you can select Line, Loand or Line and Loan.
can any one tell me how to write a proceedure to get this result(i.e report title)
Thanks
View 1 Replies
View Related
Feb 5, 2007
From Newbie to Newbie,
Add reference to:
'Microsoft ActiveX Data Objects 2.8 Library
'Microsoft ADO Ext.2.8 for DDL and Security
'Microsoft Jet and Replication Objects 2.6 Library
--------------------------------------------------------
Imports System.IO
Imports System.IO.File
Code Snippet
'BACKUP DATABASE
Public Shared Sub Restart()
End Sub
'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
Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")
'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")
'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing
'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
FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;
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")
End Sub
End Class
View 1 Replies
View Related
Dec 28, 2006
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.
View 3 Replies
View Related
May 20, 2008
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
View 5 Replies
View Related
May 29, 2008
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')
View 6 Replies
View Related
Nov 9, 2005
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.
View 5 Replies
View Related
Feb 27, 2014
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
View 6 Replies
View Related
Jan 11, 2008
Do triggers work on the field level or only for full table updates, etc...?
I want to have a trigger that will change a field on another table based on the update of a specific field on a table.
Could anyone provide a sample please, if this is possible?
Thank you!
View 3 Replies
View Related
Jul 23, 2005
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
View 2 Replies
View Related