December 23, 2008 at 8:28 am
I am trying to write a store procedure that will extract data from the 1st of the month up-to current date within that month - ending on the last day of the month. For instances today is the 23rd - I need to extract data from a table where the date starting from the 1st through the 23rd, however I must extract ONE DAY at a time, therefore the query must loop through the dates.
I have a very complexed query already in place that calls on several functions - I can only process 1 day at a time.
How do I reloop through a query extracting data from the 1st up-to the current date one day at a time?
December 23, 2008 at 8:45 am
Did you have a question that you wanted to ask?
December 23, 2008 at 10:22 am
Why must you return only 1 row at a time? Couldn't you return the data as a set and let the business layer or UI handle the looping? IMO that would be more effecient, as you would have one call to the DB and the rest done in memory. If you post some DDL, test data, and expected/desired results as mentioned in the link in my signature then you will likely get some solutions proposed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 23, 2008 at 10:59 am
Why do you have to loop? What is the requirement that states you have to process a single day at a time?
FWIW, you could build a stored procedure that accepts as input the date you want to search, and query for everything on that one date. Example:
CREATE PROCEDURE dbo.MySingleDaySearch
@inputDate datetime
AS
DECLARE @startDate datetime;
DECLARE @endDate datetime;
SET @startDate = dateadd(day, datediff(day, 0, @inputDate), 0); -- remove time portion
SET @endDate = dateadd(day, 1, @startDate); -- set the end date to tomorrow
SELECT ...
FROM dbo.MyTable t
WHERE t.MyDateColumn >= @startDate
AND t.MyDateColumn < @endDate;
GO
But, I gotta say that running this for a full month by 'looping' on each date is not the right way to solve the problem. If you posted the problem you are trying to solve - it might be easier for us to recommend a better solution than looping.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply