Post reply

Duration between min and max date

  • May 19, 2022 at 12:31 pm

    #4035566

    Hi Expert,

    I wanted to calculate max date for prodstatus =5 - min date for prodstatus=4 and max date for prodstatus =6 - min date for prodstatus=5

    Create table

    CREATE TABLE [dbo].[testdata](

    [Prodnumber] [nchar](20) NULL,

    [Prodid] [nchar](20) NULL,

    [Prodstatusid] [nchar](20) NULL,

    [PrdStartDate] [date] NULL,

    [PrdEndDate] [date] NULL,

    [prodstatus] [nchar](23) NULL

    ) ON [PRIMARY]

    GO

    Insert

    insert into [BI_DW].[dbo].[testdata]

    values('Prod1000','873','7','2021-06-16', '11-05-2022', '4'),

    ('Prod1000','873', '18', '2022-05-12',NULL,'7'),

    ('Prod1000', '873', '19', '2022-05-12', NULL,'5'),

    ('Prod10000', '1254','4', '2022-03-28', '2022-03-30', '6'),

    ('Prod10000', '1254','1', '2022-03-3', NULL, '7'),

    ('Prod10002', '4427', '5', '2022-03-28', '2022-03-31', '4'),

    ('Prod10002', '4427', '8', '2022-04-01', NULL, NULL),

    ('Prod10002', '5603', '8', '2022-04-01', NULL, 2)

     

    expected output attached

     

    • This topic was modified 2 years, 6 months ago by  Shree23.
    • This topic was modified 2 years, 6 months ago by  Shree23.
    • This topic was modified 2 years, 6 months ago by  Shree23.

    Attachments:
    You must be logged in to view attached files.

  • May 19, 2022 at 3:32 pm

    #4035723

    Dude!  Check what you attached. ?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 19, 2022 at 4:31 pm

    #4035731

    Here is the image.. attached again to main question

    • This reply was modified 2 years, 6 months ago by  Shree23.
    • This reply was modified 2 years, 6 months ago by  Shree23.

    Attachments:
    You must be logged in to view attached files.

  • May 20, 2022 at 3:47 am

    #4035974

    You have 2022-05-28 as a date in your desired output but no such date exists in your test data.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

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