Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Harshika
Starting Member
1 Post |
 Posted - 2010-08-30 : 12:43:10
|
| Hi,My requirement is to find Gross Margin which is difference between two line items i.e Net Sales and Cost of Sales. We can differentiate then using a node column from table2. Net Sales has node value MIS40' and Cost of Sales has node value 'MIS50'. Here is the logic which I used.Select ISNULL ( [NetSale], 0 ) – ISNULL ( [COS], 0) as Difference from ( Select sum (case dim1 when ‘Net Sales & Transfers’ then MACH1_BI_SL_M1.BFV. JE_LN_GLBL_AMT else 0 end) as ‘NetSale’, sum (case dim2 when ‘ Cost of Sales’ then MACH1_BI_SL_M1.BFV. JE_LN_GLBL_AMT else 0 end) *-1 as ‘COS’ from MACH1_BI_SL_M1.BFV,MACH1_BVAL.FNCL_STMT_RPT_NODE_REL_CS_ITM where (MACH1_BVAL.FNCL_STMT_RPT_NODE_REL_CS_ITM.FNCL_STMT_RPT_NODE_NM . dim2 = 'MIS50') AND (MACH1_BVAL.FNCL_STMT_RPT_NODE_REL_CS_ITM.FNCL_STMT_RPT_NODE_NM . dim2 = 'MIS40'))As of now I get the Syntax Error and can someone please correct of my logic is incorrect.Appreciate your help.Thanks,Harsh |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-08-30 : 12:56:35
|
| For starters you need to alias your table in your subquery(MACH1_BVAL.FNCL_STMT_RPT_NODE_REL_CS_ITM.FNCL_STMT_RPT_NODE_NM . dim2 = 'MIS40')) as TableJimEveryday I learn something that somebody else already knew |
 |
|
|
|
|
|
Difference between Line Items to Calculate Gross M