Guide me - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Guide me

Author

Topic

manthan1980
Starting Member

3 Posts

Posted - 2006-10-06 : 02:40:43

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

3271 Posts

Posted - 2006-10-06 : 03:20:24

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"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-06 : 03:26:34

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"

manthan1980
Starting Member

3 Posts

Posted - 2006-10-06 : 05:31:28

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.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-06 : 05:50:41

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"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-06 : 09:52:52

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