Query help- Need to create dummy rows

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  June 30, 2014 at 10:29 am

  #290524

  Hi All,

  I have a requirement that I need help with,

  I am getting the table Mytemp populated from SRC tables that will have only good data...

  but after populating I need to check if Any Row is missing for an YEAR/PlanNo/Network combination for a particular DOCid ,if yes then I need to create a DUMMY row with AMOUNT1 value as 0.00 and Amount 2 value will be taken from the other table that I provided by joining with YEAR/Planno/Network

  Here is some code

  CREATE TABLE [dbo].[Mytemp](

  [Type] [char](2) NULL,

  [Version] [char](2) NULL,

  [cDocId] [char](25) NULL,

  [Plancode] [char](10) NULL,

  [Network] [char](10) NULL,

  [Year1] [char](30) NULL,

  [PlanNo] [char](30) NULL,

  [Amount1] [char](30) NULL,

  [Amount2] [char](30) NULL

  )

  INSERT INTO Mytemp ( Type,Version,cDocId,Plancode ,Network,Year1 ,PlanNo,Amount1,Amount2) VALUES ( '07', '06', '1125001FE2014063008000003', 'POS_HMO ' , 'HMO ', '2014 ', '458 ', '118.00 ', '2900.00 ')

  INSERT INTO Mytemp ( Type,Version,cDocId,Plancode ,Network,Year1,PlanNo,Amount1,Amount2) VALUES ( '07', '06', '1125001FE2014063008005137', 'POS_HMO ' , 'HMO ', '2014 ', '456 ', '143.96 ', '4500.00 ')

  INSERT INTO Mytemp ( Type,Version,cDocId,Plancode ,Network,Year1,PlanNo,Amount1,Amount2) VALUES ( '07', '06', '1125001FE2014063008005137', 'POS_HMO ' , 'POS ', '2013 ', '456 ', '0.00 ', '8000.00 ')

  INSERT INTO Mytemp ( Type,Version,cDocId,Plancode ,Network,Year1,PlanNo,Amount1,Amount2) VALUES ( '07', '06', '1125001FE2014063008005137', 'POS_HMO ' , 'HMO ', '2013 ', '456 ', '1978.69 ', '6700.00 ')

  INSERT INTO Mytemp ( Type,Version,cDocId,Plancode ,Network,Year1,PlanNo,Amount1,Amount2) VALUES ( '07', '06', '1125001FE2014063008010854', 'POS_HMO ' , 'POS ', '2013 ', '458 ', '2.14 ', '8000.00 ')

  INSERT INTO Mytemp ( Type,Version,cDocId,Plancode ,Network,Year1,PlanNo,Amount1,Amount2) VALUES ( '07', '06', '1125001FE2014063008010854', 'POS_HMO ' , 'HMO ', '2013 ', '458 ', '0.00 ', '2900.00 ')

  INSERT INTO Mytemp ( Type,Version,cDocId,Plancode ,Network,Year1,PlanNo,Amount1,Amount2) VALUES ( '07', '06', '1125001FE2014063008010854', 'POS_HMO ' , 'POS ', '2014 ', '458 ', '15.67 ', '8000.00 ')

  select * from Mytemp

  Here is the 2nd table to get Amount 2 values from by Joining on YEAR/Planno/Network...

  ----------------------------------------2nd TABLE to fetch amount2 VALUE from

  CREATE TABLE [dbo].[Amount](

  [PlanNo] [char](3) NULL,

  [Year] [char](4) NULL,

  [Network] [varchar](6) NULL,

  [AmountValue] [decimal](12, 2) NULL

  )

  INSERT INTO Amount ( PlanNo,Year,Network,AmountValue) VALUES ( '456', '2013', 'HMO', 6700.00)

  INSERT INTO Amount ( PlanNo,Year,Network,AmountValue) VALUES ( '456', '2013', 'POS ', 8000.00)

  INSERT INTO Amount ( PlanNo,Year,Network,AmountValue) VALUES ( '456', '2014', 'HMO', 4500.00)

  INSERT INTO Amount ( PlanNo,Year,Network,AmountValue) VALUES ( '456', '2014', 'POS ', 8000.00)

  INSERT INTO Amount ( PlanNo,Year,Network,AmountValue) VALUES ( '458', '2013', 'HMO', 2900.00)

  INSERT INTO Amount ( PlanNo,Year,Network,AmountValue) VALUES ( '458', '2013', 'POS ', 8000.00)

  INSERT INTO Amount ( PlanNo,Year,Network,AmountValue) VALUES ( '458', '2014', 'HMO', 2900.00)

  INSERT INTO Amount ( PlanNo,Year,Network,AmountValue) VALUES ( '458', '2014', 'POS ', 8000.00)

  Expected results for Docid =1125001FE2014063008010854(an extra row for 2014 HMO with 2900 Amount2 value)

  07061125001FE2014063008010854POS_HMO HMO 2014 458 0.00 2900.00

  Any help on this please..Thanks in Advance

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  June 30, 2014 at 11:54 am

  #1725944

  Any help...............

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• Xedni

  SSCertifiable

  Points: 6951

  More actions

  June 30, 2014 at 11:55 am

  #1725945

  Try something like this. Create a framework table of all the potential values you want (the ones which dont define the "Amount" columns) and then left join it to your actual result. Where the joins fail, substitute the values you want.

  ;with src as

  (

  select distinct c.type, c.Version, c.PlanCode, c.cDocId, a.*

  from Amount a

  inner join MyTemp c

  on a.PlanNo = c.PlanNo

  and a.[year] = c.year1

  )

  select

  Type = a.Type,

  Version = a.Version,

  PlanNo = a.PlanNo,

  Year = a.Year,

  Network = a.Network,

  PlanCode = a.PlanCode,

  cDocId = a.cDocID,

  Amount1 = cast(isnull(b.Amount1, 0) as float),

  Amount2 = a.AmountValue

  from src a

  left outer join myTemp b

  on a.cDocID = b.cDocID

  and a.PlanNo = b.PlanNo

  and a.[year] = b.year1

  and a.network = b.network

  Executive Junior Cowboy Developer, Esq.[/url]

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  June 30, 2014 at 12:05 pm

  #1725947

  Hi..

  Thank you

  it was a great help and on time..

  i will implement this on my complete code now...

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  June 30, 2014 at 1:37 pm

  #1725969

  Thanks...works gr8...

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]