Warning: Null value is eliminated by an aggregate
I m using this SP in SQL 2005 and Getting this error plz help me
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[systek_salesRpt]
@username varchar(50) = NULL,
@RoleID int = NULL,
@ItemTitle varchar(50) = NULL,
@jumpid int
AS
BEGIN
CREATE TABLE #saletemp
(
SubscriptionID int,
Noofsales int,
subscriberID int ,
OwnerID int,
Rolename varchar(50),
Type varchar(50),
Itemid int,
Totalprice float,
Commission float,
RoleID int,
ItemTitle varchar(50),
CreatedDate DateTime,
Username varchar(50)
)
INSERT INTO #saletemp(SubscriptionID,Noofsales,subscriberID,Ow nerID,Rolename,Type,Itemid,Totalprice,Commission,R oleID,ItemTitle,CreatedDate,UserName)
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(D.VideoID,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
D.VideoTitle as ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'videos')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='videos')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
INNER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleNam e,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,A.RoleID,B.CreatedD ate,H.UserName
UNION ALL
------------------------------------------------------------------series-------------------------------------------------------------------------
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(E.SeriesId,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
E.seriesTitle as ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'series')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='series')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
LEFT OUTER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleNam e,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,A.RoleID,E.seriesTi tle,B.CreatedDate,H.userName
UNION ALL
-----------------------------------------------------------group id----------------------------------------------------------------------------
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(F.GroupID,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
F.GroupName AS ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'Groups')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='Groups')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
LEFT OUTER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleNam e,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,C.RoleID,A.RoleID,F .GroupName,B.CreatedDate,H.username
----------------------------------------------------removing duplicate subscrptionid---------------------------------------------------------
DECLARE @Count int
DECLARE @SubsCriptionID AS int
DECLARE vendor_cursor CURSOR
FOR
SELECT SubscriptionID,Count(SubscriptionID) - 1 FROM #saletemp Where RoleName <> 'smartIcast' GROUP BY SubscriptionID HAVING Count(subscriptionid) > 1
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @SubsCriptionID,@Count
WHILE @@FETCH_STATUS = 0
BEGIN
SET ROWCOUNT @Count
DELETE FROM #saletemp WHERE #saletemp.SubscriptionID = @SubsCriptionID
SET ROWCOUNT 0
FETCH NEXT FROM vendor_cursor INTO @SubsCriptionID,@Count
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor
----------------------------------------------------------------------------------------------------------------------------------------------
IF @jumpid = 0
BEGIN
SELECT subscriptionid,noofsales,subscriberid,ownerid,Role name,Type,itemID,
'$' + (CAST(ROUND(TotalPrice,2) AS Varchar)) AS TotalPrice,
'$' + (CAST(ROUND(Commission,2) AS Varchar)) AS Commission,
RoleID,ItemTitle,CreatedDate,UserName
FROM #saletemp
END
IF @jumpid = 1
BEGIN
SELECT subscriptionid,noofsales,subscriberid,ownerid,Role name,Type,itemID,
'$' + (CAST(ROUND(TotalPrice,2) AS Varchar)) AS TotalPrice,
'$' + (CAST(ROUND(Commission,2) AS Varchar)) AS Commission,
RoleID,ItemTitle,CreatedDate,UserName
FROM #saletemp
Where
RoleID = @RoleID
OR
ItemTitle LIKE '%'+ @ItemTitle + '%'
OR
username LIKE '%' + @username + '%'
END
DROP TABLE #saletemp
END
hyder_master
hyder_master
|
