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
 Using IF statement and Minus/Subtract Operator
 New Topic  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
52249 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
52249 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
52249 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
52249 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
52249 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
2168 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  
 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