Help in query - 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

Help in query

Author

Topic

Sambasivam
Starting Member

36 Posts

Posted - 2008-12-22 : 16:55:00

1st Query
=========================================================
SELECT
SUM(Field1)------------------------A
FROM
tableAct
WHERE
BUid = (Select Buid FROM tableBU where BUName = ‘’)
AND
Locid = (Select locid from tableloc where locname = ‘’)
AND
Monthid = (Select min(monthid) -1 from tablemonth where year = ‘2009’)

===============================================================
2nd Query

SELECT
SUM(Field1) + SUM(Field2) – SUM(Field3) + SUM(Field4) --------------B
FROM
tablePl
WHERE
BUid = (Select Buid FROM tableBU where BUName = ‘’)
AND
Locid = (Select locid from tableloc where locname = ‘’)
AND
Monthid BETWEEN
(Select min(monthid) from tablemonth where year = ‘2009’)
AND
(Select max(monthid) from tablemonth where year = ‘2009’)

Actually I need the value of SUM(Field1) [from 1st query] – expression result from 2nd query. Which is A- B in a single query. Is that possible? Please let me know.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-22 : 17:34:48

[code]SELECT
(SELECT
SUM(Field1)------------------------A
FROM
tableAct
WHERE
BUid = (Select Buid FROM tableBU where BUName = ‘’)
AND
Locid = (Select locid from tableloc where locname = ‘’)
AND
Monthid = (Select min(monthid) -1 from tablemonth where year = ‘2009’)) -
SUM(Field1) + SUM(Field2) – SUM(Field3) + SUM(Field4) --------------B
FROM
tablePl
WHERE
BUid = (Select Buid FROM tableBU where BUName = ‘’)
AND
Locid = (Select locid from tableloc where locname = ‘’)
AND
Monthid BETWEEN
(Select min(monthid) from tablemonth where year = ‘2009’)
AND
(Select max(monthid) from tablemonth where year = ‘2009’)[/code]

Sambasivam
Starting Member

36 Posts

Posted - 2008-12-22 : 17:46:15

Looks to be like this is incomplete. Im getting error.

Sambasivam
Starting Member

36 Posts

Posted - 2008-12-22 : 17:54:44

I did modified and was able to run. Sorry for that confusion. Can I use ROLLUP with this query (it should apply for both the queries).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-22 : 22:37:49

did you mean this?


DECLARE @MinMonth int,@MaxMonth int

Select @MinMonth=min(monthid),@MaxMonth=max(monthid) from tablemonth where year = ‘2009’

SELECT
SUM(CASE WHEN Monthid=@MinMonth-1 THEN Field1 ELSE 0 END) AS A,
SUM(CASE WHEN Monthid BETWEEN @MinMonth AND @MaxMonth THEN Field1 ELSE 0 END) + 
SUM(CASE WHEN Monthid BETWEEN @MinMonth AND @MaxMonth THEN Field2 ELSE 0 END) – 
SUM(CASE WHEN Monthid BETWEEN @MinMonth AND @MaxMonth THEN Field3 ELSE 0 END) + 
SUM(CASE WHEN Monthid BETWEEN @MinMonth AND @MaxMonth THEN Field4 ELSE 0 END) AS B
FROM
tableAct ta
join (Select Buid FROM tableBU where BUName = ‘’) t1
on t1.Buid =ta.Buid 
join(Select locid from tableloc where locname = ‘’)t2
on t2.locid =ta.locid 
GROUP BY Monthid

WITH ROLLUP

Sambasivam
Starting Member

36 Posts

Posted - 2008-12-23 : 08:58:22

1st Query
=========================================================
SELECT
Field1------------------------A ( I need this for each rank say Senior Manager, Manager etc)
FROM
tableAct
InnerJoin Rank R ON R.Rankid = tableAct.Rankid
WHERE
BUid = (Select Buid FROM tableBU where BUName = ‘’)
AND
Locid = (Select locid from tableloc where locname = ‘’)
AND
Monthid = (Select min(monthid) -1 from tablemonth where year = ‘2009’)

===============================================================
2nd Query

SELECT
SUM(Field1) + SUM(Field2) – SUM(Field3) + SUM(Field4) --------------B ( I need this for each rank as A)
FROM
tablePl
InnerJoin Rank R ON R.Rankid = tableAct.Rankid
WHERE
BUid = (Select Buid FROM tableBU where BUName = ‘’)
AND
Locid = (Select locid from tableloc where locname = ‘’)
AND
Monthid BETWEEN
(Select min(monthid) from tablemonth where year = ‘2009’)
AND
(Select max(monthid) from tablemonth where year = ‘2009’)

Actually I need the value of (Field1) [from 1st query] – expression result from 2nd query. Which is A- B in a single query. Is that possible? Please let me know. So it
Field 1 (A) – expression result of B for each rank.

Sorry for the confusion earlier.Please help me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-25 : 02:00:55

use WHERE EXISTS or WHERE Field1 IN (2ndquery)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources