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

• eshwararao.ch

  Old Hand

  Points: 318

  More actions

  February 19, 2014 at 10:20 pm

  #289653

  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!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 20, 2014 at 3:11 am

  #1690582

  Thanks for posting readily-consumable data, great job. I prefer working with #temp tables and it only took moments to convert your script.

  Figuring out how to filter unwanted rows in this kind of scenario can be quite tricky, where removal of a whole bunch of rows depends on only one (of the bunch of rows) matching something. Here's a way of making it easy.

  1. Write your query without the filter and with a couple of visually-useful columns from each of the tables appearing in the output. Use '#' '#' as a separator between the columns of one table and the next. "Stare and compare" to ensure that your joins are correct and you have all the columns you need to assess whether or not your filter is working.

  2. Write the filter as an OUTER APPLY block and incorporate it into your query. OUTER APPLY works a little like LEFT (OUTER) JOIN and won't eliminate rows from your output - but might introduce more.

  "Stare and Compare" again. If the filter is working correctly then rows to be eliminated will be marked as such in the output of the APPLY.

  All you have to do now is change the OUTER APPLY to something like NOT EXISTS, "Stare and Compare" once more and you're done.

  Here's a query which takes you to part 2, see if you can work out how to complete it:

  SELECT

  c.*,

  '#' '#',

  cao.*,

  '#' '#',

  ca.*,

  '#' '#',

  cp.*,

  '#' '#',

  x.*

  FROM #Consumer c

  INNER JOIN #ConsumerAccountOwners cao ON cao.CnsmrID = c.CnsmrID

  LEFT JOIN #ConsumerAccount ca ON ca.CnsmrID = cao.CnsmrID AND ca.CnsmrAcctID = cao.CnsmrAcctID

  INNER JOIN #ConsumerPhone cp ON cp.CnsmrID = c.CnsmrID

  OUTER APPLY (

  SELECT Flag = 'Gotcha'

  FROM #ConsumerAccountOwners o

  INNER JOIN #ConsumerPhone p ON p.CnsmrID = o.CnsmrID

  WHERE o.CnsmrAcctID = cao.CnsmrAcctID

  AND p.UpdateDate = CAST(GETDATE() AS DATE)

  ) x

  ORDER BY cao.CnsmrID, cao.CnsmrAcctID

  Here's a sample script with #temp tables:

  DROP TABLE #Consumer

  CREATE TABLE #Consumer (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

  DROP TABLE #ConsumerAccount

  CREATE TABLE #ConsumerAccount (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

  DROP TABLE #ConsumerAccountOwners

  CREATE TABLE #ConsumerAccountOwners (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'

  DROP TABLE #ConsumerPhone

  CREATE TABLE #ConsumerPhone (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'

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden