Help with Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with Query

Author

Topic

eire1130
Starting Member

6 Posts

Posted - 2010-01-21 : 13:53:52

Hi,

I need some help with this query. I have set of data that looks like this:
Closing date
Closing date+1
Closing date+2
Closing date+3
deactivated date+4
deactivated date+5
Closing date+6
Closing date+7

Where the +X dates are just later dates.

What I need to do is show the minimum date and ignore everything before the deactivated date.

The below query will show the minimum date for the current stage (closing) but it sees the stages prior to deactivation as well, giving me an erroneously early date. This does ignore any stage that is not "closing" for example, but I want it to ignore any stage that has happened if a deactivation has happened (think of deactivated as like a tabula rasa, but we need to keep the history for other reasons)

Any idea how I can accomplish this?

Key:
Opportunity.Oppo_Status this will reflect the current status
OpportunityProgress.Oppo_Status this is contains all past status's and can have many of the same entry type (see table above).

(
SELECT Oppo_OpportunityId, Oppo_Status, MIN(Oppo_CreatedDate) Oppo_CreatedDate
From OpportunityProgress
GROUP BY Oppo_OpportunityId, Oppo_Status) OpportunityProgress
ON Opportunity.Oppo_OpportunityId = OpportunityProgress.Oppo_OpportunityId
AND Opportunity.Oppo_Status = OpportunityProgress.Oppo_Status

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-21 : 14:18:18

How do we know if a deactivation has happened? What field does it represent?
We need the folllowing information
Table Structure,Sample data and Expected Output.

eire1130
Starting Member

6 Posts

Posted - 2010-01-21 : 14:46:46

quote:
Originally posted by vijayisonly

How do we know if a deactivation has happened? What field does it represent?
We need the folllowing information
Table Structure,Sample data and Expected Output.

I'm not really sure how to answer table structure,

Sample data, see above

Expected output, in the above case, would be +6. I do not want to see +0

Right now it is pulling the +0

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-21 : 15:05:57

Please see this and re-state your question.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

eire1130
Starting Member

6 Posts

Posted - 2010-01-21 : 15:20:37

quote:
Originally posted by vijayisonly

Please see this and re-state your question.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

1.

What I need to do is show the minimum date and ignore everything before the deactivated date.

The query will show the minimum date for the current stage (closing) but it sees the stages prior to deactivation as well, giving me an erroneously early date. This does ignore any stage that is not "closing" for example, but I want it to ignore any stage that has happened if a deactivation has happened (think of deactivated as like a tabula rasa, but we need to keep the history for other reasons)

2.

3.

I have a set of data that looks like this:

Closing, date+0
Closing, date+1
Closing, date+2
Closing, date+3
deactivated, date+4
deactivated, date+5
Closing, date+6
Closing, date+7

Where the +X dates are just later dates.

4.

Key:
Opportunity.Oppo_Status this will reflect the current status
OpportunityProgress.Oppo_Status this is contains all past status's and can have many of the same entry type (see table above).

(
SELECT Oppo_OpportunityId, Oppo_Status, MIN(Oppo_CreatedDate) Oppo_CreatedDate
From OpportunityProgress
GROUP BY Oppo_OpportunityId, Oppo_Status) OpportunityProgress
ON Opportunity.Oppo_OpportunityId = OpportunityProgress.Oppo_OpportunityId
AND Opportunity.Oppo_Status = OpportunityProgress.Oppo_Status

This pulls date +0

5.

In the above case, Date +6 should be the expected value, in the above matrix.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-21 : 15:43:25

Replace you inner select with this and try...
Since you haven't provided table structure...check if the column names are correct.

SELECT a.Oppo_OpportunityId, a.Oppo_Status, MIN(a.Oppo_CreatedDate) as Oppo_CreatedDate
From OpportunityProgress a left join
(
SELECT Oppo_OpportunityId
, Oppo_Status
, MAX(Oppo_CreatedDate) AS Oppo_CreatedDate 
FROM OpportunityProgress 
where [status] = 'deactivated' 
GROUP BY Oppo_OpportunityId, Oppo_Status
) b
on a.Oppo_OpportunityId = b.Oppo_OpportunityId and a.Oppo_Status = b.Oppo_Status
where a.Oppo_CreatedDate > coalesce(b.Oppo_CreatedDate,0)
GROUP BY a.Oppo_OpportunityId, a.Oppo_Status

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-21 : 15:44:59

Sample data

declare @t table (Oppo_OpportunityId int,Oppo_Status int,[status] varchar(20),Oppo_CreatedDate datetime)
insert @t
select 1,1,'closing','2010-01-01'
union all select 1,1,'closing','2009-01-02'
union all select 1,1,'deactivated','2009-01-03'
union all select 1,1,'deactivated','2009-01-04'
union all select 1,1,'closing','2009-01-05'
union all select 1,1,'closing','2009-01-06'
union all select 2,1,'closing','2009-01-02'
union all select 2,1,'closing','2009-01-05'
union all select 2,1,'closing','2009-01-06'

Query

SELECT a.Oppo_OpportunityId, a.Oppo_Status, MIN(a.Oppo_CreatedDate) as Oppo_CreatedDate
From @t a left join
(
SELECT Oppo_OpportunityId
, Oppo_Status
, MAX(Oppo_CreatedDate) AS Oppo_CreatedDate 
FROM @t 
where [status] = 'deactivated' 
GROUP BY Oppo_OpportunityId, Oppo_Status
) b
on a.Oppo_OpportunityId = b.Oppo_OpportunityId and a.Oppo_Status = b.Oppo_Status
where a.Oppo_CreatedDate > coalesce(b.Oppo_CreatedDate,0)
GROUP BY a.Oppo_OpportunityId, a.Oppo_Status

Result

Oppo_OpportunityId Oppo_Status Oppo_CreatedDate
------------------ ----------- -----------------------
1                  1           2009-01-05 00:00:00.000
2                  1           2009-01-02 00:00:00.000

eire1130
Starting Member

6 Posts

Posted - 2010-01-22 : 14:42:48

quote:

Originally posted by vijayisonly

Sample data

declare @t table (Oppo_OpportunityId int,Oppo_Status int,[status] varchar(20),Oppo_CreatedDate datetime)
insert @t
select 1,1,'closing','2010-01-01'
union all select 1,1,'closing','2009-01-02'
union all select 1,1,'deactivated','2009-01-03'
union all select 1,1,'deactivated','2009-01-04'
union all select 1,1,'closing','2009-01-05'
union all select 1,1,'closing','2009-01-06'
union all select 2,1,'closing','2009-01-02'
union all select 2,1,'closing','2009-01-05'
union all select 2,1,'closing','2009-01-06'

Query

SELECT a.Oppo_OpportunityId, a.Oppo_Status, MIN(a.Oppo_CreatedDate) as Oppo_CreatedDate
From @t a left join
(
SELECT Oppo_OpportunityId
, Oppo_Status
, MAX(Oppo_CreatedDate) AS Oppo_CreatedDate 
FROM @t 
where [status] = 'deactivated' 
GROUP BY Oppo_OpportunityId, Oppo_Status
) b
on a.Oppo_OpportunityId = b.Oppo_OpportunityId and a.Oppo_Status = b.Oppo_Status
where a.Oppo_CreatedDate > coalesce(b.Oppo_CreatedDate,0)
GROUP BY a.Oppo_OpportunityId, a.Oppo_Status

Result

Oppo_OpportunityId Oppo_Status Oppo_CreatedDate
------------------ ----------- -----------------------
1                  1           2009-01-05 00:00:00.000
2                  1           2009-01-02 00:00:00.000

Thanks for the response,

I tried using your suggestion, and I am still getting the wrong date:

Here is the code:

LEFT JOIN
(
SELECT a.Oppo_OpportunityId, a.Oppo_Status, MIN(a.Oppo_CreatedDate) Oppo_CreatedDate
From OpportunityProgress a
Left Join
(
SELECT Oppo_OpportunityId, Oppo_Status, Oppo_Stage, MAX(Oppo_CreatedDate) Oppo_CreatedDate
FROM OpportunityProgress
Where Oppo_Stage = '%Deactivated%'
Group By Oppo_OpportunityId, Oppo_Status, Oppo_Stage
) b
ON a.Oppo_OpportunityId = b.Oppo_OpportunityId
Where a.Oppo_CreatedDate > COALESCE(b.Oppo_CreatedDate,0)
Group By a.Oppo_OpportunityId, a.Oppo_Status) c
ON Opportunity.Oppo_OpportunityId = c.Oppo_OpportunityId
AND Opportunity.Oppo_Status = c.Oppo_Status

In the inner group / select, I made a few slight changes, I deleted out the "and a.Oppo_Status = b.Oppo_Status"

as this would restrict to all non-deactivated cases

I added Oppo_Stage to the inner group, because it shows the word "deactivated" while status shows the following format "deactivated - Some Reason"

Using your table from above, it is pulling the 2009-01-02

When what I want is the 2009-01-05.

Any ideas on what I am doing wrong?

eire1130
Starting Member

6 Posts

Posted - 2010-01-22 : 15:49:10

I think I just fixed it!

All I did was switch the inner Left join to an Inner Join.

eire1130
Starting Member

6 Posts

Posted - 2010-01-22 : 16:00:19