Post reply

Help with insert statement

  • May 21, 2015 at 4:39 am

    #398487

    I have a table with records that are in the form shown below:

    [POLCOUNT_ID] [int] IDENTITY(1,1) NOT NULL,

    [ReportingDate] [date] NOT NULL,

    [Scheme] [varchar](100) NOT NULL,

    [Source] [varchar](11) NOT NULL,

    [PolicyType] [varchar](12) NOT NULL,

    [MethodOfPayment] [varchar](30) NOT NULL,

    [PolicyCount] [int]

    The data is input automatically daily and about 100 records for each day. My issue is when I ran a check to find missing days in the date sequence, I found that the job to input the data failed on several days over the last few years. I am looking for help in creating a script that will input the missing dates and a copy of the data for the previous day inserted for the date that was missing. For example I could have the following data

    1, 2014-03-03, walking, cash, new, cheque, 444

    2, 2014-03-03, Running, Hot, new, cheque, 22

    3, 2014-03-03, Jumping, Jump, new, cheque, 232

    4, 2014-03-03, Swimming, Fine, new, Card, 124

    5, 2014-03-03, walking, cash, new, Cash, 77

    6, 2014-03-05, walking, cash, new, cheque, 23

    7, 2014-03-05, Running, Hot, new, cheque, 356

    8, 2014-03-05, Jumping, Jump, new, cheque, 44

    9, 2014-03-05, Swimming, Fine, new, Card, 65

    10, 2014-03-05, walking, cash, new, Cash, 134

    My need would be to insert the following

    1, 2014-03-04, walking, cash, new, cheque, 444

    2, 2014-03-04, Running, Hot, new, cheque, 22

    3, 2014-03-04, Jumping, Jump, new, cheque, 232

    4, 2014-03-04, Swimming, Fine, new, Card, 124

    5, 2014-03-04, walking, cash, new, Cash, 77

    So if I miss a day I need to insert the missing date along with the data from the previous day that had data. I created a script that will tell me of missing dates, so I know the dates that there is absolutely no data for those dates. I have a second issue where there is some missing data per Scheme. So in effect I have an issue where there is absolutely no data for a certain date and another where there is some dates where some schemes have no data. I would appreciate any help.

  • May 21, 2015 at 7:00 am

    #1799644

    Sorry I should have said

    My need would be to insert the following

    11, 2014-03-04, walking, cash, new, cheque, 444

    12, 2014-03-04, Running, Hot, new, cheque, 22

    13, 2014-03-04, Jumping, Jump, new, cheque, 232

    14, 2014-03-04, Swimming, Fine, new, Card, 124

    15, 2014-03-04, walking, cash, new, Cash, 77

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply