SQL Server Performance

Issue with getting phone number for primary and secondary owners of an account

Discussion in 'ALL SQL SERVER QUESTIONS' started by eshwar401, Feb 19, 2014.

  1. eshwar401 New Member



    Hi Team,
    I am facing an issue with a SQL Query
    Here is my requirement: Get all the consumers, consumer phones number where the phone number is not updated today for primary consumer and its secondary consumers!

    -----here is the SQL of input and output
    ---------input------------
    Declare @Consumer table
    (
    CnsmrID bigint
    )
    Insert into @Consumer
    Select 3 Union All
    Select 4 Union All
    Select 5 Union All
    Select 6 Union All
    Select 7 Union All
    Select 8 Union All
    Select 9 Union All
    Select 10

    Declare @ConsumerAccount table
    (
    CnsmrID bigint,
    CnsmrAcctID Bigint
    )
    Insert into @ConsumerAccount
    Select 1,105 Union All
    Select 3,103 Union All
    Select 4,104 Union All
    Select 5,105 Union All
    Select 6,106 Union All
    Select 7,107 Union All
    Select 8,108 Union All
    Select 9,109 Union All
    Select 10,109

    Declare @ConsumerAccountOwners table
    (
    CnsmrID bigint,
    CnsmrAcctID Bigint,
    Ownership Varchar(15)
    )
    Insert into @ConsumerAccountOwners
    Select 3,103,'Primary' Union all
    Select 4,104,'Primary' Union all
    Select 1,105,'Secondary' Union all
    Select 5,105,'Primary' Union all
    Select 6,106,'Primary' Union all
    Select 7,107,'Primary' Union all
    Select 8,108,'Primary' Union all
    Select 9,108,'Secondary' Union all
    Select 9,109,'Primary' Union all
    Select 10,109,'Secondary'


    Declare @ConsumerPhone table
    (
    CnsmrID bigint,
    CnsmrPhnNumber Bigint,
    UpdateDate datetime
    )
    Insert into @ConsumerPhone
    Select 5,12345333,'06-17-2013' Union all
    Select 5,12345334,'06-18-2013' Union all
    Select 5,12345335,'06-19-2013' Union all
    Select 6,12345336,'06-20-2013' Union all
    Select 6,12345337,'06-21-2013' Union all
    Select 6,12345338,'02-20-2014' Union all
    Select 7,12345339,'06-22-2013' Union all
    Select 7,12345340,'06-24-2013' Union all
    Select 7,12345341,'06-25-2013' Union all
    Select 8,12345342,'06-26-2013' Union all
    Select 8,12345343,'06-27-2013' Union all
    Select 8,12345344,'06-28-2013' Union all
    Select 9,12345345,'06-29-2013' Union all
    Select 9,12345346,'06-30-2013' Union all
    Select 9,12345347,'06-30-2013' Union all
    Select 10,123453459,'06-29-2013' Union all
    Select 10,222323232,'02-20-2014'

    ----------------Output------------
    --103 there are no cnsmrs with phone number so not required
    --104 there are no cnsmrs with phone numbers so not required
    --106 shouldn't be generated because the primary consumer phone number got updated today for primary consumer cnsmrid 6
    --109 shouldn’t be generated because the phone number is updated for the secondary cnsmr cnsmrid 10
    Select 5 CnsmrID,105 CnsmrAcctID,12345333 CnsmrPhoneNumber Union All
    Select 5,105,12345334 Union All
    Select 5,105,12345335 Union All
    Select 7,107,12345339 Union All
    Select 7,107,12345340 Union All
    Select 7,107,12345341 Union All
    Select 8,108,12345342 Union All
    Select 8,108,12345343 Union All
    Select 8,108,12345344 Union All
    Select 9,108,12345345 Union All
    Select 9,108,12345346 Union All
    Select 9,108,12345347

    Thanks for the help

    Eshwar!

Share This Page