Min Date with a twist 3

[navigator703]

Hey Folks-

I have a query here where I am looking for the first time a property has gone active. The twist is that I only want properties where they have gone active in last 18 months [July 2006]. I am have been looking for a while in other posts for another topic like mine, but nothing is quite what I am looking for.

SELECT P.PRPTY_ID, P.PRPTY_NM, P.DIST_CD, P.LOC_CD, P.PRPTY_TY_CD, MIN (ST1.ACTIVE_DATE) AS ACTIVE_DT

FROM T_PRPTY P INNER JOIN (SELECT S1.PRPTY_ID, S1.STAT_TY_CD AS STAT_TY_CD, MIN (S1.STAT_CHNG_DT) AS ACTIVE_DATE
	FROM T_STAT S1
	WHERE (DATEDIFF(mm,S1.STAT_CHNG_DT, GETDATE()) <= 18) AND S1.STAT_TY_CD = 'ACTIVE'
	GROUP BY S1.PRPTY_ID, S1.STAT_TY_CD, S1.STAT_CHNG_DT) AS ST1 ON P.PRPTY_ID = ST1.PRPTY_ID

GROUP BY P.PRPTY_ID, P.DIST_CD, P.PRPTY_NM, P.LOC_CD,  P.PRPTY_TY_CD

ORDER BY P.PRPTY_ID

[ul]
[li]sample data[/li][/ul]
OR00119 Snellvile Shop OR-Snellvile 1392 BR 10/23/2006
OR00365 Market Shop OR-Beltsville 1020 BR 08/23/2007


I am still coming up with older data than my criteria is filtering for. Here is some of the results:

[ul]
[li]sample bad data[/li][/ul]
OR00119 Snellvile Shop OR-Snellvile 1392 BR 10/23/2006
OR00365 Market Shop OR-Beltsville 1020 BR 08/23/2007
WA00119 Downtown Shop WA-Seattle 302 BR 09/20/2004


If you have any suggestions, let me know!

~ a journey of a thousand miles must begin with a single step ~

 

[gmmastros]

navigator703,

As you can see, Erik is picking up my slack again.

You'll notice that his last query, and my last query are very similar, with just one significant difference.

The difference is this...

Having MIN(S1.STAT_CHNG_DT) >= DateAdd(mm, -18, GetDate())
HAVING Min(S.Stat_Chng_Dt) >= DateAdd(mm, DateDiff(mm, 0, GetDate()) - 18, 0)

The first one will subtract 18 months from Today's date AND time. So you would end up with something like... 2006-07-25 16:51:56.200

Erik's version will return 2006-07-[!]01[/!] 00:00:00.000

Truth is... I had intended to mention this earlier, but forgot. Before you 'put this away' and forget about it, you should at least give it some thought and decide which one you really want to use.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

True, true, George, I just gave him the same expression he was using already.

DateDiff counts the number of boundaries crossed, so 1/1/2008 to 1/31/2008 is 0 months, though 1/31/2008 to 2/1/2008 is 1 month. Not quite the same thing as 1 month of duration, which is what you are doing (though of course it's often approximate).

And I would have left you to this thread but I missed your post of 25 Jan 08 12:44 (see my post one minute later) in a posting race condition!

 

[navigator703]

Now I am a bit torn. I feel that the time is import. I have a number of records that were updated multiple times within a minute. I will stick with Erik's response

HAVING Min(S.Stat_Chng_Dt) >= DateAdd(mm, DateDiff(mm, 0, GetDate()) - 18, 0)

For one thing the timestamp would limit whether a record has been updated on that day AND time. I don't want to limit my result set on that alone. I would rather pick up the entire day's worth of records. Hope that makes sense. Thanks Erik

~ every wise man started out by asking many questions~

 

[gmmastros]

>> I would rather pick up the entire day's worth of records.

Then you'll need option number 3.

[tt][blue]
DateAdd(Day, DateDiff(day, 0, DateAdd(mm, -18, GetDate())), 0)
[/blue][/tt]

To see the difference, you should run this:

Select DateAdd(Month, Datediff(month, 0, GetDate())-18, 0)
Select DateAdd(mm, -18, GetDate())
Select DateAdd(Day, DateDiff(day, 0, DateAdd(mm, -18, GetDate())), 0)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

I would turn the third expression inside-out and do it this way:

DateAdd(mm, -18, DateDiff(dd, 0, GetDate())

 

[navigator703]

Erik-

That did the trick! Thanks again!

~ every wise man started out by asking many questions~