Query help- Need to create dummy rows

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    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)

    07 06 1125001FE2014063008010854 POS_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

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

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

    Thanks [/font]

  • Xedni

    SSCertifiable

    Points: 6947

    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

    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

    Thanks...works gr8...

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

    Thanks [/font]

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply