Consider this query:

WITH date_range AS (
SELECT 
    TO_DATE('&Start_Date', 'MM/DD/YYYY') AS start_date,
    TO_DATE('&End_Date', 'MM/DD/YYYY') AS end_date
FROM dual),cross_join AS (
SELECT DISTINCT mr.GUID, mr.FILE_DATE, mr.INSERT_DATE
FROM foo.IMPORTANT_FILE_LOG mr
WHERE mr.FILE_DATE BETWEEN (SELECT start_date FROM date_range) AND (SELECT end_date FROM date_range)),lag_time_calc AS (
SELECT 
    cj.GUID,
    cj.FILE_DATE,
    COUNT(*) AS FILE_COUNT,
    SUM(TO_DATE(cj.INSERT_DATE, 'MM/DD/YYYY') - TO_DATE(cj.FILE_DATE, 'MM/DD/YYYY')) AS LAG_TIME_SUM
FROM 
    cross_join cj
GROUP BY 
    cj.GUID, cj.FILE_DATE) SELECT 
CASE lj.GUID
    WHEN '403B4FA1D3B842EEA8BF74CA88CABE8E' THEN 'foo'
    WHEN '1DFC1005E5364737B3AFD4881BD0E826' THEN 'fooey'
    WHEN 'A4F064312736428186B29AE039E92E72' THEN 'fooeytwoy'
    WHEN '6A45F5FEB9F449E9B851766B1078B566' THEN 'foomanchu'
    WHEN 'CEEA7FFA1FB44A40AE94E656B353E617' THEN 'foomanchewy'
    WHEN '7F720724D9E74DEE88A94A0238FFA848' THEN 'fooman'
    WHEN 'DC8421BA4A6E4341A6937634C8964505' THEN 'foom'
    WHEN '22388EFFCF4E4E0A91BB5478455EBF6D' THEN 'foomy'
    WHEN 'E02F2425C49C84E07B39C6FCD201196C' THEN 'fooo'
    WHEN 'F57A45D4A59844C1BAB023C8D5A1E02E' THEN 'foodo'
    WHEN '8E4EF0736A9D42ED95BD6D1832DDEC76' THEN 'foodoo'
    WHEN '867C950059AE46EDA0665F6363ADE4CB' THEN 'foowho'
    WHEN 'CE19D0B47AE94604B53572322E8937E7' THEN 'foogoo'
    WHEN '359A215F933A4ABA83AA269DD782777E' THEN 'fooshoe'
    WHEN '55ADCD90DE664BD280B059F40586E86A' THEN 'foomu'
    WHEN '3F859E3E391E4758BB9F73C6147A683E' THEN 'foolou'
    WHEN '3D731F587C44453BB619B881E5C81480' THEN 'foopoo'
    WHEN 'B997CF6A08EF46C48A7C3A302E695466' THEN 'foou'
    WHEN '480A1FC22C8B18ECE05400C0DD22F37A' THEN 'fooyou'
    WHEN '7BD3D20C403746989BCCDD703BC1FCC4' THEN 'foonew'
    WHEN 'C6C74E872363479BAFE48A9DC3317560' THEN 'fooroo'
    ELSE 'UNKNOWN'
END AS CUSTOMER,TO_CHAR(lj.FILE_DATE, 'MM/DD') AS FILE_DATE,
lj.FILE_COUNT AS TOTAL_FILE_CNT,
TO_CHAR(lj.LAG_TIME_SUM / lj.FILE_COUNT, 'FM999.999') AS LAG_TIME FROM 
lag_time_calc lj ORDER BY 
CUSTOMER ASC, FILE_DATE ASC

FILE_DATE has multiple rows in the source table, with various INSERT_DATEs that differ by anywhere from 1-10 days. I want to get an average of the difference between INSERT_DATE and FILE_DATE.

I’ve done a few of the calculations by hand and the values come out to 1.22, 3.33, etc. But all the results for LAG_TIME are 1.5, 3.5, etc. No matter what I try, I cannot get Oracle to return LAG_TIME in anything except intervals of 0.5.

I’ve tried everything that I know: change to numeric data type, performing the calculations in the body instead of the declaration and vice versa, casting as decimal type, to_char… I’ve even tried multiplying the calculation by 1.0 to ensure it’s the right data type and I cannot get Oracle to express the quotient in anything except 0.5 intervals.

I’ve spent the better half of the day trying to figure this out and I’m starting to get frustrated- can anyone help?