### Zodiac sign evaluation

F

Function to get zodiac sign for selected date.

DECLARE
in_date     CONSTANT DATE := TRUNC(SYSDATE);
curr_sign   VARCHAR2(30);
--
FUNCTION get_zodiac (
in_date DATE
)
RETURN VARCHAR2 AS
mmdd CONSTANT CHAR(4) := TO_CHAR(in_date, 'MMDD');
BEGIN
RETURN CASE
WHEN mmdd BETWEEN '0321' AND '0420' THEN 'ARIES'        -- beran
WHEN mmdd BETWEEN '0421' AND '0521' THEN 'TAURUS'       -- byk
WHEN mmdd BETWEEN '0522' AND '0621' THEN 'GEMINI'       -- blizenci
WHEN mmdd BETWEEN '0622' AND '0722' THEN 'CANCER'       -- rak
WHEN mmdd BETWEEN '0723' AND '0823' THEN 'LEO'          -- lev
WHEN mmdd BETWEEN '0824' AND '0923' THEN 'VIRGO'        -- panna
WHEN mmdd BETWEEN '0924' AND '1023' THEN 'LIBRA'        -- vahy
WHEN mmdd BETWEEN '1024' AND '1122' THEN 'SCORPIO'      -- stir
WHEN mmdd BETWEEN '1123' AND '1221' THEN 'SAGITTARIUS'  -- strelec
WHEN mmdd BETWEEN '1222' AND '1231' THEN 'CAPRICORN'    -- kozoroh
WHEN mmdd BETWEEN '0101' AND '0120' THEN 'CAPRICORN'    -- kozoroh
WHEN mmdd BETWEEN '0121' AND '0219' THEN 'AQUARIUS'     -- vodnar
WHEN mmdd BETWEEN '0220' AND '0320' THEN 'PISCES'       -- ryby
END;
END;
BEGIN
-- show all days with same zodiac sign from selected date
FOR c IN (
SELECT TRUNC(SYSDATE) + LEVEL - 1 AS date_
FROM DUAL
CONNECT BY LEVEL <= 31
) LOOP
IF curr_sign IS NULL THEN
curr_sign := get_zodiac(c.date_);
END IF;
--
IF curr_sign = get_zodiac(c.date_) THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(c.date_, 'YYYY-MM-DD') || ' ' || get_zodiac(c.date_));
END IF;
END LOOP;
END;
/

You can also create compatiblitity matrix bit using BITAND function:

WITH z (bit, name, from_mmdd, to_mmdd, partners) AS (
SELECT 1,    'ARIES',       '0321', '0420', 16 + 256            FROM DUAL UNION ALL  -- beran
SELECT 2,    'TAURUS',      '0421', '0521', 32 + 512 + 8 + 2048 FROM DUAL UNION ALL  -- byk
SELECT 4,    'GEMINI',      '0522', '0621', 64 + 1024           FROM DUAL UNION ALL  -- blizenci
SELECT 8,    'CANCER',      '0622', '0722', 128 + 2048          FROM DUAL UNION ALL  -- rak
SELECT 16,   'LEO',         '0723', '0823', 1 + 256             FROM DUAL UNION ALL  -- lev
SELECT 32,   'VIRGO',       '0824', '0923', 2 + 512             FROM DUAL UNION ALL  -- panna
SELECT 64,   'LIBRA',       '0924', '1023', 4 + 1024            FROM DUAL UNION ALL  -- vahy
SELECT 128,  'SCORPIO',     '1024', '1122', 8 + 2048            FROM DUAL UNION ALL  -- stir
SELECT 256,  'SAGITTARIUS', '1123', '1221', 1 + 16              FROM DUAL UNION ALL  -- strelec
SELECT 512,  'CAPRICORN',   '1222', '1231', 2 + 128             FROM DUAL UNION ALL  -- kozoroh
SELECT 512,  'CAPRICORN',   '0101', '0120', 2 + 128             FROM DUAL UNION ALL  -- kozoroh
SELECT 1024, 'AQUARIUS',    '0121', '0219', 4 + 64              FROM DUAL UNION ALL  -- vodnar
SELECT 2048, 'PISCES',      '0220', '0320', 8 + 128             FROM DUAL            -- ryby
)
SELECT
z.name,
MAX(CASE BITAND(z.partners, 1)    WHEN 0 THEN NULL ELSE 'Y' END) AS aries,
MAX(CASE BITAND(z.partners, 2)    WHEN 0 THEN NULL ELSE 'Y' END) AS taurus,
MAX(CASE BITAND(z.partners, 4)    WHEN 0 THEN NULL ELSE 'Y' END) AS gemini,
MAX(CASE BITAND(z.partners, 8)    WHEN 0 THEN NULL ELSE 'Y' END) AS cancer,
MAX(CASE BITAND(z.partners, 16)   WHEN 0 THEN NULL ELSE 'Y' END) AS leo,
MAX(CASE BITAND(z.partners, 32)   WHEN 0 THEN NULL ELSE 'Y' END) AS virgo,
MAX(CASE BITAND(z.partners, 64)   WHEN 0 THEN NULL ELSE 'Y' END) AS libra,
MAX(CASE BITAND(z.partners, 128)  WHEN 0 THEN NULL ELSE 'Y' END) AS scorpio,
MAX(CASE BITAND(z.partners, 256)  WHEN 0 THEN NULL ELSE 'Y' END) AS sagittaurius,
MAX(CASE BITAND(z.partners, 512)  WHEN 0 THEN NULL ELSE 'Y' END) AS capricorn,
MAX(CASE BITAND(z.partners, 1024) WHEN 0 THEN NULL ELSE 'Y' END) AS aquarius,
MAX(CASE BITAND(z.partners, 2048) WHEN 0 THEN NULL ELSE 'Y' END) AS pisces
FROM z
GROUP BY z.name, z.bit
ORDER BY z.bit;