SQL fiscal dates, flat files, misery

Question

Using sqlite3, python

I have a crappy legacy file legacy_sales:

item | year | week | mon | tue | wed | thu | fri | sat |sun 
4444   2011     29    10    0     4    15     18    25   30

And I have an awesome new file sales (this is an example of the 'mon' entry from above):

item | units |     date
4444   10       2011-03-14

I have the start date of fiscal years in another table fiscal

year | start_date
2011   2010-08-30

With this information what is the best and most efficient way to accomplish this:

insert into sales from (insert magic here)

without using any UDFs...

Any experience with such drivel?

score 1 · Accepted Answer · answered Mar 22 '11 at 08:31

insert into sales from (
    -- here be magic
    select  item, units, DATEADD(day, DayInYear, start_date)
    from    (
        select  item,
                year,
                (week - 1) * 7 + 0 as DayInYear,
                mon as Units
        from    legacy_sales
        union all
        select  item, year, (week - 1) * 7 + 1, tue
        from    legacy_sales
        union all
        select  item, year, (week - 1) * 7 + 2, wed
        from    legacy_sales
        union all
        select  item, year, (week - 1) * 7 + 3, thu
        from    legacy_sales
        union all
        select  item, year, (week - 1) * 7 + 4, fri
        from    legacy_sales
        union all
        select  item, year, (week - 1) * 7 + 5, sat
        from    legacy_sales
        union all
        select  item, year, (week - 1) * 7 + 6, sun
        from    legacy_sales
        ) ls
        inner join fiscal on
            fiscal.year = ls.year
)

There must be some equivalent. Worst case though, many systems implement date as an Integer number of days from a particular baseline, so simply adding the number of days (perhaps with appropriate typecasting) should suffice. — Disillusioned, Mar 22 '11 at 10:31

score 0 · Answer 2 · answered Mar 22 '11 at 04:07

converting from: legacy_sales

item | year | week | mon | tue | wed | thu | fri | sat |sun 
4444   2011     29    10    0     4    15     18    25   30

to: sales

item | units |     date
4444   10       2011-03-14

with needs this about in English python:

for row in legacy_sales:
    year = row['year']
    week = row['week']
    for day_name in "mon | tue | wed | thu | fri | sat |sun".split(" | "):
        some how turn (year, week, day_name) into year_month_day
        insert into sales values (row['item'], row[day_name], year_month_day)

SQL fiscal dates, flat files, misery

2 Answers2