Подсчет сделок по временному интервалу

Question

У меня есть история транзакцийTRANSACTION_HISTORY"таблица с полем отметки времени

TRANS_ID-----SIGNATURE_TIMESTAMP-------SIGNATURE_USR---INTERNAL_STATUS

2148091------22/07/2015 15:35:06 ---------- RS1------------00

2148091------22/07/2015 15:36:08----------  RS1------------01

2148091------22/07/2015 15:46:07----------  RS1------------B1

2139220------22/07/2015 15:36:07----------  RS1------------00

2148089------22/07/2015 15:31:42----------  GC1------------00

Я пытаюсь получить ежедневное количество транзакций, сгруппированных по типу транзакции в разные временные интервалы по статусу по пользователю. Все транзакции, которые имеют time stamp <= 9 A.M.then 9 A.M. и так далее.

Status--------- 9:00 A.M.----------11:00 A.M.------3:00 P.M.---Grand Total

Pending (00) -----7-----------------9--------------21----------37

User1 ------------2-----------------3--------------13----------18

User2 ------------4-----------------2---------------6----------12

User3 ------------1-----------------4---------------2-----------7

Booked(01)  ------3-----------------4--------------12----------19

User1 ------------0-----------------1---------------7-----------8

User2 ------------2-----------------1---------------3-----------6

User3 ------------1-----------------2---------------2-----------5

Completed(B1)-----3-----------------4--------------12----------19

User1 ------------0-----------------1--------------7------------8

User2 ------------2-----------------1--------------3------------6

User3 ------------1-----------------2--------------2------------5

Вот код, который я написал до сих пор:

---

выберите t.description,

   case
     when ts.id in ('B1', 'B2', 'B3') then
      'Completed'
     else
      ts.description
   end as status_Desc,
   hist.signature_timestamp,
   usr.login_name as user_name,
   case
     when hist.signature_timestamp <=
          to_date('22/07/2015 09:00:00', 'dd/mm/yyyy hh24:mi:ss') then
      '9 AM'
     when hist.signature_timestamp <=
          to_date('22/07/2015 11:00:00', 'dd/mm/yyyy hh24:mi:ss') then
      '11 AM'
     when hist.signature_timestamp <=
          to_date('22/07/2015 15:00:00', 'dd/mm/yyyy hh24:mi:ss') then
      '3 PM'
     when hist.signature_timestamp <=
          to_date('22/07/2015 18:30:00', 'dd/mm/yyyy hh24:mi:ss') then
      '6:30 PM'
   end as time_Slot from k$transaction_header  h,
   k$transtypes          t,
   k$transaction_history hist,
   k$usr                 usr,
   k$TRANSACTION_STATUS  ts where h.trtype = t.trtype and hist.trans_id = h.id  and usr.id = hist.signature_usr and ts.id = hist.internal_status   and H.ACCOUNT_DTE >= sysdate - 1

Спасибо,

Юсеф

Answer 1

Хитро, но попробуйте это:

    with myCte
as
(
select Signature_Usr,
Case
 when CONVERT(Time,TimeofTransaction) < CONVERT(TIME,'1900-01-01 9:00:00.000') then Before9
 when CONVERT(Time,TimeofTransaction) < CONVERT(TIME,'1900-01-01 10:00:00.000') and 
  CONVERT(Time,TimeofTransaction) >= CONVERT(TIME,'1900-01-01 9:00:00.000') then After9Before10
 /* And So On */
 End as TimePeriord
 from myTable

)

select Signature_USR,COUNT(Signature_USR) as CountOfTrans,TimePeriod
 from myCte
 Group by Rollup(Signature_USR,TimePeriod)