I do a bit of googling and I don't really find much. Then I discovered NEXT_DAY in an Oracle Doc. So, NEXT_DAY takes a DATE argument and a CHAR argument. The CHAR argument we'll call DayOfWeek. It is 'SUN'...'SATURDAY', meaning you use either the full name of the day of the week or the standard abbreviation and pass a date in and your results are the date of the next DayOfWeek that you supplied. For example:
select next_day('01-JAN-2013', 'SUNDAY') mydate from dual;
Gets:
MYDATE
1/6/2013
So, the first sunday after the first was the 6th. Ok. Now for the tricky part:
select next_day('06-JAN-2013', 'SUNDAY') mydate from dual;
Gets:
MYDATE
1/13/2013
At first, this threw me for a loop, but then it started to make more sense. It is called NEXT_DAY after all, so it would not return the same date. It would return the date of the next DayOfWeek. So, the way to short circuit that if you want to check the closest day is date - 1. So,
select next_day('05-JAN-2013', 'SUNDAY') mydate from dual;
Gets:
MYDATE
1/6/2013
So, now we come to the part where I slay the dragon, save the day and get the girl. Or keep struggling to restore the Thieve's Guild to its former glory. Anyway, I created a table to store a years worth of 3 day weekends, so to speak.
CREATE TABLE WEEKEND_DATES (
WEEKEND_DATE DATE
);
I think looped through a year's worth of days (one for each week) and inserted the weekend dates for each week. Like so:
DECLARE
l_i NUMBER;
mydate DATE;
BEGIN
l_i := 0;
LOOP
SELECT NEXT_DAY (TRUNC(SYSDATE) - L_I, 'FRIDAY') INTO MYDATE FROM DUAL;
INSERT INTO WEEKEND_DATES
VALUES (MYDATE);
SELECT NEXT_DAY (TRUNC(SYSDATE) - L_I, 'SATURDAY') INTO MYDATE FROM DUAL;
INSERT INTO WEEKEND_DATES
VALUES (MYDATE);
SELECT NEXT_DAY (TRUNC(SYSDATE) - L_I, 'SUNDAY') INTO MYDATE FROM DUAL;
INSERT INTO WEEKEND_DATES
VALUES (MYDATE);
L_I := L_I + 7;
EXIT WHEN L_I >= 373;
END LOOP;
COMMIT;
END;
So, now we have the table WEEKEND_DATES filled with the dates for all of the Fridays, Saturdays and Sundays over the last year. Now we can join that to the transaction table or use an In Clause and a sub-select or an exists with a sub-select to get our desired results. Here is a final query:
SELECT
CASE timestamp
WHEN NEXT_DAY (timestamp - 1, 'FRIDAY') THEN 'FRIDAY'
WHEN NEXT_DAY (timestamp - 1, 'SATURDAY') THEN 'SATURDAY'
WHEN NEXT_DAY (timestamp - 1, 'SUNDAY') THEN 'SUNDAY'
ELSE 'NOT A WEEKEND AND SHOULD BE IMPOSSIBRU!!!'
END
DAY_OF_WEEK,
T.*
FROM TRANSACTIONS T
WHERE TRUNC(timestamp) IN (
SELECT WEEKEND_DATE FROM WEEKEND_DATES);
So, you may be looking at this and going why are you TRUNCing everything? Well, the NEXT_DAY function retains the time portion of the date supplied. So, the first time though this process, the only results received were the ones that matched on the full time as well as date. When you TRUNC a date, it sets the time portion to midnight. This allows you to compare based on any time during the day. TRUNC does have other options so google that if you are interested and I have touched on it in past posts. Also, know that the IN clause might not be the best performing of the options available, but because this was a one-time ad hoc query and results returned quickly enough, I did not dwell on performance. Now if you sick fucks are wondering about the CASE statement above, I simply had the common decency to provide the knowledge of which DayOfWeek the given record actually fell on. I'm cool like that. Now, since we are limiting to days that are Friday, Saturday or Sunday, no other days of the week should be possible. If the results had any IMPOSSIBRU in it, then I rawdogged the pooch like a motherfucker somewhere along the line. I'll need to circle the wagons, go back to the beginning and figure out the failure point, after I kick the pooch down the stairs (just in case).
Alright, I think that is it for today. Class dismissed.
Fuckers.
No comments:
Post a Comment