Find tenants with middle of the month rents

navigator703 · Jun 26, 2006

Good afternoon all-

In this SQL code, we are looking for tenants with split month rents. We are working on trying to fix this rent problem to where it matches for the end of the month like normal rents do, but in the meanwhile, we need a list in order to fix them in the future. Right now this means that the rents are starting and ending at the middle of the months, instead of at the beginning. For example, the rent stops on the 14th and then starts on the 15th of each month. As of now it does work, but not to what we are looking for. We are looking more along the lines of showing each month, July 14th and July 15th. The code now is producing different years and months from each record. It is giving the correct months and all, but still. If you have any suggestions of making it cleaner or making it run quicker, we would be appreciative.

[!]Here is the code![/!]

Code:

SELECT	TNNT_ID, CST_CTR_CD, CO_CD, OBLIG_ID, CMNC_DT, EXPR_DT

FROM  T_OBLIG

WHERE((MONTH(CMNC_DT) = 01 AND DAY(CMNC_DT) NOT IN (1,31)) OR 
	(MONTH(EXPR_DT) = 01 AND DAY(EXPR_DT) NOT IN (1,31))) OR
	
	((MONTH(CMNC_DT) = 02 AND DAY(CMNC_DT) NOT IN (1,28)) OR
	(MONTH(EXPR_DT) = 02 AND DAY(EXPR_DT) NOT IN (1,28))) OR

	((MONTH(CMNC_DT) = 03 AND DAY(CMNC_DT) NOT IN (1,31)) OR
	(MONTH(EXPR_DT) = 03 AND DAY(EXPR_DT) NOT IN (1,31))) OR
	
	((MONTH(CMNC_DT) = 04 AND DAY(CMNC_DT) NOT IN (1,30)) OR
	(MONTH(EXPR_DT) = 04 AND DAY(EXPR_DT) NOT IN (1,30))) OR
	
	((MONTH(CMNC_DT) = 05 AND DAY(CMNC_DT) NOT IN (1,31)) OR
	(MONTH(EXPR_DT) = 05 AND DAY(EXPR_DT) NOT IN (1,31))) OR

	((MONTH(CMNC_DT) = 06 AND DAY(CMNC_DT) NOT IN (1,30)) OR
	(MONTH(EXPR_DT) = 06 AND DAY(EXPR_DT) NOT IN (1,30))) OR

	((MONTH(CMNC_DT) = 07 AND DAY(CMNC_DT) NOT IN (1,31)) OR
	(MONTH(EXPR_DT) = 07 AND DAY(EXPR_DT) NOT IN (1,31))) OR

	((MONTH(CMNC_DT) = 08 AND DAY(CMNC_DT) NOT IN (1,31)) OR
	(MONTH(EXPR_DT) = 08 AND DAY(EXPR_DT) NOT IN (1,31))) OR

	((MONTH(CMNC_DT) = 09 AND DAY(CMNC_DT) NOT IN (1,30)) OR
	(MONTH(EXPR_DT) = 09 AND DAY(EXPR_DT) NOT IN (1,30))) OR

	((MONTH(CMNC_DT) = 010 AND DAY(CMNC_DT) NOT IN (1,31)) OR
	(MONTH(EXPR_DT) = 010 AND DAY(EXPR_DT) NOT IN (1,31))) OR

	((MONTH(CMNC_DT) = 011 AND DAY(CMNC_DT) NOT IN (1,30)) OR
	(MONTH(EXPR_DT) = 011 AND DAY(EXPR_DT) NOT IN (1,30))) OR

	((MONTH(CMNC_DT) = 012 AND DAY(CMNC_DT) NOT IN (1,31)) OR
	(MONTH(EXPR_DT) = 012 AND DAY(EXPR_DT) NOT IN (1,31))) 
	
ORDER BY TNNT_ID

If you have any questions or suggestions, please let me know. Thanks.

Navigator

navigator703 · Jun 27, 2006

Does anybody have any new thoughts on this. Thanks!

gmmastros · Jun 27, 2006

Post some sample data from the T_OBLIG table (no names, addresses, phone numbers, etc...)

Then show the expected results.

With this information, it will be easier for us to help you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

gmmastros · Jun 27, 2006

I'm not sure if this is what you are looking for, but here goes.

One approach to this problem could be to:

1. Find the tennants that have rents that DO start on the first of the month.
2. Get the tennants that do NOT fall in the list of tennants from step 1.

Also... Looking at your where clause, you seem to be extracting the month from the date so you can determine if the date falls on the last day of the month. This not only causes extra work, but will also fail on leap years. I can pretty much guarantee that your slow performance is caused by all the calcs and OR's in your where clause. After thinking about this a little, I came up with an idea. If you add 1 day to the date, and THAT date is the first, then you know the original date was the last day of the month. Using this logic, we can simplify the where clause which should help to improve performance.

I came up with a query that should return the list of tennants that do have rents that start on the first of the month. Here's the query.

Code:

Select TNNT_ID
From   T_OBLIG 
Where  Day(CMNC_DT) = 1
       Or Day(CMNC_DT + 1) = 1
       Or Day(EXPR_DT) = 1
       Or Day(EXPR_DT + 1) = 1

This query returns EXACTLY the wrong results. But we can use the results to get the data you are looking for by making it a LEFT JOINed subquery and cheching for nulls. Here's how.

Code:

Select T.TNNT_ID, CST_CTR_CD, CO_CD, OBLIG_ID, CMNC_DT, EXPR_DT
From   T_OBLIG T
       Left Join (
          [purple]Select TNNT_ID
          From   T_OBLIG 
          Where  Day(CMNC_DT) = 1
                 Or Day(CMNC_DT + 1) = 1
                 Or Day(EXPR_DT) = 1
                 Or Day(EXPR_DT + 1) = 1[/purple]
          ) As A On T.TNNT_ID = A.TNNT_ID
Where  A.TNNT_ID Is NULL

Notice the [purple]purple[/purple] part. It's the same query that we used to get the tennants that have rents starting on the first of the month. It became a subquery, joined back to the original table, with a where clause added on so that we ONLY get records that don't match from both tables.

I hope this query helps, and with the explanation I've given, you should be able to modify this query if it is not exactly what you are looking for.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

navigator703 · Jun 27, 2006

Here is a small part of this table known as T_OBLIG

Code:

TNNT_ID	   CMNC_DT	 EXPR_DT
2204	    01/01/1998   01/17/2003
4323	    09/01/1997   08/31/1998
3310	    09/01/1997   08/12/2001
3311	    06/23/1998   06/22/2001
5433	    08/01/1997   07/15/1998
4433	    08/15/1998   07/31/1999
4434	    12/01/1997   11/30/1998
4626	    03/23/1997   02/28/2000
4068	    05/18/1997   04/30/1999
4469	    02/01/2000   01/31/2001

The expected results would look somewhat like:

Code:

TNNT_ID	   CMNC_DT	 EXPR_DT
2204	    01/14/1998   01/15/2003
4626	    03/23/1997   02/28/2000
4068	    05/18/1997   04/30/1999

If you need anymore background let me know. Thanks again!

gmmastros · Jun 27, 2006

When I run the query I presented above, I get different results, but I'm not sure why. Perhaps you can exmplain.

Code:

[green]-- Dummy Data[/green]
Declare @T Table(TNNT_ID Integer, CMNC_DT DateTime, EXPR_DT DateTime)

Insert Into @T Values(2204,'01/01/1998','01/17/2003')
Insert Into @T Values(4323,'09/01/1997','08/31/1998')
Insert Into @T Values(3310,'09/01/1997','08/12/2001')
Insert Into @T Values(3311,'06/23/1998','06/22/2001')
Insert Into @T Values(5433,'08/01/1997','07/15/1998')
Insert Into @T Values(4433,'08/15/1998','07/31/1999')
Insert Into @T Values(4434,'12/01/1997','11/30/1998')
Insert Into @T Values(4626,'03/23/1997','02/28/2000')
Insert Into @T Values(4068,'05/18/1997','04/30/1999')
Insert Into @T Values(4469,'02/01/2000','01/31/2001')

[green]-- End of dummy data[/green]

Select * --T.TNNT_ID, CST_CTR_CD, CO_CD, OBLIG_ID, CMNC_DT, EXPR_DT
From   @T T
       Left Join (
          Select TNNT_ID
          From   @T 
          Where  Day(CMNC_DT) = 1
                 Or Day(CMNC_DT + 1) = 1
                 Or Day(EXPR_DT) = 1
                 Or Day(EXPR_DT + 1) = 1
          ) As A On T.TNNT_ID = A.TNNT_ID
Where  A.TNNT_ID Is NULL

I get...

[tt][blue]
TNNT_ID CMNC_DT EXPR_DT
------- ---------- ----------
3311 1998-06-23 2001-06-22
4626 1997-03-23 2000-02-28
[/blue][/tt]

This seems right (if i've understood your requirements properly).

In your expected results, you have a row for 2204, but the data doesn't match the input. You also have a row for 4626, but the EXPR_DT falls on the last day of the month, and you appear to be missing 3311. Perhaps you can explain what I'm missing here so I can help you better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

navigator703 · Jun 29, 2006

I'm sorry after further review, we had some really bad data. I did find a good set of data that you can gain a sense of what I am looking for

Code:

TNNT_ID       CMNC_DT     EXPR_DT
2204        01/01/1998   01/17/1998
4323        09/01/1997   08/31/1997
3310        09/01/2001   08/31/2001
3311        06/22/1998   06/23/1998
5433        08/01/1997   07/31/1998
4433        07/02/1999   07/20/1999
4434        12/10/1997   12/11/1997
4626        02/19/2000   02/22/2000
4068        05/31/1997   05/01/1997
4469        02/01/2000   01/31/2001

gmmastros · Jun 29, 2006

And the expected results from this new set of data....

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

navigator703 · Jun 30, 2006

The expected results should be:

Code:

TNNT_ID       CMNC_DT     EXPR_DT
2204        01/01/1998   01/17/1998
3311        06/22/1998   06/23/1998
4433        07/02/1999   07/20/1999

and so on....

Thanks!

gmmastros · Jun 30, 2006

I apologize for misunderstanding your original requirements. I thought that record 2204 was 'ok' because CMNC_DT falls on the first of the month. I now realize that BOTH dates must be at the beginning or end of the month. After realizing this, it was simple to modify the query I previously posted. So, please... try this one.

Code:

Select T.TNNT_ID, CST_CTR_CD, CO_CD, OBLIG_ID, CMNC_DT, EXPR_DT
From   T_OBLIG T
       Left Join (
          Select TNNT_ID
          From   T_OBLIG
          Where  (Day(CMNC_DT) = 1 Or Day(CMNC_DT + 1) = 1)
                 And (Day(EXPR_DT) = 1 Or Day(EXPR_DT + 1) = 1)
          ) As A On T.TNNT_ID = A.TNNT_ID
Where  A.TNNT_ID Is NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

navigator703 · Jun 30, 2006

Also,

I have found out that my data all along was correct! Please consider that some of the rents fall on different years as in the first data set.

Code:

TNNT_ID       CMNC_DT     EXPR_DT
5102        01/01/1998   01/17/2003
4200        09/01/1997   08/31/1998
3894        09/01/1997   08/12/2001
3301        06/23/1998   06/22/2001
2513        08/01/1997   07/15/1998
2103        08/15/1998   07/31/1999
1114        12/01/1997   11/30/1998
4000        03/23/1997   02/28/2000
0028        05/18/1997   04/30/1999
2019        02/01/2000   01/31/2001

And then others will fall in the same year.

Code:

TNNT_ID       CMNC_DT     EXPR_DT
2204        01/01/1998   01/17/1998
4323        09/01/1997   08/31/1997
3310        09/01/2001   08/31/2001
3311        06/22/1998   06/23/1998
5433        08/01/1997   07/31/1998
4433        07/02/1999   07/20/1999
4434        12/10/1997   12/11/1997
4626        02/19/2000   02/22/2000
4068        05/31/1997   05/01/1997
4469        02/01/2000   01/31/2001

I have talked to the managers and they all feel that there is some kind of solution to this problem in the future, but for now, we have to deal. If you have any thoughts or suggestion, please let me know. Thanks again for all your help.

navigator703 · Jul 5, 2006

Does anybody have any new thoughts on this. Thanks!

~ a journey of a thousand miles must begin with a single step ~

navigator703 · Jul 13, 2006

I am still looking for some insight into this problem. Any new suggestions are welcome.

Navigator703

~ a journey of a thousand miles must begin with a single step ~

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Find tenants with middle of the month rents

navigator703

MIS

navigator703

MIS

gmmastros

Programmer

gmmastros

Programmer

navigator703

MIS

gmmastros

Programmer

navigator703

MIS

gmmastros

Programmer

navigator703

MIS

gmmastros

Programmer

navigator703

MIS

navigator703

MIS

navigator703

MIS

Similar threads

Part and Inventory Search

Sponsor