Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- d_Messages_Dim.message_type,
- product_dim.product_type,
- count(purchase_fact.purchase_fact_id)
- FROM
- product_dim INNER JOIN purchase_fact ON (product_dim.product_dim_id=purchase_fact.product_dim_id)
- INNER JOIN (
- select
- message_id
- ,subscriber_id
- ,product_id
- ,ticket_id
- ,name
- ,data
- ,message_type
- ,status
- ,client_params
- ,source
- ,created_at
- ,updated_at
- from messages_fact
- ) d_Messages_Dim ON (d_Messages_Dim.message_id=purchase_fact.message_id)
- WHERE
- (
- date(d_Messages_Dim.created_at) BETWEEN @prompt('Enter Start Date:','D',,Mono,Free,Persistent,,User:0) AND @prompt('Enter End Date:','D',,Mono,Free,Persistent,,User:1)
- AND
- d_Messages_Dim.message_type In ( 'SCHEDULED','INITAL OFFERS','OnDemand','VIEW_TRIGGER' )
- AND
- (
- (
- product_dim.product_type = 'Brew'
- AND
- purchase_fact.state = 'New'
- )
- OR
- (
- product_dim.product_type = 'Service'
- AND
- purchase_fact.state = 'authorized'
- )
- OR
- (
- product_dim.product_type = 'Ringtone'
- AND
- purchase_fact.product_id <> 38196
- AND
- purchase_fact.state In ( 'authorized','provisioned','unprovisioned' )
- )
- OR
- (
- product_dim.product_type = 'Wallpaper'
- AND
- purchase_fact.product_id <> 63213
- AND
- purchase_fact.state In ( 'authorized','provisioned','unprovisioned' )
- )
- )
- )
- GROUP BY
- d_Messages_Dim.message_type,
- product_dim.product_type
Add Comment
Please, Sign In to add comment