Category Archives: Tipps and Tricks

Calculating the number of weekdays between two dates

14 Thursday Jul 2011

Posted by in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Tags

, ,

How can we calculate the number of business days (Monday – Friday) between two given dates….

This is a question often asked in the SQL community.

Two solutions for this problem:

Solution 1:

Calculate the number of days between two date values and subtract the number of Saturdays and Sundays in between.

WITH DATA AS (SELECT TO_DATE('05.09.2011','dd.mm.yyyy') date_from                    ,TO_DATE('31.10.2011','dd.mm.yyyy') date_to                FROM dual)
SELECT date_to - date_from + 1
     - (((NEXT_DAY(date_to-7,'Saturday') - NEXT_DAY(date_from,'Saturday'))/7) + 1)
     - (((NEXT_DAY(date_to-7,'Sunday')   - NEXT_DAY(date_from,'Sunday'))/7) + 1)
  FROM data
/

Solution 2:

Count the number of days that are neither Saturdays nor Sundays between two date values using a recursive query.

SELECT COUNT(DECODE(TO_CHAR(TO_DATE('05.09.2011','dd.mm.yyyy') + (ROWNUM - 1),'DY'),'SAT',NULL,'SUN',NULL,'1'))
   FROM dual
 CONNECT BY ROWNUM <= (TO_DATE('31.10.2011','dd.mm.yyyy') - TO_DATE('05.09.2011','dd.mm.yyyy') + 1) 
/ 

Rounding monetary values

26 Friday Feb 2010

Posted by in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Tags

, ,

A question that occurs from time to time is how to round a (monetary) amount to e.g. 5, 25, 50 cents. Basically this can be done easily using the ROUND function.

ROUNDING to 5 cents = ROUND(amount*20)/20
amount   multiplied by 20   rounded to integer   divided by 20
------   ----------------   ------------------   -------------
  1.02              20.40                   20            1.00 
  1.03              20.60                   21            1.05 
  1.37              27.40                   27            1.35

the same principle works for:

  •  round to 25 cents = ROUND(amount*4)/4
  •  round to 50 cents = ROUND(amount*2)/2

basically the general formula is:

ROUND(amount*(100/rounding_base)) / (100/rounding_base)

Finding Gaps

15 Saturday Aug 2009

Posted by in ORACLE, SQL, Tipps and Tricks, Uncategorized

≈ Leave a comment

Tags

,

One of the things often asked for is how to find gaps in a list of numbers. So here’s a rather old suggestion:

WITH gaps (id) AS (SELECT  1 FROM dual UNION ALL
                   SELECT  3 FROM dual UNION ALL
                   SELECT  4 FROM dual UNION ALL
                   SELECT  7 FROM dual UNION ALL
                   SELECT 15 FROM dual)
    ,borders AS (SELECT MIN(id) mins
                       ,MAX(id) maxs
                   FROM gaps)
SELECT mins + (ROWNUM - 1) AS missing_value
  FROM borders
CONNECT BY mins + (ROWNUM - 1) <> maxs
MINUS
SELECT id
  FROM gaps
/