SQL Help - producing summary and detail in same row

[notadba]

This might be one of those ‘is it worth trying to do?” questions…

I need to write some sql to produce a dataset that really makes no sense – however it is a supplied format that we have no control over (legislative requirement).

Aside from the other 20+ columns of data, we have been asked to provide a summed total of allowance payments for each employee where the total of summed amounts is greater than $1000 for the fiscal year to date. The allowance payments are possibly made up by payments against 15+ allowance codes. This part is fine. However, in the next column, they need a char string of all of the allowance codes used in the summed total (i.e. non zero amounts) for that employee. So there could be 15 codes or 2 or 0…

Can anyone give me some pointers on how to build this text string (if possible).

Very basic Sample set

ID ERN_CD AMT
1 105 200.00
1 ABC 190.00
1 A34 700.00
2 105 300.00
3 105 250.00
3 A34 500.00
3 A35 500.00
3 C99 10.00

Results Required

ID ALLOW_PAID CHAR_STRING
1 1090.00 105~ABC~A34
2 0.00
3 1260.00 105~A34~A35~C99

While this looks more suited to a program rather than straight sql, would be interested to see any approaches to solving this.

Thanks in advance

 

[r937]

how many times do you need to do this?

if it is to be a regular process, you may wish to write some application code for it

the application code might run on a simple query's sorted result set, or you could do the same application logic in a stored procedure

so many things depend on your particular circumstances, not the least of which is your choice of database management system, which you did not mention

if you're using MySQL, there's a handy function called GROUP_CONCAT that makes this type of problem really trivial, so if you only have to do this once, it might even be worth the time to download mysql just to run this one function

r937.com | rudy.ca

 

[notadba]

Rudy,

Fast response! This is a yearly(?) but seems to change a little each year. We are unfortunately on DB2 (UDB 7.1 for OS/390) with no access to OLAP/Ranking functions.. (thought I would add that as you are usually very helpful with DB2 advice).

Was looking at a basic approach to getting a series of columns in max(ERN_CD) for each code as a set of aliased coumns, putting that inside a nother select to sum the values where total over $1000, but not sure how/when to then bring in the codes to be strung.

 

[r937]

in the example data that you've given, why is id 2 in the result set? its total is 300.00, not 0.00, and that's not above 1000

you know, if it's just an annual process, i would probably do this on my desktop computer with mysql (it's really trivial), and output the results as a flat file to bring back into db2

r937.com | rudy.ca

 

[notadba]

Are you on commission to MySQL ;-) The function you are describing sounds exactly like what we need - but not feasible given the 'secure' environment where I am working.

These two columns are just a subset of the 25 we need so ID 2 would be returned in the overall result set - just have a zero/no codes in this component. I didn't really explain that too thoroughly, I can get around the outer join/null issue once I can work out this 'small problem'.

Thanks again for your fast response.

 

[notadba]

Think we have solved it here - but not through SQL. Found an old instance of Brio on the server, and it pivots char strings when they are used as 'facts' exactly the way they need it.

Happy to see any sql approaches though.

 

[r937]

thank you for posting back

how did you do the total > 1000 logic while showing details?

r937.com | rudy.ca

 

[notadba]

We are still in the extract development phase - so will either do a really inefficient (...having sum(AMT) >= 1000)

or, just sort the pivotted results in Excel (which is their required format of return) and update the cells via a formula... Clunky but at least it will work without putting any smarts into it ;-)