help with pulling values for multiple dates...

Question

help with pulling values for multiple dates...

quayludious

SSC Enthusiast

Points: 145
More actions
August 4, 2009 at 8:25 am

#72403

hi there,
This is probably stupid but I'm thinking there must be a 'better' way to do it.
I have a need to pull values from a table, in order to do some math on them, for the current month, current month -1, current month -2, current month -3 and current month -6
What I have at the moment is:
select ID,a,b,c,cMonth.eomdate,pm.a,pm2.b,pm3.c,pm6.c
from TableA cMonth where eomdate = fnmonthend(getdate())
inner join tablea pm
on cMonth.ID=pMonthID
and pm.eomdate=fnmonthend(dd,-1,eomdate)
inner join tablea pm2
on cMonth.ID=pMonthID
and pm.eomdate=fnmonthend(dd,-2,eomdate)
inner join tablea pm3
on cMonth.ID=pMonthID
and pm.eomdate=fnmonthend(dd,-3,eomdate)
inner join tablea pm6
on cMonth.ID=pMonthID
and pm.eomdate=fnmonthend(dd,-6,eomdate)
Is there a better way than using the inner joins?
thanks,
Michael

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

It looks like you're comparing dates in a function of sorts. That can be slow. If you can calculate those dates out for a query and store them in variables (which go in the query) that will be faster. Otherwise, there's not magic way to get data for those specific time periods.

quayludious SSC Enthusiast Points: 145 More actions · Answer 2

yeah, fnmonthend just makes sure that I get the specific month end regardless of the date I pass in... so if the data happened to be 5/17/2009 it would come out as 5/31/2009.

Ok, I was just curious more than not. the code works fine, its even very quick it just looks cludgy... to the point of if the user came back and said oh btw can you add 4 more different months I'd just throw something at them cludgy. 😉

thanks!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

quayludious (8/4/2009)
Is there a better way than using the inner joins?

If I'm reading your code example correctly, the answer is "yes, absolutely". Please post the code for the function and let's have a go at it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

quayludious SSC Enthusiast Points: 145 More actions · Answer 4

The function is just a piece of stock code that is used in the system to return the month end date regardless of the date passed in.

ALTER FUNCTION

[dbo].[fnsysMonthEnd]

(

@datDatedatetime

)

RETURNS datetime

AS

BEGIN

DECLARE

@dtAsOfDate datetime

SET @dtAsOfDate = @datDate

SET @dtAsOfDate = DATEADD(millisecond,-DATEPART(millisecond, @dtAsOfDate),@dtAsOfDate)

SET @dtAsOfDate = DATEADD(second,-DATEPART(second, @dtAsOfDate),@dtAsOfDate)

SET @dtAsOfDate = DATEADD(minute,-DATEPART(minute, @dtAsOfDate),@dtAsOfDate)

SET @dtAsOfDate = DATEADD(Hour,-DATEPART(Hour, @dtAsOfDate),@dtAsOfDate)

SET @dtAsOfDate = DATEADD(month,1,@dtAsOfDate)

SET @dtAsOfDate = DATEADD(day,-DATEPART(day, @dtAsOfDate),@dtAsOfDate)

RETURN @dtAsOfDate

END

The whole thing runs in about a second so I'm not sure what could readily be done.

thanks though!

quayludious SSC Enthusiast Points: 145 More actions · Answer 5

any thoughts on it or should I just leave well enough alone?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

quayludious (8/13/2009)
any thoughts on it or should I just leave well enough alone?

Agh... sorry... this got lost in about a zillion other emails. I'll try to get back to this soon.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 7

Have look at Gail's blog (http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/)

She has a number of very useful date/time functions. The one for finding the last day of the month is done in a single statement, which should make it easier to incorporate as inline SQL, rather than have to rely on a function, and suffer the performance hit that can go with scalar functions.

nigel. SSChampion Points: 11636 More actions · Answer 8

Your function can be rewritten using this single statement, which is an adpatation of Gail's from the link in the previous post:

select dateadd(dd,-1,dateadd(mm, datediff(mm,0, getdate())+1,0))

The result has a time component of 00:00:00.000 as in your function, whereas Gail's yeilds the last millisecond of the month.

Secondly

Regards

Nigel

Nigel
How to post questions

nigel. SSChampion Points: 11636 More actions · Answer 9

Try this to get a set of the month end dates you are interested in, it might help you on your way.

Note: You'll need to read Jeff Moden's Tally Table article[/url], and create one.

SELECT

dateadd(dd,-1,dateadd(mm,DATEDIFF(mm,0,getdate()) - (N-2),0))

FROM

tally

WHERE

N IN (1,2,3,4,7)

Regards

Nigel

Nigel
How to post questions

quayludious SSC Enthusiast Points: 145 More actions · Answer 10

yeah, thanks 🙂 I replaced all the function calls in the procedure with that statement. I've never read before that scalar functions were 'bad' but I guess ya learn something new every day.

the tally table option might work out for the multiple dates... definally cleaner/shorter looking. I'll give it a shot and see how it flys.

thanks!

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 11

Now how many times have I read Jeff's article and never culled that little tidbit out? :w00t: Thanks for the tip Nigel!

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."