DeveloperBarn

Hi,

Can someone guide me in the right direction.

I have table called Registration_Deadline design is like this

Id
Name
Email
DateTime
Cost

then there is another table called Registration

ID
Reg_Id
Name
Address
AmountPaid

What I have to do is if the date in Registration_deadline is one day older then todays date I want to get all the records for that registration from the registration table.

I want to create a excel sheet and then email that excel sheet to a person at midnight

Can someone tell me where I should start.

Todd

Are there keys linking the two tables together? If so, which ones?

__________________
Being educated does not make you intelligent.

Hello,

Id is the primary key in Registration_deadline table and Reg_Id is the foreign key in the Registration table.

Might I suggest the following design...

Note: I'm not sure why you have a "Name" column in your RegDeadline table, when one already exists in your Registration table. I am also not sure whey you have an "Email" column in your RegDeadline table, as that column would be more appropriately associated with the entity stored in the Registration table. Not sure why you have a "Cost" column in your RegDeadline table, that would be more appropriately associated with the entity in your Registration table. The "AmountPaid" column would be more appropriate for the RegDeadline table.

Below is a best practice design, which adhere's to proper naming conventions and takes into consideration my notes above.

RegDeadline Table
-----------------
RegDeadlineID (PK)
RegistrationID (FK)
DeadlineDate
AmountPaid
InActive


Registration Table
-----------------
RegistrationID (PK)
Name
Address
Email
Cost
InActive

Based on the above database design structure you could execute the following query to get your results.

Memnoch,

thanks for your reply.

What I am trying to do is make it automated so no manual interaction.

IT should happen on the server at 1am the next day

SO the best route is to create a sql report which will include the query or stored procedure and then schedule it.

is this a right way to go and solve this issue

You could do one of the following:

1) Create the sql as a stored procedure, then write a simple console application that connects to the database and executes the procedure. The setup the console app up as a scheduled task and schedule it to kickoff at 1:00 AM every day.

OR

2) Create a new SQL job and schedule it that way.

#2 is preferred as the least amount of steps possible reduces the risk of some chain in the process breaking.