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;