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