I have two related tables in my SQL database that I wish to join as follows:
------------------------------------
tblCustomers
ID (pk)
Name
etc.
tblCustomerManagers
ID (pk)
CustomerID (fk)
Manager (this *is* an fk but for the purposes of demonstration is
not)
StartDate (indicates the date upon which the manager took / is taking
control of the company)
------------------------------------
Example entries are:
tblCustomers
1 Microsoft
2 Symantec
3 Borland
tblCustomerManagers
1 1 Barry 01/01/03
2 1 Peter 01/07/03
3 2 Norman 01/02/03
4 3 Terry 01/01/03
5 3 Peter 01/07/05
------------------------------------
What I want to do is extract, in one query, a list of all customers and
their *current* associated manager, so the result set today would be:
Microsoft Peter 01/07/03
Symantec Norman 01/02/03
Borland Terry 01/01/03
Currently I have:
SELECT [Name], [Manager], [StartDate]
FROM tblCustomers
INNER JOIN tblCustomerManagers ON tblCustomerManagers.[CustomerID] =
tblCustomers.[ID]
WHERE [StartDate] <= GETDATE()
ORDER BY [Name], [StartDate] DESC
but this obviously returns multiple entries for customers having managers
prior to today eg:
....
Microsoft Peter 01/07/03
Microsoft Barry 01/01/03
....
I know this is a simple question but I cannot think of a way of doing it
without making the query extremely complicated.
Hi i'm developing a website for my final year project with ASP.net and SQL Server 2005 databse. The problem i'm facing since last week is that, i'm unable to use "Select" query to fetch the data from my SQL Server database. i've used the following code to INSERT the database which is successfull.SqlDataSource sample= new SqlDataSource(); sample.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ToString();sample.InsertCommandType = SqlDataSourceCommandType.Text; sample.InsertCommand = "INSERT INTO Table_Name(Fields) VALUES (@Fields)"; sample.InsertParameters.Add("Fields", FieldsTxtBox.Text);
It successfully updates my database fields. I've tried many codes that i've found over the internet but none worked. can anyone please tell me how to use SELECT query in the fashion as the above one? I shall be very thankfull for the help. Regards, Jigzy
AA Guyz i want to calculate class position of students from a table.Sample data is as follows...Roll # - Name - Marks1 - ABC - 602 - DEF - 603 - GHI - 574 - JKL - 555 -MNO - 506 -PQR - 53The query should return the following result.Roll # - Name - Marks - POSITION1 - ABC - 60 - 12 - DEF - 60 - 13 - GHI - 57 - 34 - JKL - 55 - 45 -MNO - 50 - 56 -PQR - 53 - 6I want query in MS SQL Server 2000
For example if I have a winform with a textbox and search button. If I type in ABCD1234. I won't know what the data is coming into the X Table. So there will be hundreds of description data in the description field in X table.
So in the X Table, there are ABCD1234,ABCD_1234,ABCD/1234,ABCD 1234
But I will only pick this 'ABCD1234' record up, and the rest won't be picked. They are variations of the same description. It needs to pick also ABCD_1234,ABCD/1234,ABCD 1234
If also type in 5678, in another column like code in X Table. It would have 005678,05678,56780. But I will only pick this '5678' record up, and the rest won't be picked. They are variations of the same code. It needs to pick also 005678,05678,56780
I have tried LIKE, FREETEXT and CONTAINS.
But another idea is a table of alternate descriptions for those that you know about and look those up and search for them all. But how can that be done?
create table testedit ( editid int ,Tguid varchar(20) ,ttime numeric(4,2) ,numApp int ,numOrg int ,custid varchar(1) )
INSERT INTO testedit values(1,'ABC',12.52,40,11,'Z') INSERT INTO testedit values(2,'ABC',12.52,500,33,'Z') INSERT INTO testedit values(3,'ABC',12.53,500,33,'Z')
Out of this records I would like to select only the 1st and the 3rd record. ie. My result should only have the below rows
I have two tables in my database. I want to insert date and no.of hours worked on that day for a particular project. So in a week if end user enters 7 dates and hrs for each day...... i have to insert those values into the table against one project only. Can any one please help me out how to run insert query for 7 times (in a week) with different parameters
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
I need to create either a sql query or a procedure and i am not sure how to go about it.Simply i have two fields in a table. 1 for products values and 1 for how many of that product was purchased.What i am wondering is how to calculate this in a sql query.can someone help pleaseIlan
I am deleting a database but I am not seeing any entry in server logs or in event viewer. and same thing is happens when I am deatching a database.Can any body tell me the way through which I can know when a particualr database was deleted or deatched.
I am trying to connect MS SQL 2000 with C language using ODBC drivers in win 2k environment
i have a UTF 8 string stored in a nvarchar field of the MS SQL table and i am trying to retrieve this field from C and store it in a txt file everythin is working perfectly fine the probs is that the UTF 8 string is not getting being retrieved correctly
Hello All, I have 2 tables Trans & Slmast In Trans Tran_type,Tran_amt,Slcode ---------------------------- VES,10000.00,AB01 DBN,20000.00,AB01 CRN,30000.00,AB01 BRT,40000.00,AB01 DBN,10000.00,AB02 CRN,50000.00,AB02 BRT,20000.00,AB03 CRN,30000.00,AB03
In Slmast Slcode,Sldesc -------------- AB01,ABC Ltd AB02,CAB Ltd AB03,BAC Ltd
All transaction related datas will be in Trans for which Slmast is the master. I want to sum those records based on slcode,tran_type for slcode which does not have tran_type with 'VES'
Select (Loccode+Glcode+Slcode) as LGS,Refno,Refdt Into #VST from Trans_Transnarr Where Trtype='VES' and (Refdt>=Convert(Datetime,@SDt,103) and Refdt<=Convert(Datetime,@EDt,103)) and (Cancflg Is Null or Cancflg='R') and Loccode=@Loccode and Glcode=@Glcode
Select Sl.Sldesc,Tr.Loccode,Tr.Glcode,Tr.Slcode, X.Refno,Convert(Varchar,X.Refdt,103) as Refdt, Sum(Case When Tr.Trtype='VES' Then Tr.Tramt Else 0 End) as VES, Sum(Case When Tr.Trtype='DBN' Then Tr.Tramt Else 0 End) as DBN, Sum(Case When (Tr.Trtype='CPM' or Tr.Trtype='BPM') Then Tr.Tramt Else 0 End) as Refund, Sum(Case When (Tr.Trtype='JNL' and Tr.Drcrflag='D') Then Tr.Tramt Else 0 End) as JNLD, Sum(Case When Tr.Trtype='SPS' Then Tr.Tramt Else 0 End) as SPS, Sum(Case When (Tr.Trtype='CRT' or Tr.Trtype='BRT') Then Tr.Tramt Else 0 End) as Receipt, Sum(Case When Tr.Trtype='ADV' Then Tr.Tramt Else 0 End) as ADV, Sum(Case When Tr.Trtype='CRN' Then Tr.Tramt Else 0 End) as CRN, Sum(Case When (Tr.Trtype='JNL' and Tr.Drcrflag='C') Then Tr.Tramt Else 0 End) as JNLC, Sum(Case When Tr.Trtype='SRT' Then Tr.Tramt Else 0 End) as SRT Into #VOS from Trans_Journal Tr,Slmast Sl,#VST X Where (Sl.Loccode=@Loccode and Tr.Glcode=Sl.Glcode and Tr.Slcode=Sl.Slcode) and (Tr.Loccode+Tr.Glcode+Tr.Slcode)=X.LGS and (Tr.Cancflg Is Null or Tr.Cancflg='R') Group By Tr.Loccode,Tr.Glcode,Tr.Slcode,Sl.Sldesc,X.Refno,X.Refdt
ALTER TABLE #VOS ADD OPDR Float,OPCR Float
UPDATE #VOS SET #VOS.OPDR = S.DRAMT,#VOS.OPCR = S.CRAMT FROM #VOS, (SELECT GLCODE,SLCODE, (CASE WHEN DRCRFLAG = 'D' THEN OPBAL ELSE 0 END) AS DRAMT,(CASE WHEN DRCRFLAG = 'C' THEN OPBAL ELSE 0 END) AS CRAMT FROM OBDATA WHERE LOCCODE=@Loccode and GLCODE =@Glcode) S WHERE #VOS.GLCODE = S.GLCODE AND #VOS.SLCODE = S.SLCODE
Always wondered, now I have the time to ask - we currently backup ALLdatabases and these seem to all grow in size, the MSDB BAK files are now35mb each and growing!!! Can we remove these from our Enterprise Manager?- Master- Model- MSDBCheers, @sh
Hi allI am creating a web services application in Visual Studio Dot netwhich links to a MS SQL database.I need to create a form which lets the user pick his field ofchoice(doesnt matter what it is..) using drop down boxes,radio buttonsetc and query the database according to what he has picked andretrieve only those columns and rows.The thing is, I do not want to hardcode the queries usingOleDbDataAdapter or SqlDataAdapter, but want the query to be generatedon the fly, once the user picks his fields from the form.I'd greatly appreciate any help.Thanks,m
I am writting a very simple ASP.NET code, I am simlply trying to establish a connection with the database in SQL Server 2000 and than closing the connection but still I am getting an exception,the name of the database is mydatabase, SQL Server 2000 is running in the windows authenticationfiltered=SSPI. The name of SQL Server 2000 running onfiltered=xyz, I checked the name from SQL Server Service Manager.Also I would like to state here that I also tryed to establish a connection using C# and the connection was successfully establish which means that there is no problem with SQL Server also I sucessfully established the connection with MS Access and my ASP.NET application which proves that there is no problem with IIS also.Also please check that the connection string which I am providing is correct or not.The exception which I am getting is as follows: /////////////////////////////////////////////////////////////////////////////// An exception occured while establishing connection Login failed for user 'xyzASPNET'. //////////////////////////////////////////////////////////////////////////// Please also note that I have a user with the name xyz/Administrator in the logins of Sql Server 2000. Also note one more thing that the name of my PC is xyz, I think that there is some problem with the connection string which i am providing so please especially check the connection string that either I have missed some thing or some thing has gone wrong,also please tell that are there any security restructions for ASP.NET to access SQL Server 2000 or some thing like that which I have to remove.The code is as follows: ////////////////////////////////////////////////////////////////////////////////////// <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %>
The same above code is also present in the attachement file data.aspx , please guide me where I am making mistake and please tell me that how can I correct this mistake so that the code executes correctly.Also please tell that what things should be included in the connection string and also please check the connection string of this code.
Need your help !! I have a database say for e.g Test_db . I have data in the form of Image and text in the database . The image data or symbols corresponds to the right text data . This database is being replicated to other servers . The servers are SQL Server 7.0 with SP 3 .
But when this database is replicated , the subscriber does not get the data in the right form . The image and text fields do not correspond properly as it is on the publisher . But sometimes it works .
This is a strange problem and i could not make out what could be the exact problem . Does any body have insight of this problem pl . Any kind of help will be appreciated .
How much RAM is necessary for concurrent SQL connections? My server has 2 Xeon processors, 1GB RAM, a 10GB database size on a TCP/IP box. CPU utilization can run 80-90% and pages/sec can sometimes jump up to 40, especially is the users are running reports from the SQL database. I've been told by some people that I have enough RAM to support the 130 SQL user connections. Others I've been told that I need at least 40MB RAM per user (which means that I'm about 4GB short of RAM). Based on previous entries, I obviously need more, but how much more is the question.
Hi, I have a doubt..... I have a text field that is 255 bytes in lenght and I want the contents of this full field to be split.....on various gouds which may be ',' or and or 'And' etc
This field stores all the skills...
So i have created a search module ....but this search is taking awfully long as u know text field searches are tedious...
Can u please suggest a way I could parse/split the text field to enable faster search....
I am trying to create a script that will return information from 4 different tables in a database using different where clauses.
This is working correctly, however the primary table the query uses has multiple entries for different people and i only want to return the last row per person. I have got this to work using a MAX(t.column) statement, but when used in conjunction with all the where clauses things go wrong.
This is what i have
Code:
CREATE VIEW Firereport1 AS
select max(t.punch_id) as punch, e.EMPLOYEE_FIRSTNAME, e.EMPLOYEE_LASTNAME, c.CLOCK_NAME, t.PUNCH_TIME, d.DEPT2_DESCRIPTION
FROM TM_PUNCH t, EMPLOYEE e, COMM_CLOCK c, DEPT2 d
WHERE t.PUNCH_EMPID = e.EMPLOYEE_ID and e.EMPLOYEE_ONCLOCK <> 'False' and e.EMPLOYEE_LASTTIMELOC = c.CLOCK_ID and e.EMPLOYEE_LASTDEPT2 = d.DEPT2_ID
group by e.EMPLOYEE_FIRSTNAME, e.EMPLOYEE_LASTNAME, c.CLOCK_NAME, t.PUNCH_TIME, d.DEPT2_DESCRIPTION
GO
This returns multiple entries because there are multiple unique entries for t.punch_time.
What i was thinking is i need a where statement on t.time_punch, d.dept2_description, c.clock_name that says something along the lines of where return t.punch_time where t.punch_id=punch (**substitute correct column names**)
Hi folks; I have two SQL machines; SERVER1 (Publisher) and SERVER2(Pull Subscriber). Every thing seems to be working but: I've just imported 17,000 records from another SQL database into a published table of the production server SERVER1 using IMPORT-EXPORT wizard. Now the subscriber doesn't get updated with these records. When i run the merge-agent; the agent just stops with success without pulling the data.
When i set the agent-profile to ROWCOUNT-VALIDATION; it fails at this table.
Publisher db at SERVER1 has recovery-model =FULL Subscriber db at SERVER2 has recvery-model=BULK-LOGGED
