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

Is Grouping In Tables Based On Field Values Possible?!

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

Need Help To Create Tables Based On Word Doc Table

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

SQL Server 2012 :: Merging Two Tables Based On Date Field?

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

How Do U Create A Table Based On A Query

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

Query String Field Based On Text Format

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

Query Which Calculates A Field Value Based On Previous Row's Values

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

Transact SQL :: Query To Show Most Recent Value Based On Date Field

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

Query To Combine Two Tables Based On Third Table

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

Management Studio - How To Index Tables Based On Query - Please Help

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

Query Based On Two Temp Tables Runs Forever

Oct 28, 1998

View 1 Replies View Related

SQL Server 2012 :: Increment Query Based On Two Tables?

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

How To Manipulate String In Query And Create New Field

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

SQL Server 2014 :: Query To Combine Two Tables Based Third Table

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

Help Create Query Against Two Tables

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

How To Create An SQL Query With GROUP BY And Two Tables?

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

Sql Query Which Uses Multiple Tables But No Common Field To Join

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

Multiple Foreign Keys On Same Field, Based On Other Field

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

Separating One Field Into Two Fields Based On A Character In The Field

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

Dynamic Create Table, Create Index Based Upon A Given Database

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

Create Date Field From Substring Of Text Field

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

Need Query Help Re:Create &"Name&" Field & Populate

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

Where Based On Length Of Field.

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

Sum Of One Filed Based On Value Of Another Field

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

Getting One Field Based On Two Other Parameters

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

Solution!-Create Access/Jet DB, Tables, Delete Tables, Compact Database

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

I Need A Sp That Will Update A Field Based On Conditions

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

Calculating A Field Value Based On Other Records

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

Retrieving Data Based On Value In A Field

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

Can I Split A Field Based On A Character?

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

Combine Text Field Based On Value

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

Trigger Based On Specific Field??

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

Counting Based On A Field Combination

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







Copyrights 2005-15 www.BigResource.com, All rights reserved