Inserting records for missing values

• rekha_sara

  SSC-Addicted

  Points: 467

  More actions

  November 4, 2009 at 9:10 am

  #137056

  create table projdetails(ID varchar(10),year numeric(4,0),det1 numeric(10,2),det2 numeric(10,2))

  Table contains details

  ID Year Det1 Det2

  100 2001 0.0 0.0

  100 2002 0.0 0.0

  100 2003 0.0 0.0

  100 2007 0.0 0.0

  100 2008 0.0 0.0

  101 1998 0.0 0.0

  101 1999 0.0 0.0

  101 2000 0.0 0.0

  101 2005 0.0 0.0

  101 2006 0.0 0.0

  Here if you see for a particular ID, in the year column,there are certain years missing.For eg:for ID 100,

  3 yrs are missing.(i.e,2004,2005,2006) and for ID 101, 4 yrs are missing.

  The requirement is to find out the missing yrs for each ID and add dummy records for these yrs.

  Except for ID and Year,rest all fields remain same for the dummy records.Also as you can see

  the range of Years(max and min) for each ID varies.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 4, 2009 at 9:29 am

  #1074879

  good job on giving us the CREATE TABLE; you gave us sample data, but not in an easy-to-use format.

  you'll see below, along with the INSERT INTO statements, we are able to offer you a complete,tested example in the future. try to include that part too!

  the key here is you need to come up with some kind of a calendar table that has all the years you expect in the data.

  then you insert based on a LEFT OUTER JOIN of the desired data to teh data that already exists.

  I'm not sure you need to INSERT the data into your table, or just be able to procude the results in a SELECT.

  I've included both below:

  create table projdetails(

  ID varchar(10),

  year numeric(4,0),

  det1 numeric(10,2),

  det2 numeric(10,2))

  INSERT INTO projdetails

  SELECT 100,2001,0.0,0.0 UNION ALL

  SELECT 100,2002,0.0,0.0 UNION ALL

  SELECT 100,2003,0.0,0.0 UNION ALL

  SELECT 100,2007,0.0,0.0 UNION ALL

  SELECT 100,2008,0.0,0.0 UNION ALL

  SELECT 101,1998,0.0,0.0 UNION ALL

  SELECT 101,1999,0.0,0.0 UNION ALL

  SELECT 101,2000,0.0,0.0 UNION ALL

  SELECT 101,2005,0.0,0.0 UNION ALL

  SELECT 101,2006,0.0,0.0

  --i want to SELECT data for each Id for years 1999 through today's year:

  SELECT

  projdetails.ID,

  Calendar.year,

  isnull(projdetails.det1,0.0) as det1,

  isnull(projdetails.det2,0.0) as det2

  from projdetails

  CROSS JOIN (SELECT 1999 AS year UNION ALL

  SELECT 2000 AS year UNION ALL

  SELECT 2001 AS year UNION ALL

  SELECT 2002 AS year UNION ALL

  SELECT 2003 AS year UNION ALL

  SELECT 2004 AS year UNION ALL

  SELECT 2005 AS year UNION ALL

  SELECT 2006 AS year UNION ALL

  SELECT 2007 AS year UNION ALL

  SELECT 2008 AS year UNION ALL

  SELECT 2009 AS year ) Calendar

  --inserting where there is no match:

  INSERT INTO projdetails

  SELECT

  --projdetails.*, --uncomment so you can see the missing data

  MyAlias.*

  FROM

  ( SELECT

  projdetails.ID,

  Calendar.year,

  isnull(projdetails.det1,0.0) as det1,

  isnull(projdetails.det2,0.0) as det2

  from projdetails

  CROSS JOIN (SELECT 1999 AS year UNION ALL

  SELECT 2000 AS year UNION ALL

  SELECT 2001 AS year UNION ALL

  SELECT 2002 AS year UNION ALL

  SELECT 2003 AS year UNION ALL

  SELECT 2004 AS year UNION ALL

  SELECT 2005 AS year UNION ALL

  SELECT 2006 AS year UNION ALL

  SELECT 2007 AS year UNION ALL

  SELECT 2008 AS year UNION ALL

  SELECT 2009 AS year ) Calendar

  )MyAlias

  LEFT OUTER JOIN projdetails

  ON MyAlias.ID = projdetails.ID AND MyAlias.year = projdetails.year

  WHERE projdetails.ID IS NULL

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!