Hi All,
Need help on my below query.In this query we are checking what are the files we received from which vendor,brand,file type(XML),day_of_week and some average of file count.But while doing it my query does not give proper sorting format.
Need help on my below query.In this query we are checking what are the files we received from which vendor,brand,file type(XML),day_of_week and some average of file count.But while doing it my query does not give proper sorting format.
select VENDOR, BRAND, FILE_TYPE, day_number, day_of_week_avg, average_since, total_file_count, total_days, average_per_day,
count(fd.file_id) as current_file_count, abs((count(fd.file_id)-average_per_day)/average_per_day) as percent_difference
from
(select
vendor_cd as vendor,
brand_cd as brand,
file_type_cd as file_type,
count(*) as total_File_Count,
count(distinct trunc(rec_insert_ts)) as total_days,
round(count(*)/count(distinct trunc(rec_insert_ts)),2) as average_per_day,
to_char(rec_insert_ts,'DAY') Day_OF_WEEK_avg,
to_char(REC_INSERT_TS,'D') as day_number,
to_char(trunc(sysdate)-60,'DD-MON-YY') as average_since
from file_detail
where rec_insert_ts>sysdate-60 and file_type_cd not like '%CDI%' and file_type_cd = ::FILE_TYPE
group by vendor_cd, brand_cd, file_type_cd,
to_char(rec_insert_ts,'DAY'), to_char(REC_INSERT_TS,'D'),
trunc(sysdate)-60
) f
left outer join file_detail fd on fd.vendor_cd = f.vendor and fd.brand_cd = f.brand and fd.file_type_cd = f.file_type
and trunc(fd.rec_insert_ts)>trunc(sysdate)-7
and file_type_cd not like '%CDI%'
and to_char(fd.rec_insert_ts,'DAY') = day_of_week_avg
and file_type_CD = ::FILE_TYPE
group by VENDOR, BRAND, FILE_TYPE, total_file_count,
total_days, average_per_day, day_number, day_of_week_avg, average_since, to_char(fd.rec_insert_ts,'DAY'),trunc(fd.rec_insert_ts)
order by VENDOR, BRAND, FILE_TYPE, trunc(fd.rec_insert_ts);
Here the data will pick from last thursday to till today so wednesday will 1 so I need the data should come in desired order format:
actual day of week
|
today's day of week
|
desired order
|
formula
| |
Sunday
|
1
|
3
|
5
| |
Monday
|
2
|
3
|
6
| |
Tuesday
|
3
|
3
|
7
| |
Wednesday
|
4
|
3
|
1
| |
Thursday
|
5
|
3
|
2
| |
Friday
|
6
|
3
|
3
| |
Saturday
|
7
|
3
|
4
|
