CODEWITHSUNDEEP
mysql
John Woo
John Woo

Reputation: 263803

Filling empty records through SELECT..INSERT statement

Before anything else, here is the simplified schema (with dummy records) of the database:

ItemList

ItemID     ItemName     DateAcquired     Cost       MonthlyDep    CurrentValue
================================================================================
1          Stuff Toy    2011-12-25       100.00         10.00         100.00
2          Mouse        2011-12-23       250.00         50.00         200.00
3          Keyboard     2011-12-17       250.00         30.00         190.00
4          Umbrella     2011-12-28       150.00         20.00         110.00
5          Aircon       2011-12-29       950.00         25.00         925.00

DepreciationTransaction

ItemID     DateOfDep       MonthlyDep     
======================================
2          2012-01-31         250.00
3          2012-01-31          30.00
4          2012-01-31          20.00
5          2012-01-31          25.00
3          2012-02-29          30.00
4          2012-02-29          20.00

I need your suggestions to help me solve this problem. Basically I am creating a depreciation monitoring system of a certain LGU. The problem of the current database is that it lacks some records for a specific date of depreciation, for instance:

Lacking Records (this is not a table from the database)

ItemID      LackingDate
============================
1            2012-01-31
1            2012-02-29
2            2012-02-29
5            2012-02-29

And because of the lacking records, I cannot generate the depreciation report for the month of MARCH. Any idea how can I insert missing records on the DepreciationTransaction?

What have I done so far? None. But a simple query that calculates the newly depreciated value (which produces incorrect value because of the missing records)

Upvotes: 0

Views: 367

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43464

The problem here is that you will have to generate data. MySQL is not intended to generate data, you should do that at an application level and just tell MySQL to store it. In this case, the application should check wether there are missing records and create them if needed.

Leaving that aside, you can (awfully) create dynamic data with MySQL like this:

select il.itemId, endOfMonths.aDate from ((
  select aDate from (
    select @maxDate - interval (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) day aDate from
    (select 0 as a union all select 1 union all select 2 union all 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) a, /*10 day range*/
    (select 0 as a union all select 1 union all select 2 union all 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) b, /*100 day range*/
    (select 0 as a union all select 1 union all select 2 union all 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) c, /*1000 day range*/
    (select 0 as a union all select 1 union all select 2 union all 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) d, /*10000 day range*/
    (select @minDate := (select min(dateAcquired) from il),
            @maxDate := '2012-03-01') e
  ) f
  where aDate between @minDate and @maxDate and aDate = last_day(aDate)
) endOfMonths, il)
left join dt
on il.itemId = dt.itemId and endOfMonths.aDate = dt.dateOfDep
where dt.itemId is null and last_day(il.dateAcquired) < endOfMonths.aDate

Depending on the length of the date range you can reduce the amount of dynamically generated results (10000 days means over 27 years of records each representing one day) by removing tables (d, c, b and a) and removing them from the upper formula. Setting the @minDate and @maxDate variables will allow you to specify the dates between you want to filter the results. This dates should be the min date from which you have an item and the max date should be march, in your case.

In plain english: If select min(dateAcquired) from il returns a date before '2012-03-01' - 10000 days then you'll have to add another union.

Finally, just add the insert statement (if you really need to insert those records).

Upvotes: 1

Mike Lue
Mike Lue

Reputation: 839

You may build a temporary table, which contains the date needed. And use the table to LEFT OUTER JOIN the "DepreciationTransaction" table.

SELECT dt.date_value, dt.itemid, ISNULL(SUM(dt.MonthlyDep), 0)
FROM tmp_date
    LEFT OUTER JOIN
    DepreciationTransaction AS dt
    ON tmp_date.date_value = dt.DateOfDep
GROUP BY dt.date_value, dt.itemid

Of course, if your want that all of the items to be on report, you should make a cartesian product with tmp_date and items_id.

Upvotes: 1

Related Questions