Where I am placing WHERE clause in PIVOT with date transaction
amount_usd paytype SendItemDateTime1
5.00 google 2015-04-01
2.00 google 2015-04-01
5.00 transfer 2015-04-01
15.00 google 2015-04-01
5.00 google 2015-04-01
2.00 google 2015-04-02
60.00 google 2015-04-02
60.00 google 2015-04-02
5.00 google 2015-04-03
Above is my demo database with amount_usd, paytype and SendItemDateTime1 columns. when i use pivok with below request it gives below output which SendItemDateTime1 is not a group ... what is the problem?
select amount_usd, paytype, SendItemDateTime1 from tblMobile_RequestOrderLog
where status = 1 and sendstatus = 1 and enable = 1
and SendItemDateTime1 between '4/1/2015' and '4/30/2015'
order by SendItemDateTime1
Below is the result of the query above.
SenditemDateTime1 google mol molpay molstore paypal transfer
2015-04-01 15.00 NULL NULL NULL NULL NULL
2015-04-01 5.00 NULL NULL NULL NULL NULL
2015-04-01 15.00 NULL NULL NULL NULL NULL
2015-04-01 5.00 NULL NULL NULL NULL NULL
2015-04-01 60.00 NULL NULL NULL NULL NULL
2015-04-01 10.00 NULL NULL NULL NULL NULL
And below I want ...
SendItemDate google mol molpay molstore paypal transfer
2015-04-01 32 0 0 0 0 5
2015-04-02 122 0 0 0 0 0
2015-04-03 5 0 0 0 0 0
sorry, first time posed the question here ...
Edit
This work for me with the "Where" clause:
SELECT SendItemDateTime1, COALESCE([google], 0), COALESCE([transfer], 0),
COALESCE([paypal], 0),COALESCE([molpay], 0)
FROM (Select SendItemDateTime1, paytype, amount_usd
from tblMobile_RequestOrderLog
where gameidn = 248 and status = 1 and sendstatus = 1 and enable = 1
and SendItemDateTime1 between '4/1/2015 12:00:00 AM'
and '4/30/2015 11:59:59'
) X
PIVOT
(
SUM(amount_usd)
for [paytype] IN ([google],[transfer],[paypal],[molpay])
) piv;
source to share
You can roll up the data in your first table with the following query - you just need to explicitly specify all the columns of the payment type. I assumed it is SUM()
used as aggregation:
SELECT SendItemDateTime1, [google],[transfer],[paypal],[molpay]
FROM MyTable
PIVOT
(
SUM(amount_usd)
for [paytype] IN ([google],[transfer],[paypal],[molpay])
) piv;
Edit, Re where can I filter
If the filter predicate can be applied to finite columns, then WHERE
it can be applied after the PIVOT. Otherwise, if filtering needs to be done on unsecured columns, you can use a CTE or a view as you did. Here's an example of a prefilter in CTE and postfilter WHERE:
-- Prefilter of non-pivot columns done in CTE or Derived table
WITH cte AS
(
SELECT [amount_usd], [paytype], [SendItemDateTime1]
FROM MyTable
WHERE [amount_usd] > 2
)
SELECT SendItemDateTime1, COALESCE([google], 0), COALESCE([transfer], 0),
COALESCE([paypal], 0),COALESCE([molpay], 0)
FROM cte
PIVOT
(
SUM(amount_usd)
for [paytype] IN ([google],[transfer],[paypal],[molpay])
) piv
-- Post filter of pivot columns done on the final projection
WHERE SendItemDateTime1 > '2015-01-01';
source to share