Returning data from table based on date ranges

  • sc-w

    Hall of Fame

    Points: 3658

    Hi There,

    I have been play with some scripts and getting a bit stuck.

    So I have a table called charges that stores rent and service charges for properties. Some properties pay rent monthly and others in English quarter days which are (March 25th/June 24th/September 29th and December 25th).

    What I am trying to do is search for the quarter starting 24th June to 29th September and to display the charges due in that date range based on the data in the charges table.

    Based on the sample table/data below I would like to see the following results that start and end dates falling in the dates above. See results below.

    Is there a query that can check the date and determine how many months to display for the given date range for the monthly due charges and also teh 1 off quarterly chagres?

    Thanks for looking.

    Lease ref             Charge Type Code           From                     to                            Period Amount

    1                              Rent                                      01/07/2020         31/07/2020         1000.00

    1                              Service Charge                  01/07/2020         31/07/2020         100.00

    1                              Rent                                      01/08/2020         31/08/2020         1000.00

    1                              Service Charge                  01/08/2020         31/08/2020         100.00

    1                              Rent                                      01/09/2020         30/09/2020         1000.00

    1                              Service Charge                  01/09/2020         30/09/2020         100.00

    4                              Rent                                      25/06/2020         28/09/2020         3000.00

    4                              Service Charge                  25/06/2020         28/09/2020         300.00

    USE testDB 

    CREATE TABLE [charges](
    [uri] [int] NOT NULL,
    [lease_ref] [numeric](6, 0) NOT NULL,
    [charge_type_code] [varchar](50) NULL,
    [start_date] [datetime] NULL,
    [end_date] [datetime] NULL,
    [due_date_code] [varchar](50) NULL,
    [annual_amount] [numeric](13, 2) NOT NULL,
    [period_amount] [numeric](13, 2) NOT NULL
    ) ON [PRIMARY]

    GO

    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('1', '1', 'Rent', '2020-08-01', '2021-08-01', 'Monthly', '12000', '1000')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('2', '1', 'Service Charge', '2020-08-01', '2021-08-01', 'Monthly', '1200', '100')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('3', '4', 'Rent', '2020-08-01', '2021-08-01', 'EQD', '12000', '3000')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('4', '4', 'Service Charge', '2020-08-01', '2021-08-01', 'EQD', '1200', '300')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('5', '5', 'Rent', '2021-08-01', '2026-08-01', 'EQD', '36000', '3000')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('6', '5', 'Service Charge', '2021-08-01', '2026-08-01', 'EQD', '3600', '300')


    USE testDB 

    CREATE TABLE [charges](
    [uri] [int] NOT NULL,
    [lease_ref] [numeric](6, 0) NOT NULL,
    [charge_type_code] [varchar](50) NULL,
    [start_date] [datetime] NULL,
    [end_date] [datetime] NULL,
    [due_date_code] [varchar](50) NULL,
    [annual_amount] [numeric](13, 2) NOT NULL,
    [period_amount] [numeric](13, 2) NOT NULL
    ) ON [PRIMARY]

    GO

    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('1', '1', 'Rent', '2020-08-01', '2021-08-01', 'Monthly', '12000', '1200')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('2', '1', 'Service Charge', '2020-08-01', '2021-08-01', 'Monthly', '1200', '100')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('3', '4', 'Rent', '2020-08-01', '2021-08-01', 'EQD', '12000', '1200')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('4', '4', 'Service Charge', '2020-08-01', '2021-08-01', 'EQD', '1200', '100')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('5', '5', 'Rent', '2021-08-01', '2026-08-01', 'EQD', '36000', '3000')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('6', '5', 'Service Charge', '2021-08-01', '2026-08-01', 'EQD', '3600', '300')

    • This topic was modified 3 weeks, 3 days ago by  sc-w.
    • This topic was modified 3 weeks, 3 days ago by  sc-w.
  • kcecil 47772

    Grasshopper

    Points: 17

    If I understand your question, one solution could be to generate a date/calendar table with a column to return the English Quarter for a given date. Then just Join to the calendar table based on the from date, picking the EngQtr.

    Here is a nice article on generating such a table (you would have to add the logic for English Quarter).

    https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

     

    • This reply was modified 3 weeks, 3 days ago by  kcecil 47772.
  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720359

    I would concur that a calendar table is the best choice here. There aren't functions that map out specific dates to a custom calendar. While you can code for this, and likely calculate the dates, it's easier for all developers and report writers if you use a calendar table, essentially a date dimension, and then join to that.

    We have a few articles here as well:

     

  • sc-w

    Hall of Fame

    Points: 3658

    Thank you guys. That's very helpful. I will take a look at this documentation.

  • Jeff Moden

    SSC Guru

    Points: 996807

    sc-w wrote:

    So I have a table called charges that stores rent and service charges for properties. Some properties pay rent monthly and others in English quarter days which are (March 25th/June 24th/September 29th and December 25th).

    To be honest, Calendar tables can be a bit of a PITA depending, of course, on what you're doing.

    I don't know about "English Quarter Days" so let me ask the question... do the dates you have above apply to ALL years or do they change every year?  If they change every year, what are they based on?

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Steve Collins

    SSC Eights!

    Points: 874

    The OP could generate as many years as needed with a tally table 🙂

    declare
    @start_yr int=2020,
    @years int=20;


    ;with eng_qtr_cte(start_dt, end_dt, yr, qtr) as (
    select datefromparts((@start_yr-1)+n, 12, 26), datefromparts(@start_yr+n, 3, 25), @start_yr+n, 'Q1' from dbo.fnTally(0, @years)
    union all
    select datefromparts(@start_yr+n, 3, 26), datefromparts(@start_yr+n, 6, 24), @start_yr+n, 'Q2' from dbo.fnTally(0, @years)
    union all
    select datefromparts(@start_yr+n, 6, 25), datefromparts(@start_yr+n, 9, 29), @start_yr+n, 'Q3' from dbo.fnTally(0, @years)
    union all
    select datefromparts(@start_yr+n, 9, 30), datefromparts(@start_yr+n, 12, 25), @start_yr+n, 'Q4' from dbo.fnTally(0, @years))
    select * from eng_qtr_cte order by 1;

    • This reply was modified 3 weeks, 2 days ago by  Steve Collins. Reason: union all
  • sc-w

    Hall of Fame

    Points: 3658

    Hi Jeff, thanks for your reply. The dates will not changes each year they will remain the same. What are your thoughts based on this? Thanks.

    Jeff Moden wrote:

    To be honest, Calendar tables can be a bit of a PITA depending, of course, on what you're doing.

    I don't know about "English Quarter Days" so let me ask the question... do the dates you have above apply to ALL years or do they change every year?  If they change every year, what are they based on?

  • sc-w

    Hall of Fame

    Points: 3658

    Thanks SCDecade, I tried your script but it won't work in SQL 2008, I should have mentioned the version I am using. I will see If I can find a fix for this to see the results.

    Thanks for your input.

  • This was removed by the editor as SPAM

  • Steve Collins

    SSC Eights!

    Points: 874

    https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

    https://www.sqlservercentral.com/blogs/tally-tables-in-t-sql


    declare
    @start_yr int=2020,
    @years int=20;


    ;with
    tally(n) as
    (
    select row_number() over (order by (select null))-1
    from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
    ),
    eng_qtr_cte(start_dt, end_dt, yr, qtr) as (
    select datefromparts((@start_yr-1)+n, 12, 26), datefromparts(@start_yr+n, 3, 25), @start_yr+n, 'Q1' from tally where n<@years
    union
    select datefromparts(@start_yr+n, 3, 26), datefromparts(@start_yr+n, 6, 24), @start_yr+n, 'Q2' from tally where n<@years
    union
    select datefromparts(@start_yr+n, 6, 25), datefromparts(@start_yr+n, 9, 29), @start_yr+n, 'Q3' from tally where n<@years
    union
    select datefromparts(@start_yr+n, 9, 30), datefromparts(@start_yr+n, 12, 25), @start_yr+n, 'Q4' from tally where n<@years)
    select * from eng_qtr_cte order by 1;

     

  • Jeffrey Williams

    SSC Guru

    Points: 88587

    How about something like this:

      Drop Function If Exists dbo.fnGetDates;
    Go

    Create Function dbo.fnGetDates (
    @fromDate datetime
    , @toDate datetime
    , @dateType varchar(20)
    )
    Returns Table
    As
    Return

    With t(n)
    As (
    Select t.n
    From (
    Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
    )
    , monthlyDates (StartDate, EndDate)
    As (
    Select dateadd(month, datediff(month, 0, @fromDate) + n.Number, 0)
    , dateadd(month, datediff(month, -1, @fromDate) + n.Number, -1)
    From (Select Top (datediff(month, @fromDate, @toDate))
    checksum(row_number() over(Order By @@spid) - 1) As rn
    From t t1, t t2) n(Number)
    Where @dateType = 'Monthly'
    )
    , quarterDates (QuarterName, StartDate, EndDate)
    As (
    Select d.QuarterName
    , dt.QuarterStart
    , QuarterEnd = dateadd(day, -1, lead(dt.QuarterStart) over(Order By dt.QuarterStart))
    From (Select Top (datediff(year, @fromDate, @toDate) + 1)
    checksum(row_number() over(Order By @@spid) - 1) As rn
    From t t1, t t2) As t(n)
    Cross Apply (Values ('Q1', 83), ('Q2', 174), ('Q3', 271), ('Q4', 358)) As d(QuarterName, OffSet)
    Cross Apply (Values (dateadd(year, datediff(year, 0, @fromDate) + t.n, d.OffSet))) As dt(QuarterStart)
    Where @dateType = 'EQD'
    )
    Select DateTypeName = concat(year(md.StartDate), ' M', month(md.StartDate))
    , md.StartDate
    , md.EndDate
    From monthlyDates md
    Union All
    Select DateTypeName = concat(year(qd.StartDate), ' ', qd.QuarterName)
    , qd.StartDate
    , qd.EndDate
    From quarterDates qd
    Where qd.StartDate <= @toDate
    And qd.EndDate > @fromDate;
    Go

    Declare @charges Table (
    uri int Not Null
    , lease_ref numeric(6, 0) Not Null
    , charge_type_code varchar(50) Null
    , start_date datetime Null
    , end_date datetime Null
    , due_date_code varchar(50) Null
    , annual_amount numeric(13, 2) Not Null
    , period_amount numeric(13, 2) Not Null
    );

    Insert Into @charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount)
    Values (1, 1, 'Rent', '2020-08-01', '2021-08-01', 'Monthly', 12000, 1200)
    , (2, 1, 'Service Charge', '2020-08-01', '2021-08-01', 'Monthly', 1200, 100)
    , (3, 4, 'Rent', '2020-08-01', '2021-08-01', 'EQD', 12000, 1200)
    , (4, 4, 'Service Charge', '2020-08-01', '2021-08-01', 'EQD', 1200, 100)
    , (5, 5, 'Rent', '2021-08-01', '2026-08-01', 'EQD', 36000, 3000)
    , (6, 5, 'Service Charge', '2021-08-01', '2026-08-01', 'EQD', 3600, 300);

    Select *
    From @charges c
    Cross Apply dbo.fnGetDates(c.[start_date], c.end_date, c.due_date_code) eq
    Order By
    c.uri
    , c.lease_ref
    , c.[start_date];
    ;

    If I am understanding what you are looking for - you want a row for each month if the due_date_code is 'Monthly' and a row for each Quarter if the due_date_code is 'EQD' where the dates fall between the start and end dates (excluding the end date itself).

    The function will generate the range of dates based on the from/to dates and the date type passed into the function.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden

    SSC Guru

    Points: 996807

    sc-w wrote:

    Hi Jeff, thanks for your reply. The dates will not changes each year they will remain the same. What are your thoughts based on this? Thanks.

    My thoughts on this are... I'm confused.  It loaded your first example data and it didn't look right to me.  The following code proves it isn't.

     SELECT *
    ,PeriodAmtShouldBe = CASE
    WHEN due_date_code = 'Monthly' THEN annual_amount/12.0
    WHEN due_date_code = 'EQD' THEN annual_amount/4.0
    END
    FROM charges
    WHERE period_amount <> CASE
    WHEN due_date_code = 'Monthly' THEN annual_amount/12.0
    WHEN due_date_code = 'EQD' THEN annual_amount/4.0
    END
    ;

    That returns the following...

    Please notice your period_amount compared to the amount it should be.

    I also can't understand why you provided a second data set but I truncated the table and loaded that looking for data that makes sense and got the following when I ran my "checker" code above.  The results are even more odd...

    Not wanting to presume that my "checker" code is correct, I ask you what's going on with this data because I'm certainly not understand what you have provided.

    Also, your example "desired results" have dates from the month of July 2020... you example data does not.

    It would be really helpful if you made some correct example data and provided some results that properly match that data.

    Thanks.

     

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • sc-w

    Hall of Fame

    Points: 3658

    Sorry Jeff, you are right my sample data was not correct, I must have copied the wrong thing at the time.

    The results are right below as lease ref 5 would not be included between 24th June to 29th September.

    Thanks for looking.

    Lease ref             Charge Type Code      From                   to                         Period Amount

    1                                 Rent                                  01/07/2020         31/07/2020          1000.00

    1                                Service Charge                 01/07/2020         31/07/2020            100.00

    1                                 Rent                                  01/08/2020         31/08/2020          1000.00

    1                               Service Charge                 01/08/2020         31/08/2020            100.00

    1                                Rent                                  01/09/2020         30/09/2020          1000.00

    1                                Service Charge                 01/09/2020         30/09/2020            100.00

    4                               Rent                                  25/06/2020         28/09/2020          3000.00

    4                               Service Charge                 25/06/2020         28/09/2020            300.00

    USE testDB2
    CREATE TABLE [charges](
    [uri] [int] NOT NULL,
    [lease_ref] [numeric](6, 0) NOT NULL,
    [charge_type_code] varchar(50) NULL,
    [start_date] [datetime] NULL,
    [end_date] [datetime] NULL,
    [due_date_code] varchar(50) NULL,
    [annual_amount] [numeric](13, 2) NOT NULL,
    [period_amount] [numeric](13, 2) NOT NULL
    ) ON [PRIMARY]
    GO
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('1', '1', 'Rent', '2020-07-01', '2021-07-01', 'Monthly', '12000', '1000')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('2', '1', 'Service Charge', '2020-07-01', '2021-07-01', 'Monthly', '1200', '100')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('3', '4', 'Rent', '2020-07-01', '2021-07-01', 'EQD', '12000', '3000')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('4', '4', 'Service Charge', '2020-07-01', '2021-07-01', 'EQD', '1200', '300')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('5', '5', 'Rent', '2021-07-01', '2026-07-01', 'EQD', '36000', '9000')
    insert into charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount) VALUES ('6', '5', 'Service Charge', '2021-07-01', '2026-07-01', 'EQD', '3600', '900')


  • Jeffrey Williams

    SSC Guru

    Points: 88587

    How are you determining what periods to include?  Are you looking for any charges that are due based on the current date - or something else?

    Using what I previously posted - with your new data:

      Drop Function If Exists dbo.fnGetDates;
    Go

    --==== This function creates the dates based on the due date code
    Create Function dbo.fnGetDates (
    @fromDate datetime
    , @toDate datetime
    , @dateType varchar(20)
    )
    Returns Table
    As
    Return

    With t(n)
    As (
    Select t.n
    From (
    Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
    )
    , monthlyDates (StartDate, EndDate)
    As (
    Select dateadd(month, datediff(month, 0, @fromDate) + n.Number, 0)
    , dateadd(month, datediff(month, -1, @fromDate) + n.Number, -1)
    From (Select Top (datediff(month, @fromDate, @toDate))
    checksum(row_number() over(Order By @@spid) - 1) As rn
    From t t1, t t2) n(Number)
    Where @dateType = 'Monthly'
    )
    , quarterDates (QuarterName, StartDate, EndDate)
    As (
    Select d.QuarterName
    , dt.QuarterStart
    , QuarterEnd = dateadd(day, -1, lead(dt.QuarterStart) over(Order By dt.QuarterStart))
    From (Select Top (datediff(year, @fromDate, @toDate) + 1)
    checksum(row_number() over(Order By @@spid) - 1) As rn
    From t t1, t t2) As t(n)
    Cross Apply (Values ('Q1', 83), ('Q2', 174), ('Q3', 271), ('Q4', 358)) As d(QuarterName, OffSet)
    Cross Apply (Values (dateadd(year, datediff(year, 0, @fromDate) + t.n, d.OffSet))) As dt(QuarterStart)
    Where @dateType = 'EQD'
    )
    Select DateTypeName = concat(year(md.StartDate), ' M', month(md.StartDate))
    , md.StartDate
    , md.EndDate
    From monthlyDates md
    Union All
    Select DateTypeName = concat(year(qd.StartDate), ' ', qd.QuarterName)
    , qd.StartDate
    , qd.EndDate
    From quarterDates qd
    Where qd.StartDate <= @toDate
    And qd.EndDate > @fromDate;
    Go

    --==== Create some sample data
    Declare @charges Table (
    uri int Not Null
    , lease_ref numeric(6, 0) Not Null
    , charge_type_code varchar(50) Null
    , start_date datetime Null
    , end_date datetime Null
    , due_date_code varchar(50) Null
    , annual_amount numeric(13, 2) Not Null
    , period_amount numeric(13, 2) Not Null
    );

    Insert Into @charges (uri, lease_ref, charge_type_code, start_date, end_date, due_date_code, annual_amount, period_amount)
    Values (1, 1, 'Rent', '2020-07-01', '2021-07-01', 'Monthly', 12000, 1000)
    , (2, 1, 'Service Charge', '2020-07-01', '2021-07-01', 'Monthly', 1200, 100)
    , (3, 4, 'Rent', '2020-07-01', '2021-07-01', 'EQD', 12000, 3000)
    , (4, 4, 'Service Charge', '2020-07-01', '2021-07-01', 'EQD', 1200, 300)
    , (5, 5, 'Rent', '2021-07-01', '2026-07-01', 'EQD', 36000, 9000)
    , (6, 5, 'Service Charge', '2021-07-01', '2026-07-01', 'EQD', 3600, 900);

    --==== Here is the solution...
    Declare @startDate datetime = '2020-07-01'
    , @endDate datetime = '2020-09-01';

    Select LeaseRef = c.lease_ref
    , ChargeTypeCode = c.charge_type_code
    , FromDate = eq.StartDate
    , ToDate = eq.EndDate
    , PeriodAmount = c.period_amount
    From @charges c
    Cross Apply dbo.fnGetDates(c.[start_date], c.end_date, c.due_date_code) eq
    Where eq.StartDate <= @endDate
    And eq.EndDate >= @startDate
    Order By
    c.lease_ref
    , eq.StartDate
    , c.charge_type_code;

    The results I get are:

    You will notice that the FromDate for LeaseRef = 4 is actually 2020-06-24 instead of 2020-06-25 (as in your example) because you previously stated the beginning of the quarter starts on June 24th.

    I would also recommend that you start using YYYY-MM-DD or YYYYMMDD date formats instead of regional specific formats like DD/MM/YYYY which can be interpreted incorrectly based on the language of the system.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden

    SSC Guru

    Points: 996807

    @Jeffrey Williams...

    I think you're on the right track but...

    1. Due to your hard-coded offsets for the start of each quarter, what's going to happen on a Leap Year?
    2. Although probably not likely but still entirely possible, what's going to happen if the difference between @FromDate and @ToDate exceeds 100 months?

    Good comment on the start date issue for the quarter.  I was going to hum a rock at the OP on that one but then saw your good comment.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 19 total)

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