Geek Answers Handbook

Sort data based on current system date

I am stuck in one request,

where I have data for 52 weeks, which I can organize according to sysdate, getting the week number and comparing with the week number of the data. now what I want to do is if the number of the current week is 10, I want to organize all the weeks in descending order, for example, depending on the date sysdate week10, week9, week8 ......... week11,

with this request

select "Weekly","Quarter","SALES","Monthly",week_number from fiscal_calendar where week_number <= TO_CHAR(TO_DATE(sysdate,'DD-mon-YYYY'),'iw') order by week_number desc;

I can sort the data up to week 1, but I want to continue the sequence, as, for example, on week 11 so I'm doing something wrong please advice

+4
source share
2 answers

If I understand correctly, you can try

 SELECT * FROM (SELECT "Weekly","Quarter","SALES","Monthly",week_number FROM fiscal_calendar WHERE week_number <= TO_NUMBER(TO_CHAR(SYSDATE,'IW')) ORDER BY week_number desc) t1 UNION ALL SELECT * FROM (SELECT "Weekly","Quarter","SALES","Monthly",week_number FROM fiscal_calendar WHERE week_number > TO_NUMBER(TO_CHAR(SYSDATE,'IW')) ORDER BY week_number) t2

Here's a simplified SQLFiddle example

+4
source

We can use case() (or decode() ) in the order by ... clause

 order by case when week_number = to_number(to_char(sysdate, 'WW')) then 1 when week_number < to_number(to_char(sysdate, 'WW')) then 2 when week_number > to_number(to_char(sysdate, 'WW')) then 3 end ASC , week_number DESC
0
source

More articles:


All Articles