Using IF statement and Minus/Subtract Operator

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

Using IF statement and Minus/Subtract Operator

Author

Topic

satyen
Starting Member

21 Posts

Posted - 2012-12-03 : 10:14:03

I am currently working on a SQL query and I am having a hard time completing this task.
Please bear with me as I think this may not be easy to explain over the forums.

Right now I am working on the following query:

select
--tmpka
(select SUM(soa_bal)*
from dbo.ips_coa
where acode between '100' and '130' ) AS 'tmpka',
-- dtifee
(select SUM(soa_bal)
from dbo.ips_coa
where acode = '136')AS 'dtifee',
-- tmpetf
(select SUM(soa_bal)
from dbo.ips_coa
where acode = '143')AS 'tmpetf',
-- tmpVAT
(select SUM(soa_bal)
from dbo.ips_coa
where acode = '146')AS 'tmpVAT',
-- tmpacr
(select SUM(soa_bal)
from dbo.ips_coa
where acode IN ('135','139','142','144','145','147','149','150','151','152','161','165','166','148','164','170'))AS 'tmpacr',
from dbo.ips_coa

Now firstly I need to add the following query which I am not able to:

--dcpetStatCost
(SELECT ips_case.casecode,
substring(ips_case.dcpet,1,1),
CASE WHEN substring(ips_case.dcpet,1,1) = 'O'
THEN 4000
WHEN substring(ips_case.dcpet,1,1) = 'C'
THEN 4000
WHEN substring(ips_case.dcpet,1,1) = 'X'
THEN 1500
WHEN substring(ips_case.dcpet,1,1) = 'D'
THEN 1500
ELSE 0) AS 'dcpetStatCost'

Now what I would like to implement the following so gives me the correct result:

ROUND(CASE WHEN tmptotcr = 0 THEN 0
WHEN (tmpka - #dtifee - #dcpetStatCost.#dcStatAmt - tmpetf - (tmpVAT) - tmpacr) = 0 THEN 0
ELSE ((tmpka - #dtifee - #dcpetStatCost.#dcStatAmt - tmpetf - (tmpVAT) - tmpacr)/tmptotcr )*100 END,2) as EstDiv

I would appreciate if anyone can help and work out a solution forme. I would be very grateful.
Thank you in adavnce.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 10:22:05

The first queries can be integrated as below


SELECT SUM(CASE WHEN acode between '100' and '130'  THEN soa_bal ELSE 0 END) AS  tmpka,
SUM(CASE WHEN acode = '136'  THEN soa_bal ELSE 0 END) AS  dtifee,
SUM(CASE WHEN acode = '143'  THEN soa_bal ELSE 0 END) AS  tmpetf,
SUM(CASE WHEN acode = '146'  THEN soa_bal ELSE 0 END) AS  tmpVAT,
SUM(CASE WHEN acode IN ('135','139','142','144','145','147','149','150','151','152','161','165','166','148','164','170') THEN soa_bal ELSE 0 END) AS  tmpacr 
FROM  dbo.ips_coa

for second query didnt understand table from which you get columns. is it ips_case? in which case how is it related to ips_coa table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satyen
Starting Member

21 Posts

Posted - 2012-12-03 : 10:45:02

Relation as follows:
ips_case c join ips_coa co ON c.casecode = co.casecode

Upon the completion of the above how do I implement the following:

ROUND(CASE WHEN tmptotcr = 0 THEN 0
WHEN (tmpka - #dtifee - #dcpetStatCost.#dcStatAmt - tmpetf - (tmpVAT) - tmpacr) = 0 THEN 0
ELSE ((tmpka - #dtifee - #dcpetStatCost.#dcStatAmt - tmpetf - (tmpVAT) - tmpacr)/tmptotcr )*100 END,2) as EstDiv

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 10:58:52

so does first values have to be calculated for each casecode?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satyen
Starting Member

21 Posts

Posted - 2012-12-03 : 11:08:57

Yes they do in order to do the calculation below:

ROUND(CASE WHEN tmptotcr = 0 THEN 0
WHEN (tmpka - #dtifee - #dcpetStatCost.#dcStatAmt - tmpetf - (tmpVAT) - tmpacr) = 0 THEN 0
ELSE ((tmpka - #dtifee - #dcpetStatCost.#dcStatAmt - tmpetf - (tmpVAT) - tmpacr)/tmptotcr )*100 END,2) as EstDiv

So the below statement needs to be combined with the syntax in red

SELECT SUM(CASE WHEN acode between '100' and '130' THEN soa_bal ELSE 0 END) AS tmpka,
SUM(CASE WHEN acode = '136' THEN soa_bal ELSE 0 END) AS dtifee,
SUM(CASE WHEN acode = '143' THEN soa_bal ELSE 0 END) AS tmpetf,
SUM(CASE WHEN acode = '146' THEN soa_bal ELSE 0 END) AS tmpVAT,
SUM(CASE WHEN acode IN ('135','139','142','144','145','147','149','150','151','152','161','165','166','148','164','170') THEN soa_bal ELSE 0 END) AS tmpacr

add the below to the above statement

(SELECT ips_case.casecode,
substring(ips_case.dcpet,1,1),
CASE WHEN substring(ips_case.dcpet,1,1) = 'O'
THEN 4000
WHEN substring(ips_case.dcpet,1,1) = 'C'
THEN 4000
WHEN substring(ips_case.dcpet,1,1) = 'X'
THEN 1500
WHEN substring(ips_case.dcpet,1,1) = 'D'
THEN 1500
ELSE 0) AS 'dcpetStatCost'
FROM dbo.ips_coa co join dbo.ips_case c ON casecode.c = casecode.co

Something like that

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 11:17:50

the stub will look like below

but i dont know from where you get other columns like tmptotcr,#dcstaAmt etc so make sure you put columns inside correct query for full solution


SELECT *,
ROUND(CASE WHEN tmptotcr = 0 THEN 0
WHEN (tmpka - #dtifee - #dcpetStatCost.#dcStatAmt - tmpetf - (tmpVAT) - tmpacr) = 0 THEN 0
ELSE ((tmpka - #dtifee - #dcpetStatCost.#dcStatAmt - tmpetf - (tmpVAT) - tmpacr)/tmptotcr )*100 END,2) as EstDiv
FROM
(
SELECT casecode,
SUM(CASE WHEN acode between '100' and '130' THEN soa_bal ELSE 0 END) AS tmpka,
SUM(CASE WHEN acode = '136' THEN soa_bal ELSE 0 END) AS dtifee,
SUM(CASE WHEN acode = '143' THEN soa_bal ELSE 0 END) AS tmpetf,
SUM(CASE WHEN acode = '146' THEN soa_bal ELSE 0 END) AS tmpVAT,
SUM(CASE WHEN acode IN ('135','139','142','144','145','147','149','150','151','152','161','165','166','148','164','170') THEN soa_bal ELSE 0 END) AS tmpacr 
FROM dbo.ips_case  
GROUP BY casecode)c
INNER JOIN
(SELECT ips_case.casecode,
SUM(
CASE WHEN substring(ips_case.dcpet,1,1) = 'O'
THEN 4000
WHEN substring(ips_case.dcpet,1,1) = 'C'
THEN 4000
WHEN substring(ips_case.dcpet,1,1) = 'X'
THEN 1500
WHEN substring(ips_case.dcpet,1,1) = 'D'
THEN 1500 
ELSE 0
END) AS 'dcpetStatCost'
FROM dbo.ips_coa 
GROUP BY ips_case.casecode)co 
ON c.casecode = co.casecode

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satyen
Starting Member

21 Posts

Posted - 2012-12-03 : 11:23:14

Thank you buddy. Much appreciated. I am trying to debug the query so I will post again if I require assistance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 11:24:52

ok...will try to help if you need more assistance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satyen
Starting Member

21 Posts

Posted - 2012-12-05 : 09:09:02

How do I implement this short piece of code to set 'tmptotcr' to the 'Mail Query'

SELECT Casecode,
sum(CASE
WHEN unsec <> 0 and (claimform is null or claimform = 0)
THEN unsec
WHEN unsec = 0 and claim <> 0 and (claimform is null or claimform = 0)
THEN claim
WHEN unsec = 0 and claim = 0 and ledger <> 0 and (claimform is null or claimform = 0)
THEN ledger
WHEN claimform = 1
THEN 0
ELSE 0
END) AS TotalCreditors
FROM ips_contact

Main Query -

SELECT *,
ROUND(CASE WHEN tmptotcr = 0 THEN 0
WHEN (tmpka - dtifee - dcpetStatCost - tmpetf - (tmpVAT) - tmpacr) = 0 THEN 0
ELSE ((tmpka - dtifee - dcpetStatCost - tmpetf - (tmpVAT) - tmpacr/tmptotcr)*100 END,2) as EstDiv
FROM
(
SELECT
SUM(CASE WHEN acode between '100' and '130' THEN soa_bal ELSE 0 END) AS 'tmpka',
SUM(CASE WHEN acode = '136' THEN soa_bal ELSE 0 END) AS dtifee,
SUM(CASE WHEN acode = '143' THEN soa_bal ELSE 0 END) AS tmpetf,
SUM(CASE WHEN acode = '146' THEN soa_bal ELSE 0 END) AS tmpVAT,
SUM(CASE WHEN acode IN ('135','139','142','144','145','147','149','150','151','152','161','165','166','148','164','170') THEN soa_bal ELSE 0 END) AS tmpacr
FROM dbo.ips_coa
GROUP BY casecode)c
INNER JOIN
(SELECT
SUM(
CASE WHEN substring(ips_case.dcpet,1,1) = 'O'
THEN 4000
WHEN substring(ips_case.dcpet,1,1) = 'C'
THEN 4000
WHEN substring(ips_case.dcpet,1,1) = 'X'
THEN 1500
WHEN substring(ips_case.dcpet,1,1) = 'D'
THEN 1500
ELSE 0
END) AS 'dcpetStatCost'
FROM dbo.ips_case
GROUP BY ips_case.casecode)co
ON c.casecode = co.casecode

I would appreciate if anyone can help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-06 : 01:18:32

how is ips_contact connected to other tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satyen
Starting Member

21 Posts

Posted - 2012-12-06 : 04:38:17

Relation as follows:
ips_case c join ips_contact con ON c.casecode = con.casecode

Casecode is a primary key on all tables they are link like the above

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-06 : 05:06:42

May be it works.............


SELECT *,
ROUND(CASE WHEN tmptotcr = 0 THEN 0
			WHEN (tmpka - dtifee - dcpetStatCost - tmpetf - (tmpVAT) - tmpacr) = 0 THEN 0
			ELSE ((tmpka - dtifee - dcpetStatCost - tmpetf - (tmpVAT) - tmpacr/tmptotcr)*100 END,2) as EstDiv,
TotalCreditors			
FROM ( SELECT SUM(CASE WHEN acode between '100' and '130' THEN soa_bal ELSE 0 END) AS 'tmpka',
				SUM(CASE WHEN acode = '136' THEN soa_bal ELSE 0 END) AS dtifee,
				SUM(CASE WHEN acode = '143' THEN soa_bal ELSE 0 END) AS tmpetf,
				SUM(CASE WHEN acode = '146' THEN soa_bal ELSE 0 END) AS tmpVAT,
				SUM(CASE WHEN acode IN ('135','139','142','144','145','147','149','150','151','152','161','165','166','148','164','170') THEN soa_bal ELSE 0 END) AS tmpacr 
		FROM dbo.ips_coa 
		GROUP BY casecode)c
INNER JOIN (SELECT casecode,SUM(CASE WHEN substring(ips_case.dcpet,1,1) = 'O' THEN 4000
							WHEN substring(ips_case.dcpet,1,1) = 'C' THEN 4000
							WHEN substring(ips_case.dcpet,1,1) = 'X' THEN 1500
							WHEN substring(ips_case.dcpet,1,1) = 'D' THEN 1500
							ELSE 0 END) AS 'dcpetStatCost'
			FROM dbo.ips_case 
			GROUP BY ips_case.casecode)co ON c.casecode = co.casecode
INNER JOIN (SELECT Casecode, sum(CASE WHEN unsec <> 0 and (claimform is null or claimform = 0) THEN unsec
									WHEN unsec = 0 and claim <> 0 and (claimform is null or claimform = 0) THEN claim
									WHEN unsec = 0 and claim = 0 and ledger <> 0 and (claimform is null or claimform = 0) THEN ledger
									WHEN claimform = 1 THEN 0 
									ELSE 0 END) AS TotalCreditors
			FROM ips_contact ) con ON co.casecode = con.casecode

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources