SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Guide me
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

manthan1980
Starting Member

3 Posts

Posted - 10/06/2006 :  02:40:43  Show Profile  Reply with Quote
can you guide me to write a query to get cumulative salary, like this

Ename Salary Cu_Sal
----- ------ -------
A 15000 15000
B 10000 25000
C 20000 45000
D 45000 90000


Do i need to use functions or can it be done by using a subquery.IF yes please explain


one more doubt.Is there a query to write a word in the reverse format.
for ex:SQL--->LQS

also on the same lines can we write a query to convert the case of the string like for ex:SeRVer---->sErvER


Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 10/06/2006 :  03:20:24  Show Profile  Reply with Quote
Here's my take:
SELECT Ename, MAX(Salary) AS Salary, SUM(Salary) AS Cu_Sal
FROM table
WHERE YEAR(datecolumn) = YEAR(GETDATE())
GROUP BY Ename
Reverse format:
SELECT REVERSE(myColumn) FROM table

There is no function for switching the case of a string unless you want them all to be in upper (UPPER(myColumn)) or lower case (LOWER(myColumn))

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Edited by - Lumbago on 10/06/2006 03:20:38
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 10/06/2006 :  03:26:34  Show Profile  Reply with Quote
Oooops, sorry!! Misunderstood the query completely...here's another take:
DECLARE @table table (Ename varchar(1), Salary int)
INSERT INTO @table
SELECT 'A', 15000 UNION ALL SELECT 'B', 10000 UNION ALL 
SELECT 'C', 20000 UNION ALL SELECT 'D', 45000

SELECT Ename, Salary, Cu_sal = 
  (SELECT SUM(Salary) AS Cu_sal 
    FROM @table b 
    WHERE b.Ename <= a.Ename)
FROM @table a
Don't think this is possible without a subquery...but maybe some of the geniouses here say differently...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Edited by - Lumbago on 10/06/2006 03:31:11
Go to Top of Page

manthan1980
Starting Member

3 Posts

Posted - 10/06/2006 :  05:31:28  Show Profile  Reply with Quote
the query you said for the reverse format will change it for the entire column. Is there way to change it to specific values in the column.
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5556 Posts

Posted - 10/06/2006 :  05:50:41  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
quote:
Originally posted by manthan1980

the query you said for the reverse format will change it for the entire column. Is there way to change it to specific values in the column.



Use SUBSTRING() along with REVERSE() function to reverse only specific part of column.

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 10/06/2006 :  09:52:52  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by manthan1980

can you guide me to write a query to get cumulative salary, like this

Ename Salary Cu_Sal
----- ------ -------
A 15000 15000
B 10000 25000
C 20000 45000
D 45000 90000


Do i need to use functions or can it be done by using a subquery.IF yes please explain


one more doubt.Is there a query to write a word in the reverse format.
for ex:SQL--->LQS

also on the same lines can we write a query to convert the case of the string like for ex:SeRVer---->sErvER





Where do you want to show data?
If you use front end application, do Running Total there

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 10/07/2006 :  02:09:39  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
have a look at this links

http://blogs.conchango.com/jamiethomson/archive/2006/02/28/3001.aspx
http://www.sqlteam.com/item.asp?ItemID=3856

Chirag
Go to Top of Page

manthan1980
Starting Member

3 Posts

Posted - 10/09/2006 :  02:03:40  Show Profile  Reply with Quote
thanks for the solutions. it worked.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 10/09/2006 :  09:38:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I think you didnt answer to my question

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000