sql server - SQL show entries where a specific field shows up multiple times -
first off here data using:
name, phone, email & invoicecost
i trying write query selects name, phone & email details where
- the user (identified email) has made 2 or more purchases within last 180 days
- the total cost of of invoices within these 180 days comes on 500
below sql have come far doubt of use @ all:
select name, phone, email, invoicecost invoice join (select invoice.email, count(*) totalcount, sum( case when invoice.invoicedate >= (getdate() - 180) 1 else 0 end) lastsixmonths invoice group invoice.email) e on invoice.email = e.email (e.totalcount > 1 , e.lastsixmonths = 1);
i can't figure out how incorporate total cost part, please help!
assuming consider unique combinations of name, phone, , email individual customers:
select name, phone, email, count(*) totalcount, sum(invoicecost) totalcost invoice group email, name, phone datediff(d, invoicedate, getdate()) <= 180 having count(*) >= 2 or sum(invoicecost) > 500.0
Comments
Post a Comment