Date Question...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: frank20 (frank20_at_cox.net)
Date: 09/23/04

  • Next message: The Eeediot: "Saving text to MS-SQL..." 
    Date: Thu, 23 Sep 2004 12:45:50 -0500

    Have a SQL Server database that I am populating (for a client) using file
    data from legacy COBOL / Unix system. They are using Microstrategy against
    the database for BI purposes...and it is working. When I load rows from
    legacy files, I am including a data/time stamp.

    Legacy system follows basically a weekly cycle (for financial reporting
    purposes), so majority of data produced is associated with a given week.
    However, the individual files are processed and arrive to me for loading
    'wily-nily'. The majority of these files do not have any embedded 'date'
    based grouping value...some have 'week ending date', but not many.

    Right now, client can use 'date stored' as a 'loose' grouping method for
    developing reports (I say 'loose' because a set of associated weeks files
    could carry 7 different 'date stored' values). Client would prefer that all
    rows have some kind of 'information date' value that equates to a single day
    of the week...so grouping by week would be 'tight'.

    The idea would be to set 'information date' on Sunday, then date-stamp all
    files received / processed into the database during that week with
    'information date' of Sunday; Come next Sunday, new 'information date' set.

    Is there any, I dare to say, 'easy' way to do this? Has anyone out there
    handled a similar kind of requirement?

    I am thinking that one way would be to create a 'week group' ('information
    date') table, a scheduled program that updates it each Sunday night, then
    access that table for date/time stamp to insert when DTS packages process /
    load files from legacy system. Does that make sense?

    Feedback welcome 

    -- 
Regards,
Frank J. Hannaford
Phone: (402) 453-4326 (direct)
Email: frank20@cox.net
Email:  fhannaford@coretechomaha.com
Email: forefront@cox.net
  • Next message: The Eeediot: "Saving text to MS-SQL..."

    Relevant Pages