-
aggregate values !!!!!!!
--- Sum All Receipts and returns and Group by Period
l'm running this script and my total is just not adding up. if l have -120 in 200102
and -234 in 200210 my total is wrong.what am l doing wrong ? receipts have to be +ve and returns -Ve.. Please assist
Select
Case
When LPR312 LIKE 'E%' Then 'PAYROLL BACKED BY D/O'
When LPR312 LIKE 'NL%'Then 'NU - PAY'
When LPR312 LIKE 'JL9%'Then 'SBSA JV'
When LPR312 LIKE 'JL0%'Then 'E - PLAN'
Else 'Other' End Product
,Case When AMT151 < 0 Then -AMT151
Else 0 End As Receipts
,Case When AMT151 > 0 Then -AMT151
Else 0 End As Returns
,Sum(Case Period When 200201 Then AMT151 Else 0 End) As '200201'
,Sum(Case Period When 200202 Then AMT151 Else 0 End) As '200202'
,Sum(Case Period When 200203 Then AMT151 Else 0 End) As '200203'
,Sum(Case Period When 200204 Then AMT151 Else 0 End) As '200204'
,Sum(Case Period When 200205 Then AMT151 Else 0 End) As '200205'
,Sum(Case Period When 200206 Then AMT151 Else 0 End) As '200206'
,Sum(Case Period When 200207 Then AMT151 Else 0 End) As '200207'
,Sum(Case Period When 200208 Then AMT151 Else 0 End) As '200208'
,Sum(Case Period When 200209 Then AMT151 Else 0 End) As '200209'
,Sum(Case Period When 200210 Then AMT151 Else 0 End) As '200210'
,Sum(Case Period When 200211 Then AMT151 Else 0 End) As '200211'
,Sum(Case Period When 200212 Then AMT151 Else 0 End) As '200212'
,Sum(AMT151) AS Total
,Count(*) As RecordCount
From Import
Group By LPR312,Amt151
-
Hi,
I don't quite see what u r trying to achieve, but there is something wrong with your group by :
Group By LPR312,Amt151
I cannot see why u r grouping by amt151 as your r also summing up its value.
-
It looks like you are trying to create a pivot table.
You state that receipts have to be positive however in this statement:
Case When AMT151 < 0 Then -AMT151
Else 0 End As Receipts
you seem to be forcing the column to a negative value? Can you post some DDL as to better clarify the problem?
Jeff
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|