28. juni 2007 - 08:44Der er
3 kommentarer og 1 løsning
Samle 2 Select sætning i en Stored Procedure
Hej!
Hvordan kan jeg samle disse 2 SQL sætninger i et samlet resultat, jeg tænker at man måske kan gøre det i en Stored Procedure, men er ikke sikker på at det er smartest.
SELECT SUM(CASE WHEN c.Status = 0 THEN 1 ELSE 0 END) AS InActive, SUM(CASE WHEN c.Status BETWEEN 1 AND 8 THEN 1 ELSE 0 END) AS Active, SUM(CASE WHEN c.Status > 8 THEN 1 ELSE 0 END) AS Prospekt FROM CustomerEmployee ce INNER JOIN Customer c ON ce.CustomerID = c.ID WHERE (ce.EmployeeID = @EmployeeID)
SELECT COUNT(*) AS TotalVisits, COUNT(DISTINCT CustomerID) AS UniqueVisits FROM Appointment WHERE (EmployeeID = @EmployeeID)
Om ikke andet kan det altid laves med et par subselects:
SELECT SUM(CASE WHEN c.Status = 0 THEN 1 ELSE 0 END) AS InActive, SUM(CASE WHEN c.Status BETWEEN 1 AND 8 THEN 1 ELSE 0 END) AS Active, SUM(CASE WHEN c.Status > 8 THEN 1 ELSE 0 END) AS Prospekt, (SELECT COUNT(*) FROM Appointment WHERE EmployeeID = @EmployeeID) as TotalVisits, (SELECT COUNT(DISTINCT CustomerID) FROM Appointment WHERE EmployeeID = @EmployeeID) as UniqueVisits, FROM CustomerEmployee ce INNER JOIN Customer c ON ce.CustomerID = c.ID WHERE (ce.EmployeeID = @EmployeeID)
SELECT SUM(CASE WHEN c.Status = 0 THEN 1 ELSE 0 END) AS InActive, SUM(CASE WHEN c.Status BETWEEN 1 AND 8 THEN 1 ELSE 0 END) AS Active, SUM(CASE WHEN c.Status > 8 THEN 1 ELSE 0 END) AS Prospekt, (SELECT COUNT(*) FROM Appointment WHERE EmployeeID = @EmployeeID) as TotalVisits, (SELECT COUNT(DISTINCT CustomerID) FROM Appointment WHERE EmployeeID = @EmployeeID) as UniqueVisits FROM CustomerEmployee ce INNER JOIN Customer c ON ce.CustomerID = c.ID WHERE (ce.EmployeeID = @EmployeeID)
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.