SQL - Execute Query based on Date

Posted on 2007-04-06
Medium Priority
Last Modified: 2012-05-05
This is a kicker!!!  
Have a active table that has additions often::
 Date                 Time
04/06/2007          9:30am
04/06/2007           10:01am
03/01/2007            5:00pm
I run a query only if the Max date iin the table = to today's date.   I also run a count on the max days..
so in case I would return a 2.  Works great.
NOW the kicker...
How do you run the query again and only return the changes for that date?  
New data in table is:
Date                 Time
04/06/2007          11:00am
04/06/2007           12:00pm
04/06/2007          9:30am
04/06/2007           10:01am
03/01/2007            5:00pm

So....I need to be able to run a query if the count of the same day goes beyond a certain count and then only return what was not already sent out.  In this case there would only be 2 new entries sent out.
declare @CurrentDate Nvarchar(11), @TableEntryDate Nvarchar(11), @MaxDateCount int

set @CurrentDate = (CONVERT (Nvarchar(11), getdate() , 100 ))
set @TableEntryDate = (SELECT max(CONVERT (nvarchar(11), Date, 100))FROM MYDB.dbo.Table)
set @MaxDateCount = (select count(*)FROM MYDB.dbo.Table where [Date] = @CurrentDate)

At this point I use either
IF @TableEntryDate = @CurrentDate  or
if @MaxDateCount = 3
I am looking at setting 2 more varibles..a minTime and MaxTime and maiking this work....
This is where I need the assitance.....
Question by:TechMonster
  • 2
  • 2
LVL 50

Expert Comment

ID: 18867581
no doesn't make sense...

please explain you scenario in business english terms ...

what are you trying to achieve..?
where would you store/indicate a row had already been processed?

use style 112 YYYYMMDD for your dates it the most unambiguous and flexible ...
LVL 42

Expert Comment

ID: 18867593
It seems to me that somehow you need to pass in an indicator of the max date/time already processed.  I don't see how you can ascertain it just from looking at the table.

Also, I really doubt this will work because it compares [Date] includes time, but @CurrentDate does not:

set @MaxDateCount = (select count(*)FROM MYDB.dbo.Table where [Date] = @CurrentDate)

You need to apply the same CONVERT expression to [Date] that you applied to getdate().  (like you did for @TableEntryDate).

Author Comment

ID: 18867624
OK, I have a scheduled job that runs a SQL Query every 5min.
The query sends out a report if there is a entry which equals the current day.
The problem is that it keeps adding all the entries from that date.

What I am trying to accomplish is that it will only send out the report with the NEW entries from that day.

I was thinking of somehow using capturing a MaxTime and MinTime to tell the query when to start and when to stop.
LVL 42

Accepted Solution

dqmq earned 2000 total points
ID: 18868264
Well, yes, you need  a mechanism that keeps track of what has already been printed.  There are countless ways to approach that, but let me suggest a simple one.  Create a table to keep track of print requests. Ultimately, a PrintRequest may have other attributes, but for this question you only need this:

Create Table PrintRequest (PrintRequestID INT,  PrintRequestTime DATETIME)

Then selecting unprinted entries goes like this

DECLARE @CurrentTime DateTime
SET @CurrentTime = GETDATE()
 WHERE [Date] >  (SELECT MAX([PrintRequestTime]) From PrintRequest)
      AND [Date] <= @CurrentTime
INSERT INTO PrintRequest
   SELECT MAX(PrintRequestID) + 1, @CurrentTime FROM PrintRequest

<Produce Report or whatever here>




Author Comment

ID: 18868476
dqmq: - I think your solution is really close to what I had in mind.
I was thinking of creating a second table to put the entries from the current day and then query off of that table for the time.  

I haven't tried it yet but will do it early next week.  

Thanks for your input.  

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question