1

I'm writing a margin report on our General Ledger and I've got the basics working, but I need to merge the rows based on specific logic and I don't know how...

My data looks like this:

value1      value2      location  date          category                     debitamount    creditamount
2029        390         ACT       2012-07-29    COSTS - Widgets and Gadgets  0.000          3.385
3029        390         ACT       2012-07-24    SALES - Widgets and Gadgets  1.170          0.000

And my report needs to display the two columns together like so:

plant   date          category               debitamount    creditamount
ACT     2012-07-29    Widgets and Gadgets    1.170          3.385

The logic to join them is contained in the value1 and value 2 column. Where the last 3 digits of value 1 and all three digits of value 2 are the same, the rows should be combined. Also, the 1st digit of value 1 will always been 2 for sales and 3 for costs (not sure if that matters)

IE 2029-390 is money coming in for Widgets and Gadgets sold to customers, while 3029-390 is money being spent to buy the Widgets and Gadgets from suppliers.

How can I so this programmatically in my stored procedure? (SQL Server 2008 R2)

Edit: Would I load the 3000's into one variable table the and the 2000's into another, then join the two on value2 and right(value1, 3)? Or something like that?

bluehiro
  • 91
  • 2
  • 9

1 Answers1

1

Try this:

SELECT RIGHT(LTRIM(RTRIM(value1)),3) , value2, MAX(location), MAX(date), MAX(category), SUM(debitamount), SUM(creditamount) FROM table1 GROUP BY RIGHT(LTRIM(RTRIM(value1)),3), value2

It will sum the credit amount and debit amount. It will choose the maximum string value in the other columns, assuming they are always the same when value2 and the last 3 digits of value1 are the same it shouldn't matter.

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
  • 5,711
  • 8
  • 54
  • 70
  • Please mark this as correct if it helps. Let me know if you have any questions and I will offer feedback. – zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz Jan 23 '12 at 23:13
  • Thank you for your help, I honestly didn't know I could use MAX() like that. In the end I did load the 3000's and 2000's into their own table variables and then combined them using your method. Worked like a charm. Thanks!!! – bluehiro Jan 24 '12 at 21:38
  • I guess the only question I could add would be this: How could I make the Category column always choose the "Sales - Widget and Gadgets" instead of choosing COSTS sometimes and other times SALES. The report data is accurate but the first 7 characters of the category names are a misleading at times. – bluehiro Jan 24 '12 at 21:43
  • If I understand correctly you could replace "MAX(category)" with "'Sales - Widget and Gadgets' As category". You could also try a case statement if you want the category to display based on one of the other columns. – zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz Jan 26 '12 at 21:19
  • 1
    Awesome, I went with a CASE statement. – bluehiro Jan 27 '12 at 21:57