| Author |
 Topic  |
|
|
satyen
Starting Member
21 Posts |
 Posted - 12/03/2012 : 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
India
47023 Posts |
Posted - 12/03/2012 : 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 - 12/03/2012 : 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
India
47023 Posts |
Posted - 12/03/2012 : 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 - 12/03/2012 : 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
India
47023 Posts |
Posted - 12/03/2012 : 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 - 12/03/2012 : 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
India
47023 Posts |
Posted - 12/03/2012 : 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 - 12/05/2012 : 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
India
47023 Posts |
Posted - 12/06/2012 : 01:18:32
|
how is ips_contact connected to other tables
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
satyen
Starting Member
21 Posts |
Posted - 12/06/2012 : 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
Flowing Fount of Yak Knowledge
India
1391 Posts |
Posted - 12/06/2012 : 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 |
|
|
| |
Topic |
|
|
|
Using IF statement and Minus/Subtract Operator
