none
Get values missing from table RRS feed

  • Question

  • I am trying to get the values missing in table 2 for a time period.

    create table ##temp1
    ( region varchar(255), timeskey int)
    insert into ##temp1
    values
    ( 'Rest of Asia','201901'),
    ( 'Rest of Asia','201902'),
    ( 'Rest of Asia','201903')
    create table ##temp2
    ( location varchar(255),region varchar(255), timeskey int)

    insert into ##temp2
    values
    ( 'India','Rest of Asia','201901'),
    ( 'India','Rest of Asia','201902'),
    ( 'India','Rest of Asia','201903'),
    ( 'New Zealand','Rest of Asia','201901'),
    ( 'New Zealand','Rest of Asia','201902')

    The out put I am looking is to get after joining the table 1 and 2 is

    New Zealand,Rest of Asia ,201903 has it is missing in temp2 table for that time frame.  I tried the below query however the null value is not populating has India region has value.

    select * from  ##temp1 as A
    left join ##temp2 as B
    on A.cmiregion = b.cmiregion
    and a.timeskey = b.timeskey
    order by b.location

    Wednesday, August 7, 2019 8:00 PM

Answers

  • create table ##temp1
    ( region varchar(255), timeskey int)
    insert into ##temp1
    values
    ( 'Rest of Asia','201901'),
    ( 'Rest of Asia','201902'),
    ( 'Rest of Asia','201903')
    create table ##temp2
    ( location varchar(255),region varchar(255), timeskey int)
    
    insert into ##temp2
    values
    ( 'India','Rest of Asia','201901'),
    ( 'India','Rest of Asia','201902'),
    ( 'India','Rest of Asia','201903'),
    ( 'New Zealand','Rest of Asia','201901'),
    ( 'New Zealand','Rest of Asia','201902') 
    
    ;with mycte as (
    Select distinct location  from ##temp2)
    ,mycte2 as (
    Select  region,timeskey, location from ##temp1, mycte 
    )
    
    Select m.region,m.location,b.region,b.timeskey from mycte2 m
    left join ##temp2 as B
    on m.location = b.location and m.timeskey = b.timeskey
    order by m.location
    
     
    
    
    drop  table  ##temp2, ##temp1 

    • Marked as answer by Abhishek_300 Wednesday, August 14, 2019 11:19 AM
    Wednesday, August 7, 2019 8:12 PM
    Moderator
  • Hi Abhishek_300,

     

    Would you like this one?

     
    create table ##temp1
    ( region varchar(255), timeskey int)
    insert into ##temp1
    values
    ( 'Rest of Asia','201901'),
    ( 'Rest of Asia','201902'),
    ( 'Rest of Asia','201903')
    create table ##temp2
    ( location varchar(255),region varchar(255), timeskey int)
    
    insert into ##temp2
    values
    ( 'India','Rest of Asia','201901'),
    ( 'India','Rest of Asia','201902'),
    ( 'India','Rest of Asia','201903'),
    ( 'New Zealand','Rest of Asia','201901'),
    ( 'New Zealand','Rest of Asia','201902')
    
    
    ;with mycte as (
    Select distinct location  from ##temp2)
    ,mycte2 as (
    Select  region,timeskey, location from ##temp1 cross join  mycte 
    )
    Select m.location,m.region,m.timeskey 
    from mycte2 m
    /*
    location      region              timeskey
    ------------- ------------------- -----------
    India         Rest of Asia        201901
    India         Rest of Asia        201902
    India         Rest of Asia        201903
    New Zealand   Rest of Asia        201901
    New Zealand   Rest of Asia        201902
    New Zealand   Rest of Asia        201903
    */
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 8, 2019 2:32 AM

All replies

  • create table ##temp1
    ( region varchar(255), timeskey int)
    insert into ##temp1
    values
    ( 'Rest of Asia','201901'),
    ( 'Rest of Asia','201902'),
    ( 'Rest of Asia','201903')
    create table ##temp2
    ( location varchar(255),region varchar(255), timeskey int)
    
    insert into ##temp2
    values
    ( 'India','Rest of Asia','201901'),
    ( 'India','Rest of Asia','201902'),
    ( 'India','Rest of Asia','201903'),
    ( 'New Zealand','Rest of Asia','201901'),
    ( 'New Zealand','Rest of Asia','201902') 
    
    ;with mycte as (
    Select distinct location  from ##temp2)
    ,mycte2 as (
    Select  region,timeskey, location from ##temp1, mycte 
    )
    
    Select m.region,m.location,b.region,b.timeskey from mycte2 m
    left join ##temp2 as B
    on m.location = b.location and m.timeskey = b.timeskey
    order by m.location
    
     
    
    
    drop  table  ##temp2, ##temp1 

    • Marked as answer by Abhishek_300 Wednesday, August 14, 2019 11:19 AM
    Wednesday, August 7, 2019 8:12 PM
    Moderator
  • Hi Abhishek_300,

     

    Would you like this one?

     
    create table ##temp1
    ( region varchar(255), timeskey int)
    insert into ##temp1
    values
    ( 'Rest of Asia','201901'),
    ( 'Rest of Asia','201902'),
    ( 'Rest of Asia','201903')
    create table ##temp2
    ( location varchar(255),region varchar(255), timeskey int)
    
    insert into ##temp2
    values
    ( 'India','Rest of Asia','201901'),
    ( 'India','Rest of Asia','201902'),
    ( 'India','Rest of Asia','201903'),
    ( 'New Zealand','Rest of Asia','201901'),
    ( 'New Zealand','Rest of Asia','201902')
    
    
    ;with mycte as (
    Select distinct location  from ##temp2)
    ,mycte2 as (
    Select  region,timeskey, location from ##temp1 cross join  mycte 
    )
    Select m.location,m.region,m.timeskey 
    from mycte2 m
    /*
    location      region              timeskey
    ------------- ------------------- -----------
    India         Rest of Asia        201901
    India         Rest of Asia        201902
    India         Rest of Asia        201903
    New Zealand   Rest of Asia        201901
    New Zealand   Rest of Asia        201902
    New Zealand   Rest of Asia        201903
    */
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 8, 2019 2:32 AM
  • Try this too:

    select     t2.region, t2.[location], t1.timeskey

    from       ##temp2 as t2

    inner join ##temp1 as t1 on t1.region = t2.region

    except

    select     t2.region, t2.[location], t2.timeskey

    from       ##temp2 as t2

    order by   timeskey, region, [location]

     

    • Edited by Viorel_MVP Thursday, August 8, 2019 6:23 AM
    Thursday, August 8, 2019 6:20 AM
  • Hi Abhishek_300,

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 14, 2019 9:35 AM