0

I have a table defined in Oracle11g schema like this

Txn_summ_dec
=================
id 
currentdate
resource_id
user_id
trans_id
eventdescptn

each resource has different event descriptions. I give a date range (of maximum 1 month or less) and resource_id and I want to get distinct count of all users for the given resource id, group by currentdate, eventdescptn

So I have the following query 

SELECT COUNT(DISTINCT(txn_summ_dec.user_id)) as dusers, currentDate, eventdescptn
FROM Txn_summ_dec
WHERE resource_id = 1
AND currentdate BETWEEN TO_DATE('2011-12-01', 'YYYY-MM-DD') 
                    AND TO_DATE('2011-12-31', 'YYYY-MM-DD')
GROUP BY currentdate, eventdescptn

and it gives me rightly the result below

dusers   currentdate            eventdescptn 
182      12/01/2011 00:00:00    Save 
33       12/04/2011 00:00:00    Save 
98       12/01/2011 00:00:00    Read
22       12/30/2011 00:00:00    Write

I want result in the following format:
From the query with the given date range is suppose 5th to 5th of a month (or less) I want results for all dates in the range for all eventdescptn of a resource. If there is no result for a particular date in the range, for a particular event descptn then it should still have that record in the resultset with a 'dusers' value = 0
so if a resource has 3 different eventdescptns (Save, Read, Write) and the date range is 5th to 30th of a month then there should be a total of 26X3 = 78 records in the resultset..
How do I write a query for that? Also I will need to convert it to hibernate later.. but Sql to start with is fine Thanks in advance

Clockwork-Muse
  • 12,806
  • 6
  • 31
  • 45
pri_dev
  • 11,315
  • 15
  • 70
  • 122
  • Labelling a stored `timestamp` column 'currentdate' is the _wrong_ thing to do - it should be labelled something like `event_occurred` (it's certainly not 'current' anymore). You're going to want to create an `event` table that holds descriptions, and only store ids. You're going to want to group by the **DAY** of the timestamp, not on something with (at minimum) second accuraccy. – Clockwork-Muse Jan 26 '12 at 01:10
  • sure, changing the name of the column is fine with me, it stores only date no time info..Also are you suggesting to use a temporary table event to get the result? if so how to do it? – pri_dev Jan 26 '12 at 01:47
  • No, not a temporary table, create a permanent `event` table, and only store ids in this summary table. Also, are you _sure_ that your `currentdate` field is type date - you're including the time in your results - and you may want the time for logging purposes anyways. Also, the reccommendation for range comparisons is minimum inclusive, maximum exclusive; so `AND currentDate >= TO_DATE('2011-12-01', 'YYYY-MM-DD') AND currentDate < TO_DATE('2011-12-01', 'YYYY-MM-DD') + INTERVAL 1 MONTH` (guessing at oracle syntax here). – Clockwork-Muse Jan 26 '12 at 16:34

2 Answers2

0

Check the accepted answer here: generate days from date range

If I understand you correctly you don't necessarily have an event for every date in the range in your log. That answer gives you a way to materialize a list of dates in the range. If you can modify it to include 1 of each of those dates per event, you would just have to join back to the results you have already aggregated here and set the null dUsers to zero.

Community
  • 1
  • 1
Brian Mieras
  • 121
  • 1
  • 7
0

I haven't tried this, but I wonder if you could you do:

 WITH the_query AS (
      ... your query here ...
 )
 SELECT dusers, currentdate, eventdescptn 
   FROM the_query
  WHERE 0 != ( SELECT COUNT(*) FROM the_query )
 UNION
 SELECT 0, NULL, NULL
   FROM the_query
  WHERE 0 = ( SELECT COUNT(*) FROM the_query )
eaolson
  • 14,717
  • 7
  • 43
  • 58
  • Given that the OP wants data for all dates (in the range), no (because you're giving a `null` date). I'm fairly confident that the second half of the statement isn't going to return anything unless the original query returns **0** results. Also, if it _did_ generate the 'missing' dates, you haven't given it any termination conditions - 'infinite' results. – Clockwork-Muse Jan 26 '12 at 16:38