Hej :)
Staram sie zrobić jedno zadanie z sql.ru (tak, wiem ;\ )
Oto jego treść:
The PFAS Museum Director elections are held in leap years only, on the first Tuesday after the first Monday in April.
For each date from the Battles table, determine the closest election date following it.
Output: battle name, date of battle, election date. Note: output format for dates should be "yyyy-mm-dd".
Dodatkowo opis tych relacji:
Short database description "Ships"
The database of naval ships that took part in World War II is under consideration. The database consists of the following relations:
Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)
Battles(name, date)
Outcomes(ship, battle, result)
Ships in classes all have the same general design. A class is normally assigned either the name of the first ship built according to the corresponding design, or a name that is different from any ship name in the database. The ship whose name is assigned to a class is called a lead ship.
The Classes relation includes the name of the class, type (can be either bb for a battle ship, or bc for a battle cruiser), country the ship was built in, the number of main guns, gun caliber (bore diameter in inches), and displacement (weight in tons). The Ships relation holds information about the ship name, the name of its corresponding class, and the year the ship was launched. The Battles relation contains names and dates of battles the ships participated in, and the Outcomes relation - the battle result for a given ship (may be sunk, damaged, or OK, the last value meaning the ship survived the battle unharmed).
Notes: 1) The Outcomes relation may contain ships not present in the Ships relation. 2) A ship sunk can’t participate in later battles. 3) For historical reasons, lead ships are referred to as head ships in many exercises.4) A ship found in the Outcomes table but not in the Ships table is still considered in the database. This is true even if it is sunk.
i moje dotychczasowe rozwiązanie :
SELECT name,
date::date,
MIN(_date)::date
FROM (
SELECT name,
date,
_year,
generate_series(
make_date(
_year,
4,
(
CASE
WHEN EXTRACT(isodow FROM make_date(_year, 4, 1)) = 1 THEN 2
WHEN EXTRACT(isodow FROM make_date(_year, 4, 1)) = 2 THEN 8
WHEN EXTRACT(isodow FROM make_date(_year, 4, 1)) = 3 THEN 7
WHEN EXTRACT(isodow FROM make_date(_year, 4, 1)) = 4 THEN 6
WHEN EXTRACT(isodow FROM make_date(_year, 4, 1)) = 5 THEN 5
WHEN EXTRACT(isodow FROM make_date(_year, 4, 1)) = 6 THEN 4
WHEN EXTRACT(isodow FROM make_date(_year, 4, 1)) = 7 THEN 3
END
)::integer
),
make_date(_year, 4, 30),
'7 days'
) as _date
FROM (
SELECT name,
date,
EXTRACT (
YEAR
FROM generate_series(date, date + '4 years', '1 year')
)::integer _year
FROM battles
) t1
WHERE mod(_year, 400) = 0
OR (
mod(_year, 4) = 0
AND mod(_year, 100) != 0
)
) as t2
WHERE date <= _date
GROUP BY name,
date
ORDER by name
Niestety, utknąłem. Pierwszą serie testów przechodzi, natomiast później sprawdzane są testy do których nie mam wglądu wiec ciężko mi sie zahaczyć gdzie może być problem.
Ktoś może ma jakiś pomysł?
Pozdrawiam