How To Split A Field Into Two Fields
I have the following fields in table A:
GL_ID| Date |GL_Name_VC | Amount |Period_TI|Year_SI
===============================================================
1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005
===============================================================
1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006
===============================================================
1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006
===============================================================
1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance
=====================================================================
01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000
01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0
01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0
01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |16,000
The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005
Debit = All positive amount
Credit = All negative amount
Net Change = Total Credit - Total Debit in Period 01
Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
View Complete Forum Thread with Replies
Related Forum Messages:
How To Split A Field Into Two Fields
I have the following fields in table A: GL_ID| Date |GL_Name_VC | Amount Period_TI|Year_SI =============================================== 1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005 =============================================== 1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006 =============================================== 1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006 =============================================== 1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006 The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below: Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance =============================================== 01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000 01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0 01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0 01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |6,000 The formula for the above calculated fields are as below: Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal Guys, hope someone out there can help me with the sql command for the above report?
View Replies !
UNION With A Max On Split Date/Time Fields
I have 2 tables, each with one ID field, a separate Date and Time fields and a number of other fields. The tables contain duplicates on the ID field. I want to do a UNION keeping only the record with the latest Date and Time. This would work: SELECT MyTab.myKeyField, Max(MyTab.myDate) AS myDate FROM (SELECT myKeyField, myDate from Table1 union SELECT myKeyField, myDate from Table2) AS MyTab GROUP BY MyTab.myKeyField But is only taking care of Date, not Time (some records have the same date but different times) The other problem is, when I add more fields, I have to include them in the GROUP BY clause, and this way I end up with duplicates (because some other fields have different values) Is there a way to do this?
View Replies !
Split Fields And Display Query Result
Hi, I'm having a problem in spliting the fields I need to ru the following query to join two tables and getting the output as shown. Query: select cusl.user_name, pmts.bill_ref_info, pmts.payee_acid, pmts.cust_acid, pmts.txn_amt,pmts.pmt_id from cusl, pmts where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive' Output: user_name bills_ref_info payee_acid cust_acid txn_amt SAMEER ALLA0210181#123456#Amita 378902010021095 383702070051411 1.000 16318 SAMEER BARB0GNFCOM#6788990#Vikram Kalsan 378902010021095 383702070051411 1.000 16327 SAMEER BKID0000200#378902010099678#Vikram 378902010021095 383702070051411 1.000 14031 SAMEER undefined#123456789123456#Vikram 378902010021095 383702070051411 1.000 13918 Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query. Can this be done? Please guide me on this...
View Replies !
How Can I Split Fields And Depending One Column Decide The Foreing Key
I´m wondering how to solve the following scenario with SSIS I have a CITY table and a STATE table, I have to load a file with the information regarding to the CITY: the state table is like this: StateCode(PK) stateLegalCode stateName ============= ============== ========= 1 01 Florida the city table is like this: citycode(PK) cityLegalCode cityname StateCode(FK) ============ ============= ======== ============= 1 1001 Quakertown 1 the file has the following information cityLegalCode cityName ============= ======== 01-1001 Quakertown ... how can I load the file into CITY table: 1-) with the file's cityLegalCode I have to split the string and if the two initial digits are 01 the registry must have 1 in the StateCode(FK). how can I do something like that using SSIS??? thanks
View Replies !
Split A Field
Whats the best way to do the following?Field1 in Table1 contains numbers and characters seperated by #Examples: aaa#01, kjhkjhjh#21 and jlkjlkj#123How can I create two new fields in Table1, one containing what is tothe left of the # and the other what is to the right?Regards,Ciarán
View Replies !
Separating One Field Into Two Fields Based On A Character In The Field
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 Replies !
How To Split The Field In The Sql Query?
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
View Replies !
Can I Split A Field Based On A Character?
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 Replies !
Split Up Comma-delimited Field
I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas. Any ideas how I could do this in T-SQL?
View Replies !
Split One Field In Multiple New Rows
HiHo, just a beginners question: I have the following row with 2 fields: Field 1: Task A Field 2:´ 1;2;3;4 The number of semicolon divided elements in Field 2 is variabel. I would like to create new rows like: Row 1 Field 1: A Field 2: 1 Row 2 Field 1: A Field 2: 2 Row 3 Field 1: A Field 2: 3 Row 4 Field 1: A Field 2: 4 I think I should use a Foreach Loop. But I don't exactly how to do it? best regards Chris
View Replies !
Conditional Split On Field In Csv File
I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually. Thanks
View Replies !
Split Address Into Multiple Field
i want to write a code that can split the addresses into multiple fields. anyone can point me out where to start? lets say i have "12 north plaza boulevard apt.16" and i want it to become: address_number 12 suffixA North street_name plaza blvd suffixb apt16
View Replies !
One For The SQL Gurus: Split A Delimited Field Into Rows
Hi. I'm trying to write an SQL Query that will take a delimited field and return each item as a row. Example Take the AuthorizedRoles and TabID fields from the Tabs table AuthorizedRoles TabID 0;11;__________1 0; 15 ;17;______6 -2;____________7 I would like to return a unique record for each Authorized Role AuthorizedRole TabID 0____________1 11___________1 0____________6 15___________6 17___________6 -2___________7 Any ideas? Cheers Dave
View Replies !
Two Int Fields Or One Varchar Field
I am setting up a database that will receive a lot of data from twoseparate telephone centers, the log table will in a short time haveover 1 million lines, and I was wondering if I should use 1 identifyfield or two:case 1:[Id] [int] IDENTITY (1, 1) NOT NULL[ServerId] [int] NOT NULLcase 2:[Id] [varchar(20)] IDENTITY NOT NULLWhere in case 1 I would just use a combination of Id and ServerId toidentify the line, where in case 2 I would have the Id field a varcharthat would look something like A-000001, A-000002 for server 1 andB-000001, B-000002 for server 2Which solution will be faster when searching for a record when thewill have over 1 million lines?
View Replies !
Break One Field Into 3 Fields
Hello all, I have a problem. I have the address like following: 204 east 40th street,brooklyn,ny 11229 I have to break this field into four fields: address,city,state,zip address should be: 204 east 40th street city should be: brooklyn state should be: ny and zip should be: 11229 How would I be able to do that? Any help will be appreciated. Thanks in advance!!
View Replies !
Same Field From Several To Seperate Fields Of A Single Row
Hi, I'm struggling with this. I'd like to perform a joined query from two or more tables and take the same field from several rows of one table into seperate fields of a single row in a new table. Like this: table 1 uidunameuloc 1mehere 1methere 2youhere 2 youthere table 2 uidulocuparam#uparamval 1here1a 1here2b 1here3c 1there1d 1there2e 1there3f 2here1g 2here2h 2here3i 2there1j 2there2k 2there3l result in table 3 uidunameuloc uparval1 uparval2 uparval3 1mehere a b c 1methere d e f 2youhere g h i 2 youthere j k l uparam# field in table 2 always the same sequence Any ideas???? Thanks...
View Replies !
How To Concate 2 Ore More Text Fields Into One Field?
Hello, I hope someone has already done this, but I have a table with a text column- example ColA, now i want to run a query to select the ColA in this table and combine the results of ColA into a ColB in another table. Something like - Note: the codes below doesn't work!! DECLARE @ResultID as int DECLARE @AccID int DECLARE _rows CURSOR FOR SELECT AccID FROM tableA FETCH NEXT FROM _rows INTO @AccID WHILE (@@fetch_status <> -1) BEGIN UPDATE TableB SET Report = Report + (SELECT txtField FROM tableA WHERE AccID = @AccID) WHERE AccID = @AccID FETCH NEXT FROM _rows INTO @AccID END Thanks in advance
View Replies !
Joining Two Fields To Single Field
If I have a database table with the following columns: ID Other_ID Description And I want to join the two ID fields to one field in another table that contains the following fields: ID Name How would i do that? Here is some sample data and what I would like returned TABLE1 ID Other_ID Description row 1 1 2 Number1 row 2 3 1 Number2 TABLE2 ID Name row 1 1 John row 2 2 Bob row 3 3 Bill I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like: John Bob Number1 The only way around it now is that I store Other_Name in Table1. Thanks.
View Replies !
Non-existing Field In Fields Collection
I'm developing a report in RS 2000 that uses two datasets. The first dataset provides data to a drill-down report on the top part of the screen and the second small dataset provides some data for additional summary information on the bottom of the screen. I'm getting a runtime error "An unexpected error occurred in Report Processing. The expression referenced a non-existing field in the fields collection." Of course, I've edited every expression I can think of: text boxes, data grid, parameters, filters, drill-down lists... I think I've gone through every object on the report but I can't isolate the bad expression. Can anyone suggest a better way to debug this type of error? It compiles fine, but fails in runtime. thanks.
View Replies !
Update Field Based Upon Other Fields
Hi, I have a table with eight (8) fields, including the primary key (rfpid). Three of the fields are foreign keys, which take their values form lookup tables. They are int fields (pmid, sectorid, officeid). One of the fields in this table is based on putting together the descriptive field in the lookup table for sector (tblsector). The two other fields to be part of this string are the rfpname and rfpid. This creates the following string: rfpsector_nameproposalscurrent_year fpname_08_rfpid NOTE: The words rfp, proposals are words that have to be part of string; the slashes are to also appear. current_year would be defaulting to datepart = year (2008) The part that has the last two digits of the current year then the underscore and then the rfpid should be connected by an underscore to the rfpname. I am at a loss and would greatly appreciate any help. Thanks
View Replies !
Join Several Fields To Create New Field
What would be the recommendation/approach in creating a seperate field in which joins several differate fields together. I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d. So instead of: a 122 b joe c st I would have: d 122 joe st Thanks!
View Replies !
Updating A Field With Info From Other Fields
I am trying to make a field that has info from othe fields Table = Page0 Fields = LName, FName, SS I want a new field (Folder) to be all three fields.. for example LName= Smith FName= John SS= 1234 I want to update Folder = Smith_John_1234
View Replies !
Display All Fields Including The Field Has Dup
I have one table where i want to use aggregate function for duplicate rows and also be able to select all fields to display. How would i do that? Here is my query: select Z, count(*)as num from Table group by Z having count(Z) > 1 ----- this returns 213 rows Select Z, A,B,C,D From Table Group By Z, A,B,C,D Having Count(Z)>1 ----This gives me nothing Hope I am explaining this correctly as to what I want. Thanks, Saru Brochu
View Replies !
Combine Column Fields Together Into One Field
Hello to All, I needs help to combine these together but how would I eliminate necessarily zero in front of "PropertyHouseNumber". Table: DirectHome Column fields....... PropertyHouseNumber, PropertyStreetDirection, PropertyStreetName, PropertyMODE 0000001091 , W , 000026TH , RD Thank you RV
View Replies !
Displaying Two Fields In One Field When They Are Seperate Datatypes
Hi All. I apologise if i have not posted this in the correct Topic before i start. But was uncertain where to post this query. This is my first project in ASP.NET, MS Visual Web Developer 2005 Express and SQL Server 2005 Express. I have relatively little experience, so please bare with me. I have the table below:- Column Name Data Type OrderID intOrderNo intInvoiceNo varchar(50)PurchaseDate smalldatetimeCost moneyInsure money I wish to have a dropdownlist on an aspx page display the OrderNo and the Cost e.g. 12345 £3.50. I have tried the SQL Statement below but that just calculates the addition of both. SELECT OrderNo + ' ' + Cost AS Expr1 FROM [Order] Do i have to convert them both to string/varchar first? If so, how? Thanks for your replies.
View Replies !
Changing One Field Updates 3 Other Fields, Use A Join?
i currently have a table like this.. user_id username app_id app2_id app3_id app4_id 1 john 3 4 5 6 2 mike 4 5 6 6 3 manager 4 5 6 6 4 vicepres 5 6 6 6 5 ceo 6 6 6 6 6 board 6 6 6 6 the basic pattern is... a user has approvers, and those approvers have approvers as well... i have 4 columns of approvers.. and if my first approver is the manager, then my second approver will be the managers approver and my third approver will be the managers approver's approver and so on.. on my actual page, i have select fields for the app, app2, app3, app4 and i need it so that when i change the very first app, it'll automatically update app2, app3, and app4 any ideas how i would do this? im pretty new to sql but im thinking i would use some type of join? thanks
View Replies !
Parsing A Field Data And Populating Other Fields
I have just started to look at SQL and have a theory question that I could apply to a test I want to run. I have some legacy data from a previous project and the database was not designed properly (in my opinion). They have ONE field to capture City and State information. All the data is formatted City, State . Does SQL have commands that can look at data in a field, strip out info before and info after a comma and then write that to other fields? So, I would like to normalize this to take the data in a field called CityState and parse it, trim it and then populate two new fields 1) City and 2) State. Thanks for your help! Scott
View Replies !
Concatenating Three Longint Fields Into A Date Field
Good afternoon one and all, I have the following problem that I can use some help with : I have a table in a linked server that has the date stored in three fields (i.e. day,month and year (I have no idea why)). I would like to concatenate these three fields together into a datetime format in a SQL statement Something like SELECT ([stc_dd] & '/' & [stc_mm] & '/' & [stc_yy]) AS stkdate (the above line does not work) Hope that is clear, thanx in advance for any and all help Gurmi
View Replies !
JOINing On CHAR Fields Of Different Field Lengths
Can someone comment on why joining two tables on CHAR fields of different lengths would generate unexpected results? I had an issue where I ran an update that used an inner join on two tables. The field I used in the join was char(50) in one table and char(13) in another table. The result gave bad matches. After changing the field types both to varchar(30), the problem was eliminate. Any comments on this would be appreciated. Rye Guy
View Replies !
Help With SQL Statement To Find First And Last Populated Field In String Of Fields
Hello all,I'm at a loss on how to do this. We're using MS SQL 2000 Server and Ihave a list of fields I need to find the first and last entry for.Here's an example of the table:Number - VarChar(10)Jan - IntFeb - IntMar - IntApr - IntMay - IntJune - IntANd it'll look something like this:NumberJanFebMarAprMayJun12322001901922012032054432433322 4565423754694665And I need to create a table with this:NumberFirstLastDifference123220020554432433456235423754665-89I'm not sure if this'll copy over correctly, but I have gaps in thedata so I can't just say Jun-Jan, but I need tofind the first fieldwith data and last field with data, then find the difference of these.Suggestions? Is there a loop or something I can do in TSQL that'll dothis? I'd like to do this in Query Analyzer since it's just a one-timereport. Thanks --Alex
View Replies !
Combine And Convert Int Fields To Create One Field, Should Be Simple
I want to combine two of my fields somewhat like when you combine to char fields and concatenate them. But these two fields are int. How do i do that? Here's my query right now: Code Block SELECT AutoID, CONVERT(Varchar(Mars_Calender_Year )+ CONVERT(Varchar(Mars_Calender_Period_Code) as MarsId FROM NavisionReplication.dbo.Tbl_Mars_Calender ORDER BY Mars_Calender_Year DESC what am i doing wrong?
View Replies !
Combine Separate Date && Time Fields Into One Datetime Field?
Good morning.I am importing an XLS file into one of my tables. The fields are:Date Id Time IO12/22/2006 2 12:48:45 PM 912/22/2006 16 5:40:55 AM 112/22/2006 16 12:03:59 PM 2When I do the import, I get the following:Date Id Time IO12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 212/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 112/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2Here are my doubts:1. Would it be better to combine the Date & Time fields into onecolumn? If so, how?2. What issues or problems might I have when I program SQL reports, ifI leave the fields as they are?Any comments or suggestions will be very much welcomed.Cheers mates.
View Replies !
Trying To CONCAT A TEXT Field With 2 VARCHAR Fields - Brick Wall.
Hi all. I have been going round and round for the past 2 days on this and would appreciate any help. In a view select statement, I need to concat 2 varchar fields with a text field. If I CONVERT the TEXT field to VARCHAR, only the first 30 characters of the field appear in the result set. If I convert the VARCHAR fields to TEXT, I get an error that I cannot CONCAT TEXT fields. I'm not sure what to do. Can someone please offer some assistance? Thanks in advance! Steve
View Replies !
Combine Separate Date && Time Fields Into One Datetime Field?
Good morning. I am importing an XLS file into one of my tables. The fields are: Date Id Time IO 12/22/2006 2 12:48:45 PM 9 12/22/2006 16 5:40:55 AM 1 12/22/2006 16 12:03:59 PM 2 When I do the import, I get the following: Date Id Time IO 12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2 12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1 12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2 Here are my doubts: 1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages? 2. If I don't combine them, should I use varchar or datetime data type? 2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are? Any comments or suggestions will be very much welcomed. Cheers mates.
View Replies !
How To Pull Data Into A Datagrid From The Same Table, 2 Fields, And Display Them As 1 Field In The SqlDataSource?
Hi, Im trying to pull data from 2 fields in the same table into a SqlDataSource that feeds into a GridView, and display them as 1 field in GridView? I have a database table that has entries of users and their friends. so this tblFriendUser has a column called UserName and another column called FriendUserName. I am trying to get a list of friends for that particular user. Note that if User1 initiated the friend request, he will be listed as UserName and his friend as FriendUserName, but if his friend initiated the friend request, it will be vice versa: him being the FriendUserName and his friend the UserName. So I want the following 2 queries run and merged into one query in order to return 2 columns only: UserFriendID & UserName, is that possible? Is my design bad? Any suggestions/advice would help! Thanks a lot! SELECT UserFriendID, UserName FROM tblUserFriends WHERE (UserName = @UserName); SELECT UserFriendID, FriendUserName AS UserName FROM tblUserFriends WHERE (FriendUserName= @UserName);
View Replies !
Database Triggers - SQL Server - Fields Only Allowed If Listed In Another Field In Another Table
I would like to ensure data integrity in a column (actually multiple columns will need a trigger) in my table(s) by setting up a trigger which allows an update of my database field only if the value which is being written to the field in the database exists in another column (in another "check" table).eg. I only want values "Yes", "No" or "" in many of my fields, which I store in a column named "YesNoBlank" in another table.Does anyone know the easy way to do this? / Syntax for the trigger?
View Replies !
View Field Content Update When &"real&" Table Fields Change
Hi,I use view to join difference table together for some function. However,when the "real" table fields changed (e.g. add/delete/change field). Theview table still use the "old fields".Therefore everytimes when I change the real table, I also needed open theview table and save it by SQL enterprise manager manually for update theview table field.Can we use a SQL command or other method to update it directly?Regards,Silas
View Replies !
Subreports: Parameter Value Dropdown Shows Sum And Count Fields But Not The Actual Data Fields.
I have just started using SQL Server reporting services and am stuck with creating subreports. I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields. For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport. When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field. Am I missing something here? Any help is appreciated. Thanks, Sirisha
View Replies !
Public Overridable ReadOnly Default Property Fields() As ADODB.Fields
sir I have got this error message to establish connction with recordset vb .net, Can you please rectify this Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields' my code like this rs = New ADODB.Recordset rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic) If txtUserName.Text = rs.Fields.Append(userid) Then MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation") End If thanks
View Replies !
Update Fields With Searched First Date Record Fields
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View Replies !
Search Multipe Fields, Compounding Fields, Like, Contains...?
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View Replies !
|
TRACKER
