|
Did you try SUM function to get sum of working hours.
example:
select sum(workinghours) from mytable
Or post your query and we will see how concretely to add too sum of working hours.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hi,
I have CLR procedure which sends request to server and get the url, I need to open that url into browser. I am trying to do it in the application code by Process.Start("IExplore.exe",url). When I debug the code it runs fine but when it deploy to SQL Server to call from CLR procedure it returns error "Request Failed".
Hope someone can help to solve this.
Thanks
|
|
|
|
|
I have two Tables LIKE Emp(EmpNo,EmpName,DeptNo),Dept(No,Name).
Emp Table looks like:
1 xxx 1,2
2 yyy 2,3
3 zzz 1
Dept Table:
1 aaaa
2 bbbb
3 cccc
My Output will be:
1 xxx aaaa,bbbb
2 yyy bbbb,cccc
3 zzz aaaa
How can I do this in Sqlserver2005
|
|
|
|
|
Your database model is faulty IMAO.
How about:
Emp
EmpID Empname
1 xxx
2 yyy
3 ZZZ
Dept
DeptID Deptname
1 aaaa
2 bbbb
3 cccc
Emp_Dept
EmpID DeptID
1 1
1 2
2 2
2 3
3 1
then you can use
SELECT EmpName,DeptName
FROM Emp e
Join EmpDept ed
ON e.EmpID = ed.EmpID
JOIN Dept d
ON ed.Deptid = d.DeptID
And if you really need to get the deptnames as commaseparated values you can have a look in this thread[^] how to do that.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Ignore this response - it was wrong. Monday morning syndrome.
Do you work in 2 departments? I admit that some corporate structures work in this way, but for the purpose of the OP's question it was unecessary to add a many-to-many relationship between employees and departments. His original structure of 1 department hads many employees, ie a one-to-many relationship (or to put this another way each employee works in just 1 department) would have worked just fine, with just one join in the query.
modified on Monday, August 9, 2010 7:55 AM
|
|
|
|
|
No, if you look at the example data in the OP, it is a many-to-many relationship. Employee xxx works in two departments, aaaa and bbbb. Department aaaa has two employees, xxx and zzz.
|
|
|
|
|
My bad, I interpreted as the nameof the first employee to be "xxx 1" - I now see the deptNo field is a comma-separated list
|
|
|
|
|
J4amieC wrote: Monday morning syndrome.
I know everything about that, except it usually happens to me any day of the week
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Ignore this answer. I mis-interpreted your source data.
The above answer, although correct confuses your question somewhat.
All you need is to join employee table to department table using the foreign/primary key between employee and department.
SELECT EmpNo, EmpName, DeptName
FROM Emp
INNER JOIN Dept
ON Emp.DeptNo=Dept.No
modified on Monday, August 9, 2010 7:54 AM
|
|
|
|
|
TRY THIS:
SELECT Emp.EmployeeID, Emp.FirstName,
STUFF((SELECT ',' + DeptName FROM Dept WHERE Dept.DeptID = Emp.DeptID FOR XML PATH('')),1,1,'') AS DeptNames
FROM Employees Emp
|
|
|
|
|
Hi Friends,
I installed Oracle 11g in my XP 64bit OS, now I try to run some scripts in SQLPLUS. How can I check the current default path under SQLPLUS? Namely, where should I store these scripts? From the installation summary, I know where the ORACLE_HOME and ORACLE_BASE are, do I have to set environment variables? Confusing....Hope someone can give me a suggestion.
Thanks
|
|
|
|
|
Your post has so much spaces, it is very hard to read. Fix it so, people may look at it
|
|
|
|
|
The default SQLPlus directory is %ORACLE_HOME%\dbs
The SQLPATH environment variable will allow you to specify the default search path for scripts.
Personally I don't use SQLPlus, I find it a PITA.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
I think on Windows it is not necessary to set Oracle's environment variables manually (different from Linux). Scripts can be stored quite anywhere, as you can give the path e.g.
START e:\temp\myscript.sql
But Oracle has often some problems when the path contains spaces (perhaps also with national characters or when the path is "long"), so just avoid that - Oracle is still an 1980ies legacy software.
|
|
|
|
|
thanks so much Guys...^_^
|
|
|
|
|
Dear guru's,
I'm a beginner with "database-programming". My standard task is to read some xml-file in a dataset, then I "hack around" with some relations and display the result in any tabular form (e.g. DataGridView). This is a lot of programming for each different view which I want to have on my data.
But I feel, this is stupid and there are much more effective ways to manage this. My question: How are such tasks (different views on data, without a lot of programming) done?
Any tip for a beginner?
Thanks in advance
modified on Thursday, August 5, 2010 4:22 PM
|
|
|
|
|
Can you elaborate on what you consider different views of the data ?
Do you mean that the same data is filtered, sorted, grouped, but basically the same ?
You can add functionality to the Gridview to do sorting.
A little bit more detail could get you better suggestions.
|
|
|
|
|
Yes, I mean what you say: Some grouping, sorting, summing up of columns,....
Best regards
|
|
|
|
|
I am trying to get the questionID from two different Select statements and
then insert into a table one by one.
DECLARE @NewID INT
This one is inserting a new surveyID, Output is SurveyID
insert into survey(title,description, surveystatus,CreatedBy,date )
values('New Survey','New Survey',1,'test',Getdate())
SELECT @NewID = SCOPE_IDENTITY()
Copying the questions with the new surveyID
INSERT SurveyQuestions(surveyid, questions,answertype)
SELECT @NewID ,questions,answertype
FROM SurveyQuestions
WHERE surveyid='81'
The problem is below here. I want to get the value of the questionId of the
first select statement then the value of the questionID of the second select
statement and insert into the table SurveyChoices one by one.
Both the select statements can have 1 or 2 or 3 rows or more but the both the
select statements will have exact number of rows.
select QuestionId from surveyquestions where surveyid=@NewID and answertype
<> 'T'
select QuestionId from surveyquestions where surveyid='81' and answertype <>
'T'
Here i am using the insert statement using the value form First select
statement and second select statement
INSERT Surveychoices(QuestionId,choice)
SELECT questionID,choice((This is the value of the First select statement.)
FROM Surveychoices
WHERE questionid=questionID(This is the value of the second select statement.)
|
|
|
|
|
Hi,
HI,
SELECT @vCount=('select COUNT(['+@v_COLUMN_NAME+']) from '+@DataTable)
print @vCount
This is my stored procedure part to find the count of a column specified.But here i m getting a conversion error
vCount is declared as int
seeism
|
|
|
|
|
Got the answer
create table #countlists (ICount int)
exec ('insert into #countlists(ICount) select COUNT(['+@v_COLUMN_NAME+']) from '+@DataTable+' where '+@v_COLUMN_NAME+' is not null')
set @vCount=(select ICount from #countlists)
drop table #countlists
|
|
|
|
|
How can I use transaction on multiple database? I use SQL Server Compact Edition.
Thanks...
|
|
|
|
|
suppose you will insert some data in a table from two other's table/data base.
Begin Tran
Use DataBase2
insert into Table1(Name,Id,Roll,Address)
select A.Name,A.Id,B.Roll,B.Address from DataBase1..Table2 A inner join DataBase2..Table3 B
on A.Id = B.Id
Commit tran
If You use this query then you will easily transaction (Insert) data from two different data base & tables.
Mrityunjoy Bhattacharjee
modified on Sunday, August 8, 2010 12:06 PM
|
|
|
|
|
Hi guys...
Actually i have a table called products which has 4 products in it say for example pepsi, miranda, coke and fanta. The table has two columns product id and productname as follows:
1 pepsi
2 miranda
3 coke
4 fanta
now i have another table called agent, which has 3 columns such as agentid, region, productid. Now each agentid has a region and productid assigned to it as follows:
1001 DXB 1
1001 AJM 4
1002 ABC 2
1003 XYZ 3
1003 DEF 4 and so on. now in my application i need to make use of a query which gives me a result of all the product name with status for any particular agent id, for example: for agentid 1001 the result should be
productname status
pepsi true
miranda false
coke false
fanta true
the query i tried is :
select a.productname, status = case b.product_id
when 1 then 'True' else 'False'
when 2 then 'True' else 'False'
when 3 then 'True' else 'False'
when 4 then 'True' else 'False' end
from products a, agent b where a.productid = b.productid and agentid ='1001'
but doest work... any help is appreciated...
|
|
|
|
|
select a.productname,
case when b.agentid is null then 'False' else 'True' end AS status
from products a
left outer join agent b on a.productid = b.productid
and b.agentid ='1001'
|
|
|
|