0
@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
perror
  • 7,071
  • 16
  • 58
  • 85
FloatingPC
  • 1
  • 4
  • 1
    https://stackoverflow.com/questions/18189188/t-sql-pivot-add-total-row – MatthewMartin Sep 22 '15 at 18:32
  • Have no clue what they did in that example.. i'm still new to coding and learning along the way. – FloatingPC Sep 22 '15 at 19:45
  • They added the ROLLUP command, ref. https://technet.microsoft.com/en-us/library/ms189305%28v=sql.90%29.aspx – MatthewMartin Sep 22 '15 at 19:48
  • I was required to add group by ResellerCode, PivotResults.[20], PivotResults.[30], PivotResults.[40], PivotResults.[50], PivotResults.[60] with rollup Results didnt come out correct.. Gave me repeat lines with almost a pyramid look results scheme – FloatingPC Sep 22 '15 at 20:30

1 Answers1

0

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
FloatingPC
  • 1
  • 4