Returning data from table based on date ranges

Question

Post reply

Returning data from table based on date ranges

sc-w

Hall of Fame

Points: 3667

July 10, 2020 at 12:35 pm

#3768912

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 4 years, 5 months ago by sc-w.
This topic was modified 4 years, 5 months ago by sc-w.

July 12, 2020 at 9:05 am

This was removed by the editor as SPAM

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

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

kcecil 47772 Grasshopper Points: 17 More actions · Answer 1

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 4 years, 5 months ago by kcecil 47772.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

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: 3667 More actions · Answer 3

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

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

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.

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

Steve Collins SSCrazy Points: 2173 More actions · Answer 5

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 4 years, 5 months ago by Steve Collins. Reason: union all

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

sc-w Hall of Fame Points: 3667 More actions · Answer 6

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: 3667 More actions · Answer 7

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.

Steve Collins SSCrazy Points: 2173 More actions · Answer 8

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;

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

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 9

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

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

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.

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

sc-w Hall of Fame Points: 3667 More actions · Answer 11

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: 90304 More actions · Answer 12

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.

This reply was modified 4 years, 5 months ago by Jeffrey Williams.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

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

@Jeffrey Williams...

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

Due to your hard-coded offsets for the start of each quarter, what's going to happen on a Leap Year?
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.

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