4 Replies Latest reply on Mar 3, 2014 7:19 PM by Dan Aspinall

    Custom SQL how do I convert single date to all dates?

    Dan Aspinall

      Hi folks.

       

      I'm trying to work out the best way to convert a query I have been given for a certain date, in to some custom SQL, so I can extract the same results for all dates.

       

      The query is as follows and is used to find "initial payers" of our subscriber model:

       

      select count(distinct SP.id) as "18-Nov-13"

      from gb_service_provider SP

      join bill_account A on A.id = SP.account_id

      join pay_transaction T on T.payer_id = A.payer_id

      where T.processed_time >= date '2013-11-18' 

      and t.processed_time < date '2013-11-19'

      and T.tender_type_id <> 2

      and T.transaction_type_id = 1

      and T.transaction_status_id = 2

      and ( select count(*) from pay_transaction T2 where  T2.processed_time < date '2013-11-19' and T2.payer_id = A.payer_id and T2.tender_type_id <> 2 and T2.transaction_type_id = 1 and T2.transaction_status_id = 2) =1;

       

      The part I'm struggling with is the subquery.

      This makes sure the results only bring back the first time a payment is made which matches the tender and transaction type and status.

      I need to bring back data for each day over a period of time, which I guess would require a GROUP BY, but I can't work out how I make sure that the subquery also works for a range of dates.

       

      I know this is more related to Oracle SQL but I've had great help here in the past so hoping someone can at least point me in the right direction.

       

      Dan.