Author |
Topic |
sayut
Starting Member
5 Posts |
Posted - 2010-08-26 : 04:30:18
|
Hai Pro, Here my Statementparameter: payout if {mjClaim.Ins}=1 then {mjClaim.Payable}+{mjClaim.GHAllowance}else {mjClaim.DueToHospital}+{@reimburse} parameter reimburse: if {mjclaim.DueToStaff} > 0 then {mjclaim.DueToStaff}else 0 |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-08-26 : 06:03:47
|
What are you trying to do? That code doesn't even make sense, there are table references but no SELECT statement.--Gail ShawSQL Server MVP |
|
|
sayut
Starting Member
5 Posts |
Posted - 2010-08-26 : 07:03:41
|
That fomula from crystal report only i already try to do with case statement but the result not same with the cystal report output.SELECT TOP (100) PERCENT dbo.mjClaim.Costcentre, SUM(dbo.mjClaim.DueToHospital) AS Amount, COUNT(dbo.mjClaim.ClaimID) AS Claims, SUM(dbo.mjClaim.MC) AS MC, CASE WHEN MAX(mjClaim.Ins) = 0 THEN SUM(mjClaim.Payable) + SUM(mjClaim.GHAllowance) ELSE SUM(mjClaim.DueToHospital) + SUM(CASE WHEN mjClaim.DueToStaff < 0 THEN mjClaim.DueToStaff ELSE 0 END) END AS PayOut, SUM(dbo.mjClaim.DueToHospital) AS DueToHospital, SUM(dbo.mjClaim.DueToStaff) AS DueToStaff, SUM(dbo.mjClaim.GHAllowance) AS GHAllowance, SUM(dbo.mjClaim.Payable) AS PayableFROM dbo.mjClaim INNER JOIN dbo.mjUser_SectionID_Access ON dbo.mjClaim.Costcentre = dbo.mjUser_SectionID_Access.SectionIDWHERE (dbo.mjClaim.PayorID = 3445) AND (dbo.mjUser_SectionID_Access.EmployeeID = 951672)GROUP BY dbo.mjClaim.CostcentreORDER BY dbo.mjClaim.CostcentreSomethig like this |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-26 : 11:03:16
|
as per your report expression, the equivalent sql should beCASE WHEN MAX(mjClaim.Ins) = 1 THEN SUM(mjClaim.Payable) + SUM(mjClaim.GHAllowance) ELSE SUM(mjClaim.DueToHospital) + SUM(CASE WHEN mjClaim.DueToStaff > 0 THEN mjClaim.DueToStaff ELSE 0 END) END AS PayOut ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sayut
Starting Member
5 Posts |
Posted - 2010-08-26 : 19:39:03
|
Hi visakh16,I already try that statement but the total payout accurate when I group by using mjClaim.Ins.If I remove mjClaim.Ins in Group By my Total Payout answer is wrong.Below OutPut When Group By not include mjClaim.InsCostcentre Amount Claims MC PayOut DueToHospital DueToStaff GHAllowance Payable0000085513 8874.8000 9 0 9124.8000 8874.8000 250.0000 0.0000 250.00000000085514 15005.2000 43 4 9237.9200 15005.2000 201.0000 0.0000 9237.92000000085515 13553.4400 63 2 3660.3000 13553.4400 285.0000 0.0000 3660.30000000085517 64.0000 12 1 164.0000 64.0000 100.0000 0.0000 100.00000000085518 14744.0000 36 1 14177.9500 14744.0000 1614.4500 0.0000 14177.95000000085519 23069.7300 63 0 5824.1500 23069.7300 100.0000 0.0000 5824.15000000085526 8419.7000 59 7 9132.7000 8419.7000 713.0000 0.0000 930.0000Below OutPut When Group By include mjClaim.Ins Costcentre Amount Claims MC PayOut DueToHospital DueToStaff GHAllowance Payable Ins0000085513 8874.8000 9 0 9124.8000 8874.8000 250.0000 0.0000 250.0000 00000085514 5968.2800 39 4 6169.2800 5968.2800 201.0000 0.0000 201.0000 00000085514 9036.9200 4 0 9036.9200 9036.9200 0.0000 0.0000 9036.9200 10000085515 10441.6400 61 2 10726.6400 10441.6400 285.0000 0.0000 548.5000 00000085515 3111.8000 2 0 3111.8000 3111.8000 0.0000 0.0000 3111.8000 10000085517 64.0000 12 1 164.0000 64.0000 100.0000 0.0000 100.0000 00000085518 2180.5000 32 1 3794.9500 2180.5000 1614.4500 0.0000 1614.4500 00000085518 12563.5000 4 0 12563.5000 12563.5000 0.0000 0.0000 12563.5000 10000085519 17345.5800 61 0 17445.5800 17345.5800 100.0000 0.0000 100.0000 00000085519 5724.1500 2 0 5724.1500 5724.1500 0.0000 0.0000 5724.1500 10000085526 8419.7000 59 7 9132.7000 8419.7000 713.0000 0.0000 930.0000 00000085527 3508.8500 19 1 3508.8500 3508.8500 0.0000 0.0000 0.0000 0 |
|
|
sayut
Starting Member
5 Posts |
Posted - 2010-08-26 : 19:51:40
|
|
|
|
souni
Starting Member
17 Posts |
Posted - 2010-08-26 : 21:57:01
|
<spam removed> |
|
|
souni
Starting Member
17 Posts |
Posted - 2010-08-26 : 21:57:45
|
<spam removed> |
|
|
souni
Starting Member
17 Posts |
Posted - 2010-08-26 : 21:58:01
|
<spam removed> |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-28 : 00:55:34
|
include Ins in your resultset and post. I need to see what values its returning.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|