Need help on the following scenario.
Consider the following dataset
transaction created_date closed_date
1 1-jan-2011 5-jan-2011
2 2-jan-2011 10-jan-2011
3 5-jan-2011 5-feb-2011
4 10-jan-2011 6-mar-2011
5 1-feb-2011 10-feb-2011
6 3-feb-2011 10-mar-2011
7 5-mar-2011 15-mar-2011
I want to get the distinct count of transactions created and closed in each month without using full outer join.
The resultant data set is to be as follows
month created_count closed_count
jan 4 2
feb 2 2
mar 1 3
To get this resultset i used the following full outer join query
sel x.mnth, x.created_count,y.closed_count
(sel cal_table.month as mnth, count(1) as created_count from table_a
join cal_table on table_a.created_date=cal_table.date
group by 1)x
Full outer join
(sel cal_table.month as mnth1, count(1) as closed_count from table_a
join cal_table on table_a.closed_date=cal_table.date
group by 1) y on x.mnth=y.mnth1
The reason i want this without the use of full outer joins is because the counts will be split across various other parameters like source, origin etc.. If i use full outer join i have to use all those parameters in both the subqueries and finally join both the subqueries on all those parameters.
Can anyone please suggest somethign here?
sel cal_table.month as mnth,
sum(case when extract(month from created_date = cal_table.month then 1 else 0 end),
sum(case when extract(month from closed_date = cal_table.month then 1 else 0 end),
group by 1