Solved

SQL - Execute Query based on Date

Posted on 2007-04-06
5
271 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
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 ...
0
 
LVL 42

Expert Comment

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

Author Comment

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

Accepted Solution

by:
dqmq earned 500 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()
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
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.  
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 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