Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL - Execute Query based on Date

Posted on 2007-04-06
5
Medium Priority
?
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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()
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

670 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