How to stop double counting certain recordsets

Chopstik · Aug 29, 2005

I have a query that I was asked to create that would retrieve a list of all future rent obligations. Everything was going (more or less) ok until I realised that certain results were being double-counted. The way my query is structured it works ok provided the rent starts at the beginning of one month and ends at the end of another month (or, put another way, the rent obligation does not start and end in the same month). I have racked my brain to find a solution that I am sure is not nearly as difficult as I am making it out to be and am coming up empty at the moment. Any thoughts would be greatly appreciated. My code is below and I can provide any clarification that may be needed. Thanks! (I should also point out that the joined view (V_NON_EST_OBLIGS) is a view that returns all obligations that are not estimated.)

Code:

SELECT D.doc_id, (SELECT MIN(dist_cd) FROM T_PRPTY JOIN T_PRPTY_DOC ON T_PRPTY.prpty_id = T_PRPTY_DOC.prpty_id 
WHERE T_PRPTY_DOC.doc_id = D.doc_id) AS dist_cd, DT.doc_ty_desc,
ISNULL(SUM((CASE WHEN YEAR(O1.cmnc_dt) < YEAR(GETDATE()) + 1 AND (YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 1)
	OR (YEAR(O1.cmnc_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.cmnc_dt) <= 1 AND
	(YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 1))
	THEN O1.per_perd_amt/O1.perd_lngth_qty END)),0) AS Jan,
ISNULL(SUM((CASE WHEN YEAR(O1.cmnc_dt) < YEAR(GETDATE()) + 1 AND (YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 2)
	OR (YEAR(O1.cmnc_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.cmnc_dt) <= 2 AND
	(YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 2))
	THEN O1.per_perd_amt/O1.perd_lngth_qty END)),0) AS Feb,
ISNULL(SUM((CASE WHEN YEAR(O1.cmnc_dt) < YEAR(GETDATE()) + 1 AND (YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 3)
	OR (YEAR(O1.cmnc_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.cmnc_dt) <= 3 AND
	(YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 3))
	THEN O1.per_perd_amt/O1.perd_lngth_qty END)),0) AS Mar,
ISNULL(SUM((CASE WHEN YEAR(O1.cmnc_dt) < YEAR(GETDATE()) + 1 AND (YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 4)
	OR (YEAR(O1.cmnc_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.cmnc_dt) <= 4 AND
	(YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 4))
	THEN O1.per_perd_amt/O1.perd_lngth_qty END)),0) AS Apr,
ISNULL(SUM((CASE WHEN YEAR(O1.cmnc_dt) < YEAR(GETDATE()) + 1 AND (YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 5)
	OR (YEAR(O1.cmnc_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.cmnc_dt) <= 5 AND
	(YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 5))
	THEN O1.per_perd_amt/O1.perd_lngth_qty END)),0) AS May,
ISNULL(SUM((CASE WHEN YEAR(O1.cmnc_dt) < YEAR(GETDATE()) + 1 AND (YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 6)
	OR (YEAR(O1.cmnc_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.cmnc_dt) <= 6 AND
	(YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 6))
	THEN O1.per_perd_amt/O1.perd_lngth_qty END)),0) AS Jun,
ISNULL(SUM((CASE WHEN YEAR(O1.cmnc_dt) < YEAR(GETDATE()) + 1 AND (YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 7)
	OR (YEAR(O1.cmnc_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.cmnc_dt) <= 7 AND
	(YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 7))
	THEN O1.per_perd_amt/O1.perd_lngth_qty END)),0) AS Jul,
ISNULL(SUM((CASE WHEN YEAR(O1.cmnc_dt) < YEAR(GETDATE()) + 1 AND (YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 8)
	OR (YEAR(O1.cmnc_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.cmnc_dt) <= 8 AND
	(YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 8))
	THEN O1.per_perd_amt/O1.perd_lngth_qty END)),0) AS Aug,
ISNULL(SUM((CASE WHEN YEAR(O1.cmnc_dt) < YEAR(GETDATE()) + 1 AND (YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 9)
	OR (YEAR(O1.cmnc_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.cmnc_dt) <= 9 AND
	(YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 9))
	THEN O1.per_perd_amt/O1.perd_lngth_qty END)),0) AS Sep,
ISNULL(SUM((CASE WHEN YEAR(O1.cmnc_dt) < YEAR(GETDATE()) + 1 AND (YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 10)
	OR (YEAR(O1.cmnc_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.cmnc_dt) <= 10 AND
	(YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 10))
	THEN O1.per_perd_amt/O1.perd_lngth_qty END)),0) AS Oct,
ISNULL(SUM((CASE WHEN YEAR(O1.cmnc_dt) < YEAR(GETDATE()) + 1 AND (YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 11)
	OR (YEAR(O1.cmnc_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.cmnc_dt) <= 11 AND
	(YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 11))
	THEN O1.per_perd_amt/O1.perd_lngth_qty END)),0) AS Nov,
ISNULL(SUM((CASE WHEN YEAR(O1.cmnc_dt) < YEAR(GETDATE()) + 1 AND (YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 12)
	OR (YEAR(O1.cmnc_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.cmnc_dt) <= 12 AND
	(YEAR(O1.expr_dt) > YEAR(GETDATE()) + 1 
	OR YEAR(O1.expr_dt) = YEAR(GETDATE()) + 1 AND MONTH(O1.expr_dt) >= 12))
	THEN O1.per_perd_amt/O1.perd_lngth_qty END)),0) AS Dec
FROM T_DOC D
JOIN T_DOC_TY DT ON D.doc_ty_cd = DT.doc_ty_cd
LEFT JOIN V_ALL_NON_EST_OBLIGS O1
            ON O1.doc_id = D.doc_id AND O1.oblig_cls_cd = 'Rent' AND O1.acct_cd = '000000'

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

johnherman · Aug 29, 2005

How about SELECT DISTINCT?

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

Chopstik · Aug 29, 2005

Doesn't work. A single document can have multiple obligations and they are summed up in the subselect. The problem occurs when one obligation stops (for example) on June 15 and the next obligation starts on June 16 - thereby counting both of those obligations when it should only count one of them.

I could exclude one, but I also have cases where I legitimately have two obligations that should be counted (for example, there are two landlords for the same property).

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

maswien · Aug 29, 2005

I suggest you post the make up data, not exactly the real data, the data better include all the scenerios in the real data, then give an example that what kind of results you want the query get from the data. That way will save a lot time for you and the people who want help you.

Chopstik · Aug 29, 2005

Ok, let me try this. There are essentially two tables involved in this query. The Document table is considered the parent while the Obligation table is the child. Each Document can have multiple obligations associated with it, either sequential (one obligation for 2005, 2006, etc) or in conjunction (multiple landlords for a single property/document). The purpose of the query is to cycle through each of the obligations and, based on their commencement and expiration dates in relation to the month in question, sum the obligation amounts for each month to produce the future monthly obligation amount for each document.

This works ok with the query I posted above except in cases where the commencement and expiration date occurs in the middle of a given month. When this happens, it is double-counting the amounts.

Code:

[b]Document[/b]
DocID: VA00100

[b]Obligations[/b] <--being counted twice and shouldn't be.
ObligID          CommenceDt          ExpireDt
---------------------------------------------
11111            1/16/04             1/15/05
11112            1/16/05             1/15/06  
[b]Document[/b]
DocID: VA00101

[b]Obligations[/b]  <--works fine.
ObligID          CommenceDt          ExpireDt
---------------------------------------------
22221            1/1/05              12/31/05
22222            1/1/06              12/31/06  

[b]Document[/b]
DocID: VA00102

[b]Obligations[/b]  <--also works fine (s/b counting both).
ObligID          CommenceDt          ExpireDt
---------------------------------------------
33331            1/1/05              12/31/05
33332            1/1/05              12/31/05

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

maswien · Aug 29, 2005

You mentioned:

(multiple landlords for a single property/document).

Does the following results belong to what you mentioned?

Document
DocID: VA00100

Obligations <--being counted twice and shouldn't be.
ObligID CommenceDt ExpireDt
---------------------------------------------
11111 1/16/04 1/15/05
11112 1/16/05 1/15/06

If it belongs to, how do you know it's a single property multiple landlord? Or you just
get that from the commencement date and expired date looks like? You want count it once as following?

Code:

    11111 or 11112 ?         1/16/04        1/15/06

Chopstik · Aug 29, 2005

If it were a single property with multiple landlords, then it would have 4 obligations associated with it. Two going from 1/16/04 --> 1/15/05 and two going from 1/16/05 --> 1/15/06. I would only want the twelve months for each. What is currently happening is that it is pulling 13 months (because the months overlap). Does that make sense?

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

Chopstik · Aug 30, 2005

Something else I should point out - when I say that it is double counting, I mean that it is double counting the value for January. So if the amount is $6000/mo, it will show $6000 for Feb-Dec, but $12000 for Jan. It is that Jan value that I need so that it will only count one of the records, not both of them. Thanks.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

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!

How to stop double counting certain recordsets

Chopstik

Technical User

johnherman

MIS

Chopstik

Technical User

maswien

Technical User

Chopstik

Technical User

maswien

Technical User

Chopstik

Technical User

Chopstik

Technical User

Similar threads

Part and Inventory Search

Sponsor