dates and times in a subquery with a group by
360275Dec 20 2012 — edited Dec 20 2012I have some data that I need to group to the Month, Day, Year, Hour and minute in a subquery. Then I need to summarize it to the Month and year in the parent query.
If I try to group by the field itself, it is not taking it down to hour and minutes - just the day, so I am losing records.
if I do a TO_char(visitdate, 'DD-MON-YY HH:MI AM') in the subquery, then the main query no longer sees it as a date, so cannot do my TO_CHAR(VISITDATE,'MON-YYYY') in the parent. I could parse out the pieces using string manipulation, but that seems rather silly.
Is there a way to keep as a date in my sub query and then convert to a string?
it looks a little like this, with some other fields that I have to max, sum ...
visit provider person visitdate
1 2 1 12/20/2012 10:30
2 2 2 12/20/2012 10:30
3 2 5 12/20/2012 11:30
4 3 3 12/21/2012 11:30
5 3 4 12/21/2012 11:30
I need to boil this down to
provider visitdate
2 12/20/2012 10:30
3 12/21/2012 11:30
2 12/20/2012 11:30
Then I use that in a subquery where I use just the month and year
TO_CHAR(VISITDATE,'MON-YYYY') AS APPT_MO_YR
right now if I do a group by visitdate on the subquery it returns
provider visitdate
2 12/20/2012
3 12/21/2012
even if I do a group by to_date(visitdate, 'DD-MON-YY HH:MI AM')
it is still returning :
provider visitdate
2 12/20/2012
3 12/21/2012