Returning data from table based on date ranges

1

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 30th June to 28h September and to display the charges due based on the data in the charges table. Based on the sample table/data code below I would like to see the following that are only between the start and end dates.

Is there a query that can check the date and determine how many months to display for the given date range?

Results I would like to see from sample data.
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

Code to create sample DB and table/Data

USE testDB

CREATE TABLE [charges](
[uri] [int] NOT NULL,
[lease_ref] [numeric](6, 0) NOT NULL,
[charge_type_code] varchar NULL,
[start_date] [datetime] NULL,
[end_date] [datetime] NULL,
[due_date_code] varchar 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'

2

I think some data got cut off, so doesn't match your example. Also, ddl doesn't work. Need to add length to varchar when creating table. Please update and fix

3

Hi Mike, Yes sorry I messed up the copying of my sample data. Let me try that again. I added an image of the results to make it a little clear. Thanks for looking.

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 period starting 30th June to 28h September and to display the charges due based on the data in the charges table. Based on the sample table/data code below I would like to see the following data output that are only between the start and end dates.

Is there a query that can check the date and determine how many months to display for the given date range?

Results I would like to see from sample data.

Capture
Capture919×293 8.49 KB

Code to create sample DB and table/Data

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', '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-08-01', '2026-08-01', 'EQD', '3600', '900')

5

maybe like so?

select lease_ref,
       charge_type_code,
	   start_date as _from, 
	   DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, end_date) + 1, 0)) as _to,
            EOMONTH(end_date) as _toShorter,  --sql server 2012
	   period_amount 

from [charges]
6

hi

you can compare dates ...

where
date_filter
between from_date and to _date

this link may help

7

Hi Yosiasz,

Thank you for this, unfortunately I am using SQL 2008, I should have mentioned the version in my original post. I will see if there is a workaround for your code to test the results.

Thanks again.

8

The emonth is only for 2012 the rest should work for you. Look at it carefully