I have two tables. The first, LeadSales
keeps track of leads we have sold to brokers. The second, LeadSalesCreditActions
keeps a history of all transactions of each sale, including refund requests.
Here's a sample (some columns emitted and names shortened for ease of viewing here):
LeadSales
LeadSalesID LeadID SoldToContactID Amount CurrentCreditStatus
-------------------------------------------------------------------------
190 3241 45 3.50 0 (new)
191 3398 45 3.75 1 (refund requested)
192 3410 45 3.95 2 (refund granted)
So, Contact 45 had purchased 3 leads. He's requested a credit on two of them, and one credit has been granted while one is still pending.
LeadSalesCreditActions
ID CreatedByContactID LeadSalesID Comment CreditStatus
------------------------------------------------------------------------
250 NULL 190 NULL 0 (new)
251 NULL 191 NULL 0 (new)
252 45 191 "Dude, no good" 1 (refund requested)
253 NULL 192 NULL 0 (new)
254 45 192 "Dudes, bad data" 1 (refund requested)
255 1 192 "Sorry about that" 2 (refund granted)
Again, LeadSalesCreditActions
keeps a log of every transaction against a sale. So there is a record of when a lead is sold, another if the broker requests a credit, and another when we grant or deny the credit.
Now, we want to see a report of every current Credit Request that is still pending. Meaning, we have not yet granted or denied the request.
I have a report query that joins the two tables together. This is working fine, but is missing a couple things...
var creditActions = (get List<LeadSalesCreditActions> where Max CreditStatus = 1)
var leadSales = (get List<LeadSales> where Max CurrentCreditStatus = 1)
var joined = from leadSale in leadSales
join creditAction in creditActions on leadSale.LeadSalesID equals creditAction.LeadSalesID into crs
from creditAction in crs.DefaultIfEmpty()
select new CreditSearchItemModel
{
LeadSaledID = leadSale.LeadSalesID,
Lead = leadSale.Lead,
Contact = leadSale.Contact,
Comment = creditAction.Comment,
LeadSalesCreditReason = creditAction.LeadSalesCreditReason,
LeadSalesType = leadSale.LeadSalesType,
DateCreated = creditAction.DateCreated,
CountCreditsRequested = ??????
CountLeadsPurchased = ?????
};
Everything is working fine here IF I leave off the least two Count
items. I get exactly the results I want. Except... What I am having trouble with is how to get the two Counts: CountCreditsRequested
and CountLeadsPurchased
. In the case of Contact 45, these numbers would be:
CountCreditsRequested = 2
CountLeadsPurchased = 3
So the final report would look like this:
LeadID Broker Requests Reason Comment
--------------------------------------------------------------------------
3398 John Doe 2/3 Bad Contact Info "Dude, no good" [Refund Button] [Deny Button]
But that's just for Contact 45. This query is not Contact specific and we need a set of results for all contacts. And of course the numbers in the "Results" column would be the same for each broker, no matter how many times he appears on this report.
I've found a few other threads that kinda come close, like this one, but none are getting me all the way there.
I hope all of this makes sense!! Thanks!!