Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    126

    Unanswered: Help with denormalized data

    My ERP system (SAP Business One) does not have a "payment type" field, instead it has a field for the amount of each type of payment. It makes for annoying reporting.

    If I have the following data in the Incoming Payments table (ORCT)
    Code:
    DocEntry	CashSum	CreditSum	CheckSum	TrsfrSum	DocTotal				
    0		100	0		0		0		100
    1		0	100		0		0		100
    2		0	0		100		0		100
    3		0	0		0		100		100
    What is an efficient SQL query to return something like this? (It is okay if the type returns an integer code instead of a string description):
    Code:
    DocEntry	Type		Total	
    0		Cash		100
    1		Credit		100
    2		Check		100
    3		Transfer	100
    I am currently using:
    Code:
    SELECT	T0.DocEntry,
    		CASE 
    			WHEN T0.CashSum <> 0 THEN 'Cash'
    			WHEN T0.CreditSum <> 0 THEN 'Credit'
    			WHEN T0.CheckSum <> 0 THEN 'Check'
    			WHEN T0.TrsfrSum <> 0 THEN 'Transfer'
    		END AS [Type],
    		CASE 
    			WHEN T0.CashSum <> 0 THEN T0.CashSum
    			WHEN T0.CreditSum <> 0 THEN T0.CreditSum
    			WHEN T0.CheckSum <> 0 THEN T0.CheckSum
    			WHEN T0.TrsfrSum <> 0 THEN T0.TrsfrSum
    		END AS [Total]
    FROM	ORCT T0
    I think I've just been staring at this too long and wanted some fresh eyes to think outside of the box on this one.

    I am using this to report on our customer's monthly statements so that I can display "12/13/06 Wire Transfer $100". There are many more complications to this query such as currencies and which document number or reference to display, but I have figured out most of those on my own.

    Thank you,
    Mike

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    seems ok to me.

    if you wanted you could hide this query in a view and then it might be more pleasant to work with.

  3. #3
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Why do not add calculated columns?
    You can test this:

    Code:
    alter table ORCT
      add [Type] as case when isnull(CashSum, 0) <> 0   then 'Cash'
                         when isnull(CreditSum, 0) <> 0 then 'Credit'
                         when isnull(CheckSum, 0) <> 0  then 'Check'
                         when isnull(TrsfrSum, 0) <> 0  then 'Transfer'
                    end
    go
    
    alter table ORCT
      add [Total] as case when isnull(CashSum, 0) <> 0   then CashSum
                          when isnull(CreditSum, 0) <> 0 then CreditSum
                          when isnull(CheckSum, 0) <> 0  then CheckSum
                          when isnull(TrsfrSum, 0) <> 0  then TrsfrSum
                     end
    go
    
    select top 10 *
    from ORCT
    Also you can add an index on [Type]

    Code:
    create nonclustered index IX_ORCT_Type on ORCT ([Type] asc)

    If you do not want to create calculated columns, you can add two fixed columns (Type and Total) and filling it using trigger.
    Last edited by Kris Zywczyk; 12-14-06 at 04:34.
    Regards
    Kris Zywczyk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •