sqlsumpivotsqlresultsetmapping

get sums for pivot results


@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

Solution

  • Here is what i had to do to get the sums i was looking to get done.. I would post a snippet but i dont have enough of a reputation.. Thanks for your help

    @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