Call Dates Effective Range

Question

Post reply

Call Dates Effective Range

Yash34

SSC Enthusiast

Points: 112
More actions
August 30, 2024 at 9:19 pm

#4452744

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!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Site Owners SSC Guru Points: 80325 More actions · Answer 1

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Collins SSCrazy Points: 2173 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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)