Multiple conditions in Group By

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

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple conditions in Group By

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-02-16 : 14:21:38

hi
I am wrint a query for my report.plz help me
Foloowing are the conditions

Table 1 CLAIN_INFO
CLAIM_CODE
CLAIM_AMOUNT
NET_AMOUNT
INV_STATUS

Table 2
CLAIMTYPE
CLAIMCODE
CLM_DESC

Gross claim means = sum of all claim_amount
On hold claim means = sum of all net claim which status is inv
Invoiced claims means = sum of all net claims which status is not inv
Group by claim code

REPORT
Claim code|claim desc|# |gross claim|# |onholdClaim |# |invoiced claims
202 |cashdics |41|2,000 |12|14,000 |02|1,000
503 |price prod|10|500 |1 |780 |45|100

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-02-17 : 10:06:03

See if this'll work:

SELECT	a.ClaimCode
	, a.Clm_Desc
	, COUNT(b.claim_code) 'GrossClaimCount'
	, SUM(b.claim_amount) 'GrossClaimSum'
	, COUNT(CASE WHEN b.inv_status = 1 THEN b.claim_code ELSE null END) 'OnHoldCount'
	, SUM(CASE WHEN b.inv_status = 1 THEN b.net_amount ELSE 0 END) 'OnHoldSum'
	, COUNT(CASE WHEN b.inv_status = 0 THEN b.claim_code ELSE null END) 'InvoicedCount'
	, SUM(CASE WHEN b.inv_status = 0 THEN b.net_amount ELSE 0 END) 'InvoicedSum'

FROM	Claim_info a JOIN <Table 2 name> b
	ON a.ClaimCode = b.ClaimCode

GROUP BY a.ClaimCode, a.Clm_Desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources