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?
2