Create a customer calendar from its history

  • Hello,

    I would like to create a calendar from a history table for a customer, so that I can read the status for each day.

    Date        SupportlevelID
    2020-12-29  4
    2020-12-30  NULL
    2020-12-31  NULL
    2020-12-29  NULL

    But I only have a history table where I can find dates of changes.

    SupportlevelID | date                    | CostumerID

    4 | 2019-12-05 14:05:41.540 | 36289
    4 | 2019-12-12 11:43:25.350 | 36289
    NULL | 2019-12-30 14:23:48.070 | 36289
    NULL | 2020-06-05 15:30:01.147 | 36289?

     

    So, the date when the level 4 is ending is the 2019-12-29 and the begining of the Level NULL is the 30th.

    I habe the calendertable to join it, but do not come up with the solution.

     

    Thank for help

    Olaf

     

  • Does your first table represent your desired results?

    How is CustomerId relevant?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Yes, the fist table is my respected result, with the costumerID.

    The second table is like our costumer history table looks like. By changig somethind on the costumer, just a row with the old values.

     

  • olaf.renk wrote:

    Yes, the fist table is my respected result, with the costumerID.

    The second table is like our costumer history table looks like. By changig somethind on the costumer, just a row with the old values.

    OK, but as your first table does not contain CustomerId, how is it useful?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • OK, sorry, it should contain the customer ID. This is 12345.

    ...

    2020-12-29 | 4 | 12345

    2020-12-30 | NULL | 12345

    2020-12-31 | NULL | 12345

    2020-12-29 | NULL | 12345

    ...

    • This reply was modified 1 year, 11 months ago by  olaf.renk.
    • This reply was modified 1 year, 11 months ago by  olaf.renk.
    • This reply was modified 1 year, 11 months ago by  olaf.renk.
  • Please set up a valid set of DDL for the tables (real and expected results) with some insert statements that allow someone to reproduce this.

Viewing 6 posts - 1 through 5 (of 5 total)

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