Query to add missing rows using values from prior period

I have a r开发者_开发技巧ecord set for inspections of many pieces of equipment. The four cols of interest are equip_id, month, year, myData.

My requirement is to have EXACTLY ONE record per month for each piece of equipment.

I have a query that makes the data unique over equip_id, month, year. So there is no more than one record for each month/year for a piece of equipment. But now I need to simulate data for the missing month. I want to simply go back in time to get the last piece of my data.

So that may seem confusing, so I'll show by example.

Given this sample data:

equip_id  month  year  myData
-----------------------------
1         1      2010  500
1         2      2010  600
1         5      2010  800
2         2      2010  300
2         4      2010  400
2         6      2010  500

I want this output:

equip_id  month  year  myData
-----------------------------
1         1      2010  500
1         2      2010  600
1         3      2010  600
1         4      2010  600
1         5      2010  800
2         2      2010  300
2         3      2010  300
2         4      2010  400
2         5      2010  400
2         6      2010  500

Notice that I'm filling in missing data with the data from the month (or two months etc.) before. Also note that if the first record for equip 2 is in 2/2010 than I don't need a record for 1/2010 even though I have one for equip 1.

I just need exactly one record for each month/year for each piece of equipment. So if the record does not exist I just want to go back in time and grab the data for that record.

Thanks!

By no means perfect:

SELECT equip_id, month, mydata
FROM (

   SELECT equip_id, month, mydata FROM equip
   UNION ALL
   SELECT EquipNum.equip_id, EquipNum.Num, 
     (SELECT Top 1 mydata 
      FROM equip 
      WHERE equip.month<n.num And equip.equip_id=equipnum.equip_id 
      ORDER BY equip.month desc) AS Data
   FROM 
     (SELECT e.equip_id, n.Num 
      FROM 
        (SELECT DISTINCT equip_id FROM equip)  AS e, 
     Numbers AS n)  AS EquipNum 
   LEFT JOIN equip 
   ON (EquipNum.Num = equip.month) 
   AND (EquipNum.equip_id = equip.equip_id)
   WHERE EquipNum.Num<DMax("month","equip")
   AND 
     (SELECT top 1 mydata 
      FROM equip 
      WHERE equip.month<n.num And equip.equip_id=equipnum.equip_id 
      ORDER BY equip.month desc) Is Not Null 
    AND equip.equip_id Is Null AND equip.Month Is Null) AS x
ORDER BY equip_id, month

For this to work you need a Numbers table, in this case it needs only hold integers from 1 to 12. The numbers table I used is called Numbers and the field is called Num.

EDIT re years comment

SELECT equip_id, year, month, mydata
FROM (

   SELECT equip_id, year, month, mydata FROM equip
   UNION ALL
   SELECT en.equip_id, en.year, en.Num, (SELECT Top 1 mydata 
      FROM equip e
      WHERE e.month<n.num And e.year=en.year And e.equip_id=en.equip_id 
      ORDER BY e.month desc) AS Data
   FROM (SELECT e.equip_id, n.Num, y.year
      FROM 
    (SELECT DISTINCT equip_id FROM equip)  AS e, 
    Numbers AS n, 
    (SELECT DISTINCT year FROM equip)  AS y)  AS en 
   LEFT JOIN equip AS e ON en.equip_id = e.equip_id
   AND en.year = e.year
   AND en.Num = e.month
   WHERE en.Num<DMax("month","equip") AND 
     (SELECT Top 1 mydata 
      FROM equip e
      WHERE e.month<n.num And e.year=en.year And e.equip_id=en.equip_id 
      ORDER BY e.month desc) Is Not Null
   AND e.equip_id Is Null 
   AND e.Month Is Null) AS x

ORDER BY equip_id, year, month

I've adjusted to account for year and month... The primary principles remain the same as the original queries presented where just the month. However, for applying a month and year, you need to test for the SET of YEAR + MONTH, ie: what happens if Nov/2009, then jump to Feb/2010, You can't rely on just a month being less than another, but the "set". So, I've apply the year * 12 + month to prevent a false value such as Nov=11 + year=2009 = 2009+11 = 2020, then Feb=2 of year=2010 = 2010+2 = 2012... But 2009*12 = 24108 + Nov = 11 = 24119 compared to 2010*12 = 24120 + Feb =2 = 24122 -- retains proper sequence per year/month combination. The rest of the principles apply. However, one additional, I created a table to represent the span of years to consider. For my testing, I added a sample Equip_ID = 1 entry with a Nov-2009, and Equip_ID = 2 with a Feb-2011 entry and the proper roll-over works too. (Table C_Years, column = year and values of 2009, 2010, 2011)

SELECT 
      PYML.Equip_ID,
      PYML.Year,
      PYML.Mth,
      P1.MyData
   FROM 
      ( SELECT 
             PAll.Equip_ID,
             PAll.Year,
             PAll.Mth,
             ( SELECT MAX( P1.Year*12+P1.Mth )
                   FROM C_Preset P1
                   WHERE PAll.Equip_ID = P1.Equip_ID
                     AND P1.Year*12+P1.Mth <= PAll.CurYrMth) as MaxYrMth
           FROM 
              ( SELECT 
                      PYM1.Equip_ID,
                      Y1.Year,
                      M1.Mth,
                      Y1.Year*12+M1.Mth as CurYrMth
                   FROM 
                      ( SELECT p.equip_id,
                               MIN( p.year*12+p.mth ) as MinYrMth,
                               MAX( p.year*12+p.mth ) as MaxYrMth
                            FROM 
                               C_Preset p
                            group by 
                               1
                      ) PYM1,
                      C_Years Y1,
                      C_Months M1
                   WHERE 
                          Y1.Year*12+M1.Mth >= PYM1.MinYrMth
                      AND Y1.Year*12+M1.Mth <= PYM1.MaxYrMth
              ) PAll
      ) PYML,
      C_Preset P1
   WHERE 
          PYML.Equip_ID = P1.Equip_ID
      AND PYML.MaxYrMth = P1.Year*12+P1.Mth

If this is going to be a repetative thing/report, I would just create a temporary table with 12 months -- then use that as the primary table, and do a left OUTER join to the rest of your data. This way, you know you'll always get every month, but only when a valid join to the "other side" is identified, you'll get that data too. Ooops... missed your point about the filling in missing elements from the last element... Thinking...

The following works... and I'll describe the elements to what is going on. First, I created a temp table "C_Months" with a column Mth (month) with numbers 1-12. I used "Mth" as an abbreviation of Month to not cause possible conflict with POSSIBLE reserved word MONTH. Additionally, in my query, the table reference "C_Preset" is the prepared set of data you mentioned you already have of distinct elements.

SELECT 
      LVM.Equip_ID,
      LVM.Mth,
      P1.Year,
      P1.MyData
   FROM 
      ( SELECT 
              JEM.Equip_ID,
              JEM.Mth,
              ( SELECT MAX( P.Mth ) 
                   FROM C_Preset P
                   WHERE P.Equip_ID = JEM.Equip_ID
                     AND P.Mth <= JEM.Mth ) as MaxMth
           FROM 
              ( SELECT distinct 
                      p.equip_id,
                      c.mth
                   FROM 
                      C_months c,
                      C_Preset p
                   group by 
                      1, 2
                   HAVING 
                          c.mth >= MIN( p.Mth )
                      and c.mth <= MAX( p.Mth )
                   ORDER BY 
                      1, 2 ) JEM
      ) LVM,
      C_Preset P1
   WHERE 
          LVM.Equip_ID = P1.Equip_ID
      AND LVM.MaxMth = P1.Mth
   ORDER BY 
      1, 2

The inner most query is a query of the available months (C_Months) associated with a given equipment ID. In your example, equipment ID 1 had a values of 1,2,5. So this would return 1, 2, 3, 4, 5. And for Equipment ID 2, it started with 2, but ended with 6, so it would return 2, 3, 4, 5, 6. Hence the aliased reference JEM (Just Equipment Months)

Then, the field selection for MaxMth (Maximum month)... This is the TRICKY ONE

      ( SELECT MAX( P.Mth ) 
           FROM C_Preset P
           WHERE P.Equip_ID = JEM.Equip_ID
             AND P.Mth <= JEM.Mth ) as MaxMth

From this, stating I want the maximum month AVAILABLE (from JEM) associated with the given equipment that is AT OR LESS than the month In question (detecting the highest "valid" equipment item/month within the qualified list. The result of this would result in...

Equip_ID Mth    MaxMth
  1      1      1
  1      2      2
  1      3      2
  1      4      2
  1      5      5

  2      2      2
  2      3      2
  2      4      4
  2      5      4
  2      6      6

So, for your example of ID = 1, you had months 1, 2, 5 (3 and 4 were missing), so the last valid month that 3 and 4 would refer to is sequence #2's month. Likewise for ID = 2, you had months 2, 4 and 6... Here, 3 would refer back to 2, 5 would refer back to 4.

The rest is the easy part. Now, we join your LVM (Last Valid Month) result as shown above to your original C_Preset (less records). But since we now have the last valid month to directly associate to an existing record in the C_Preset, we join by equipment id and the MaxMth colum, and NOT THE ACTUAL month.

Hope this helps... Again, you'll probably have to change my "mth" column references to "month" to match your format.