|
Assuming SQL Server, You can achieve this using the following PIVOT
select empcode, basicSalary, ISNULL(Bonus,0) as Bonus, ISNULL(Transport,0) as Transport
from
(SELECT s.empcode, s.basicSalary, a.description, a.amount
FROM salary s
LEFT JOIN additions a
ON s.empcode=a.empcode ) AS SalaryWithAdditions
PIVOT(
SUM(amount)
FOR description IN ([Bonus],[Transport])
) AS PivotTable
Output with your test data:
empcode basicSalary Bonus Transport
160 3000 180 25
170 4000 0 0
|
|
|
|
|
Check the following query...
SELECT T1.empcode,T1.[basic Salary],B.Bonus,T.Transport
FROM Table1 T1
INNER JOIN (SELECT Amount As Bonuus, empcode FROM Table2 WHERE Additions = 'Bonus') B ON B.empcode = T1.empcode
INNER JOIN (SELECT Amount AS Transport, empcode FROM Table2 WHERE Additions = 'Transport') T ON T.empcode = T1.empcode
Adjust the inner join to left join if required...
Thanks
|
|
|
|
|
I am trying to retrieve the Earliest and latest Dates and prices
Using the following code, which works fine. I need to enter the values to a variable
as seen below, which does not work can someone please help
Thanks in advance,
Michael
USE "Sales"
GO
SET NOCOUNT ON;
DECLARE
@dtEarliestDate DATETIME,
@fltEarliestPrice REAL,
@dtLatestDate DATETIME,
@fltLatestPrice REAL
SELECT * FROM
(
SELECT TOP 1 dtDateTime, fltPrice FROM Customers ORDER BY dtDateTime DESC
UNION ALL
SELECT TOP 1 dtDateTime, fltPrice FROM Customers ORDER BY dtDateTime ASC
)
AS B
--SET @dtEarliestDate = B.dtDateTime
--SET @dtLatestDate = B.dtDateTime
--SET @fltLatestPrice = B.fltPrice
--SET @fltEarliestPrice = B.fltPrice
|
|
|
|
|
This SQL works for me ...
begin
declare @max_date datetime
set @max_date = (select max(creation_date) from fsformula)
print @max_date
end
|
|
|
|
|
|
I haven't tested this but try it .
SELECT TOP 1 @dtLatestDate=dtDateTime, @fltLatestPrice =fltPrice FROM Customers ORDER BY dtDateTime DESC
SELECT TOP 1 @dtEarliestDate =dtDateTime, @fltEarliestPrice=fltPrice FROM Customers ORDER BY dtDateTime ASC
|
|
|
|
|
Hi,
I have to join four tables together and am having trouble. My query as it stands is returning data, but it's not the data I need. I do not think I have all four tables joined together correctly. How would I join the tables properly? Thank you.
Justin
I need to join the CXADMIN.RO_FAILURE_DTL RF, CXADMIN.RO_HIST RH, saadmin.sa_repair_part@elgsad rp, and saadmin.sa_code_group_task_dtl @ELGSAD cg tables together.
Here is my query so far:
SELECT distinct RF.REPAIR_ORD, RH.RECV_UNIT, RH.RECV_SERIAL_NBR, rf.created_date, RP.FAULT_CODE, RP.REPAIR_ACTION_CODE, cg.task_code
FROM CXADMIN.RO_FAILURE_DTL RF, CXADMIN.RO_HIST RH, saadmin.sa_repair_part@elgsad rp, saadmin.sa_code_group_task_dtl @ELGSAD cg
WHERE RF.REPAIR_ORD = RH.REPAIR_ORD and Rp.REPAIR_ORD = cg.REPAIR_ORD
AND RF.FAILURE_CODE ='DISK'AND RH.CURR_FACILITY_ID ='23' AND RF.CREATED_DATE >'1-JUN-2010' AND RF.CREATED_DATE < '1-dec-2010'
AND (CG.TASK_CODE ='PHMD' OR CG.TASK_CODE ='PHSN' OR CG.TASK_CODE ='CHMD' OR CG.TASK_CODE ='CHSN')
|
|
|
|
|
For joining tables, SQL offers a very specific keyword, it is documented here[^]. You would need three of those for joining four tables together.
Warning: There are some variations, controlling what to do when rows in one table don't match rows in the other tables. Here is an article that might help you on the subject: Visual Representation of SQL Joins[^]
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Your code is using an implicit equijoin and a cartesianjoin.
WHERE RF.REPAIR_ORD = RH.REPAIR_ORD and Rp.REPAIR_ORD = cg.REPAIR_ORD
For simplicity, let me assume the actual table names are RF, RH, RP and CG. RH and RH are joined and RP and CG are joined but
RF is not joined to RP or CG; and also RH is not joined to RP and CG. So the end result is a cartesian product of two equijoins (RF-RH X RP-CG). I would suggest using an explicit join. From your code, I assume that REPAIR_ORD is common to all the tables. So my code would look like
SELECT .....
FROM
RF INNER JOIN RH
ON RF.REPAIR_ORD=RH.REPAIR_ORD
INNER JOIN RP
ON RH.REPAIR_ORD=RP.REPAIR_ORD
INNER JOIN CG
ON RP.REPAIR_ORD=CG.REPAIRD_ORD
WHERE ....
I am using inner joins but depending on what you need, you may find left joins or right joins more suitable.
|
|
|
|
|
Hi,
I have to query an update for date and time. I know how to update the date alone, but I am having trouble with adding the time to the function. Right now, as it stands, it reads 4/20/2011 1:32:07 PM. I need the dock_date field to read 4/21/2011 7:00:00 AM. When I add 07:00:00 AM to the query I receive a DB error of: ORA-01830: date format picture ends before converting entire input string. Can someone please help me?
Thank you,
Justin
I am joining two tables: cxadmin.ro_hist and cxadmin.ord_dtl and use the in function b/c I have to update multiple rows.
My query so far is:
UPDATE cxadmin.ro_hist
SET DOCK_DATE = '20-APR-2011 7:00:00 AM'
where repair_ord in (
select recv_repair_ord from cxadmin.ord_dtl
where ord_nbr = '602945RR' AND DOCK_DATE > '20-APR-2011' )
|
|
|
|
|
Have you tried to convert on date type?
e.g TO_DATE('20-APR-2011 7:00:00 AM', 'dd-MM-yyyy HH:MI:SS AM')
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.cacttus.com
|
|
|
|
|
|
No problem
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.cacttus.com
|
|
|
|
|
For SQL Server you might try:
SET DOC_DATE = CAST('20-APR-2011 7:00:00 AM' AS DATETIME)
or possibly:
SET DOC_DATE = '20-APR-2011 7:00:00' -- NO AM
|
|
|
|
|
I was reading this (( http://technet.microsoft.com/en-us/library/ms181055.aspx[^] )) today, and was shocked (shocked I tell you) by the following statement:
The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:
SELECT * FROM Person;
SELECT * FROM Person.Person;
|
|
|
|
|
Nothing another shower couldn't fix, I hope.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
:: PIEBALD closes his blinds ::
Actually, I've already switched to tequila.
|
|
|
|
|
Hi, I use MS Access 2003 database and I want to Update all records in 1 column. How to make this in once? I use VB.NET
|
|
|
|
|
I would try a regular update statement without a WHERE clause, as in:
UPDATE table_name SET column_name=value
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
In this column have numeric data from 1 to 12 and I want to increase them with 1 e.g. 1->2 etc
|
|
|
|
|
Have you tried
UPDATE table_name SET column_name=column_name+1
?
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Thanks this is work for me!
|
|
|
|
|
I made an database application using LINQTOSQL IN c#(using visual studio)
I just wanted to know how many different connections at a same time can be made from different computers? (let say to a same table of database?)
|
|
|
|
|
This depends on your design but basically the answer is unlimited. Think about the big web sites, do you think they can accept limitations on the connections to their databases, no they design their systems to meet the projected requirements.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
wjbjnr wrote: I just wanted to know how many different connections at a same time can be made
from different computers?
64k. Or some number pretty close to that.
There are gotchas for that.
1. They must exist at the same time.
2. The number can be smaller, quite a bit, if the clients are rapidly opening and closing connections.
3. The number can be smaller, specifically 5000, if you have not explicitly updated the MS Windows registry of the server box. This is the maximum and is impacted by 2 above as well.
None of that has to do with the database and certainly not tables. It has to do with the IP address. So if you have a computer with more than one real IP Address then each is subject to the same restrictions.
See the following
http://msdn.microsoft.com/en-us/library/aa560610.aspx[^]
Be very careful if you start messing with those values.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
