Create a customer calendar from its history

  • olaf.renk

    SSC Veteran

    Points: 219

    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

     

  • Phil Parkin

    SSC Guru

    Points: 244589

    Does your first table represent your desired results?

    How is CustomerId relevant?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • olaf.renk

    SSC Veteran

    Points: 219

    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.

     

  • Phil Parkin

    SSC Guru

    Points: 244589

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • olaf.renk

    SSC Veteran

    Points: 219

    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 week, 4 days ago by  olaf.renk.
    • This reply was modified 1 week, 4 days ago by  olaf.renk.
    • This reply was modified 1 week, 4 days ago by  olaf.renk.
  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719974

    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 6 (of 6 total)

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