@DateMonth as INT,
@DateYear as INT
AS
--set @DateMonth = 08
--SET @DateYear = 2015
DECLARE @FromDate DateTime;
DECLARE @ToDate DateTime;
SET @FromDate = CONVERT(DateTime, convert(varchar, @DateMonth) + '/01/' + convert(varchar, @DateYear))
SET @ToDate = EOMonth(@FromDate)
select * from (select C.RESELLERCODE, iAdjustmentAmount, count(iAdjustmentAmount) AS AdjTotal
FROM [EricssonRawData].[dbo].[tbl_EricssonRawData]
JOIN AirVoice.dbo.Customers C WITH(NOLOCK)
on C.SubscriberNumber = iSubscriberMSISDN
where convert(DateTime,substring(FileName,21,8)) between @FromDate and @ToDate
and iRecordType like 'A'
and iServiceClass = 531
and iAdjustmentType like 'ACTIVATION'
and iAdjustmentAmount >= '20'
group by RESELLERCODE, iAdjustmentAmount) as Test
PIVOT
(
sum(AdjTotal) FOR iAdjustmentAmount IN ([20],[30],[40],[50],[60])
) AS PivotResults
--group by ResellerCode
order by ResellerCode
Current Results..
RESELLERCODE 20 30 40 50 60
US3353 NULL 2 NULL NULL NULL
US3385 NULL 44 NULL 3 NULL
US3403 4 NULL NULL NULL NULL
US3341 NULL 2 NULL NULL NULL
What I would like "total"
RESELLERCODE 20 30 40 50 60 TOTAL
US3341 NULL 2 NULL NULL NULL 2
US3353 NULL 2 NULL NULL NULL 2
US3385 NULL 44 NULL 3 NULL 47
US3403 4 NULL NULL NULL NULL 4
@DateMonth as INT,
@DateYear as INT
AS
--set @DateMonth = 08
--SET @DateYear = 2015
DECLARE @FromDate DateTime;
DECLARE @ToDate DateTime;
SET @FromDate = CONVERT(DateTime, convert(varchar, @DateMonth) + '/01/' + convert(varchar, @DateYear))
SET @ToDate = EOMonth(@FromDate)
select SimSoldTo
, PivotResults.[20]
, PivotResults.[30]
, PivotResults.[40]
, PivotResults.[50]
, PivotResults.[60]
--Sum the total Numbers of PINS Sold
, coalesce(PivotResults.[20],0) + coalesce(PivotResults.[30],0) + coalesce(PivotResults.[40],0)
+ coalesce(PivotResults.[50],0) + coalesce(PivotResults.[60],0) as SimSoldCount
--Sum the $ Amount ofthe PINS Sold
, coalesce(PivotResults.[20],0)*20 + coalesce(PivotResults.[30],0)*30 + coalesce(PivotResults.[40],0)*40
+ coalesce(PivotResults.[50],0)*50 + coalesce(PivotResults.[60],0)*60 as SumOfSales
--SUM the % Commission being paid
, coalesce(PivotResults.[20],0)*20 *.03 + coalesce(PivotResults.[30],0)*30 *.03 + coalesce(PivotResults.[40],0)*40 *.03
+ coalesce(PivotResults.[50],0)*50 *.03 + coalesce(PivotResults.[60],0)*60 *.03 as "Commission Pay Out 3%"
from(SELECT C.RESELLERSIMSOLDTO as SimSoldTo
,iAdjustmentAmount as RefillAmount
,count(iAdjustmentAmount) as AdjustmentValue
FROM [EricssonRawData].[dbo].[tbl_EricssonRawData]
JOIN AirVoice.dbo.Customers C WITH(NOLOCK)
on C.SubscriberNumber = iSubscriberMSISDN
where convert(DateTime,substring(FileName,21,8)) between @FromDate and @ToDate
and iServiceClass like '531'
and iRecordType like 'A'
and iAdjustmentAmount >= '20'
and C.ResellerActivationDate > DATEADD(year,-3,GETDATE())
group by C.RESELLERSIMSOLDTO, iAdjustmentAmount
) as Great
PIVOT
(
sum(AdjustmentValue) FOR RefillAmount IN ([20],[30],[40],[50],[60])
) AS PivotResults
order by SimSoldCount desc