CASorter
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
RfidTagProductContractRece iptDataVie w 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.
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
RfidTagProductContractRece
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.