Call Dates Effective Range

  • Hi Everyone, I have the data in the below form :

    Desired output:

    Here what I did is I made a range of dates of the same year in 1st image using the call date start date(in Select)

    and now I have the 1st date of the year starting to the day before the effective call date i.e. 06/06, and call price which is applicable after mid-year i.e. 06/07, So from 06/07 to year-end(12/31)

    The same is happening for the year 2024

    The whole scenario is for a use case security_master_id, and there can exist a security that might have more call dates as well so a generic query would be required considering such edge cases, can anyone please help in getting the desired output in SSMS,

    Let me know if any more input or clarity is required

    Thanks!

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • We do need some more information here.

    While you have provided your desired output, can you also provide the input data corresponding with that desired output, so that someone here can help formulate a solution for you?

    Please provide that input data in consumable form (so that we can paste it straight into SSMS and run it). For your desired data, it might look something like this, for example:

    DROP TABLE IF EXISTS #SomeData;

    CREATE TABLE #SomeData
    (
    SecurityMasterId BIGINT
    ,CallStartDate DATE
    ,CallEndDate DATE
    );

    INSERT #SomeData
    (
    SecurityMasterId
    ,CallStartDate
    ,CallEndDate
    )
    VALUES
    (21527, '20230101', '20230606')
    ,(21527, '20230607', '20231231');

    SELECT *
    FROM #SomeData sd;

     

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • What is meant by more call dates and edge cases?  Does each [Call Date Start Date] always get split into 2 rows?  [Call Date Start Date] is an unfortunate name for a column imo as it contains space(s) and "Date" twice

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Yash34 wrote:

    Hi Everyone, I have the data in the below form :

    Desired output:

    Here what I did is I made a range of dates of the same year in 1st image using the call date start date(in Select) and now I have the 1st date of the year starting to the day before the effective call date i.e. 06/06, and call price which is applicable after mid-year i.e. 06/07, So from 06/07 to year-end(12/31) The same is happening for the year 2024 The whole scenario is for a use case security_master_id, and there can exist a security that might have more call dates as well so a generic query would be required considering such edge cases, can anyone please help in getting the desired output in SSMS, Let me know if any more input or clarity is required Thanks!

    Hey there Yash... Welcome Aboard!

    You did great with the description but it help others help you in the future if you include "Readily Consumable Meta-data and Data".  Here's one way to do that.  Another way can be found in the article at the first link in my signature line below.  Graphics tell a nice picture but you can't copy'n'paste them into SSMS.

    --===== Create the test table. This is NOT a part of the solution.
    -- We're just creating test data here.
    -- DROP TABLE IF EXISTS #CD;
    SELECT security_master_id = 21527
    ,[Call Date Start Date] = CONVERT(DATETIME,v.StringDate)
    INTO #CD
    FROM (VALUES('20230107'),('20240107'))v(StringDate)
    ;
    GO

    Here's one possible solution... you implicitly asked for formatted dates so I formatted them but it's generally a good idea to avoid such formatting in SQL.  Let the front-end or reporting tool do that, if you're using one.  If you can't quite figure out what was done, post back.

    --===== One possible solution
    SELECT security_master_id
    ,CallStartDate = CONVERT(CHAR(10),oa.CallStartDate,101) -- formatted dates should be avoided!
    ,CallEndDate = CONVERT(CHAR(10),oa.CallEndDate ,101) -- formatted dates should be avoided!
    FROM #CD
    CROSS APPLY (VALUES --Does the "split" on the date and creates two rows as requested.
    (DATEADD(yy,DATEDIFF(yy,0,[Call Date Start Date]),0), [Call Date Start Date]-1)
    ,([Call Date Start Date], DATEADD(yy,DATEDIFF(yy,0,[Call Date Start Date]),-1))
    )oa(CallStartDate,CallEndDate)
    ;

    Here's what the output of all that looks like...

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

  • The OP has left the building!

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

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