min and max startdate

  • Hi Expert,

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

    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 [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)




    tried :
    --create or alter view vStatusesChange
    --as
    WITH cte AS (SELECT ProdStatus,prodnumber, MIN(PrdStartDate) AS MinStartDate,
    MAX(t.PrdStartDate) AS MaxStartDate

    --select *
    FROM dbo.testdata t WHERE t.ProdStatus between '4' and '5'
    GROUP BY ProdStatus,prodnumber), cte2 AS (

    SELECT

    cte.MinStartDate AS MinStartDate,prodnumber, max(MaxStartDate) OVER (
    ORDER BY CAST(ProdStatus as char)) AS MaxStartDate
    FROM cte)

    SELECT *, CASE WHEN cte2.MaxStartDate IS NULL THEN 0
    ELSE DATEDIFF(DAY, MinStartDate, MaxStartDate) end AS '4_to5'
    FROM cte2
    GO

    am expecting view format


     

    Expected output:

     

    • This topic was modified 1 month, 2 weeks ago by  Shree23.
  • What about the rows which have a value of 'Prod10000'? How do you determine what value of Prodstatusid is returned; is that the MIN too? What is the logic for the columns 4/5 and 4_2? 330 isn't in your data at all.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • it would be 7  which means produnumber,min prodstartdate,prodid will remain same only maxprodstartdate and 4_5 and 4_2 value will change

    see the output:

     

  • it would be 7

    But why would it be 7? You haven't explained the logic for 4_5 and 4_2 either.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • 7 is expected because prodnumber=prod1000 comes with Minstartdate. whatsover value with minstartdate that should come and maxstartdate,4_5,4_2 will be calculated as below

    here is the calculation for 4_5 and 4_2

    calculate max date for prodstatus =5 - min date for prodstatus=4

    and max date for prodstatus =4 - min date for prodstatus=2

  • suggestion pls

  • Ok, Shree23... I just want you to know that this is how you piss people off and they end up not wanting to help you.  Your first posted on anther thread and don't check what you posted with too-small graphics and test data that doesn't match your "desired results".  You get asked about that and, instead of answering the question, you open a new thread with the same test data and totally different desired results.

    Shree23 wrote:

    suggestion pls

    My suggestion is that you stop doing that. 😉

    Good luck.

    --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 7 posts - 1 through 7 (of 7 total)

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