troubleshooting Question

How do I write this SQL more efficiently?

Avatar of jtuttle99
jtuttle99 asked on
SQL
2 Comments1 Solution158 ViewsLast Modified:
I asked SQL syntax in the last question, and a expert told me to write this SQL more efficiently.
The code works,but I would like to know how experts write more efficiently.
I'm a beginner and I would like to learn more.
Thank you
"SELECT [Employees].employeename,
(SELECT COUNT(dupe.empid)FROM [Employees] as Dupe INNER JOIN Customers ON dupe.empid = Customers.empid where Customers.empid = [Employees].empid AND Customers.ActiveCust = TRUE AND Customers.LastLogin >= dateadd('d',"&  loginspan & ",NOW)) as Active_Customers,
(SELECT COUNT(dupe.empid)FROM [Employees] as Dupe INNER JOIN Customers ON dupe.empid = Customers.empid where Customers.empid = [Employees].empid AND Customers.ActiveCust = TRUE) as Active_Customers_box,
(SELECT COUNT(dupe.empid)FROM [Employees] as Dupe INNER JOIN Customers ON dupe.empid = Customers.empid where Customers.empid = [Employees].empid AND Customers.Deposit = TRUE AND Customers.Jobfini = FALSE AND Customers.ActiveCust = TRUE) as Deposit,
(SELECT COUNT(dupe.empid) FROM[Employees] as Dupe, blog where dupe.empid=Val(IIF(ISNULL(blog.Poster),0,blog.Poster)) AND blog.PostDate >= dateadd('d',"&  loginspan & ",NOW) AND dupe.empid = [Employees].empid group by poster) AS Blogged,
(SELECT COUNT(BlogID) FROM blog INNER JOIN Customers ON blog.CustomerID = Customers.customerid WHERE NOT(ISNUMERIC(blog.Poster)) AND Customers.empID = Employees.empID AND blog.PostDate >= dateadd('d',"&  loginspan & ",NOW)) AS Customers_Postsblog,
[Employees].empID 
FROM [Employees] where [Employees].active = TRUE 
Group By Employees.employeename, empID 
ORDER BY 2 DESC;"
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros