c# - Where do I place a WHERE clause in a 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 demo database have amount_usd, paytype, , senditemdatetime1 column. when using pivok query below, come out result below, senditemdatetime1 not group by... problem?

 select amount_usd, paytype, senditemdatetime1 tblmobile_requestorderlog   status = 1 , sendstatus = 1 , enable = 1   , senditemdatetime1  between '4/1/2015' , '4/30/2015'   order senditemdatetime1  

below result 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 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 post question here...

edit

this work me "where" clause:

select senditemdatetime1, coalesce([google], 0), coalesce([transfer], 0),        coalesce([paypal], 0),coalesce([molpay], 0) (select senditemdatetime1, paytype, amount_usd        tblmobile_requestorderlog       gameidn = 248 , status = 1 , sendstatus = 1 , enable = 1              , senditemdatetime1 between '4/1/2015 12:00:00 am'                                       , '4/30/2015 11:59:59' ) x  pivot (   sum(amount_usd)   [paytype] in ([google],[transfer],[paypal],[molpay]) ) piv; 

you can pivot data in first table below query - you'll need list payment type columns explicitly. i've assumed sum() aggregation applied:

select senditemdatetime1, [google],[transfer],[paypal],[molpay] mytable pivot (  sum(amount_usd)  [paytype] in ([google],[transfer],[paypal],[molpay]) ) piv; 

sqlfiddle here

edit, re filter

if filter predicate can applied final columns, where can applied after pivot. otherwise, if filtering needs done in unpivoted columns, can use cte, or derived table have done. here's example of prefilter in cte , postfilter where:

-- prefilter of non-pivot columns done in cte or derived table cte (   select [amount_usd], [paytype], [senditemdatetime1]   mytable   [amount_usd] > 2 ) select senditemdatetime1, coalesce([google], 0), coalesce([transfer], 0),        coalesce([paypal], 0),coalesce([molpay], 0) cte pivot (  sum(amount_usd)  [paytype] in ([google],[transfer],[paypal],[molpay]) ) piv -- post filter of pivot columns done on final projection senditemdatetime1 > '2015-01-01'; 

updated fiddle