Duration between min and max date

  • 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 1 month, 1 week ago by  Shree23.
    • This topic was modified 1 month, 1 week ago by  Shree23.
    • This topic was modified 1 month, 1 week ago by  Shree23.
    Attachments:
    You must be logged in to view attached files.
  • 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)
    Intro to Tally Tables and Functions

  • Here is the image.. attached again to main question

    • This reply was modified 1 month, 1 week ago by  Shree23.
    • This reply was modified 1 month, 1 week ago by  Shree23.
    Attachments:
    You must be logged in to view attached files.
  • 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)
    Intro to Tally Tables and Functions

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

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