Solved

SQL - Execute Query based on Date

Posted on 2007-04-06
5
286 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 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

Independent Software Vendors: 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

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

626 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