Missing Data range in a set

• PNS

  Right there with Babe

  Points: 773

  More actions

  July 11, 2009 at 9:30 am

  #212069

  Guys,

  I need to find missing data within a set of records.

  There is a table which has data for a group(say a1,b1 cols makes that group in the table below) for each month for any number of years. Now the task is to find any missing month between the month of the first date(oDate) and the current month and populate Enddate(cDate) with the end of month date prior to the missing month.

  The data can be missing for any month in between and the data can come from any month till the current month. With each new start for this data, a new record will be inserted.

  Given is the script to populate source data and tranformed /final data.

  CREATE TABLE #Data1

  (

  [a] [char] (2)NOT NULL,

  [Char] (2)NOT NULL,

  [Odate] [int] NOT NULL

  )

  INSERT INTO #Data1

  SELECT 'a1','b1',20080129

  UNION

  Select 'a1','b1',20080226

  --March month is missing

  UNION

  Select 'a1','b1',20080630

  UNION

  Select 'a1','b1',20080730

  UNION

  Select 'a1','b1',20080831

  UNION

  Select 'a1','b1',20090130

  UNION

  Select 'a1','b1',20090227

  UNION

  Select 'a1','b1',20090331

  UNION

  Select 'a1','b1',20090430

  UNION

  Select 'a1','b1', 20090529

  UNION

  Select 'a1','b1', 20090630

  UNION

  SELECT 'a1','b1',20090129--New set of data

  UNION

  Select 'a1','b1',20090226

  UNION

  Select 'a1','b1',20080330

  --April is missing

  UNION

  Select 'a1','b1',20080531

  UNION

  Select 'a1','b1',20080630

  CREATE TABLE #Data2(

  [a] char(2) NOT NULL,

  Char(2) NOT NULL,

  [Odate] [int] NOT NULL,

  [CDate] [int] NULL

  )

  INSERT INTO #Data2

  SELECT

  'a1','b1',20080101/*(Start of the month of the first date when you order by date)*/,20080226/*(end of previous month before the missing month)*/

  UNION

  SELECT 'a1','b1',20080601,NULL /*should be null if no data/month is missing till the current month of the calendar*/

  UNION

  SELECT 'a2','b1',20090101,20090330

  UNION

  SELECT 'a2','b1',20080501,NULL

  select * from #data1

  select * from #data2

  Thanks in advance

  PS

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 11, 2009 at 10:16 am

  #1023530

  Hi,

  you might want to have a look at the following article:

  http://www.sqlservercentral.com/articles/Datetime+Manipulation/61822/.

  It describes a fast way to generate a sequence of numbers/dates.

  This could be used in an outer join to find the dates that are missing in your table.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• PNS

  Right there with Babe

  Points: 773

  More actions

  July 13, 2009 at 5:35 am

  #1023788

  Hi,

  I am able to find the missing months.What I am looking for is some fast way to roll up the data in a report I have shown in second table #data2

  Thanks

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 13, 2009 at 10:29 am

  #1024029

  Maybe the misunderstanding is caused by the sample data you provided: they just don't seem to match at all...

  Where can the result data for "a2" be found in base table #Data1?

  It would really help a lot if you could explain what you're looking for with samples that do support your description.

  The month March 2008 isn't miss in your sample data: Select 'a1','b1',20080330.

  Shouldn't #Data2 have two more rows with April and May 2008 in it?

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• PNS

  Right there with Babe

  Points: 773

  More actions

  July 14, 2009 at 12:10 am

  #1024251

  Sorry my misatke..I copied wrong data.

  Here is the correct one:

  CREATE TABLE #Data1

  (

  [a] [char] (2)NOT NULL,

  [Char] (2)NOT NULL,

  [Odate] [int] NOT NULL

  )

  INSERT INTO #Data1

  SELECT 'a1','b1',20080129

  UNION

  Select 'a1','b1',20080226

  --March month is missing

  UNION

  Select 'a1','b1',20080630

  UNION

  Select 'a1','b1',20080730

  UNION

  Select 'a1','b1',20080831

  UNION

  --sept month is missing

  Select 'a1','b1',20090130

  UNION

  Select 'a1','b1',20090227

  UNION

  Select 'a1','b1',20090331

  UNION

  Select 'a1','b1',20090430

  UNION

  Select 'a1','b1', 20090529

  UNION

  Select 'a1','b1', 20090630

  UNION

  Select 'a1','b1', 20090731

  UNION

  SELECT 'a2','b1',20090129--New set of data

  UNION

  Select 'a2','b1',20090226

  UNION

  Select 'a2','b1',20090330

  --April is missing

  UNION

  Select 'a2','b1',20090531

  UNION

  Select 'a2','b1',20090630

  UNION

  Select 'a2','b1',20090731

  CREATE TABLE #Data2(

  [a] char(2) NOT NULL,

  Char(2) NOT NULL,

  [Odate] [int] NOT NULL,

  [CDate] [int] NULL

  )

  INSERT INTO #Data2

  SELECT 'a1','b1',20080101/*(Start of the month of the first date when you order by date)*/,20080226/*(end of previous month before the missing month)*/

  UNION

  SELECT 'a1','b1',20080601,20080831

  UNION

  SELECT 'a1','b1',20090130,NULL/*should be null if no data/month is missing till the current month of the calendar*/

  UNION

  SELECT 'a2','b1',20090101,20090330

  UNION

  SELECT 'a2','b1',20090501,NULL

  select * from #data1

  select * from #data2

  Thanks