1

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!!

Community
  • 1
  • 1
Casey Crookston
  • 13,016
  • 24
  • 107
  • 193
  • If you need data related to each contacts, do a `group by` on the contact id and then do the projection – Developer Jan 18 '17 at 17:36

0 Answers0