locked
Minus fields from one table from another RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all,

    I need to minus each field in CTE_1 with each field in CTE_2

    how can i do this?

    WITH cte_1 AS (
    SELECT
      plob.planningLOB_description,
      UW_section,
      renewableGWP,
      cancelledGWP,
      renewedGWP,
      shareChangeCedant,
      shareChangeXL, rateChangeCedant,
      rateChangeXL,
      otherGWPChange,
      renewed,
      newBusiness,
      blockCancellations,
      outcome,
      0 AS isVersionB
     FROM [dbo].[v_report_activity] v
     
     INNER JOIN [dbo].[t_res_d_planningLOB] plob
     ON v.planningLOB_key = plob.planningLOB_key

     WHERE
      plan_key IN (1)
     
     
     )
     
    ,
     cte_2 AS (
     SELECT
      plob.planningLOB_description,
      UW_section,
      renewableGWP,
      cancelledGWP,
      renewedGWP,
      shareChangeCedant,
      shareChangeXL, rateChangeCedant,
      rateChangeXL,
      otherGWPChange,
      renewed,
      newBusiness,
      blockCancellations,
      outcome,
      0 AS isVersionB
     FROM [dbo].[v_report_activity] v
     
     INNER JOIN [dbo].[t_res_d_planningLOB] plob
     ON v.planningLOB_key = plob.planningLOB_key

     WHERE
      plan_key IN (2)
     
     )

     

    Thursday, July 29, 2010 9:01 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote 
    If values of fields are not same but number of rows are same.then you can do in this way.

WITH cte_1 AS (
SELECT 
 ROW_NUMBER() OVER (ORDRE BY UW_Section)	AS RowNo1,
plob.planningLOB_description, 
 UW_section,
 renewableGWP, 
 cancelledGWP, 
 renewedGWP,
 shareChangeCedant, 
 shareChangeXL, rateChangeCedant,
 rateChangeXL, 
 otherGWPChange, 
 renewed,
 newBusiness, 
 blockCancellations, 
 outcome, 
 0 AS isVersionB
 FROM [dbo].[v_report_activity] v
 
 INNER JOIN [dbo].[t_res_d_planningLOB] plob
 ON v.planningLOB_key = plob.planningLOB_key

 WHERE 
 plan_key IN (1)
 
 
 )
 
,
 cte_2 AS (
 SELECT 
	ROW_NUMBER() OVER (ORDRE BY UW_Section)	AS RowNo2, 
plob.planningLOB_description, 
 UW_section,
 renewableGWP, 
 cancelledGWP, 
 renewedGWP,
 shareChangeCedant, 
 shareChangeXL, rateChangeCedant,
 rateChangeXL, 
 otherGWPChange, 
 renewed,
 newBusiness, 
 blockCancellations, 
 outcome, 
 0 AS isVersionB
 FROM [dbo].[v_report_activity] v
 
 INNER JOIN [dbo].[t_res_d_planningLOB] plob
 ON v.planningLOB_key = plob.planningLOB_key

 WHERE 
 plan_key IN (2)
 
 )
SELECT
	C1.UW_section - C2.UW_section,
  C1.renewableGWP - C2.renewableGWP, 
  C1.cancelledGWP-C2.cancelledGWP, 
  C1.renewedGWP -C2.renewedGWP ,
  C1.shareChangeCedant-C2.shareChangeCedant, 
  C1.shareChangeXL-C2.shareChangeXL, 
  C1.rateChangeCedant-C2.rateChangeCedant,
  C1.rateChangeXL-C2.rateChangeXL, 
  C1.otherGWPChange-C2.otherGWPChange, 
  C1.renewed-C2.renewed,
  C1.newBusiness-C2.newBusiness, 
  C1.blockCancellations-C2.blockCancellations, 
  C1.outcome-C2.outcome, 
  C1.isVersionB
FROM
	cte_1 C1
  inner join cte_2 C2
  on
  (

   C1.RowNo1 = C2.RowNo2
  )
    • Proposed as answer by Naomi N Sunday, August 1, 2010 8:32 PM
    • Marked as answer by KJian_ Wednesday, August 4, 2010 9:08 AM
    Friday, July 30, 2010 9:21 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    You will need JOIN cte_1 and cte_2 and perfrom calculation

    select col1-col2 from cte_1 join cte_2 on.............

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 29, 2010 9:07 AM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    You will need JOIN cte_1 and cte_2 and perfrom calculation

    select col1-col2 from cte_1 join cte_2 on.............

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Naomi N Sunday, August 1, 2010 8:31 PM
    Thursday, July 29, 2010 9:07 AM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    How would you minus plob.planningLOB_description. By name , it looks like varchar value?

    What exactally is your requirement ?

    Thursday, July 29, 2010 9:27 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Apart from the plob.planningLOB_description I want to minus everything else...

    I did the below, but it takes ages to run...is the join better?

     

    WITH cte_1 AS (
    SELECT
      plob.planningLOB_description,
      UW_section,
      renewableGWP,
      cancelledGWP,
      renewedGWP,
      shareChangeCedant,
      shareChangeXL, rateChangeCedant,
      rateChangeXL,
      otherGWPChange,
      renewed,
      newBusiness,
      blockCancellations,
      outcome,
      0 AS isVersionB
     FROM [dbo].[v_report_activity] v
     
     INNER JOIN [dbo].[t_res_d_planningLOB] plob
     ON v.planningLOB_key = plob.planningLOB_key

     WHERE
      plan_key IN (1)
      
      
     )
     
    ,
     cte_2 AS (
     SELECT
      plob.planningLOB_description,
      UW_section,
      renewableGWP,
      cancelledGWP,
      renewedGWP,
      shareChangeCedant,
      shareChangeXL, rateChangeCedant,
      rateChangeXL,
      otherGWPChange,
      renewed,
      newBusiness,
      blockCancellations,
      outcome,
      0 AS isVersionB
     FROM [dbo].[v_report_activity] v
     
     INNER JOIN [dbo].[t_res_d_planningLOB] plob
     ON v.planningLOB_key = plob.planningLOB_key

     WHERE
      plan_key IN (2)
     
      
     )

    SELECT

            planningLOB_description,
      UW_section,
      renewableGWP,
      cancelledGWP,
      renewedGWP,
      shareChangeCedant,
      shareChangeXL, rateChangeCedant,
      rateChangeXL,
      otherGWPChange,
      renewed,
      newBusiness,
      blockCancellations,
      outcome
    FROM cte_1

    UNION ALL

    SELECT  planningLOB_description, UW_section,
       (-1) * renewableGWP,
       (-1) * cancelledGWP,
       (-1) * renewedGWP,
       (-1) * shareChangeCedant,
       (-1) * shareChangeXL,
       (-1) * rateChangeCedant,
       (-1) * rateChangeXL,
       (-1) * otherGWPChange,
       (-1) * renewed,
       (-1) * newBusiness,
       (-1) * blockCancellations,
       (-1) * outcome
    FROM cte_2

    Thursday, July 29, 2010 9:29 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Jus realised I cant do a join...because the fields will be different..please help
    Thursday, July 29, 2010 9:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    then how will you link the two tables? will it be according to the row number? Like first row of table1 - from first row of table2, secon from second and so on?

    Friday, July 30, 2010 8:50 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, can you make an example with some data?

    Nicola

    Friday, July 30, 2010 9:13 AM
  • Question
    Sign in to vote
    0
    Sign in to vote 
    If values of fields are not same but number of rows are same.then you can do in this way.

WITH cte_1 AS (
SELECT 
 ROW_NUMBER() OVER (ORDRE BY UW_Section)	AS RowNo1,
plob.planningLOB_description, 
 UW_section,
 renewableGWP, 
 cancelledGWP, 
 renewedGWP,
 shareChangeCedant, 
 shareChangeXL, rateChangeCedant,
 rateChangeXL, 
 otherGWPChange, 
 renewed,
 newBusiness, 
 blockCancellations, 
 outcome, 
 0 AS isVersionB
 FROM [dbo].[v_report_activity] v
 
 INNER JOIN [dbo].[t_res_d_planningLOB] plob
 ON v.planningLOB_key = plob.planningLOB_key

 WHERE 
 plan_key IN (1)
 
 
 )
 
,
 cte_2 AS (
 SELECT 
	ROW_NUMBER() OVER (ORDRE BY UW_Section)	AS RowNo2, 
plob.planningLOB_description, 
 UW_section,
 renewableGWP, 
 cancelledGWP, 
 renewedGWP,
 shareChangeCedant, 
 shareChangeXL, rateChangeCedant,
 rateChangeXL, 
 otherGWPChange, 
 renewed,
 newBusiness, 
 blockCancellations, 
 outcome, 
 0 AS isVersionB
 FROM [dbo].[v_report_activity] v
 
 INNER JOIN [dbo].[t_res_d_planningLOB] plob
 ON v.planningLOB_key = plob.planningLOB_key

 WHERE 
 plan_key IN (2)
 
 )
SELECT
	C1.UW_section - C2.UW_section,
  C1.renewableGWP - C2.renewableGWP, 
  C1.cancelledGWP-C2.cancelledGWP, 
  C1.renewedGWP -C2.renewedGWP ,
  C1.shareChangeCedant-C2.shareChangeCedant, 
  C1.shareChangeXL-C2.shareChangeXL, 
  C1.rateChangeCedant-C2.rateChangeCedant,
  C1.rateChangeXL-C2.rateChangeXL, 
  C1.otherGWPChange-C2.otherGWPChange, 
  C1.renewed-C2.renewed,
  C1.newBusiness-C2.newBusiness, 
  C1.blockCancellations-C2.blockCancellations, 
  C1.outcome-C2.outcome, 
  C1.isVersionB
FROM
	cte_1 C1
  inner join cte_2 C2
  on
  (

   C1.RowNo1 = C2.RowNo2
  )
    • Proposed as answer by Naomi N Sunday, August 1, 2010 8:32 PM
    • Marked as answer by KJian_ Wednesday, August 4, 2010 9:08 AM
    Friday, July 30, 2010 9:21 AM