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):
DocEntry Type Total
0 Cash 100
1 Credit 100
2 Check 100
3 Transfer 100
I am currently using:
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],
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.
Why do not add calculated columns?
You can test this:
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'
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
select top 10 *
Also you can add an index on [Type]
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.