CODEWITHSUNDEEP
sqloracle-database
user3544027
user3544027

Reputation: 41

Comparing Dates in PL/SQL (I'm a beginner, be gentle)

Relatively new to SQL, so still trying to get my head around a couple of concepts.

I have two tables, the first has a bunch of VARCHAR attributes, as well as a 'DAY' column which is in date format, and 'USAGE', a number.

The second table only has one column, 'HOLIDAY_DATE', also datatype date. As the name suggests, this is a bunch of dates corresponding to past and future holidays.

I'm trying to find 'USAGE' on days that are not holidays, by comparing 'DAY' in the first table to 'DATE' in the second. My select statement so far is:

SELECT DAY, USAGE
FROM FIRST_TABLE, HOLIDAYS
WHERE FIRST_TABLE.DAY, NOT LIKE HOLIDAYS.HOLIDAY_DATE
GROUP BY DAY, USAGE
ORDER BY DAY;

But this still seems to bring up ALL the days, including holidays. Not quite sure where to go from here.

Upvotes: 1

Views: 70

Answers (2)

Akash Thakare
Akash Thakare

Reputation: 22972

there may be other ways but you can use subquery concept

SELECT DAY, USAGE
FROM FIRST_TABLE
WHERE DAY NOT IN(Select HOLIDAY_DATE FROM HOLIDAYS)
GROUP BY DAY, USAGE
ORDER BY DAY;

One More thing I want to add here eventhough your query has logical issue but there is Syntaxt error also

WHERE FIRST_TABLE.DAY, NOT LIKE HOLIDAYS.HOLIDAY_DATE
                     ^

Upvotes: 1

Guntram Blohm
Guntram Blohm

Reputation: 9819

SELECT DAY, USAGE
FROM FIRST_TABLE
WHERE FIRST_TABLE.DAY NOT in (select HOLIDAY_DATE from HOLIDAYS)
GROUP BY DAY, USAGE
ORDER BY DAY

Upvotes: 1

Related Questions