SHARE
TWEET

Untitled

a guest Feb 22nd, 2019 52 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select  
  2.     pmc.[month] as 'Month',
  3.     pmc.pd_name_of_project as 'Name of Project',
  4.     tbl_div.name AS 'Name of Advisory Services Division',
  5.     TBL_PMC_UNIT.UNIT_NAME AS 'Name of Unit',
  6.     pmc.pd_project_type as 'Project Type',
  7.     pmc.accepted_tender_cost as 'Accepted Tender Cost',
  8.     pmc.work_order_date as 'Work Order Date',
  9.     pmc.tender_period_months as 'Tender Period',
  10.     pmc.project_completion_date as 'Project Completion Date',
  11.     pmc.per_pmc_charges as '% Of PMC Charges',
  12.     pmc.total_pmc_charges_scheme as 'Total PMC amount   of the Scheme',
  13.     pmc.bill_amount_certified_upto_previous_month as 'Bill amount certified upto previous Month',    
  14.     pmc.total_PMC_charges_upto_previous_month as 'Total PMC charges  upto previous Month',
  15.     pmc.receipt_PMC_charges_upto_previous_month as 'Receipt of PMC Charges upto previous Month',
  16.     pmc.balance_of_PMC_charges_upto_previous_month as 'Balance of PMC charges upto previous Month',
  17.     pmc.bill_amount_certified_current_month as 'Bill amount certified During Current Month',
  18.     pmc.PMC_charges_for_current_month as ' PMC charges  During Current Month',
  19.     pmc.receipt_PMC_charges_current_month as 'Receipt of PMC Charges During Current Monthh',
  20.     pmc.balance_of_PMC_charges_current_month as 'Balance of PMC charges During Current Month',
  21.     SUM(pmc.salary_allowance) as 'Salary & Allowance Division'
  22. FROM
  23.     TBL_PMC pmc
  24.         INNER JOIN TBL_DIV
  25.             ON TBL_DIV.ID = pmc.DIV_ID
  26.         LEFT OUTER JOIN TBL_PMC_UNIT
  27.             ON TBL_PMC_UNIT.ID=pmc.UNIT_ID  
  28. WHERE
  29.     pmc.div_id= 17
  30.     --and pmc.unit_id=@unit_id;
  31. group by  
  32.     pmc.[month]
  33.    
  34. SUM(pmc.salary_allowance) OVER(PARTITION BY pmc.[month])
  35.    
  36. select  pmc1.[month] as 'Month',
  37.     pmc2.pd_name_of_project as 'Name of Project',
  38.     tbl_div.name AS 'Name of Advisory Services Division',
  39.     TBL_PMC_UNIT.UNIT_NAME AS 'Name of Unit',
  40.     pmc2.pd_project_type as 'Project Type',
  41.     pmc2.accepted_tender_cost as 'Accepted Tender Cost',
  42.     pmc2.work_order_date as 'Work Order Date',
  43.     pmc2.tender_period_months as 'Tender Period',
  44.     pmc2.project_completion_date as 'Project Completion Date',
  45.     pmc2.per_pmc_charges as '% Of PMC Charges',
  46.     pmc2.total_pmc_charges_scheme as 'Total PMC amount   of the Scheme',
  47.     pmc2.bill_amount_certified_upto_previous_month as 'Bill amount certified upto previous Month',    
  48.     pmc2.total_PMC_charges_upto_previous_month as 'Total PMC charges  upto previous Month',
  49.     pmc2.receipt_PMC_charges_upto_previous_month as 'Receipt of PMC Charges upto previous Month',
  50.     pmc2.balance_of_PMC_charges_upto_previous_month as 'Balance of PMC charges upto previous Month',
  51.     pmc2.bill_amount_certified_current_month as 'Bill amount certified During Current Month',
  52.     pmc2.PMC_charges_for_current_month as ' PMC charges  During Current Month',
  53.     pmc2.receipt_PMC_charges_current_month as 'Receipt of PMC Charges During Current Monthh',
  54.     pmc2.balance_of_PMC_charges_current_month as 'Balance of PMC charges During Current Month',
  55.     pmc1.salary_allowance as 'Salary & Allowance Division'
  56. FROM
  57. (
  58.     SELECT [month] as 'Month',
  59.         SUM(pmc.salary_allowance) salary_allowance
  60.     FROM TBL_PMC
  61.     GROUP BY [month]
  62. ) pmc1
  63. INNER JOIN TBL_PMC pmc2
  64.     ON pmc1.[month] = pmc2.[month]
  65. INNER JOIN TBL_DIV
  66.     ON TBL_DIV.ID = pmc2.DIV_ID
  67. LEFT OUTER JOIN TBL_PMC_UNIT
  68.     ON TBL_PMC_UNIT.ID=pmc2.UNIT_ID  
  69. WHERE pmc2.div_id= 17
  70.     --and pmc.unit_id=@unit_id;
  71.    
  72. select a.*,b.[Salary & Allowance Division] from
  73. (select  
  74.     pmc.[month] as 'Month',
  75.     pmc.pd_name_of_project as 'Name of Project',
  76.     tbl_div.name AS 'Name of Advisory Services Division',
  77.     TBL_PMC_UNIT.UNIT_NAME AS 'Name of Unit',
  78.     pmc.pd_project_type as 'Project Type',
  79.     pmc.accepted_tender_cost as 'Accepted Tender Cost',
  80.     pmc.work_order_date as 'Work Order Date',
  81.     pmc.tender_period_months as 'Tender Period',
  82.     pmc.project_completion_date as 'Project Completion Date',
  83.     pmc.per_pmc_charges as '% Of PMC Charges',
  84.     pmc.total_pmc_charges_scheme as 'Total PMC amount   of the Scheme',
  85.     pmc.bill_amount_certified_upto_previous_month as 'Bill amount certified upto previous Month',    
  86.     pmc.total_PMC_charges_upto_previous_month as 'Total PMC charges  upto previous Month',
  87.     pmc.receipt_PMC_charges_upto_previous_month as 'Receipt of PMC Charges upto previous Month',
  88.     pmc.balance_of_PMC_charges_upto_previous_month as 'Balance of PMC charges upto previous Month',
  89.     pmc.bill_amount_certified_current_month as 'Bill amount certified During Current Month',
  90.     pmc.PMC_charges_for_current_month as ' PMC charges  During Current Month',
  91.     pmc.receipt_PMC_charges_current_month as 'Receipt of PMC Charges During Current Monthh',
  92.     pmc.balance_of_PMC_charges_current_month as 'Balance of PMC charges During Current Month'
  93. FROM
  94.     TBL_PMC pmc
  95.         INNER JOIN TBL_DIV
  96.             ON TBL_DIV.ID = pmc.DIV_ID
  97.         LEFT OUTER JOIN TBL_PMC_UNIT
  98.             ON TBL_PMC_UNIT.ID=pmc.UNIT_ID  
  99. WHERE
  100.     pmc.div_id= 17
  101.     --and pmc.unit_id=@unit_id;
  102. group by  
  103.     pmc.[month]
  104. ) a left join
  105.  
  106. (select  
  107.        pmc.[month] as 'Month',
  108.        SUM(pmc.salary_allowance) as 'Salary & Allowance Division'
  109. FROM
  110.     TBL_PMC pmc
  111.         INNER JOIN TBL_DIV
  112.             ON TBL_DIV.ID = pmc.DIV_ID
  113.         LEFT OUTER JOIN TBL_PMC_UNIT
  114.             ON TBL_PMC_UNIT.ID=pmc.UNIT_ID  
  115. WHERE
  116.     pmc.div_id= 17
  117.     --and pmc.unit_id=@unit_id;
  118. group by  
  119.     pmc.[month]) b
  120.  
  121. on a.month = b.month
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top