Help With Query Required...

Jul 20, 2005

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.

Any help is appreciated,
Thanks,
df

View 2 Replies


ADVERTISEMENT

SQL Query Required

Mar 23, 2000

I have a table

Table1
------

StudentId Class Test1 Marks1 Test2 Marks2 Test3 Marks3
---------------------------------------------------------------------------
1 1 1 50 3 35 5 40
2 1 1 50 4 40 7 43
3 2 1 47 3 50 6 42


I would like to Insert this data into another table in this form

Table2
------

StudentId Test Marks
---------------------------
1 1 50
1 3 35
1 5 40
2 1 50
2 4 40
2 7 43
3 1 47
3 3 50
3 6 42


Can you write a SQL Query to accomplish this.

Thank you

View 1 Replies View Related

Help Required In A Query !

Jan 29, 2008


Help me in achieving a query €¦

My table look like

col1 col2
-------------------
a 1
a 2
a 3
b 1
b 2

and I want the output as€¦

col1 col2 col3 col4
----------------------------------------
a 1 2 3
b 1 2

The col2 values should be populated as different columns for the col1 value

Thanks
J


View 3 Replies View Related

Help Required For The Select Query :-(

May 18, 2008

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);
 
 int row = 0;
try
{
row = sample.Insert();
}catch (Exception ex)
{
// Response.Redirect ("Failed.aspx");
Label1.Text = ex.ToString();
}
finally
{sample= null;
}if (row != 0)
{Response.Redirect("Success.aspx");
}
else
{Response.Redirect("Failed.aspx");
}
 
 
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

View 3 Replies View Related

Query Correction Required Urgent

May 25, 2008

Hi All,

First of all its very very urgent. Secondly I am not an expert.

Now here is my problem.

I want to loop through my html files and want to save the data in my table column. here is the Code which need correction.

CODE:
USE [AdventureWorks]
DECLARE @cmd varchar(1000);
DECLARE @FileName varchar(100);
DECLARE @FilePath varchar(100);
SET @FileName = 'C: est'+@FilePath+'html';
SET @cmd = 'INSERT INTO myTable(FileName, FileType, Document)' +
'SELECT ' + @FilePath + ', FileType,
* FROM OPENROWSET(BULK @FileName, SINGLE_CLOB) AS Document'

EXEC (@cmd);
Select * from myTable

Just for practice I am using Adventureworks db. I am using SQL Express 2005.

My html files are named something like this:

AC0234.html
DB9803.html
CG4571.html

I cannot change my file names.

Thanks in advance.

Regards;

View 2 Replies View Related

Calculating Positions Query Required...........

Aug 31, 2006

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

View 1 Replies View Related

Help Required Please + SQL Server 2005 + LIKE Query

Jan 10, 2008


Hi Guys

I am in need of some assistance please.

I am using SQL Server 2005 and C#(winforms).

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?

Any ideas guys please? with examples if possible.

Thanks Newbie.....

View 4 Replies View Related

To Get Only Required Records - Select Query Help

Jul 19, 2006

I have records which are like below.


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

1,'ABC',12.52,40,11,'Z'

3,'ABC',12.53,500,33,'Z'

Any help?










View 6 Replies View Related

How To Run Insert Query For Required No.of Times With Different Parameters...?

Apr 22, 2008

Hi All,

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

Thanks,
Praveen

View 5 Replies View Related

T-SQL (SS2K8) :: Select Query To Pull Required Data From 3 Tables

Mar 19, 2014

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 .

Example:

Create Table #EmpIDs (
EmplID int
)
Create Table #EmpRoles (
EMPID int,

[Code] ....

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.

EmpID Role
1 Role1
2 Role2
3 Role1

View 5 Replies View Related

Sql Help Required

Jun 27, 2007

 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

View 5 Replies View Related

HELP Required

Aug 27, 2007

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.

View 2 Replies View Related

Help Required

Mar 30, 2004

Psting For Charan,

Hi,

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

Help Required Urgently

Tks in Adv
Ssg

View 1 Replies View Related

Help Required

Oct 26, 2007

Hello All,
When I execute my SP it throws an error.

Could not allocate space for object '(SYSTEM table id: -449020546)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

Any help is apperciated.

Nirene

View 2 Replies View Related

Help Required

Dec 5, 2007

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'

Sample Output
Slcode,Sldesc,DBN,CRN,BRT
AB02,CAB Ltd,10000,50000,0
AB03,BAC Ltd,0,30000,20000

View 7 Replies View Related

Help Required

Jan 2, 2008

CREATE PROCEDURE VEHICLE_OUTSTANDING @SDt Varchar(10),@EDt Varchar(10),@Cocode Varchar(5),@Brcode Varchar(5),@Loccode Varchar(5),@GLCode Varchar(6)
AS

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

Not able to Update OPDR & OPCR

Error Message is
Invalid column name 'OPDR'.

Regards

Nirene Veshnav

View 1 Replies View Related

Are These DB's Required??

Sep 6, 2006

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

View 10 Replies View Related

Help Required!

Feb 28, 2007

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

View 3 Replies View Related

Required Help

Nov 16, 2006

Pl tell me this senario

if we create new database in sql 2000 with size 1024 KB now we insert 500 KB data in that database. so what is actual size of database now?????????

View 3 Replies View Related

Required Help

Nov 3, 2006

Hi,

how we use syscolumns in Sql Server 2005 (Its not work in Sql Server 2005)

View 1 Replies View Related

Help Required In ASP.NET Code

Jul 26, 2006

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" %>

void Page_Load(Object sender , EventArgs e)
{
try
{
SqlConfiltered=new SqlConnection("server=xyz;Integrated Security=SSPI;database=mydatabase;");
connection.Open();
Response.Write("<b>Connection is successfully established</b>");
connection.Close();
}
catch(Exception ex)
{
Response.Write("<b>An exception occured while establishing connection</b><br>"+" "+ex.Message.ToString());
}
}
</Script>

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.

View 1 Replies View Related

Replication Help Required !!!

Feb 8, 2002

Hello Friends...

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 .

Many thanks in advance .
Yatin.

View 2 Replies View Related

RAM Required Per SQL Connection

May 2, 2001

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.

View 3 Replies View Related

Immediate IF ALTERNATIVE REQUIRED

May 14, 2001

Hi!

I wrote a query in MS-ACCESS using IIF. Is there any way to convert it to SQL Server Query to do the same job as it do in MS-ACCEESS

e.g. Here is manipulation with one column that I did in MS-ACCESS

IIf(InStr(1,Destinations.[Destination Name],"-",1)-1<0,Destinations.[Destination Name],Left(Destinations.[Destination Name],InStr(1,Destinations.[Destination Name],"-",1)-1)) AS COUNTRY,

View 1 Replies View Related

Required....help....URGENT

Mar 15, 2000

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....

Newbie

View 1 Replies View Related

SQL Noob Help Required

Sep 26, 2007

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**)

This is a sample of the tm_punch table


Code:



PUNCH_ID PUNCH_SEQNR PUNCH_EMPID PUNCH_DATE PUNCH_TIME PUNCH_MODIFIED PUNCH_PC PUNCH_CLOCKID PUNCH_DATA1 PUNCH_DATA2 PUNCH_DATA3 PROJECT_ID
141 0 52 2007-07-05 00:00:00 603 -1 0 0 0 0 0 NULL
142 0 52 2007-07-05 00:00:00 909 -1 0 0 0 0 0 NULL
143 0 52 2007-07-05 00:00:00 911 -1 0 0 0 0 0 NULL
144 0 52 2007-07-05 00:00:00 914 -1 0 0 0 0 0 NULL




If the query works correctly what should be returned is just the line with id 144

can anyone point out where i'm going wrong

View 3 Replies View Related

Replication! Immediate Help Required Please

Aug 3, 2004

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


Please help!!

View 12 Replies View Related

URGENT HELP Required

Oct 14, 2004

Hi

I am setting up a site in win XP professional and I am getting problem connecting to a local sql server 2000 installation (developer version)

The problem is that if i specify objects in my select query without an owner name, it can't recognize the object. I need to be able to do this.

Any one know how to fix this? is this a sql server setting??

Thank you.
James

View 2 Replies View Related

Required Tablecount

Mar 25, 2008

Hi all,

I have following stored procedure. After executing this

SET @STRSQL = 'SELECT @TABLECOUNT = COUNT(1) FROM [' + @DATABASE1+ ']..CUSTOMERS'
EXEC (@STRSQL)
PRINT @TABLECOUNT

How can I get total count in @TABLECOUNT variable. I am facing error as Must declare variable @TABLECOUNT. Though I have declared it.

Does anyone knows work around for this. Thanks in advance.

--kneel

View 2 Replies View Related

Is SQL Server DB Is Required?

May 7, 2007

Hi,



I wanted to use MS SQL Reporting services with Oracle as database.



Please let me know whether the installation of Sql Server Database is required in this case?



Please, can any one suggest me in this regard.



Thanks

View 1 Replies View Related

Suggestions Required!

Jul 10, 2007



The Background:

I have a SQL Serever with more than 10 million records.

I have to update/delete/insert records on a daily basis.

I am using the standard edition of SQL Server.

The tables are constantly having data inserted into them and the server has different jobs running all day long.

My Problem:

I cannot create index on my database and the database is getting really slow as each month/year passes.

Any/All suggestions are welcomed.

View 10 Replies View Related

Is IIS Always Required For Replication?

Jan 17, 2006

I would like to take advantage of either Merge or RDA replication over HTTP, but would like to avoid setting up IIS.

Is IIS always required to support connectivity for SQL Server Mobile replication back to a desktop SQL Server database?

Thanks, Tim

View 6 Replies View Related

Required Ports

Mar 26, 2008

Hopefully someone can answer this as I am yet to find any definitive information.

User access reports by default on port 80.

Report Manager and Report Viewer are on machine A. Catalog database is on SQL Server machine B along with the database being reported on.

What ports are required to be open between machine A and machine B? Does it use standard SQL port 1433, are there any other ports used?

Thanks

View 1 Replies View Related







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