Return Date Range based on the No. of Days-CodePudding

I am trying to return date range as the 4th column based on the NoOfDays in 3rd column with the following sql query.

For ex:

if it is 70 as NoOfDays, it should return 61-70 as DateRange

if it is 51 as NoOfDays, it should return 51-60 as DateRange

if it is 100 as NoOfDays, it should return 91-100 as DateRange

if it is 174 as NoOfDays, it should return 171-180 as DateRange

DECLARE @V_START INT = 1;
DECLARE @V_END INT = 10;

SELECT GETDATE() CurrentDate, TASK_ASSIGNED_DTM,
       DATEDIFF(DAY, TASK_ASSIGNED_DTM, GETDATE()) NoOfDays, (CAST(@V_START AS VARCHAR(10)) '-' CAST(@V_END AS VARCHAR(10))) AS DateRange                       
FROM Table

CodePudding user response：

Perhaps this can work for you?

You can use modulo to determin how to format your range, like so:

with sample as (
  select * from (values(70),(51),(100),(174))x(n)
)
select Iif(n % 10 = 0, Concat(n - 9, '-', n), Concat(n - (n % 10)   1, '-', n - (n % 10)   10)) as Range
from sample;

You could apply this to your current query (untested of course) like so:

select GetDate() CurrentDate, TASK_ASSIGNED_DTM,
  n.NoOfDays, 
  Iif(n.NoOfDays % 10 = 0, 
    Concat(n.NoOfDays - 9, '-', n.NoOfDays), 
    Concat( n.NoOfDays - (n.NoOfDays % 10)   1, '-', n.NoOfDays - (n.NoOfDays % 10)   10)
  ) as Daterange                  
from table
cross apply(values(datediff(day, TASK_ASSIGNED_DTM, getdate())))n(NoOfDays);

CodePudding user response：

I tend to use a generic TIER table. It reduces the number of touch-points (removes from code) and offers a more data-driven approach for the business logic and subsequent evolutions.

Here is a dramatically simplified approach. The table variable @Tiers should be a physical table.

Example

Declare @Tiers Table ([Grp] varchar(50),[Dsc] varchar(50),[R1] int,[R2] int)
Insert Into @Tiers Values 
 ('DPD','Current',0,30)
,('DPD','31-50',31,50)
,('DPD','51-60',51,60)
,('DPD','61-70',61,70)
,('DPD','71-80',71,80)
,('DPD','81-90',81,90)
,('DPD','91-100',91,100)
,('DPD','101-170',101,170)
,('DPD','171-180',171,180)
,('DPD','181 ',181,9999)
 

Declare @YourTable Table ([TASK_ASSIGNED_DTM] date)
Insert Into @YourTable Values 
 ('2022-07-15')
,('2022-08-29')
,('2022-12-03')
,('2023-01-02')
 

Select A.*
      ,B.Dsc
 From  @YourTable A
 Join  @Tiers     B on B.Grp='DPD' and datediff(DAY,TASK_ASSIGNED_DTM,getdate()) between R1 and R2

Results

TASK_ASSIGNED_DTM   Dsc
2022-07-15          171-180
2022-08-29          101-170
2022-12-03          31-50
2023-01-02          Current