CODEWITHSUNDEEP
sql-servercoldfusionduplicatesleft-join
Steve Ontologic
Steve Ontologic

Reputation: 199

Bad logic, MSSQL, Coldfusion, duplicate results from query

I've been trying fix this query for over a week, and I can't sem to get it right. Went down a complete rabbit hole that got me close, but kept geting bigger and bigger. So I'd like to start from scratch.

As it was originally written:

<cfquery name="unbilledMisc" datasource="#request.dsn#">
SELECT  BM.*, C.Client, U.Name, R.FrequencyUnit, R.FrequencyDuration, R.RBID
FROM BilledMisc BM
    LEFT JOIN Clients C on C.ClientID = BM.ClientID
    LEFT JOIN Users U on U.UserID = BM.UserID
    LEFT JOIN RecurringBilling R ON (R.ClientID = C.ClientID AND BM.Rate = R.Rate AND BM.Title = R.Title)
WHERE (BM.CID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#CID#">) 
AND (BM.Invoiced = 0)
ORDER BY BM.DateCompleted ASC;
</cfquery>

BM*= BM.BMID, BM.Userid, BM.ClientID, BM.Rate, BM.Title, BM.CID, BM.QTY, BM.DateCompleted

The output row looks like:

<td class=""><input type="checkbox" class="BMIDCheckBoxes" name="BMID" value="#BMID#" />#Client#</td>
<td class="">
    <cfif #Qty# eq "">
     -
    <cfelse>
        <a href="##" class="UpdateFeeItem invoices" title="#BMID#">#NumberFormat(Qty, '9999.99')#</a>
    </cfif>
</td>
<td class="">#Name#</td>
<td>
<cfif #RBID# neq ''>
<cfswitch expression="#FrequencyUnit#">
    <cfcase value="d">
        <cfset FreqOutput = 'Day(s)'>
    </cfcase>
    <cfcase value="ww">
        <cfset FreqOutput = 'Week(s)'>
    </cfcase>
    <cfcase value="m">
        <cfset FreqOutput = 'Month(s)'>
    </cfcase>
    <cfcase value="q">
        <cfset FreqOutput = 'Quarter(s)'>
    </cfcase>
    <cfcase value="yyyy">
        <cfset FreqOutput = 'Year(s)'>
    </cfcase>
</cfswitch>
<a href="##" name="#RBID#" id="UpdateRecItem" class="link"><img src="images/edit_icon.png" /></a>
Recurrs Every #FrequencyDuration# #FreqOutput#
</cfif>
</td>
<td class="">#DateFormat(DateCompleted, 'mm/dd/yyyy')#</td>
<td style="width:50px;" align="center"><a href="##" class="DeleteFeeItem" title="#BMID#"><img src="images/delete_icon.png" alt="Delete" title="Delete" /></a></td>

I'm open to any suggestions, on a better way to do this.

You can set up a 1 time fee, or set up a fee that behaves like a one time fee, and then recurs every set interval (day, week, month, quater, year) for the same dollar amount, based on the per unit price and quantity.

If you make just a one time fee, everything is fine.

When you make the first recurring fee, everything is fine.

But lets say 2 weeks go by and 500 other clients. And you're gonna set up another 1 time fee for that client, setup by the same user, for the same product for the same amount, I get a duplicate row, that shares the same RBID with different BMID's

Essentially 1 of those rows has a one time fee that is supposed to recur, and another identical one time fee that thinks it's supposed to recur, and has the RBID of the first row.

If you were a small company you would remember to go in and edit the amount of the first fee, but if you're a big company and don't remember, than they hist this bug.

Please let me know if I need to elaborate on any part of this. I'd be happy to.

Edit: At a risk of going in a different direction, I've also considered adding a new column to the recurring table, that would hold the BMID for each recurring item if one exists. That way I could just reference wich recurring fee belongs to each one time fee by 1 column.

I guess on submit, it would add all of the data to a new row in billedMisc, which creates an auto incremented BMID, then if recurring fee is set add the recurring data in to a new recurringbilling row with the newly created BMID. Which would lock them together by a specific identifier instead of looking at which rate, qty, user,client... match up.

Thoughts... Feelings?

EDIT: I'm ditching this query in my project and adding a new column to one of the tables to lock it to the fee item

Mods feel free to delete if so inclined

Upvotes: 1

Views: 223

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20804

Troubleshoot as follows. Start with:

<cfquery name="test">
select BM.cid
from BilledMisc BM
where BM.CID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#CID#">) 
and BM.invoiced = 0

Then add your first join and run it again. If the recordcount increases, run some more queries to find out why.

If, at the end of the day, you are going to get multiple rows, see if a "select distinct" eliminates the problem. If it doesn't, look at the data being returned and decide which record is the one you want.

Upvotes: 1

Related Questions