Query Same Table a few times - 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 2005 Forums

Transact-SQL (2005)

Query Same Table a few times

Author

Topic

ksql32
Starting Member

15 Posts

Posted - 2008-06-25 : 08:41:07

I have two tables, activity and company, that hold activity information that is filled in by users. Periodically, we send out literature and code this as LEAD in the spot activity.action_code. I'm trying to find a way to pull all of the activities since the last lead code (there is also a field called activity.create_date). I do not want to see the LEAD activity in these result sets! I can do everything except pull by the last create date per record with this query:

select *
from company, activity
where activity.activity_id=company.activity_id
and
company.company_id in(select a.company_id from company a, activity b
where b.activity_id=a.activity_id
and action_code='LEAD') and action_code<>'LEAD'

But this pulls all of the activities on these company records, and I only want the one since the max(create_date) on each. Any help getting that last part done is appreciated.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 08:45:36

Does that mean that you should retrive only records with company_id which have a record with action_code=LEAD existing?

ksql32
Starting Member

15 Posts

Posted - 2008-06-25 : 09:19:07

No, the exact opposite. I should only retrieve company_id where the action code is NOT lead but is after the most recent lead. Does that make sense?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 09:25:19

Try this

SELECT	Company_ID,
	Create_Date
FROM	(
		SELECT	Company_ID,
			Create_Date,
			MAX(CASE WHEN Action_Code = 'LEAD' THEN Create_Date ELSE NULL END) OVER (PARTITION BY Company_ID) AS maxDate
		FROM	Activity
	) AS d
WHERE	Create_Date > maxDate

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 09:28:44

[code]select * from
(select row_number() over (partition by a.activity_id order by a.create_date desc) as rowno,
*
from company c
inner join activity a
on a.activity_id=c.activity_id
where action_code<>'LEAD')t
where t.rowno=1
and t.company_id in (select a.company_id from company a
inner join activity b
on b.activity_id=a.activity_id
where action_code ='LEAD')
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 09:30:04

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 09:35:35

[code]DECLARE @Sample TABLE (CompanyID INT, Activity VARCHAR(6), dt DATETIME)

INSERT @Sample
SELECT 1, 'Other', '20080101' UNION ALL
SELECT 1, 'LEAD', '20071231' UNION ALL
SELECT 2, 'LEAD', '20080601' UNION ALL
SELECT 3, 'Busy', '20080711' UNION ALL
SELECT 3, 'LEAD', '20080710'

-- Peso 1
SELECT CompanyID,
dt,
Activity
FROM (
SELECT CompanyID,
dt,
Activity,
MAX(CASE WHEN Activity = 'LEAD' THEN dt ELSE NULL END) OVER (PARTITION BY CompanyID) AS maxDate
FROM @Sample
) AS d
WHERE dt > maxDate

-- Visakh
select * from
(select row_number() over (partition by Activity order by dt desc) as rowno,
*
from @sample as a
where Activity<>'LEAD') t
where t.rowno=1
and t.CompanyID in (select b.CompanyID from @sample as b where b.CompanyID=t.CompanyID
and Activity ='LEAD')

-- Peso 2
SELECT t.*
FROM @Sample AS t
INNER JOIN (
SELECT CompanyID,
MAX(dt) AS maxDate
FROM @Sample
WHERE Activity = 'LEAD'
GROUP BY CompanyID
) AS x ON x.CompanyID = t.CompanyID
AND x.maxDate < t.dt[/code]

E 12°55'05.25"
N 56°04'39.16"

ksql32
Starting Member

15 Posts

Posted - 2008-06-25 : 09:56:48

Hey guys, thanks for the help. Here is what I'm getting when running everything:
visakh16:
Msg 8156, Level 16, State 1, Line 4
The column 'activity_id' was specified multiple times for 't'.

Peso:
The first one looks like it isn't joining in the company table because I'm getting bound errors.

The second one using a temp table gives me only two results:
1 2008-01-01 00:00:00.000 Other
3 2008-07-11 00:00:00.000 Busy

Thanks for helping too... I'm getting some good ideas on what to try next on my end as well!

ksql32
Starting Member

15 Posts

Posted - 2008-06-25 : 10:10:28

Ok guys, based on your help I've got this which I believe is working, so thank you very much!
SELECT *
FROM (
SELECT activity.activity_id,
activity.create_date,
action_code, company.company_id,
MAX(CASE WHEN Action_code = 'LEAD' THEN activity.create_date ELSE NULL END)
OVER (PARTITION BY (
company_id)) AS maxDate
FROM activity, company
where activity.activity_id=company.activity_id
) as d
WHERE create_date > maxDate

order by company_id

(I just added the joins on for the company/activity and everything seems to work)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 10:14:33

quote:
Originally posted by ksql32

Peso:
The second one using a temp table gives me only two results:
1 2008-01-01 00:00:00.000 Other
3 2008-07-11 00:00:00.000 Busy

Of course. You wrote you wanted all records dated after last LEAD occurence.

E 12°55'05.25"
N 56°04'39.16"

ksql32
Starting Member

15 Posts

Posted - 2008-06-25 : 10:30:53

Sorry, I meant on a per company basis. So Company ABC might have two activities since their last LEAD action_code, Company XYZ might have eight activities since their last LEAD action code. I want all ten of those activities.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 10:34:58

Yes, I know. My sample code does all that for you.
Your problem is that you didn't read the code and noticed to add more sample data for easier illustration.

DECLARE	@Sample TABLE (CompanyID INT, Activity VARCHAR(6), dt DATETIME)

INSERT	@Sample
SELECT	1, 'Other', '20080101' UNION ALL
SELECT	1, 'LEAD', '20071231' UNION ALL
SELECT	1, 'Twit', '20080102' UNION ALL
SELECT	1, 'Yak', '20080202' UNION ALL
SELECT	2, 'LEAD', '20080601' UNION ALL
SELECT	3, 'Busy', '20080711' UNION ALL
SELECT	3, 'LEAD', '20080710'

-- Peso 1
SELECT	CompanyID,
	dt,
	Activity
FROM	(
		SELECT	CompanyID,
			dt,
			Activity,
			MAX(CASE WHEN Activity = 'LEAD' THEN dt ELSE NULL END) OVER (PARTITION BY CompanyID) AS maxDate
		FROM	@Sample
	) AS d
WHERE	dt > maxDate

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources