August 7, 2018 at 2:32 am
i have rental data store at this table structure
| Property | TenantID | Trx Type | EFFDATE | ENDDATE | AMOUNT | |
| A | Tenant A | BR | 20/08/2018 | 19/08/2019 | 26,695.19 | |
| A | Tenant A | BR | 20/08/2019 | 19/08/2020 | 31,406.1 | |
| A | Tenant A | PF | 20/08/2015 | 19/08/2020 | 1,256.24 | |
| A | Tenant A | SC | 20/08/2015 | 19/08/2020 | 4,396.85 |
How can i achieve the result in the follow matter, many thanks.
| Property | TenantID | EFFDATE | ENDDATE | BR | PF | SC |
| A | Tenant A | 20/08/2018 | 31/08/2018 | 15,379.77 | 445.76 | 1,560.17 |
| A | Tenant A | 01/09/2018 | 30/09/2018 | 26,695.19 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/10/2018 | 31/10/2018 | 26,695.19 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/11/2018 | 30/11/2018 | 26,695.19 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/12/2018 | 31/12/2018 | 26,695.19 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/01/2019 | 31/01/2019 | 26,695.19 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/02/2019 | 28/02/2019 | 26,695.19 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/03/2019 | 31/03/2019 | 26,695.19 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/04/2019 | 30/04/2019 | 26,695.19 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/05/2019 | 31/05/2019 | 26,695.19 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/06/2019 | 30/06/2019 | 26,695.19 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/07/2019 | 31/07/2019 | 26,695.19 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/08/2019 | 31/08/2019 | 26,695.19 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/09/2019 | 30/09/2019 | 27,505.67 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/10/2019 | 31/10/2019 | 31,406.10 | 1,256.24 | 4,396.85 |
| A | Tenant A | 01/10/2019 | 31/10/2019 | 31,406.10 | 1,256.24 | 4,396.85 |
until the end of the last month of the tenancy like this
| A | Tenant A | 01/08/2020 | 19/08/2020 | 26,695.19 | 769.95 | 2,694.84 |
August 7, 2018 at 3:33 am
Welcome to SSC. What have you tried so far to resolve this yourself? Could you post what you have done so far and where are you having difficulty? The users here are happy to help, but we're not here to do your work for you.
You haven't explained your logic, however, but I think that you could achieve what you're after with Calendar Table: http://www.sqlservercentral.com/articles/calendar/145206/. Have a read of the article, and if your unsure how to use it, reply with the logic you need, (and what you've tried), and I'll be happy to help further.
If you want to really go the "whole hog", check the link in my signature on how to provide DDL for your tables and insert statements for your sample data.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 7, 2018 at 9:54 am
This was done by friend of mine. Some data was return as duplicate especially for those period start in the mid month. hence, looking help how can enhance it based on result above.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[asp_tableaurent]
AS
TRUNCATE TABLE tableaurent;
DECLARE @MonthStep INT;
DECLARE @t_UID nvarchar(255);
DECLARE @t_BLDGID nvarchar(200);
DECLARE @t_LEASID nvarchar(200);
DECLARE @t_INCCAT nvarchar(200);
DECLARE @t_EFFDATE datetime;
DECLARE @t_ENDDATE datetime;
DECLARE @t_AMOUNT money;
DECLARE @t_DTDIFF int;
DECLARE trans_cur CURSOR FOR
SELECT z.BLDGID, z.LEASID, z.INCCAT, z.EFFDATE, z.ENDDATE, z.AMOUNT, DATEDIFF(month, EFFDATE, ENDDATE) as DTDiff
FROM CMRECC z
WHERE z.INCCAT IN ('FR','SC','PF');
OPEN trans_cur;
FETCH NEXT FROM trans_cur INTO @t_BLDGID, @t_LEASID, @t_INCCAT, @t_EFFDATE, @t_ENDDATE, @t_AMOUNT, @t_DTDIFF;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MonthStep = 0
SET @t_UID = NEWID();
WHILE @MonthStep < @t_DTDIFF
BEGIN
INSERT INTO tableaurent VALUES (@t_UID, @t_BLDGID, @t_LEASID, @t_INCCAT, @t_EFFDATE, @t_ENDDATE, @t_AMOUNT, @t_DTDIFF, DATEPART(month, DATEADD(month,@MonthStep,@t_EFFDATE)), DATEPART(year, DATEADD(month,@MonthStep,@t_EFFDATE)), NULL );
SET @MonthStep = @MonthStep + 1
IF @MonthStep = @t_DTDIFF
BEGIN
IF DATEPART(Month, @t_ENDDATE) = DATEPART(month, DATEADD(month,@MonthStep,@t_EFFDATE))
BEGIN
INSERT INTO tableaurent VALUES (@t_UID, @t_BLDGID, @t_LEASID, @t_INCCAT, @t_EFFDATE, @t_ENDDATE, @t_AMOUNT, @t_DTDIFF, DATEPART(month, DATEADD(month,@MonthStep,@t_EFFDATE)), DATEPART(year, DATEADD(month,@MonthStep,@t_EFFDATE)), NULL );
END
END
END
FETCH NEXT FROM trans_cur INTO @t_BLDGID, @t_LEASID, @t_INCCAT, @t_EFFDATE, @t_ENDDATE, @t_AMOUNT, @t_DTDIFF;
END
CLOSE trans_cur;
DEALLOCATE trans_cur;
SELECT * FROM tableaurent;
August 14, 2018 at 11:02 am
crazysf - Tuesday, August 7, 2018 2:32 AMi have rental data store at this table structure
Property TenantID Trx Type EFFDATE ENDDATE AMOUNT A Tenant A BR 20/08/2018 19/08/2019 26,695.19 A Tenant A BR 20/08/2019 19/08/2020 31,406.1 A Tenant A PF 20/08/2015 19/08/2020 1,256.24 A Tenant A SC 20/08/2015 19/08/2020 4,396.85 How can i achieve the result in the follow matter, many thanks.
Property TenantID EFFDATE ENDDATE BR PF SC A Tenant A 20/08/2018 31/08/2018 15,379.77 445.76 1,560.17 A Tenant A 01/09/2018 30/09/2018 26,695.19 1,256.24 4,396.85 A Tenant A 01/10/2018 31/10/2018 26,695.19 1,256.24 4,396.85 A Tenant A 01/11/2018 30/11/2018 26,695.19 1,256.24 4,396.85 A Tenant A 01/12/2018 31/12/2018 26,695.19 1,256.24 4,396.85 A Tenant A 01/01/2019 31/01/2019 26,695.19 1,256.24 4,396.85 A Tenant A 01/02/2019 28/02/2019 26,695.19 1,256.24 4,396.85 A Tenant A 01/03/2019 31/03/2019 26,695.19 1,256.24 4,396.85 A Tenant A 01/04/2019 30/04/2019 26,695.19 1,256.24 4,396.85 A Tenant A 01/05/2019 31/05/2019 26,695.19 1,256.24 4,396.85 A Tenant A 01/06/2019 30/06/2019 26,695.19 1,256.24 4,396.85 A Tenant A 01/07/2019 31/07/2019 26,695.19 1,256.24 4,396.85 A Tenant A 01/08/2019 31/08/2019 26,695.19 1,256.24 4,396.85 A Tenant A 01/09/2019 30/09/2019 27,505.67 1,256.24 4,396.85 A Tenant A 01/10/2019 31/10/2019 31,406.10 1,256.24 4,396.85
A Tenant A 01/10/2019 31/10/2019 31,406.10 1,256.24 4,396.85 until the end of the last month of the tenancy like this
A Tenant A 01/08/2020 19/08/2020 26,695.19 769.95 2,694.84
This CAN be done using set-based methods, as you do NOT need a cursor. However, we'd need to know how, exactly, to pro-rate partial months, as it's clearly NOT a case of just using the number of days in the month that are actually used for the particular partial month, and it even appears that the final partial month is not pro-rated. Also, I tried to see if the PF and SC columns were a straight percentage, and that's clearly not the case either, so we'd need those calculations as well. Post back with the details.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply