alter or recreate view
-
There is a table called QCR100 containing the following information ITEM_CODE ITEM_DESCRIPTION DCODE AMOUNT 111200 investments 24000003 1630000000.0000 111200 investments 126000005 1630000000.0000 111600 Other Assets 133000009 1700000000.0000 111600 Other Assets 150000002 1730000000.0000 111701 Leasehold 190010130 1740000000.0000 111705 Furniture 190010036 1750000000.0000 111706 Computer Software 190010141 46995799.1800 Then I created a view with this command statement: SELECT CASE WHEN LEFT(e.dcode, 1) = '1' THEN isnull(SUM(a.Dr_bal_lcy - a.CR_BAL_LCY), 0) WHEN LEFT(e.dcode, 1) = '2' THEN isnull(SUM(a.CR_BAL_LCY - a.Dr_bal_lcy), 0) ELSE 0 END AS amount, e.ITEM_DESCRIPTION, e.ITEM_CODE FROM dbo.GLTEMP_CONS AS a RIGHT OUTER JOIN dbo.QCR100 AS e ON LTRIM(a.GL_code) = e.Dcode GROUP BY e.ITEM_DESCRIPTION, e.ITEM_CODE, e.Dcode How I can alter this view to sum the amount for any dupli
-
There is a table called QCR100 containing the following information ITEM_CODE ITEM_DESCRIPTION DCODE AMOUNT 111200 investments 24000003 1630000000.0000 111200 investments 126000005 1630000000.0000 111600 Other Assets 133000009 1700000000.0000 111600 Other Assets 150000002 1730000000.0000 111701 Leasehold 190010130 1740000000.0000 111705 Furniture 190010036 1750000000.0000 111706 Computer Software 190010141 46995799.1800 Then I created a view with this command statement: SELECT CASE WHEN LEFT(e.dcode, 1) = '1' THEN isnull(SUM(a.Dr_bal_lcy - a.CR_BAL_LCY), 0) WHEN LEFT(e.dcode, 1) = '2' THEN isnull(SUM(a.CR_BAL_LCY - a.Dr_bal_lcy), 0) ELSE 0 END AS amount, e.ITEM_DESCRIPTION, e.ITEM_CODE FROM dbo.GLTEMP_CONS AS a RIGHT OUTER JOIN dbo.QCR100 AS e ON LTRIM(a.GL_code) = e.Dcode GROUP BY e.ITEM_DESCRIPTION, e.ITEM_CODE, e.Dcode How I can alter this view to sum the amount for any dupli
Hope you are lloking for this query.
select item_code,ITEM_DESCRIPTION, (select max(t1.dcode) from qcr100 as t1 where t1.item_code=qcr100.item_code) as dcode, sum(cast(amount as float)) as amount from qcr100 group by item_code,ITEM_DESCRIPTION
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
-
Hope you are lloking for this query.
select item_code,ITEM_DESCRIPTION, (select max(t1.dcode) from qcr100 as t1 where t1.item_code=qcr100.item_code) as dcode, sum(cast(amount as float)) as amount from qcr100 group by item_code,ITEM_DESCRIPTION
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
actually i can write sql statement to do that. in fact i written it as another view call qcr100f because i will use it to update anorher table. but i want to improve on this giving that the gl_temp_cons has schema idkey db_bal cr_bal dcode i want to be able to get the result i post above in with one view definition thanks for your concern