|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
June 22nd, 2004, 03:15 AM
|
|||
|
|||
Group by Query
Hi,
Please help in writing a SQL query. I have a table with EmpId,DirectSales,TeamLeaderId,TeamMemSales as some of the columns.The situation is a Team Manager as sell directly, which comes under directsales and the overriding value of his team member comes under teammemsales. Now I want get sum of both for a particular manager on a daily basis. Like SaleDate sum(Directsales) sum(TeammemSales) if the given id is empid then the it is directsales and if it is in teamleaderid then it is teammemsales. How to achieve this task in query? Any help is appreciated. Thanks M.L.Srinvas
|
|
#2
June 22nd, 2004, 06:55 PM
|
|||
|
|||
|
Maybe a stored proc like this would help
Code:
CREATE PROCEDURE dbo.selSomething
@id INTEGER
AS
SET NOCOUNT ON
IF EXISTS (
SELECT TOP 1 *
FROM table WITH (NOLOCK)
WHERE EmpID = @id
)
BEGIN
--- Assume that this is an EmpID
SELECT SaleDate, SUM(DirectSales)
FROM table WITH (NOLOCK)
WHERE EmpID = @id
GROUP BY SaleDate
ORDER BY SaleDate
END
ELSE
BEGIN
--- Assume that this is an TeamLeaderID
SELECT SaleDate, SUM(TeamMemSales)
FROM table WITH (NOLOCK)
WHERE TeamLeaderID = @id
GROUP BY SaleDate
ORDER BY SaleDate
END
SET NOCOUNT OFF
Basically, it checks if there's at least one row where the EmpID = @id, and if this is the case, then it assumes that this is an EmpID, otherwise it assumes that @id refers to a TeamLeaderID.
__________________
Up the Irons What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > Group by Query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
