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.
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.
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.
I am trying to join two tables and the datetime fields need to be part of the join. They contain times in the fields too. I just need them to join on the date part though. Is there maybe a way to just return the mm/dd/yyyy format and join on that? Any and all help is appreciated.
I have a sql 2005 dev ed running an application developed in vs.net 2005 C# I have several gridviews which have a field call first name and a field called lastname I need to put both the firstname and lastname in the same cell or colum row. I do not now how to join these. Can some one help me with a SQL query string that will do this for me.
Question.I have a new table that I am adding to a script that I wrote. Thistable has 3 fields, the first 2 fields are used in the on statement asbeing = other fields in the script.The first field always has data in it, but the 2nd field is sometimesnull.So my problem is if both fields have data in them and they both matchto the data in the fields that I am linking them to, then it returnsthe 3rd field without a problem. However if the 2nd field is null thenit is returning a null for the 3rd field. I have checked and the fieldthat I am linking to is null also.So if I haveselect t1.field1, t1.field2, t2.field1, t2.field2, t2.field3from table1 t1join table2 t2on t1.field1=t2.field1 and t1.field2=t2.field2with 2 records in each tabletable1: record1: data, datarecord2: data, nulltable2: record1: data,data,datarecord2: data,null,datawhat I get from the script isrecord1: data, data,data,data,datarecord2: data,null,data,null,nullI would expectrecord2: data,null,data,null,dataI hope this makes sense, I didn't want to post the entire actual scriptas it is about 150 lines long.Thanks in advance.
I have a simple query which displays items from inventory with their latest annual test date. I want to create another unique reference in my results to use as a certificate number. The number should be a combination of the item+month+year from the test date. What is the easiest way to accomplish this?
My query and my desired results are below:
select item, test_date from inventory where cat = 'TELE' and itemised_status > 15
I would like to know if it's possible to return a single record by joining the tables below. [Persons] PersonID [int] | PageViewed [int] =============== ================= 1 10 2 5 3 2 4 12
[PersonNames] - PersonID JOINS Persons.PersonID PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int] =============== ============== ======================= =================== 1 1 Samantha Brown 5 1 2 Samantha Green 10 2 3 Richard T 10 3 4 Riko T 0 4 5 Sammie H 0
[AltNames] - backup for searches caused by common spelling mistakes AltNameID [int] | AltNames [nvarchar] ================ ============================= 1 Sam, Samantha, Sammie, Sammy 2 Riko, Rico
[PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID] NameID [int] | AltNameID [int] ============= ================ 1 1 4 1 3 2 This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch ========= ============ ================= ================= 1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy 2 5 Richard T Richard T 3 2 Riko T Riko T, Riko, Rico 4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy
[MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames].
The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field. Any help would be greatly appreciated. Thanks, Pete.
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?
Hi,All: I am using Sql 2000 db to storage my data,and I have a table including a column named "ScanDate" (Type: SmallDateTime),Now I just want to save the current system date to this column when I save my system data. when I saved it and I found that column value include the time data,not only date . So How can I just save date to my samlldatetime typed column ? thanks in advanced!
I have a very complex (for me, anyway) data transformation problem.
I've been given a flat-file of physician data from another system which must be automated for entry into the SQL server on a regular basis.
This was no problem until we discovered that several fields (all of which we wanted to use) had multiple pieces of information in them, separated by semicolons.
Well, this didn't seem to be too big of a problem, so I wrote a DTS activeX script to handle it. This is what I originally wrote:
'******************* Function Transform() 'Declare variables Dim strOffice Dim strOfficeNew Dim cChar Dim x Dim y Dim z
'Scrub values into new rows strOffice = DTSSource("Col050") x = 1 y = len(strOffice) z = 1
While x <= y cChar = Mid(strOffice, x 1) If cChar <> ";" Then strOfficeNew = strOfficeNew & cChar Else DTSDestination("Phys_No") = DTSSource("Col001") DTSDestination("Addr_No") = z DTSDestination("Addr_Office") = strOfficeNew strOfficeNew = "" z = z + 1 End If x = x + 1 Wend
'Insert final record after last semicolon If strOffice <> "" Then DTSDestination("Phys_No") = DTSSource("Col001") DTSDestination("Addr_No") = z DTSDestination("Addr_Office") = strOfficeNew End If
Transform = DTSTransformStat_OK End Function '*********************
This, of course, didn't work. WHat I got was the last part of the parsed data, which for the first record, was the second Address in the field.
I searched around, and found the following script that is supposed to allow multiple rows off of a single row, but I can't seem to merge the two and still get the data out clean.
'********** Dim nCounter nCounter = 4
Function Main()
if nCounter > 0 then Main = DTSTransformStat_SkipFetch DTSDestination("PatientNumber") = DTSSource("PatientNumber") Select Case nCounter Case 1 DTSDestination("PhysicianType") = "Admitting" DTSDestination("PhysicianId") = DTSSource ("AdmittingPhysician") Case 2 DTSDestination("PhysicianType") = "Attending" DTSDestination("PhysicianId") = DTSSource ("AttendingPhysician") Case 3 DTSDestination("PhysicianType") = "Referring" DTSDestination("PhysicianId") = DTSSource ("ReferringPhysician") Case 4 DTSDestination("PhysicianType") = "Consulting" DTSDestination("PhysicianId") = DTSSource ("ConsultingPhysician") End Select nCounter = nCounter - 1 else nCounter = 4 Main = DTSTransformStat_SkipInsert end if End Function '**************
I'm not a VB Script expert, so there's probably something very simple that I'm missing here... if someone could point it out, I'd be greatly appreciative.
How can we create a DB for a single attribute such as ORDER DETAILS, CASH RECEIPT, TAX INVOICE having more than 2 text fields.
Also, in every form attribute such as order id is not present - in order identify the same as a primay key. So, which other attributes or fields can be considered as a primary key.
Hi I need to export data to CSV file from Sql Reporting Services. I am including single quotations in my view to display in SRS for string fields. After export if we open in Notepad, for string field it is adding multiple quotations.
How to add single quotations for string fields - CSV files,
need help on update from one table to another like this this is my first table
tb_all_holiday
id
fname
Start_Date
End_Date
val_holiday
111 aaaa
15/03/2008 21/03/2008
1
222 bbbb
02/05/2008 09/05/2008
3
333 cccc
03/04/2008 15/05/2008
4
333 cccc
29/04/2008 07/07/2008
1
444 dddd
01/05/2008 02/05/2008
1
444 dddd
09/05/2008 19/08/2008
1
555 EEE
09/07/2008 09/08/2008
4
666 fff 10/09/2008 12/09/2008
1 this is my second table to insert into !
i need to insert to another table like this single row for each day from start_date TO END_DATE check each employee add row for each day insert all employee one after one
ID fname new_date val_holiday ----------------------------------------------------
While working with a vast variety of support projects, i find a sql design where all the fields in a single form (say about 100 fields which are dump data as they are not related to any reports and searching criteria) are dumped in a sql database column in a XML format. See below an example
I have the following result set but I want to stack or transpose the 3 fields into a single column. I may add more fields later, but right now I want to know what's the best and simplest way.
question need help how can i use this code below not for insert i need it for update another table but only if exist the link to the code in this FORUM http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=3208536&SiteID=1&Quote=True
Adam Haines wrote:
GPS,
Since you have such a dependency on dates and date calculations, you should implement a calendar table. A calendar table will make calculation such as this much more simplistic.
Calendar table link http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.
Note the calendar table I use is a little different than this one. The only thing you will need to change is isodate to dt.
Now the code to get the results you need:
Code Snippet declare @t table( id int, fname char(4), Start_Date datetime, End_Date datetime, val_holiday int ) insert into @t values (111, 'aaaa', '3/15/2008', '03/21/2008', 1 ) insert into @t values (222, 'bbbb', '05/2/2008', '05/9/2008', 3) insert into @t values (333, 'cccc', '04/3/2008', '05/15/2008', 4) insert into @t values (333, 'cccc', '04/29/2008', '07/07/2008', 1 )
select id, fname, cal.ISODate, val_holiday from @t t1
inner join Calendar cal on cal.isodate >= t1.start_date and cal.ISODate <= t1.end_date
I am developing an application for a big office which uses SQL Server 2000. Apart from my database, on that server, there are two databases by other companies. The administrator also has access to server but the client only wants him to backup the database.
I have two questions:
1) First of all (if it is possible) I would like to protect my own database from the other companies.
I don't want them to:
see the data in the tables (around 20 tables) make changes to the stored procedures (more than 100 stored procedures) be able to backup the database
2) The client will save sensitive data to the database (mainly currency amounts, salaries etc) which he wants to keep hidden. I am using float type for these fields and I would like to make the data encrypted. I could do it for nvarchar fields but changing these float to nvarchar would be time consuming.
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?
SELECT DENSE_RANK() OVER (ORDER BY clroot.Ctgry1.Descr ASC) AS Row1, Row_Number() OVER (partition BY clroot.Ctgry1.Descr ORDER BY clroot.Ctgry1.Descr, T1.Descr ASC) AS Row2, left(t1.ID,4)+right(t1.levelid,4) AS ERPID,T1.ID AS Ctgry1ID, clroot.Ctgry1.ID AS ParentID, T1.LevelID, clroot.Ctgry1.Descr AS Category, T1.Descr AS SubCategory,
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?
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?
How should I write the query which can return records with unique email address with any name attached?
The expected record set will be
name email --------- --------- <any> abc@abc.com <any> 123@123.com
I was thinking to select the distinct of email and just stuck at here. I encountered this problem few times and still cannot solve it. Any help will great appreciated. Thanks in advance.
I have a table with many fields but there is a single field that I do not want duplicates. If I index this specific field preventing duplicates, the entire record does not append. (The field in question is not keyed).
