I am sorry, but this you posted message in the wrong place. Please use http://groups.google.com/group/microsoft.public.sqlserver.server?lnk=sg&hl=en : hello ,
: I am a java developer but now a days i m working a SQL server 2005.N I hav to
: fine tune SQL's Queries.
: So please help me out .i m pasting a code .so please help to find wats wrong
: in this code.
: please its urgent.
: Thx
: Anirudh : select dimension1, dimension2, sum(average_assets) average_assets,
: sum(average_liabilities) average_liabilities, sum(current_assets)
: current_assets, sum(current_liabilities) current_liabilities, sum(income)
: income
: from (
: select product_category_description dimension1, group by
: product_category_description dimension2
: isnull( sum( case when isnull(sbumast.sbu_type,'ASSET') = 'ASSET' then
: average_assets else 0 "+" end),0) / cast(100 as varchar)
: average_assets,
: abs(isnull( sum( case when isnull(sbumast.sbu_type,'LIABILITY') = 'LIABILITY'
: then "+" average_liabilities else 0
: end),0))/cast("+mdivisor+" as varchar) average_assets,
: isnull( sum( case when isnull(sbumast.sbu_type,'ASSET') = 'ASSET' then
: current_assets else 0 "+" end ),0) / cast(100 as varchar)
: current_assets,
: abs(isnull( sum( case when isnull(sbumast.sbu_type, 'LIABILITY') =
: 'LIABILITY' then "+" current_liabilities else 0 end ),0))
: /cast(100 as varchar) current_liabilities ,
: isnull( sum( case when isnull(sbumast.sbu_type,'ASSET') = 'ASSET' and
: pmast.product_category "+" 1 then income_amount else 0 end),0)
: /cast(100 as varchar) income : from PF_ACCOUNT_BILL_VIEW acc
: left outer HASH join
: (select perform_id,
: sum( case when outstanding_amount_inr > 0 then outstanding_amount_inr *
: (datediff(day,outstanding_from_date,outstanding_to_date)+1) else 0 end )/
: cast(datediff(day,'2004-09-29','"+mto_date+"') + 1 as varchar)
: average_assets, : sum( case when outstanding_amount_inr 0 and '2004-10-15' between
: outstanding_from_date and outstanding_to_date then outstanding_amount_inr
: else 0 end ) current_assets,sum( case when outstanding_amount_inr > 0
: and '2004-10-15' between outstanding_from_date and outstanding_to_date
: then outstanding_amount_inr else 0 end ) current_assets, : sum( case when outstanding_amount_inr '2004-10-15' then '2004-10-15' else
: outstanding_to_date end ) outstanding_to_date : from pf_os_BILL_VIEW pf_os : where outstanding_from_date = '2004-09-29' ) as x : group by perform_id ) as os : on acc.perform_id = os.perform_id : left outer HASH join (select perform_id,sum(income_amount) as income_amount
: from ( : select perform_id,
: sum((isnull(system_income_amount,0)+isnull(manual_income_amount,0))/
: (datediff(day, income_from_date, income_to_date) + 1) * (datediff(day,
: case when income_from_date > '2004-09-29' then income_from_date else
: '2004-09-29' end, case when income_to_date = '2004-09-29' and
: income_from_date '2004-10-15' then '2004-10-15' else income_to_date end)
: )+1 ) : from pf_income_adjustments where income_to_date >= '2004-09-29' and
: income_from_date 0 and income_booked_date = '2004-09-29' and
: income_from_date 0 and income_booked_date between '2004-09-29' and
: '2004-10-15' : AND PERFORM_ID NOT IN ( SELECT DISTINCT PERFORM_ID FROM PF_ACCOUNT ACCMAST
: INNER HASH JOIN BRANCH_BG_REBATE ON ACCMAST.SOURCE_BRANCH_CODE =
: BRANCH_BG_REBATE.SOURCE_BRANCH_CODE AND GETDATE() BETWEEN
: BRANCH_BG_REBATE.FROM_MON AND BRANCH_BG_REBATE.TO_MON AND
: ACCMAST.DATA_SOURCE = 'BG' ) : UNION ALL SELECT PERFORM_ID,SUM(MONTHLY_INCOME) FROM ( : SELECT PERFORM_ID, MON_YR, (CALCULATEDINCOME/NO_OF_DAYS) * (1 +
: DATEDIFF(DAY,CASE WHEN MON_YR = '2004-10-15' THEN '2004-10-15' ELSE
: DATEADD(DAY,NO_OF_DAYS - 1,MON_YR) END)) AS MONTHLY_INCOME : FROM PF_TRAN_BG_REBATE INNER JOIN PF_PREFERENCES ON 1=1 WHERE (CASE WHEN
: '2004-09-29' = CONVERT(DATETIME,CAST(MONTH('2004-09-29') AS
: VARCHAR)+'/01/'+CAST(YEAR('2004-09-29') AS VARCHAR),101) AND MON_YR
: perform.getMaxMonthendDate('2004-09-29') THEN INCOME_FROM_DATE ELSE
: perform.getMaxMonthendDate('2004-09-29') END, CASE WHEN INCOME_TO_DATE =
: DATEADD(MONTH,1,PF_PREFERENCES.LAST_MONTHEND_DATE) THEN 1 ELSE 0 END) = 1
: AND INCOME_TO_DATE >= perform.getMaxMonthendDate('2004-09-29') AND
: INCOME_FROM_DATE '2004-10-15' then '2004-10-15' else income_to_date end)
: )+1 ) : from pf_income_adjustments INNER JOIN PF_PREFERENCES ON 1=1 : where (CASE WHEN '2004-10-15' >=
: DATEADD(MONTH,1,PF_PREFERENCES.LAST_MONTHEND_DATE) THEN 1 ELSE 0 END) = 1
: AND income_to_date >= perform.getMaxMonthendDate('2004-09-29') and
: income_from_date 0 and income_booked_date =
: DATEADD(MONTH,1,PF_PREFERENCES.LAST_MONTHEND_DATE) THEN 1 ELSE 0 END) = 1
: AND income_to_date >= perform.getMaxMonthendDate('2004-09-29') and
: income_from_date 0 and income_booked_date between
: perform.getMaxMonthendDate('2004-09-29') and '2004-10-15' : AND PERFORM_ID IN ( SELECT DISTINCT PERFORM_ID FROM PF_ACCOUNT ACCMAST INNER
: HASH JOIN BRANCH_BG_REBATE ON ACCMAST.SOURCE_BRANCH_CODE =
: BRANCH_BG_REBATE.SOURCE_BRANCH_CODE AND GETDATE() BETWEEN
: BRANCH_BG_REBATE.FROM_MON AND BRANCH_BG_REBATE.TO_MON AND
: ACCMAST.DATA_SOURCE = 'BG' ) : ) as x group by perform_id) as inc
: on acc.perform_id = inc.perform_id : Where isnull(acc.gl_account_id,'xxx') not like '0509%' and
: isnull(acc.gl_account_id,'xxx') not like '080%' and
: group by
: product_category_description AND group by product_name : ) as temp group by dimension1, dimension2
: having sum(average_assets) 0 or sum(average_liabilities) 0 or
: sum(current_assets) 0 or sum(current_liabilities) 0 or sum(income) 0
|
