Return rows where a customer bought things on same day

Question

Can someone help me with the rest of my Query.

This query gives me Customer, AdressNr, Date, Employee, Article, ActivityNr from all the sales in my Company.

SELECT  ad.Name + ' ' + ad.Vorname AS Customer,
pa.Kunde AS CustomerNr, 
CONVERT(VARCHAR(10),p.datum,126) AS Date,
(SELECT a.name + ' ' + a.Vorname AS Name FROM PRO_Mitarbeiter m LEFT JOIN ADR_Adressen a ON a.AdressNrADR=m.AdressNrADR WHERE m.MitNrPRO = l.MitNrPRO) as Employee, 
p.Artikel_1 AS Article,
l.AufgabenNrCRM AS OrderNr
FROM ZUS_Therapie_Positionen p 
INNER JOIN  CRM_AufgabenLink l ON l.AufgabenNrCRM = p.Id_Aktivitaet 
INNER JOIN CRM_Aufgaben ab ON ab.AufgabenNrCRM = p.Id_Aktivitaet 
INNER JOIN PRO_Auftraege pa ON pa.AuftragNrPRO = ab.AuftragNrPRO 
INNER JOIN ADR_Adressen ad ON ad.AdressNrADR = pa.Kunde 
INNER JOIN ADR_GruppenLink gl ON gl.AdressNrADR = ad.AdressNrADR 
INNER JOIN ADR_Gruppen g ON g.GruppeADR = gl.GruppeADR
WHERE   l.MitNrPRO != 0
GROUP BY l.AufgabenNrCRM,ad.Name,ad.Vorname,pa.Kunde,p.datum,p.Artikel_1,l.MitNrPRO
ORDER BY pa.Kunde,p.datum,l.AufgabenNrCRM

My goal is to filter this so i get only rows back where the customer has bought more then 1 Thing on the same day. It doesn't matter if a customer bought the same Article twice on the same day. I want too see this also.

It's to complicated to write some SQL Fiddle for you but in this Picture you can see what my goal is. I want to take away all rows with an X on the left side and thoose with a Circle i want to Keep.

Please provide sample results of the query above and the results you want to get. — Alex, May 12 '16 at 12:07
Possible duplicate of [SQL query for finding records where count > 1](http://stackoverflow.com/questions/7151401/sql-query-for-finding-records-where-count-1) — Tab Alleman, May 12 '16 at 12:50
1) You have a `GROUP BY`, but no aggregate function; either you really want `DISTINCT` (if you have multiple identical rows, or the rows are already unique, and you don't need it. 2) When the clause is over ~3 columns, especially from separate tables, you probably want to try something else, like pre-aggregating one or more of the tables. — Clockwork-Muse, May 12 '16 at 13:06
Why are you hiding parts of the results? I hope you aren't sending us screenshots of production data. — steinar, May 12 '16 at 13:43

Kahn · Answer 1 · 2016-05-12T13:19:16.537

As I don't speak German, I won't target this specifically to your SQL. But see the following quasi-code for a similar example that you should be able to apply to your own script.

SELECT C.CustomerName, O.OrderDate, O.OrderNumber
FROM CUSTOMER C
JOIN ORDERS O ON O.Customer_ID = C.Customer_ID
JOIN 
   (SELECT Customer_ID, OrderDate
   FROM ORDERS   
   GROUP BY Customer_ID, OrderDate
   HAVING COUNT(*) > 1) SRC 
      ON SRC.Customer_ID = O.Customer_ID AND SRC.OrderDate = O.OrderDate

In the script above, the last query (a subquery) would only return results where a customer had more than one order in a given day. By joining that to your main query, you would effectively produce the result asked in the OP.

Edit 1: Regarding your comment below, I really recommend just going over your datamodel, trying to understand what's happening here, and fixing it on your own. But there is an easy - albeit hardly optimal solution to this by just using your own script above. Note, while this is not disastrous performance-wise, it's obviously not the cleanest, most effective method either. But it should work:

;WITH CTE AS (SELECT  ad.Name + ' ' + ad.Vorname AS Customer,
   pa.Kunde AS CustomerNr, 
   CONVERT(VARCHAR(10),p.datum,126) AS [Date],
   (SELECT a.name + ' ' + a.Vorname AS Name FROM PRO_Mitarbeiter m LEFT JOIN ADR_Adressen a ON a.AdressNrADR=m.AdressNrADR WHERE m.MitNrPRO = l.MitNrPRO) as Employee, 
   p.Artikel_1 AS Article,
   l.AufgabenNrCRM AS OrderNr
   FROM ZUS_Therapie_Positionen p 
   INNER JOIN  CRM_AufgabenLink l ON l.AufgabenNrCRM = p.Id_Aktivitaet 
   INNER JOIN CRM_Aufgaben ab ON ab.AufgabenNrCRM = p.Id_Aktivitaet 
   INNER JOIN PRO_Auftraege pa ON pa.AuftragNrPRO = ab.AuftragNrPRO 
   INNER JOIN ADR_Adressen ad ON ad.AdressNrADR = pa.Kunde 
   INNER JOIN ADR_GruppenLink gl ON gl.AdressNrADR = ad.AdressNrADR 
   INNER JOIN ADR_Gruppen g ON g.GruppeADR = gl.GruppeADR
   WHERE   l.MitNrPRO != 0
   GROUP BY l.AufgabenNrCRM,ad.Name,ad.Vorname,pa.Kunde,p.datum,p.Artikel_1,l.MitNrPRO
   ORDER BY pa.Kunde,p.datum,l.AufgabenNrCRM)
SELECT C.*
FROM CTE C
JOIN (Select CustomerNr, [Date]
   FROM CTE B
   GROUP BY CustomerNr, [Date]
   HAVING COUNT(*) > 1) SRC 
      ON SRC.CustomerNr = C.CustomerNr AND SRC.[Date] = C.[Date]

This should work directly. But as I said, this is an ugly workaround where we're basically all but fetching the whole set twice, as opposed to just limiting the sub query to just the bare minimum of necessary tables. Your choice. :)

thanks for your help. My Problem is that my query includes so many Joins and also a couple of where Statements and i dont know if i have to use all the joins and where Statements on both places? — user2210516, May 12 '16 at 12:41
I tried now and it doesn't work at all :( It gives me all the results back anyway — user2210516, May 12 '16 at 13:01
Check the edited section of my post for a workaround that should work directly. It's ugly, but it should get the job done. I still recommend doing more homework to optimize the query to use only the necessary data. :) — Kahn, May 12 '16 at 13:21

score 0 · Answer 2 · answered May 12 '16 at 13:43

Tried that also and it didnt work. I also made a new query trying to Keep it so simple as possible and it doesnt work either. It still give me Single values back..

SELECT p.Datum,a.AufgabenNrCRM,auf.Kunde FROM CRM_Aufgaben a
LEFT JOIN ZUS_Therapie_Positionen p ON p.Id_Aktivitaet = a.AufgabenNrCRM
LEFT JOIN PRO_Auftraege auf ON auf.AuftragNrPRO = a.AuftragNrPRO
        LEFT JOIN 
        (SELECT pa.Datum,au.Kunde FROM CRM_Aufgaben aa 
        LEFT JOIN ZUS_Therapie_Positionen pa ON pa.Id_Aktivitaet = aa.AufgabenNrCRM
        LEFT JOIN PRO_Auftraege au ON au.AuftragNrPRO = aa.AuftragNrPRO
        GROUP BY pa.Datum,au.Kunde
        HAVING COUNT(*) > 1) SRC
ON SRC.Kunde = auf.Kunde
WHERE p.datum IS NOT NULL
GROUP BY  p.Datum,a.AufgabenNrCRM,auf.Kunde 
ORDER BY auf.Kunde,p.Datum

Return rows where a customer bought things on same day

2 Answers2