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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Using IF statement and Minus/Subtract Operator
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

satyen
Starting Member

21 Posts

Posted - 12/03/2012 :  10:14:03  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 12/03/2012 :  10:22:05  Show Profile  Reply with Quote
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/

Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 12/03/2012 :  10:45:02  Show Profile  Reply with Quote
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





Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/03/2012 :  10:58:52  Show Profile  Reply with Quote
so does first values have to be calculated for each casecode?

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

Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 12/03/2012 :  11:08:57  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/03/2012 :  11:17:50  Show Profile  Reply with Quote
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/

Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 12/03/2012 :  11:23:14  Show Profile  Reply with Quote
Thank you buddy. Much appreciated. I am trying to debug the query so I will post again if I require assistance.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/03/2012 :  11:24:52  Show Profile  Reply with Quote
ok...will try to help if you need more assistance

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

Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 12/05/2012 :  09:09:02  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/06/2012 :  01:18:32  Show Profile  Reply with Quote
how is ips_contact connected to other tables

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

Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 12/06/2012 :  04:38:17  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts

Posted - 12/06/2012 :  05:06:42  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next 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.15 seconds. Powered By: Snitz Forums 2000