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
 How to use IF statement in mssql 2008

Author  Topic 

sayut
Starting Member

5 Posts

Posted - 2010-08-26 : 04:30:18
Hai Pro, Here my Statement

parameter: 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 Shaw
SQL Server MVP
Go to Top of Page

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 Payable
FROM dbo.mjClaim INNER JOIN
dbo.mjUser_SectionID_Access ON dbo.mjClaim.Costcentre = dbo.mjUser_SectionID_Access.SectionID
WHERE (dbo.mjClaim.PayorID = 3445) AND (dbo.mjUser_SectionID_Access.EmployeeID = 951672)
GROUP BY dbo.mjClaim.Costcentre
ORDER BY dbo.mjClaim.Costcentre

Somethig like this
Go to Top of Page

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 be

CASE 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.Ins
Costcentre Amount Claims MC PayOut DueToHospital DueToStaff GHAllowance Payable
0000085513 8874.8000 9 0 9124.8000 8874.8000 250.0000 0.0000 250.0000
0000085514 15005.2000 43 4 9237.9200 15005.2000 201.0000 0.0000 9237.9200
0000085515 13553.4400 63 2 3660.3000 13553.4400 285.0000 0.0000 3660.3000
0000085517 64.0000 12 1 164.0000 64.0000 100.0000 0.0000 100.0000
0000085518 14744.0000 36 1 14177.9500 14744.0000 1614.4500 0.0000 14177.9500
0000085519 23069.7300 63 0 5824.1500 23069.7300 100.0000 0.0000 5824.1500
0000085526 8419.7000 59 7 9132.7000 8419.7000 713.0000 0.0000 930.0000

Below OutPut When Group By include mjClaim.Ins
Costcentre Amount Claims MC PayOut DueToHospital DueToStaff GHAllowance Payable Ins
0000085513 8874.8000 9 0 9124.8000 8874.8000 250.0000 0.0000 250.0000 0
0000085514 5968.2800 39 4 6169.2800 5968.2800 201.0000 0.0000 201.0000 0
0000085514 9036.9200 4 0 9036.9200 9036.9200 0.0000 0.0000 9036.9200 1
0000085515 10441.6400 61 2 10726.6400 10441.6400 285.0000 0.0000 548.5000 0
0000085515 3111.8000 2 0 3111.8000 3111.8000 0.0000 0.0000 3111.8000 1
0000085517 64.0000 12 1 164.0000 64.0000 100.0000 0.0000 100.0000 0
0000085518 2180.5000 32 1 3794.9500 2180.5000 1614.4500 0.0000 1614.4500 0
0000085518 12563.5000 4 0 12563.5000 12563.5000 0.0000 0.0000 12563.5000 1
0000085519 17345.5800 61 0 17445.5800 17345.5800 100.0000 0.0000 100.0000 0
0000085519 5724.1500 2 0 5724.1500 5724.1500 0.0000 0.0000 5724.1500 1
0000085526 8419.7000 59 7 9132.7000 8419.7000 713.0000 0.0000 930.0000 0
0000085527 3508.8500 19 1 3508.8500 3508.8500 0.0000 0.0000 0.0000 0
Go to Top of Page

sayut
Starting Member

5 Posts

Posted - 2010-08-26 : 19:51:40

Go to Top of Page

souni
Starting Member

17 Posts

Posted - 2010-08-26 : 21:57:01
<spam removed>
Go to Top of Page

souni
Starting Member

17 Posts

Posted - 2010-08-26 : 21:57:45
<spam removed>
Go to Top of Page

souni
Starting Member

17 Posts

Posted - 2010-08-26 : 21:58:01
<spam removed>
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -