Min Date with a twist 3

navigator703 · Jan 25, 2008

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.

Code:

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 ~

RiverGuy · Jan 25, 2008

It's hard to read your code with those column names, but it looks like you need a having clause, for example

GROUP BY S1.PRPTY_ID, S1.STAT_TY_CD, S1.STAT_CHNG_DT
HAVING MIN(S1.STAT_CHNG_DT) >= DATEADD(month, -18, GETDATE())

gmmastros · Jan 25, 2008

Try this...

Code:

SELECT P.PRPTY_ID, 
       P.PRPTY_NM, 
       P.DIST_CD, 
       P.LOC_CD, 
       P.PRPTY_TY_CD, 
       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
          ) 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

If this works for you, and you want me to explain it, just let me know.

-George

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

navigator703 · Jan 25, 2008

George-

I am still getting the sample bad data

~ every wise man started out by asking many questions~

navigator703 · Jan 25, 2008

A little more informationa about the data. There are many records that that have more than one S1.STAT_CHNG_DT before the 18 month cut off. I only want where the first time the property becomes 'active' and if that property falls after the 18 month cut off. Hope that makes sense

~ every wise man started out by asking many questions~

gmmastros · Jan 25, 2008

Try RiverGuy's suggestion.

-George

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

navigator703 · Jan 25, 2008

Same deal. [sad]

This has been the strangest query I have written in a while. Thanks for the suggestions. Do you have anymore?

~ every wise man started out by asking many questions~

gmmastros · Jan 25, 2008

Code:

SELECT P.PRPTY_ID,
       P.PRPTY_NM,
       P.DIST_CD,
       P.LOC_CD,
       P.PRPTY_TY_CD,
       ST1.ACTIVE_DATE AS ACTIVE_DT
FROM   T_PRPTY P
       INNER JOIN (
          SELECT   S1.PRPTY_ID
          FROM     T_STAT S1
          WHERE    S1.STAT_TY_CD = 'ACTIVE'
          GROUP BY S1.PRPTY_ID, S1.STAT_TY_CD
          Having   MIN(S1.STAT_CHNG_DT) >= DateAdd(mm, -18, GetDate())
          ) AS ST1
          ON P.PRPTY_ID = ST1.PRPTY_ID
ORDER BY P.PRPTY_ID

-George

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

gmmastros · Jan 25, 2008

Maybe I need a break. There's another problem with the last query I posted. Sorry.

Try this...

Code:

SELECT P.PRPTY_ID,
       P.PRPTY_NM,
       P.DIST_CD,
       P.LOC_CD,
       P.PRPTY_TY_CD,
       ST1.ACTIVE_DATE AS ACTIVE_DT
FROM   T_PRPTY P
       INNER JOIN (
          SELECT   S1.PRPTY_ID,
                   MIN (S1.STAT_CHNG_DT) AS ACTIVE_DATE
          FROM     T_STAT S1
          WHERE    S1.STAT_TY_CD = 'ACTIVE'
          GROUP BY S1.PRPTY_ID
          Having   MIN(S1.STAT_CHNG_DT) >= DateAdd(mm, -18, GetDate())
          ) AS ST1
          ON P.PRPTY_ID = ST1.PRPTY_ID
ORDER BY P.PRPTY_ID

-George

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

ESquared · Jan 25, 2008

A minor detail, you don't need to include or group by the STAT_TY_CD column in the derived table (since you have the WHERE condition). Taking extra stuff out may not help the query but it may help you, making it easier to see.

Second, you don't need to do MIN(ST1.ACTIVE_DATE) in the main query as you already only have one row per PRPTY_ID. Which means you don't have to do any grouping, either.

If the following query doesn't work and still gives you "bad data" then you'd best give us some sample data. Find one row in the result that you consider bad data, then query the status table and give us all the matching status sample rows (suitably disguised if necessary to protect your business information).

Code:

SELECT
   P.Prpty_Id,
   P.Prpty_Nm,
   P.Dist_Cd,
   P.Loc_Cd,
   P.Prpty_Ty_Cd,
   D.Active_Date
FROM
   T_Prpty P
   INNER JOIN (
      SELECT
         S.Prpty_Id,
         Active_Date = Min(S.Stat_Chng_Dt)
      FROM T_Stat S
      WHERE
         S.Stat_Ty_Cd = 'Active'
         AND S.Stat_Chng_Dt >= DateAdd(mm, DateDiff(mm, 0, GetDate()) - 18, 0)
         -- this will perform a single calculation and then just compare rows
         -- instead of having to do a calculation on each Stat_Chng_Dt
         -- It is also sargable if you have an index on Stat_Chng_Dt.
      GROUP BY
         S.Prpty_Id
   ) D ON P.Prpty_Id = D.Prpty_Id
ORDER BY
   P.Prpty_Id

If you didn't need to know the first date it went active in the last 18 months, but just that the property had at least one active record in the last 18 months, you could do another query which might have a more advantageous execution plan:

Code:

SELECT
   P.Prpty_Id,
   P.Prpty_Nm,
   P.Dist_Cd,
   P.Loc_Cd,
   P.Prpty_Ty_Cd
FROM
   T_Prpty P
WHERE
   EXISTS (
      SELECT *
      FROM T_Stat S
      WHERE
         S.Stat_Ty_Cd = 'Active'
         AND S.Stat_Chng_Dt >= DateAdd(mm, DateDiff(mm, 0, GetDate()) - 18, 0)
         AND S.Prpty_Id = P.Prpty_ID
   )
ORDER BY
   P.Prpty_Id

Erik

P.S. Sorry about reformatting your query but I needed to "see" it so I formatted it the way that helps me do that.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

navigator703 · Jan 25, 2008

George-

I am looking through your results set. Can you explain what you did? Did I not need the STAT_TY_CD from the derived table like Erik is talking about?

~ every wise man started out by asking many questions~

gmmastros · Jan 25, 2008

>> Did I not need the STAT_TY_CD from the derived table like Erik is talking about?

No. You don't need to have that in the group by. If you'll notice the last query I posted, I removed it too.

Think about it this way.... your where clause is filtering the records based on status = 'Active', right? Since the ONLY possible value in the recordset has status = 'Active', there's no reason to group on that column.

In fact... this leads directly to the problem you originally had. Let me explain.

Suppose you had a record where the date = Jan 1, 2004 and status = 'Active'. You would NOT want this record in your results, right, because the status goes active before July 2007, eventhough it may go active again after July 2007. Originally, you were filtering the derived table (the inner query) so that it was ONLY looking at dates after July 2007. If you had records where there was an active date BOTH before and after July 2007, those records were getting included incorrectly.

Let me show you with some data.

First. Take a look at this data.

[tt][blue]

Id Status StatusDate
----------- -------------------- -----------------------
1 Active 2004-01-01 00:00:00.000
1 Active 2005-01-01 00:00:00.000
1 Active 2006-01-01 00:00:00.000
1 Active 2007-01-01 00:00:00.000
1 Active 2008-01-01 00:00:00.000
2 Active 2007-10-01 00:00:00.000
2 Active 2008-01-01 00:00:00.000
3 Active 2004-01-01 00:00:00.000
4 Inactive 2008-01-01 00:00:00.000
[/blue][/tt]

ID 1 should not be returned because it was active before July 2007

ID 2 should be returned

ID 3 should not

ID 4 should not (Never went active)

Now, run this query. Notice that I am creating some dummy data to test on. And then writing the query to use the dummy data.

Code:

Declare @Temp Table(Id Int, Status varchar(20), StatusDate DateTime)

Insert Into @Temp Values(1, 'Active', '20040101')
Insert Into @Temp Values(1, 'Active', '20050101')
Insert Into @Temp Values(1, 'Active', '20060101')
Insert Into @Temp Values(1, 'Active', '20070101')
Insert Into @Temp Values(1, 'Active', '20080101')
Insert Into @Temp Values(2, 'Active', '20070101')
Insert Into @Temp Values(2, 'Active', '20080101')
Insert Into @Temp Values(3, 'Active', '20040101')
Insert Into @Temp Values(4, 'Inactive', '20080101')

Select Id, Min(StatusDate) 
From   @Temp
Where  Status = 'Active'
       And StatusDate > '20070601'
Group By Id

When you run this query, you will get ID 1 and 2. This happens because ID 1 has an active date that is greater than July 2007.

Now, notice what happens when we remove the WHERE on the data, but put it in the having (like my above query).

Code:

Declare @Temp Table(Id Int, Status varchar(20), StatusDate DateTime)

Insert Into @Temp Values(1, 'Active', '20040101')
Insert Into @Temp Values(1, 'Active', '20050101')
Insert Into @Temp Values(1, 'Active', '20060101')
Insert Into @Temp Values(1, 'Active', '20070101')
Insert Into @Temp Values(1, 'Active', '20080101')
Insert Into @Temp Values(2, 'Active', '20071001')
Insert Into @Temp Values(2, 'Active', '20080101')
Insert Into @Temp Values(3, 'Active', '20040101')
Insert Into @Temp Values(4, 'Inactive', '20080101')

Select   Id, Min(StatusDate) 
From     @Temp
Where    Status = 'Active'
Group By Id
Having   Min(StatusDate) > '20070601'

This time, only ID = 2 is returned (which is what you want).

Erik already explained about the group by on the outer query. It's not needed because the inner query can only return 1 row per ID, so you don't need to use Min(StatusDate) in the outer query, and you don't need to group anything either.

Does this make sense? If it doesn't, let me know the parts that are bothering you, and I will explain more.

-George

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

navigator703 · Jan 25, 2008

That makes alot of sense. WOW! I appreciate all the help guys. The having clause makes all the difference.

~ every wise man started out by asking many questions~

gmmastros · Jan 25, 2008

I should point out that this is essentially what RiverGuy had, except he didn't mention about removing the date condition from the where clause.

-George

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

kaht · Jan 25, 2008

I should point out that this is essentially what RiverGuy had, except he didn't mention about removing the date condition from the where clause.

What george is trying to say is that you should give him a star too [smile]

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small] <.[/small]

gmmastros · Jan 25, 2008

Share the love. [smile]

-George

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

navigator703 · Jan 25, 2008

Not a problem! Thanks again. [thumbsup2]

~ every wise man started out by asking many questions~

kaht · Jan 25, 2008

Share the love.

What george is trying to say here is to give me a star too.

Just kidding [smile]

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small] <.[/small]

LNBruno · Jan 25, 2008

kaht - SW? LOL!

< M!ke >
[small]Don't believe everything you think.[/small]

ESquared · Jan 25, 2008

ohhh I didn't read your post correctly.

>> I have a query here where I am looking for the first time a property has gone active

So am I to take this to mean, as George alluded to, that if a property went active before 18 months ago it should be excluded, even if another "active" record is found within the last 18 months? For that query, do what your first correspondent RiverGuy said (he obviously read more carefully than I did):

Code:

SELECT
   P.Prpty_Id,
   P.Prpty_Nm,
   P.Dist_Cd,
   P.Loc_Cd,
   P.Prpty_Ty_Cd,
   D.Active_Date
FROM
   T_Prpty P
   INNER JOIN (
      SELECT
         S.Prpty_Id,
         Active_Date = Min(S.Stat_Chng_Dt)
      FROM T_Stat S
      WHERE
         S.Stat_Ty_Cd = 'Active'
      GROUP BY
         S.Prpty_Id
      HAVING Min(S.Stat_Chng_Dt) >= DateAdd(mm, DateDiff(mm, 0, GetDate()) - 18, 0)
   ) D ON P.Prpty_Id = D.Prpty_Id
ORDER BY
   P.Prpty_Id

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Min Date with a twist 3

MIS

Programmer

Programmer

MIS

MIS

Programmer

MIS

Programmer

Programmer

Programmer

MIS

Programmer

MIS

Programmer

Programmer

Programmer

MIS

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor