Solved

SQL - Execute Query based on Date

Posted on 2007-04-06
5
241 Views
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
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.
MY CODE:
****************************************************************************************************
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.....
0
Comment
Question by:TechMonster
  • 2
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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 ...
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
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).
0
 

Author Comment

by:TechMonster
Comment Utility
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.
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
Comment Utility
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()
BEGIN TRANSACTION
SELECT * FROM YourTable
 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>

COMMIT

 
 


0
 

Author Comment

by:TechMonster
Comment Utility
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.  
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now