find missing months

• Papil

  SSCommitted

  Points: 1983

  More actions

  November 10, 2017 at 4:21 am

  #326164

  please help in finding missing months from the last one year- 2016-11 till 2017-11.
  My data looks like-

  Column1   column2
  2016-11   A
  2017-01   A
  2017-04  A
  2017-05  A
  2017-06  A
  2017-08  A
  2017-10  A
  2016-11  B
  2017-03  B

  Output be-
  2016-12   A
  2017-02   A
  2017-03  A
  2017-07  A
  2017-09  A
  2017-11  A

  similarly for B.
  Thanks

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  November 10, 2017 at 5:32 am

  #1967504

  Papil - Friday, November 10, 2017 4:21 AM

  please help in finding missing months from the last one year- 2016-11 till 2017-11.
  My data looks like-

  Column1   column2
  2016-11   A
  2017-01   A
  2017-04  A
  2017-05  A
  2017-06  A
  2017-08  A
  2017-10  A
  2016-11  B
  2017-03  B

  Output be-
  2016-12   A
  2017-02   A
  2017-03  A
  2017-07  A
  2017-09  A
  2017-11  A

  similarly for B.
  Thanks

  Can you provide your data in consumable format, please?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 10, 2017 at 5:39 am

  #1967506

  WITH Calendar AS (
 SELECT FirstOfMonth = DATEADD(MONTH,n-1,Startdate)
 FROM (SELECT Startdate = '20161101', EndDate = '20171101') d
 CROSS APPLY (
  SELECT TOP(1 + DATEDIFF(MONTH,Startdate, EndDate))
   n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
  (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n)
 ) x
),
Matrix AS (
 SELECT
  Column1 = CONVERT(CHAR(7),FirstOfMonth,121),
  Column2
 FROM Calendar c
 CROSS JOIN (VALUES ('A'),('B')) d (Column2)
)
SELECT *
FROM Matrix m
WHERE NOT EXISTS (
 SELECT 1 FROM [MyTableWithMissingValues] t
 WHERE t.Column2 = m.Column2
 AND t.Column1 = m.Column1)
ORDER BY Column2, Column1

  ^{“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

• Thom A

  SSC Guru

  Points: 99130

  More actions

  November 10, 2017 at 5:42 am

  #1967507

  DDL would be really helpful here as looking at your data, you aren't storing your dates, as dates.

  Firstly, have a look at having a Calendar table; they're a staple to a lot of date questions: http://www.sqlservercentral.com/articles/calendar/145206/. Then, you can do something like this. There is likely a quicker way, but does the job. Note, however, that it doesn't include 2017-11 as you dates stop at 2017-10 for 'A'. Without further logic, then I have no idea where this should stop.

  USE OpenGIDW;

  GO

  CREATE TABLE #Sample (MonthValue char(7), C char(1));

  GO

  INSERT INTO #Sample

  VALUES

    ('2016-11','A'),

    ('2017-01','A'),

    ('2017-04','A'),

    ('2017-05','A'),

    ('2017-06','A'),

    ('2017-08','A'),

    ('2017-10','A'),

    ('2016-11','B'),

    ('2017-03','B');

  GO

  SELECT *

  FROM #Sample;

  GO

  WITH AsDates AS (

    SELECT *,

      CONVERT(date,MonthValue + '-01') AS MonthDate

    FROM #Sample),

  MinMax AS(

    SELECT C,

      MIN(MonthDate) AS MinMonth,

      MAX(MonthDate) AS MaxMonth

    FROM AsDates

    GROUP BY C)

  SELECT DD.[Date], MM.C, AD.MonthValue

  FROM DimDate DD

    JOIN MinMax MM ON DD.[Date] BETWEEN MM.MinMonth AND MM.MaxMonth

    LEFT JOIN AsDates AD ON DD.[Date] = AD.MonthDate AND MM.C = AD.C

  WHERE DD.[Calendar Day] = 1

  AND AD.MonthValue IS NULL;

  GO

  DROP TABLE #Sample;

  GO

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Papil

  SSCommitted

  Points: 1983

  More actions

  November 10, 2017 at 5:50 am

  #1967509

  ChrisM@Work - Friday, November 10, 2017 5:39 AM

  WITH Calendar AS (
 SELECT FirstOfMonth = DATEADD(MONTH,n-1,Startdate)
 FROM (SELECT Startdate = '20161101', EndDate = '20171101') d
 CROSS APPLY (
  SELECT TOP(1 + DATEDIFF(MONTH,Startdate, EndDate))
   n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
  (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n)
 ) x
),
Matrix AS (
 SELECT
  Column1 = CONVERT(CHAR(7),FirstOfMonth,121),
  Column2
 FROM Calendar c
 CROSS JOIN (VALUES ('A'),('B')) d (Column2)
)
SELECT *
FROM Matrix m
WHERE NOT EXISTS (
 SELECT 1 FROM [MyTableWithMissingValues] t
 WHERE t.Column2 = m.Column2
 AND t.Column1 = m.Column1)
ORDER BY Column2, Column1

  it worked. thanks