problem in select query - SQL Server Forums

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

problem in select query

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-11-19 : 05:01:34

I have a select statement like this SELECT PAYMENTCODE,

VOUCHERMONTH,

HOUSEHOLDID,

PAYMENTCATEGORYCODE AS PAYMENTCATEGORYCODE1,

PAYMENTCATEGORYCODE AS PAYMENTCATEGORYCODE2,

PREMIUMPAYMENT,

OLDPREMIUMPAYMENT,

PAYMENTAMT,

ADJUSTMENTEFFECTIVEDATE,

RUNDATE

INTO #TEMP_CHP_CASE1_RESULT

FROM UST_GPA_CHP_PYMT

GROUP BY PAYMENTCODE, HOUSEHOLDID, ADJUSTMENTEFFECTIVEDATE, PAYMENTCATEGORYCODE

HAVING PAYMENTCATEGORYCODE IN ('A','D')

which will produce the result in this format
PAYMENTCATEGORYCODE1 PAYMENTCATEGORYCODE2 PREMIUMPAYMENT
------------------- ------------------- ------------------------
H H 943.08
I I 1,330.76

now i have to modify this into this output as:

PAYMENTCATEGORYCODE1 PAYMENTCATEGORYCODE2 PREMIUMPAYMENT
------------------- ------------------- ------------------------
H I 2,273.84

(premium payment amount will become the sum of the columns)
I have an idea that using cross joins it can be done but can any one help me in sorting out this

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 05:09:24

will you always have only two payment category codes?

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-11-19 : 05:10:42

yes

susan

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-11-19 : 05:23:44

PAYMENTCATEGORYCODE1 PAYMENTCATEGORYCODE2 PREMIUMPAYMENT
------------------- ------------------- ------------------------
H H 943.08
I I 1,330.76

now i have to modify this into this output as:

PAYMENTCATEGORYCODE1 PAYMENTCATEGORYCODE2 PREMIUMPAYMENT
------------------- ------------------- ------------------------
H I 2,273.84
(2,273.84 is 943.08 + 1,330.76 )

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 05:31:44

[code]SELECT

MAX(CASE WHEN PAYMENTCATEGORYCODE='H' THEN PAYMENTCATEGORYCODE ELSE NULL END) AS PAYMENTCATEGORYCODE1,

MAX(CASE WHEN PAYMENTCATEGORYCODE='I' THEN PAYMENTCATEGORYCODE ELSE NULL END) AS PAYMENTCATEGORYCODE2,

SUM(PREMIUMPAYMENT) AS PREMIUMPAYMENT
INTO #TEMP_CHP_CASE1_RESULT

FROM UST_GPA_CHP_PYMT
[/code]

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-11-19 : 05:38:09

why should we use max(case)

susan

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-11-19 : 05:47:04

hI I USED WHAT U TOLD BUT SYNTAX ERROR I S COMING NEAR WHEN

susan

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-11-19 : 05:52:29

actually i tried ur code correcting the errors the paymentcode1 and 2 is coming as null for all the records including records other than a,d

susan