Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select
- pmc.[month] as 'Month',
- pmc.pd_name_of_project as 'Name of Project',
- tbl_div.name AS 'Name of Advisory Services Division',
- TBL_PMC_UNIT.UNIT_NAME AS 'Name of Unit',
- pmc.pd_project_type as 'Project Type',
- pmc.accepted_tender_cost as 'Accepted Tender Cost',
- pmc.work_order_date as 'Work Order Date',
- pmc.tender_period_months as 'Tender Period',
- pmc.project_completion_date as 'Project Completion Date',
- pmc.per_pmc_charges as '% Of PMC Charges',
- pmc.total_pmc_charges_scheme as 'Total PMC amount of the Scheme',
- pmc.bill_amount_certified_upto_previous_month as 'Bill amount certified upto previous Month',
- pmc.total_PMC_charges_upto_previous_month as 'Total PMC charges upto previous Month',
- pmc.receipt_PMC_charges_upto_previous_month as 'Receipt of PMC Charges upto previous Month',
- pmc.balance_of_PMC_charges_upto_previous_month as 'Balance of PMC charges upto previous Month',
- pmc.bill_amount_certified_current_month as 'Bill amount certified During Current Month',
- pmc.PMC_charges_for_current_month as ' PMC charges During Current Month',
- pmc.receipt_PMC_charges_current_month as 'Receipt of PMC Charges During Current Monthh',
- pmc.balance_of_PMC_charges_current_month as 'Balance of PMC charges During Current Month',
- SUM(pmc.salary_allowance) as 'Salary & Allowance Division'
- FROM
- TBL_PMC pmc
- INNER JOIN TBL_DIV
- ON TBL_DIV.ID = pmc.DIV_ID
- LEFT OUTER JOIN TBL_PMC_UNIT
- ON TBL_PMC_UNIT.ID=pmc.UNIT_ID
- WHERE
- pmc.div_id= 17
- --and pmc.unit_id=@unit_id;
- group by
- pmc.[month]
- SUM(pmc.salary_allowance) OVER(PARTITION BY pmc.[month])
- select pmc1.[month] as 'Month',
- pmc2.pd_name_of_project as 'Name of Project',
- tbl_div.name AS 'Name of Advisory Services Division',
- TBL_PMC_UNIT.UNIT_NAME AS 'Name of Unit',
- pmc2.pd_project_type as 'Project Type',
- pmc2.accepted_tender_cost as 'Accepted Tender Cost',
- pmc2.work_order_date as 'Work Order Date',
- pmc2.tender_period_months as 'Tender Period',
- pmc2.project_completion_date as 'Project Completion Date',
- pmc2.per_pmc_charges as '% Of PMC Charges',
- pmc2.total_pmc_charges_scheme as 'Total PMC amount of the Scheme',
- pmc2.bill_amount_certified_upto_previous_month as 'Bill amount certified upto previous Month',
- pmc2.total_PMC_charges_upto_previous_month as 'Total PMC charges upto previous Month',
- pmc2.receipt_PMC_charges_upto_previous_month as 'Receipt of PMC Charges upto previous Month',
- pmc2.balance_of_PMC_charges_upto_previous_month as 'Balance of PMC charges upto previous Month',
- pmc2.bill_amount_certified_current_month as 'Bill amount certified During Current Month',
- pmc2.PMC_charges_for_current_month as ' PMC charges During Current Month',
- pmc2.receipt_PMC_charges_current_month as 'Receipt of PMC Charges During Current Monthh',
- pmc2.balance_of_PMC_charges_current_month as 'Balance of PMC charges During Current Month',
- pmc1.salary_allowance as 'Salary & Allowance Division'
- FROM
- (
- SELECT [month] as 'Month',
- SUM(pmc.salary_allowance) salary_allowance
- FROM TBL_PMC
- GROUP BY [month]
- ) pmc1
- INNER JOIN TBL_PMC pmc2
- ON pmc1.[month] = pmc2.[month]
- INNER JOIN TBL_DIV
- ON TBL_DIV.ID = pmc2.DIV_ID
- LEFT OUTER JOIN TBL_PMC_UNIT
- ON TBL_PMC_UNIT.ID=pmc2.UNIT_ID
- WHERE pmc2.div_id= 17
- --and pmc.unit_id=@unit_id;
- select a.*,b.[Salary & Allowance Division] from
- (select
- pmc.[month] as 'Month',
- pmc.pd_name_of_project as 'Name of Project',
- tbl_div.name AS 'Name of Advisory Services Division',
- TBL_PMC_UNIT.UNIT_NAME AS 'Name of Unit',
- pmc.pd_project_type as 'Project Type',
- pmc.accepted_tender_cost as 'Accepted Tender Cost',
- pmc.work_order_date as 'Work Order Date',
- pmc.tender_period_months as 'Tender Period',
- pmc.project_completion_date as 'Project Completion Date',
- pmc.per_pmc_charges as '% Of PMC Charges',
- pmc.total_pmc_charges_scheme as 'Total PMC amount of the Scheme',
- pmc.bill_amount_certified_upto_previous_month as 'Bill amount certified upto previous Month',
- pmc.total_PMC_charges_upto_previous_month as 'Total PMC charges upto previous Month',
- pmc.receipt_PMC_charges_upto_previous_month as 'Receipt of PMC Charges upto previous Month',
- pmc.balance_of_PMC_charges_upto_previous_month as 'Balance of PMC charges upto previous Month',
- pmc.bill_amount_certified_current_month as 'Bill amount certified During Current Month',
- pmc.PMC_charges_for_current_month as ' PMC charges During Current Month',
- pmc.receipt_PMC_charges_current_month as 'Receipt of PMC Charges During Current Monthh',
- pmc.balance_of_PMC_charges_current_month as 'Balance of PMC charges During Current Month'
- FROM
- TBL_PMC pmc
- INNER JOIN TBL_DIV
- ON TBL_DIV.ID = pmc.DIV_ID
- LEFT OUTER JOIN TBL_PMC_UNIT
- ON TBL_PMC_UNIT.ID=pmc.UNIT_ID
- WHERE
- pmc.div_id= 17
- --and pmc.unit_id=@unit_id;
- group by
- pmc.[month]
- ) a left join
- (select
- pmc.[month] as 'Month',
- SUM(pmc.salary_allowance) as 'Salary & Allowance Division'
- FROM
- TBL_PMC pmc
- INNER JOIN TBL_DIV
- ON TBL_DIV.ID = pmc.DIV_ID
- LEFT OUTER JOIN TBL_PMC_UNIT
- ON TBL_PMC_UNIT.ID=pmc.UNIT_ID
- WHERE
- pmc.div_id= 17
- --and pmc.unit_id=@unit_id;
- group by
- pmc.[month]) b
- on a.month = b.month
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement