Link to home
Start Free TrialLog in
Avatar of CASorter
CASorterFlag for United States of America

asked on

Sql PivotTable help

new to pivot tables here.....

here is the query


SELECT TOP (100) PERCENT RTPCRDV.CompanyLabel AS comp, RTPCRDV.CloudGroupLabel AS [Group], RTPCRDV.ClientPanelLabel AS Verb, DATEADD(dd, 0,
               DATEDIFF(dd, 0, RTPCRDV.ReceiptCreated)) AS date, COUNT(*) AS count
FROM  Product INNER JOIN
               RfidTagProductContractReceiptDataView AS RTPCRDV ON Product.Id = RTPCRDV.ProductId INNER JOIN
               ProductShape ON Product.ProductShapeId = ProductShape.Id
GROUP BY RTPCRDV.CompanyLabel, RTPCRDV.CompanyId, RTPCRDV.ClientPanelId, RTPCRDV.ClientPanelLabel, DATEADD(dd, 0, DATEDIFF(dd, 0,
               RTPCRDV.ReceiptCreated)), RTPCRDV.CloudGroupLabel
HAVING (RTPCRDV.CompanyId = 2030) AND (DATEADD(dd, 0, DATEDIFF(dd, 0, RTPCRDV.ReceiptCreated)) > CONVERT(DATETIME, '2014-06-20 00:00:00', 102))
ORDER BY date



here is what it returns


Comp      group              verb          date                                       count
Omni Inc      PrimeTime      Check In      2014-06-23 00:00:00.000      66
Omni Inc      PrimeTime      Drop Off      2014-06-23 00:00:00.000      10
Omni Inc      PrimeTime      Ship      2014-06-23 00:00:00.000      10
Omni Inc      PrimeTime      None      2014-06-24 00:00:00.000      68
Omni Inc      PrimeTime      Check In      2014-06-24 00:00:00.000      65
Omni Inc      PrimeTime      Drop Off      2014-06-24 00:00:00.000      7
Omni Inc      PrimeTime      Pick Up      2014-06-24 00:00:00.000      11
Omni Inc      PrimeTime      Prepare      2014-06-24 00:00:00.000      108
Omni Inc      PrimeTime      Ship      2014-06-24 00:00:00.000      129
Omni Inc      PrimeTime      Return      2014-06-24 00:00:00.000      1
Omni Inc      PrimeTime      Lost      2014-06-24 00:00:00.000      2
Omni Inc      PrimeTime      Check In      2014-06-25 00:00:00.000      2072
Omni Inc      PrimeTime      Drop Off      2014-06-25 00:00:00.000      557
Omni Inc      PrimeTime      Pick Up      2014-06-25 00:00:00.000      1533
Omni Inc      PrimeTime      Prepare      2014-06-25 00:00:00.000      1982
Omni Inc      PrimeTime      Ship      2014-06-25 00:00:00.000      1365
Omni Inc      PrimeTime      Lost      2014-06-25 00:00:00.000      20
Omni Inc      PrimeTime      Check In      2014-06-27 00:00:00.000      506
Omni Inc      PrimeTime      Drop Off      2014-06-27 00:00:00.000      16
Omni Inc      PrimeTime      Prepare      2014-06-27 00:00:00.000      18
Omni Inc      PrimeTime      Ship      2014-06-27 00:00:00.000      17
Omni Inc      PrimeTime      Check In      2014-06-30 00:00:00.000      1650
Omni Inc      PrimeTime      Drop Off      2014-06-30 00:00:00.000      35
Omni Inc      PrimeTime      Pick Up      2014-06-30 00:00:00.000      15
Omni Inc      PrimeTime      Prepare      2014-06-30 00:00:00.000      501
Omni Inc      PrimeTime      Ship      2014-06-30 00:00:00.000      3240
Omni Inc      PrimeTime      Check In      2014-07-01 00:00:00.000      960
Omni Inc      PrimeTime      Drop Off      2014-07-01 00:00:00.000      801
Omni Inc      PrimeTime      Pick Up      2014-07-01 00:00:00.000      480
Omni Inc      PrimeTime      Prepare      2014-07-01 00:00:00.000      480
Omni Inc      PrimeTime      Ship      2014-07-01 00:00:00.000      1060
Omni Inc      PrimeTime      Attach      2014-07-02 00:00:00.000      100
Omni Inc      PrimeTime      Check In      2014-07-02 00:00:00.000      3262
Omni Inc      PrimeTime      Drop Off      2014-07-02 00:00:00.000      821
Omni Inc      PrimeTime      Pick Up      2014-07-02 00:00:00.000      195
Omni Inc      PrimeTime      Prepare      2014-07-02 00:00:00.000      2064
Omni Inc      PrimeTime      Ship      2014-07-02 00:00:00.000      2027
Omni Inc      PrimeTime      Return      2014-07-02 00:00:00.000      20
Omni Inc      2030      26      Check In      7/3/2014      PT      225
Omni Inc      2030      28      Drop Off      7/3/2014      PT      205
Omni Inc      2030      29      Pick Up      7/3/2014      PT      150
Omni Inc      2030      30      Prepare      7/3/2014      PT      231
Omni Inc      2030      31      Ship      7/3/2014      PT      208
Omni Inc      2030      26      Check In      7/7/2014      PT      24
Omni Inc      2030      29      Pick Up      7/7/2014      PT      23
Omni Inc      2030      26      Check In      7/8/2014      PT      12
Omni Inc      2030      3      Attach      7/9/2014      PT      2
Omni Inc      2030      26      Check In      7/9/2014      None      3
Omni Inc      2030      26      Check In      7/9/2014      PT      74
Omni Inc      2030      28      Drop Off      7/9/2014      PT      14
Omni Inc      2030      29      Pick Up      7/9/2014      PT      13
Omni Inc      2030      30      Prepare      7/9/2014      None      2
Omni Inc      2030      30      Prepare      7/9/2014      PT      54
Omni Inc      2030      31      Ship      7/9/2014      PT      51
Omni Inc      2030      43      Return      7/9/2014      PT      8
Omni Inc      2030      28      Drop Off      7/10/2014      PT      7
Omni Inc      2030      26      Check In      7/11/2014      PT      102
Omni Inc      2030      28      Drop Off      7/11/2014      PT      51
Omni Inc      2030      29      Pick Up      7/11/2014      PT      39
Omni Inc      2030      30      Prepare      7/11/2014      PT      90
Omni Inc      2030      31      Ship      7/11/2014      PT      55
Omni Inc      2030      44      Lost      7/11/2014      PT      9
Omni Inc      2030      44      Lost      7/14/2014      PT      276
Omni Inc      2030      26      Check In      7/15/2014      PT      59
Omni Inc      2030      30      Prepare      7/15/2014      PT      85



here is what i am looking for    

grouping by date of each verb and count

something like this

              6/23/2014      6/24/2014      6/25/2014      …      7/11/2014
checkin      66                      65                      2072             …      102
prepare      0                     108                      1982             …         90
ship             10                     129                      1365             …        55
Dropoff      10                    7                      557                     …        51
pickup      0                      11                  1533             …        39
lost            0                    2                     20                 …                9
attach      15                    0                  0                           …      0
return      0                   1                       0            …             0


the order of the row headers on teh right is not important, just that the totals match up down the row.
SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial