1

I'm still fairly new to SQL. This is a stripped down version of the query I'm trying to run. This query is suppose to find those customers with more than 3 cases and display either the top 1 case or all cases but still show the overall count of cases per customer in each row in addition to all the case numbers.

The TOP 1 subquery approach didn't work but is there another way to get the results I need? Hope that makes sense.

Here's the code:

SELECT t1.StoreID, t1.CustomerID, t2.LastName, t2.FirstName
,COUNT(t1.CaseNo) AS CasesCount
,(SELECT TOP 1 t1.CaseNo)
FROM MainDatabase t1
INNER JOIN CustomerDatabase t2
ON t1.StoreID = t2.StoreID
WHERE t1.SubmittedDate >= '01/01/2017' AND t1.SubmittedDate <= '05/31/2017'
GROUP BY t1.StoreID, t1.CustomerID, t2.LastName, t2.FirstName
HAVING COUNT (t1.CaseNo) >= 3
ORDER BY t1.StoreID, t1.PatronID

I would like it to look something like this, either one row with just the most recent case and detail or several rows showing all details of each case in addition to the store id, customer id, last name, first name, and case count.

Data Example

Dason
  • 60,663
  • 9
  • 131
  • 148
Jenn
  • 13
  • 3

1 Answers1

0

For these I usually like to make a temp table of aggregates:

DROP TABLE IF EXISTS #tmp; 
CREATE TABLE #tmp ( 
CustomerlD int NOT NULL DEFAULT 0,
case_count int NOT NULL DEFAULT 0,
case_max int NOT NULL DEFAULT 0, 
); 
INSERT INTO #tmp 
(CustomerlD, case_count, case_max) 
SELECT CustomerlD, COUNT(tl.CaseNo), MAX(tl.CaseNo) 
FROM MainDatabase 
GROUP BY CustomerlD;

Then you can join this "tmp" table back to any other table you want to display the number of cases on, or the max case number on. And you can limit it to customers that have more than 3 cases with WHERE case_count > 3

AS7K
  • 417
  • 4
  • 20
  • Will TOP 1 and MAX work the same way by giving me the most recent results in this case? If so, then what's the difference? Thanks for answering! – Jenn Aug 11 '17 at 22:55
  • 1
    Please do not post your code as pictures.I have added the code from the picture you posted. see this meta post how to post code in your answer https://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks – ClearLogic Aug 12 '17 at 05:27
  • Sorry ClearLogic, never needed to follow all those steps before, just having issues with SQL code specifically. Thanks for clearing that up! – AS7K Aug 14 '17 at 15:57
  • @Jenn TOP 1 would take the first result in a result set, regardless of the values in that result set. MAX is an aggregate function that returns the greatest value from the given column. Assuming the "CaseNo" increases as cases are added, `MAX(CaseNo)` would get the greatest "CaseNo" for each customer since you've grouped by CustomerID. Does that make sense? – AS7K Aug 14 '17 at 16:37
  • yes that makes sense, thanks for clarifying! however, i also wanted it to return CaseDetail so I was hoping to use TOP 1 to grab the most recent CaseNo and CaseDetail. I can't use MAX(CaseDetail) as it is coded (ex. Initial Report: 1, Follow Up: 2) so it would just return the greatest number and not the CaseDetail for the most recent CaseNo. Any ideas on how to approach this? Thanks! – Jenn Aug 14 '17 at 20:49
  • Hmmmm add a case_max_detail column to the temp table, then JOIN #tmp to MainDatabase on case_max = CaseNo and set case_max_detail = the value in the resulting records. For more info about doing an UPDATE on a JOIN, check out this stackoverflow answer: https://stackoverflow.com/questions/982919/sql-update-query-using-joins – AS7K Aug 15 '17 at 14:51
  • Thanks for accepting my answer! Don't forget to upvote if you found this useful. Welcome to SQL and good luck! : ) – AS7K Aug 16 '17 at 13:44