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
 How to use IF statement in mssql 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sayut
Starting Member

5 Posts

Posted - 08/26/2010 :  04:30:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 08/26/2010 :  06:03:47  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 - 08/26/2010 :  07:03:41  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 08/26/2010 :  11:03:16  Show Profile  Reply with Quote
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 - 08/26/2010 :  19:39:03  Show Profile  Reply with Quote
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 - 08/26/2010 :  19:51:40  Show Profile  Reply with Quote

Go to Top of Page

souni
Starting Member

China
17 Posts

Posted - 08/26/2010 :  21:57:01  Show Profile  Visit souni's Homepage  Reply with Quote
<spam removed>
Go to Top of Page

souni
Starting Member

China
17 Posts

Posted - 08/26/2010 :  21:57:45  Show Profile  Visit souni's Homepage  Reply with Quote
<spam removed>
Go to Top of Page

souni
Starting Member

China
17 Posts

Posted - 08/26/2010 :  21:58:01  Show Profile  Visit souni's Homepage  Reply with Quote
<spam removed>
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/28/2010 :  00:55:34  Show Profile  Reply with Quote
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
  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.16 seconds. Powered By: Snitz Forums 2000