Home > SQL Development > Check if a certain number of entries exist in table

Check if a certain number of entries exist in table

Hi Guys,

Sorry if this is a stupid question, I've been trying to figure out a way to achieve something and the more I think about it the more confused I get!!!

The scenario is that I work for a company that has 20 branches over three areas, each with a unique ID. At the end of each month the branch managers submit the total number of days that their employees have taken off sick in that month. I am trying to prepare a report for the company directors that shows the total amount of sickness throughout the company for any given month. This is simple enough, but the directors only want a months worth of figures to be available if each of the twenty stores have submitted their figures so that the results are not skewed.

I have a simple webform which allows the store managers to submit their figures for the month and I have a table which contains the month number (1 to 12), the year, the store code and the number of sick days.

My table is called store_check and has the following fields:

current_month - numeric

current_store - char(2)

current_year - char(4)

total_sickness - numeric

The stores are divided into three regions A, B and C and each store has a unique code eg. AA, AB, AC, AD, AE, AF, BA, BB, BC, BD etc..

Could anybody tell me how I can write a SQL statement to check to see if there is a row in the table for each of the 20 stores for month number 1 of year 2008?

Thanks in advance for any advice.

Guest

which database are you using? if SQL Server, you could use a stored proc that will check record count before running the query to get the data.

Was this answer helpful ? Yes No

Guest

Hi Mehere,

Thanks for the reply, unfortunately I'm using an Oracle 9i Database.

Was this answer helpful ? Yes No

Guest

you should be able to create a stored proc in oracle as well. have a look here. i've never used oracle, but i can't think of any other way to do what you need.

Was this answer helpful ? Yes No

Guest

Thanks for the link mehere, I'll have a go at creating a stored procedure to do this, I've never done it before.

Was this answer helpful ? Yes No

Guest

Haven't worked on oracle either, but think below should work:

Create a composite primary key using storeID, month and year. This will prevent duplicate entry for the combination and then get the count(*) for a particular month & year - if count equals 20 then all the store has entry for given month and year.

Was this answer helpful ? Yes No

Guest

Quote:

Originally Posted by dev77

This was my thought also; however remembering to change a hard-coded 20 isn't going to happen

If you have a table with the store id in it, you could do a LEFT JOIN on that table with your store_check table and then count the number of NULLs in, say, the total_sickness column:

SQL Code:

Original - SQL Code
select count() from <br> (select s., t.total_sickness<br> from store_table s<br> left join store_check t<br> on (s.store_id = t.current_store)<br>) where total_sickness is null

SELECT COUNT(*) FROM 
 (SELECT s.*, t.total_sickness
   FROM store_table s
   LEFT JOIN store_check t
     ON (s.store_id = t.current_store)
) WHERE total_sickness IS NULL

As for Stored Procedures in Oracle -- I have read, albeit in old articles, that the Microsoft Oracle Provider doesn't work and that you need the Oracle OLE Provider to return record sets from Oracle. Don't know, your mileage may vary. I do know that Coldfusion and Oracle stored procedures don't work. Oracle sprocs are not a problem for ASP.NET however -- you just have to work with a REF CURSOR.

Was this answer helpful ? Yes No

Guest

Answer it

Ask Question About SQL Development

Check if a certain number of entries exist in table

Programming

Related Pages