Select Candidates With More Than One Skill (was Help Required)
Jan 18, 2005
I am struggling with a query and need urgent help.
I have a table with fields
canid
skillLevel
percent
date
and example of the results wud be
canid skillLevel Percent Date
704 1 20 n/a
705 2 10 n/a
775 1 50 n/a
what i want to do is produce a query that would return a result from the following search critera
return all results that match
skilllevel 1 and 20 percent AND skillLevel 2 and 10 percent
I tried this below but obviously im doing something wrong
select * from simulatedHistory where
((skilllevel = 1 and percent = 20) And (skilllevel = 2 and percent = 10))
I need the query returning
canid skillLevel Percent Date
704 1 20 n/a
705 2 10 n/a
any suggestions??? any help wud be much appreciated
View 9 Replies
ADVERTISEMENT
Sep 28, 2007
How am I configuring permission for an SQL database, that user within my program should'nt read private information?
as well how can I protect my database with a UN & PWD to allow access only for authenticated users, not within the program, just the *.mdf file?
View 7 Replies
View Related
Jul 21, 2013
I have a program which brings in Skill ratings for an assesmnet people with the title of "Worker have to take aolng with a file number they are assigned. The program also being in the reporting line that each worker is a part of.
SELECT distinct
o.VP,
o.AVP,
o.Director,
o.Supervisor,
o.Worker,
bs.File_NBR,
[Code] ....
The rating levels are 1 through 5. What I need to do is to do is create a table that shows the count and percentage of each rating giving to the workers for each skill grouped by Vp, AVP, Supervisor and Director. So all the works that are Ultimately under a AVP and all the wokers that are Ultimately under a director and so on.
NameRoleSkillCount of % of Count of % of
Rating 1 Rating 1 Rating 2 Rating 2
Gerald VPA1100 29% 13033%
Gerald VPB195 28% 9524%
Gerald VPB2120 35% 7018%
Gerald VPB330 9% 10025%
KimVPA1
KimVPB1
KimVPB2AND SO ON
[Code] ....
View 1 Replies
View Related
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
Apr 17, 2008
I have a select statement running within stored procedure as below. What I need to do is format the results of the select into one string by concatanting the below, and removing any null fields by using ISNULL. I am struggling with syntax. Can anyone advise? Should I be using a function? is so any sample syntax or pointers would be great..thanks marco
SELECT address1, address2, address3, address4, address5
FROM customer
View 5 Replies
View Related
Jul 23, 2005
I'm wondering if someone has thought about this, one desires to enhancehis/her sql skills proactively vs. skills gained over a period of timemerely because of work experience. SQL skill here refers to mainly MSSQL Server related skill.What could be reasonably effective ways to go for the proactiveapproach?Off head, I'm thinking of the following:*) read up and re-read on a few selected books such as sql for smartieexcept old textbooks (some of them have been read/studied)?Little challenge: definition of great sql books or online materials?*) get into a habit of visiting some good sql web sites include MS sqlweb site. Little challenge: definition of "good sql web sites"?*) form a routine of reading good SQL NGs like thisLittle challenge: which thread(s) to read? By interest only is sort oflimiting.*) find an interesting project (non-work related) to embark on to test,verify the above three?Big challenge: the above 1 through 3 is pretty dry, how could onemanage to do it in the evening/at night after work when a bit tired anddesires to relax instead?TIA.
View 2 Replies
View Related
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
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
Apr 9, 2007
Hi all,
I was wondering whether there is a way to show the user an error when the user did not select a specific parameter.
In my case, I have two optional parameters. The user has to select either one to view the report. If the user does not select either one, I would like to show some sort of an error page indicating so. However, all i am getting is a complete blank with no report in sight. I as a developer know this is an error as a result of missing parameters, but i was wondering whether I could produce a page or direct it to a page so that the user doesn't go all horrified at an empty page ?
Thanks
Bernard
View 4 Replies
View Related
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
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
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
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
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
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
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
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
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
Nov 3, 2006
Hi,
how we use syscolumns in Sql Server 2005 (Its not work in Sql Server 2005)
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
Jul 20, 2005
I have two related tables in my SQL database that I wish to join as follows:------------------------------------tblCustomersID (pk)Nameetc.tblCustomerManagersID (pk)CustomerID (fk)Manager (this *is* an fk but for the purposes of demonstration isnot)StartDate (indicates the date upon which the manager took / is takingcontrol of the company)------------------------------------Example entries are:tblCustomers1 Microsoft2 Symantec3 BorlandtblCustomerManagers1 1 Barry 01/01/032 1 Peter 01/07/033 2 Norman 01/02/034 3 Terry 01/01/035 3 Peter 01/07/05------------------------------------What I want to do is extract, in one query, a list of all customers andtheir *current* associated manager, so the result set today would be:Microsoft Peter 01/07/03Symantec Norman 01/02/03Borland Terry 01/01/03Currently I have:SELECT [Name], [Manager], [StartDate]FROM tblCustomersINNER JOIN tblCustomerManagers ON tblCustomerManagers.[CustomerID] =tblCustomers.[ID]WHERE [StartDate] <= GETDATE()ORDER BY [Name], [StartDate] DESCbut this obviously returns multiple entries for customers having managersprior to today eg:....Microsoft Peter 01/07/03Microsoft Barry 01/01/03....I know this is a simple question but I cannot think of a way of doing itwithout making the query extremely complicated.Any help is appreciated,Thanks,df
View 2 Replies
View Related
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
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
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